Edit a SQL Server 2005 XML column in ASP.NET

Hi. I've been trying to figure this out for a couple ws now and have =
posted various pleas for help, but so far I've not been able to find the =
magic I need to make this happen. This can't be too uncommon of a =
scenario. Certainly someone has done this before.
What I'm trying to do is the following:
1. Pull XML from an MS SQL Server 2005 XML column.
2. Load it into a DataSet (unless there's a better way).
3. Bind it to some ASP.NET control(s) for editing.
4. Allow user to editing of the data in the web page.
5. Get the updated data from the control(s) as XML
6. Post it back to the SQL XML column.
I can do 1-4 above... but for the life of me can't get step 5 to work.
Does someone have a simple bare-bones example of the above 6 steps =
actually working that they can post for me to examine/review?
--=20
Greg Collins [Microsoft MVP]
Visit Braintrove ( http://www.braintrove.com )On 5 23 , 7 40 , "Greg Collins [Microsoft MVP]"
<gcollins_AT_msn_DOT_com> wrote:
> Hi. I've been trying to figure this out for a couple ws now and have posted var
ious pleas for help, but so far I've not been able to find the magic I need to make
this happen. This can't be too uncommon of a scenario. Certainly someone has done th
is
before.
> What I'm trying to do is the following:
> 1. Pull XML from an MS SQL Server 2005 XML column.
> 2. Load it into a DataSet (unless there's a better way).
> 3. Bind it to some ASP.NET control(s) for editing.
> 4. Allow user to editing of the data in the web page.
> 5. Get the updated data from the control(s) as XML
> 6. Post it back to the SQL XML column.
> I can do 1-4 above... but for the life of me can't get step 5 to work.
> Does someone have a simple bare-bones example of the above 6 steps actuall
y working that they can post for me to examine/review?
> --
> Greg Collins [Microsoft MVP]
> Visit Braintrove (http://www.braintrove.com)
4. Allow user to editing of the data in the web page.
Here i think we can use javascript DOM to modify xml document
according to user's edit.
5. Get the updated data from the control(s) as XML
And now we can get the xml document of step 4.
6. Post it back to the SQL XML column.
:) here have nothing to consider.
Hope this can help.|||On 5 23 , 7 40 , "Greg Collins [Microsoft MVP]"
<gcollins_AT_msn_DOT_com> wrote:
> Hi. I've been trying to figure this out for a couple ws now and have posted var
ious pleas for help, but so far I've not been able to find the magic I need to make
this happen. This can't be too uncommon of a scenario. Certainly someone has done th
is
before.
> What I'm trying to do is the following:
> 1. Pull XML from an MS SQL Server 2005 XML column.
> 2. Load it into a DataSet (unless there's a better way).
> 3. Bind it to some ASP.NET control(s) for editing.
> 4. Allow user to editing of the data in the web page.
> 5. Get the updated data from the control(s) as XML
> 6. Post it back to the SQL XML column.
> I can do 1-4 above... but for the life of me can't get step 5 to work.
> Does someone have a simple bare-bones example of the above 6 steps actuall
y working that they can post for me to examine/review?
> --
> Greg Collins [Microsoft MVP]
> Visit Braintrove (http://www.braintrove.com)
4. Allow user to editing of the data in the web page.
Here i think we can use javascript DOM to modify xml document
according to user's edit.
5. Get the updated data from the control(s) as XML
And now we can get the xml document of step 4.
6. Post it back to the SQL XML column.
:) here have nothing to consider.
Hope this can help.|||On 5 23 , 7 40 , "Greg Collins [Microsoft MVP]"
<gcollins_AT_msn_DOT_com> wrote:
> Hi. I've been trying to figure this out for a couple ws now and have posted var
ious pleas for help, but so far I've not been able to find the magic I need to make
this happen. This can't be too uncommon of a scenario. Certainly someone has done th
is
before.
> What I'm trying to do is the following:
> 1. Pull XML from an MS SQL Server 2005 XML column.
> 2. Load it into a DataSet (unless there's a better way).
> 3. Bind it to some ASP.NET control(s) for editing.
> 4. Allow user to editing of the data in the web page.
> 5. Get the updated data from the control(s) as XML
> 6. Post it back to the SQL XML column.
> I can do 1-4 above... but for the life of me can't get step 5 to work.
> Does someone have a simple bare-bones example of the above 6 steps actuall
y working that they can post for me to examine/review?
> --
> Greg Collins [Microsoft MVP]
> Visit Braintrove (http://www.braintrove.com)
4. Allow user to editing of the data in the web page.
Here i think we can use javascript DOM to modify xml document
according to user's edit.
5. Get the updated data from the control(s) as XML
And now we can get the xml document of step 4.
6. Post it back to the SQL XML column.
:) here have nothing to consider.
Hope this can help.|||Greg Collins [Microsoft MVP] wrote:
> Hi. I've been trying to figure this out for a couple ws now and have posted var
ious pleas for help, but so far I've not been able to find the magic I need to make
this happen. This can't be too uncommon of a scenario. Certainly someone has done th
is
before.
> What I'm trying to do is the following:
> 1. Pull XML from an MS SQL Server 2005 XML column.
> 2. Load it into a DataSet (unless there's a better way).
> 3. Bind it to some ASP.NET control(s) for editing.
> 4. Allow user to editing of the data in the web page.
> 5. Get the updated data from the control(s) as XML
> 6. Post it back to the SQL XML column.
> I can do 1-4 above... but for the life of me can't get step 5 to work.
> Does someone have a simple bare-bones example of the above 6 steps actuall
y working that they can post for me to examine/review?
>
Hi, Greg
check out this:
http://msdn2.microsoft.com/en-us/library/58a18dwa.aspx
What's exactly your case - you need to serialize the whole dataset,
or a given column ?
Regards,
Petar Atanasov
http://a-wake.net|||Use a table for the values on the form instead of XML? :-))
TheSQLGuru
President
Indicium Resources, Inc.
"Greg Collins [Microsoft MVP]" <gcollins_AT_msn_DOT_com> wrote in message
news:ussV38SnHHA.4316@.TK2MSFTNGP06.phx.gbl...
Hi. I've been trying to figure this out for a couple ws now and have
posted various pleas for help, but so far I've not been able to find the
magic I need to make this happen. This can't be too uncommon of a scenario.
Certainly someone has done this before.
What I'm trying to do is the following:
1. Pull XML from an MS SQL Server 2005 XML column.
2. Load it into a DataSet (unless there's a better way).
3. Bind it to some ASP.NET control(s) for editing.
4. Allow user to editing of the data in the web page.
5. Get the updated data from the control(s) as XML
6. Post it back to the SQL XML column.
I can do 1-4 above... but for the life of me can't get step 5 to work.
Does someone have a simple bare-bones example of the above 6 steps actually
working that they can post for me to examine/review?
Greg Collins [Microsoft MVP]
Visit Braintrove ( http://www.braintrove.com )|||What about using the WriteXml method of the DataSet after the changes are
made to it? Does that do anything useful?
"Greg Collins [Microsoft MVP]" <gcollins_AT_msn_DOT_com> wrote in message
news:ussV38SnHHA.4316@.TK2MSFTNGP06.phx.gbl...
Hi. I've been trying to figure this out for a couple ws now and have
posted various pleas for help, but so far I've not been able to find the
magic I need to make this happen. This can't be too uncommon of a scenario.
Certainly someone has done this before.
What I'm trying to do is the following:
1. Pull XML from an MS SQL Server 2005 XML column.
2. Load it into a DataSet (unless there's a better way).
3. Bind it to some ASP.NET control(s) for editing.
4. Allow user to editing of the data in the web page.
5. Get the updated data from the control(s) as XML
6. Post it back to the SQL XML column.
I can do 1-4 above... but for the life of me can't get step 5 to work.
Does someone have a simple bare-bones example of the above 6 steps actually
working that they can post for me to examine/review?
Greg Collins [Microsoft MVP]
Visit Braintrove ( http://www.braintrove.com )

Edit a SQL Server 2005 XML column in ASP.NET

Hi. I've been trying to figure this out for a couple weeks now and have posted various pleas for help, but so far I've not been able to find the magic I need to make this happen. This can't be too uncommon of a scenario. Certainly someone has done this before.
What I'm trying to do is the following:
1. Pull XML from an MS SQL Server 2005 XML column.
2. Load it into a DataSet (unless there's a better way).
3. Bind it to some ASP.NET control(s) for editing.
4. Allow user to editing of the data in the web page.
5. Get the updated data from the control(s) as XML
6. Post it back to the SQL XML column.
I can do 1-4 above... but for the life of me can't get step 5 to work.
Does someone have a simple bare-bones example of the above 6 steps actually working that they can post for me to examine/review?
Greg Collins [Microsoft MVP]
Visit Braintrove ( http://www.braintrove.com )
Greg Collins [Microsoft MVP] wrote:
> Hi. I've been trying to figure this out for a couple weeks now and have posted various pleas for help, but so far I've not been able to find the magic I need to make this happen. This can't be too uncommon of a scenario. Certainly someone has done this before.
> What I'm trying to do is the following:
> 1. Pull XML from an MS SQL Server 2005 XML column.
> 2. Load it into a DataSet (unless there's a better way).
> 3. Bind it to some ASP.NET control(s) for editing.
> 4. Allow user to editing of the data in the web page.
> 5. Get the updated data from the control(s) as XML
> 6. Post it back to the SQL XML column.
> I can do 1-4 above... but for the life of me can't get step 5 to work.
> Does someone have a simple bare-bones example of the above 6 steps actually working that they can post for me to examine/review?
>
Hi, Greg
check out this:
http://msdn2.microsoft.com/en-us/library/58a18dwa.aspx
What's exactly your case - you need to serialize the whole dataset,
or a given column ?
Regards,
Petar Atanasov
http://a-wake.net
|||Use a table for the values on the form instead of XML? :-))
TheSQLGuru
President
Indicium Resources, Inc.
"Greg Collins [Microsoft MVP]" <gcollins_AT_msn_DOT_com> wrote in message
news:ussV38SnHHA.4316@.TK2MSFTNGP06.phx.gbl...
Hi. I've been trying to figure this out for a couple weeks now and have
posted various pleas for help, but so far I've not been able to find the
magic I need to make this happen. This can't be too uncommon of a scenario.
Certainly someone has done this before.
What I'm trying to do is the following:
1. Pull XML from an MS SQL Server 2005 XML column.
2. Load it into a DataSet (unless there's a better way).
3. Bind it to some ASP.NET control(s) for editing.
4. Allow user to editing of the data in the web page.
5. Get the updated data from the control(s) as XML
6. Post it back to the SQL XML column.
I can do 1-4 above... but for the life of me can't get step 5 to work.
Does someone have a simple bare-bones example of the above 6 steps actually
working that they can post for me to examine/review?
Greg Collins [Microsoft MVP]
Visit Braintrove ( http://www.braintrove.com )
|||What about using the WriteXml method of the DataSet after the changes are
made to it? Does that do anything useful?
"Greg Collins [Microsoft MVP]" <gcollins_AT_msn_DOT_com> wrote in message
news:ussV38SnHHA.4316@.TK2MSFTNGP06.phx.gbl...
Hi. I've been trying to figure this out for a couple weeks now and have
posted various pleas for help, but so far I've not been able to find the
magic I need to make this happen. This can't be too uncommon of a scenario.
Certainly someone has done this before.
What I'm trying to do is the following:
1. Pull XML from an MS SQL Server 2005 XML column.
2. Load it into a DataSet (unless there's a better way).
3. Bind it to some ASP.NET control(s) for editing.
4. Allow user to editing of the data in the web page.
5. Get the updated data from the control(s) as XML
6. Post it back to the SQL XML column.
I can do 1-4 above... but for the life of me can't get step 5 to work.
Does someone have a simple bare-bones example of the above 6 steps actually
working that they can post for me to examine/review?
Greg Collins [Microsoft MVP]
Visit Braintrove ( http://www.braintrove.com )

Easier way to change collation on columns.

Hi everybody
Running a SQL Server 2000 std edition + SP3.
Restored a couple of databases from a SQL Server 7 to 2000.
I need to change the collation of these databases.
So I used alter database to change the default collation.
Now I need to know if there is a way to change all the
columns in the existing tables to the new default
collation other than having to change them by hand.
Is there a script or some wizard that can help me or am I
stuck with this monster assignment to change every string
datatype to the new default collation?
Thanks in advance
RisunRisun,
You have to script it and do it manually. There are some scripts on the
internet, I believe that will do this for you. One easy way is to create
a new database with the new collation and then copy the data over using
a script or dts.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Risun wrote:
> Hi everybody
> Running a SQL Server 2000 std edition + SP3.
> Restored a couple of databases from a SQL Server 7 to 2000.
> I need to change the collation of these databases.
> So I used alter database to change the default collation.
> Now I need to know if there is a way to change all the
> columns in the existing tables to the new default
> collation other than having to change them by hand.
> Is there a script or some wizard that can help me or am I
> stuck with this monster assignment to change every string
> datatype to the new default collation?
> Thanks in advance
> Risun

Easier way to change collation on columns.

Hi everybody
Running a SQL Server 2000 std edition + SP3.
Restored a couple of databases from a SQL Server 7 to 2000.
I need to change the collation of these databases.
So I used alter database to change the default collation.
Now I need to know if there is a way to change all the
columns in the existing tables to the new default
collation other than having to change them by hand.
Is there a script or some wizard that can help me or am I
stuck with this monster assignment to change every string
datatype to the new default collation?
Thanks in advance
Risun
Risun,
You have to script it and do it manually. There are some scripts on the
internet, I believe that will do this for you. One easy way is to create
a new database with the new collation and then copy the data over using
a script or dts.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Risun wrote:

> Hi everybody
> Running a SQL Server 2000 std edition + SP3.
> Restored a couple of databases from a SQL Server 7 to 2000.
> I need to change the collation of these databases.
> So I used alter database to change the default collation.
> Now I need to know if there is a way to change all the
> columns in the existing tables to the new default
> collation other than having to change them by hand.
> Is there a script or some wizard that can help me or am I
> stuck with this monster assignment to change every string
> datatype to the new default collation?
> Thanks in advance
> Risun
sql

Easier way to change collation on columns.

Hi everybody
Running a SQL Server 2000 std edition + SP3.
Restored a couple of databases from a SQL Server 7 to 2000.
I need to change the collation of these databases.
So I used alter database to change the default collation.
Now I need to know if there is a way to change all the
columns in the existing tables to the new default
collation other than having to change them by hand.
Is there a script or some wizard that can help me or am I
stuck with this monster assignment to change every string
datatype to the new default collation?
Thanks in advance
RisunRisun,
You have to script it and do it manually. There are some scripts on the
internet, I believe that will do this for you. One easy way is to create
a new database with the new collation and then copy the data over using
a script or dts.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Risun wrote:

> Hi everybody
> Running a SQL Server 2000 std edition + SP3.
> Restored a couple of databases from a SQL Server 7 to 2000.
> I need to change the collation of these databases.
> So I used alter database to change the default collation.
> Now I need to know if there is a way to change all the
> columns in the existing tables to the new default
> collation other than having to change them by hand.
> Is there a script or some wizard that can help me or am I
> stuck with this monster assignment to change every string
> datatype to the new default collation?
> Thanks in advance
> Risun

Dynamically creating SQL Server Report

I've been working with Crystal Reports for a couple of months and I'm now interested in creating a SQL Server Report but need assistance. With CR I'm able to point a report to a dataset within the application and then modify the dataset by passing parameters from the web page that hosts the crystalreportviewer. The CR then reflects the updated dataset. I'm looking to create the same functionality with SQL Server Reports but I'm unfamiliar. Below is the code I use for one of my reports called 'Holds'. Any assistance is appreciated!

Holds.aspx

<form id="form1" runat="server">
<div class="rptParameters">
<table cellpadding="0" cellspacing="0" border="0">
<tr align="left" valign="middle">
<td>
Season:
</td>
<td>
<asp:DropDownList ID="ddlSeason" runat="server" AutoPostBack="true" OnSelectedIndexChanged="ddlSeason_SelectedIndexChanged">
<asp:ListItem Value="" Text=""></asp:ListItem>
<asp:ListItem Value="MC0607" Text="MC0607"></asp:ListItem>
<asp:ListItem Value="MC0708" Text="MC0708"></asp:ListItem>
</asp:DropDownList>
</td>
</tr>
</table>
</div>
<asp:Panel ID="rptPanel" runat="server" CssClass="rptViewer" Visible="false">
<CR:CrystalReportViewer ID="CrystalReportViewer1" runat="server" AutoDataBind="True"
HasCrystalLogo="False" EnableDrillDown="False" />
</asp:Panel>
</form>

Holds.aspx.cs

using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections;
using System.Text;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using CrystalDecisions.Shared;
using CrystalDecisions.CrystalReports.Engine;
using CrystalDecisions.Web;
public partialclass Holds : System.Web.UI.Page
{
private ReportDocument myReport;
// Handles initial page loadprotected void Page_Load(object sender, EventArgs e)
 {
if (!IsPostBack)
 {
 Session["Season"] =null;
 }
if (Session["Season"] !=null)
 {
 ConfigureCrystalReports();
 }
 }
// Handles configuring crystal reportsprivate void ConfigureCrystalReports()
 {
 myReport =new ReportDocument();
string reportPath = Server.MapPath("reports/Holds.rpt");
 myReport.Load(reportPath);
 DataSet dataSet = DataSetConfiguration.Holds;
 myReport.SetDataSource(dataSet);
 CrystalReportViewer1.ReportSource = myReport;
 }
// Handles season dropdownlist selectedIndexChangedprotected void ddlSeason_SelectedIndexChanged(object sender, EventArgs e)
 {
if (ddlSeason.SelectedIndex == 0)
 {
 Session["Season"] =null;
 }
else
 {
 Session["Season"] = ddlSeason.SelectedValue;
 ConfigureCrystalReports();
 }
 }
}

DataSetConfiguration.cs

using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
/// <summary>
/// Summary description for DataSetConfiguration
/// </summary>public class DataSetConfiguration
{
public DataSetConfiguration()
 {
//
 // TODO: Add constructor logic here
 // }// Handles creating holds datasetpublic static DataSet Holds
 {
get {// get parametersstring SEASON = System.Web.HttpContext.Current.Session["Season"].ToString();
string TK_EVENT_STATUS ="TK_EVENT_" + SEASON +"_STATUS";
string TK_EVENT ="TK_EVENT_" + SEASON;
 DataSet dataSet =new DataSet();
string query ="SELECT TOP 100 PERCENT A.[STATUS], '" + SEASON +"' AS [SEASON], C.NAME AS [DESC]," +
" A.[Z_ID] AS [EVENT], A.[NSTATUS], B.[NAME], B.[DATE], B.[TIME]" +
" FROM " + TK_EVENT_STATUS +" A LEFT OUTER JOIN" +
" " + TK_EVENT +" B ON A.Z_ID = B.Z_ID LEFT OUTER JOIN" +
" TK_SEAT_STATUS C ON A.STATUS = C.STATUS COLLATE SQL_Latin1_General_CP1_CS_AS" +
" WHERE (A.Z_ID NOT LIKE '%P') AND (A.Z_ID NOT LIKE 'W%')" +
" AND (A.STATUS <> 'O') ORDER BY B.[DATE], B.[TIME], B.[NAME]";
 dataSet.ReadXmlSchema(@."D:\Web Sites\Holds\App_Code\HoldsDataSet.xsd"); SqlConnection conn =new SqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnectionString"].ConnectionString);
 SqlDataAdapter cmd =new SqlDataAdapter(query, conn);
 cmd.Fill(dataSet,"Holds");
return dataSet;
 }
 }
}

Thanks!

I created a solution for my question. Now I have a SQL Server Report(.rdlc) displaying the data.

Holds.aspx

<form id="form1" runat="server">
<div class="rptParameters">
<table cellpadding="0" cellspacing="0" border="0">
<tr align="left" valign="middle">
<td>
Season:
</td>
<td>
<asp:DropDownList ID="ddlSeason" runat="server" AutoPostBack="true" OnSelectedIndexChanged="ddlSeason_SelectedIndexChanged">
<asp:ListItem Value="" Text=""></asp:ListItem>
<asp:ListItem Value="MC0607" Text="MC0607"></asp:ListItem>
<asp:ListItem Value="MC0708" Text="MC0708"></asp:ListItem>
</asp:DropDownList>
</td>
</tr>
</table>
</div>
<div>
<rsweb:ReportViewer ID="ReportViewer1" runat="server" Font-Names="Verdana" Font-Size="8pt" Height="400px" Width="400px">
</rsweb:ReportViewer>
</div>
</form>

Holds.aspx.cs

using System;using System.Data;using System.Data.SqlClient;using System.Configuration;using System.Collections;using System.Text;using System.Web;using System.Web.Security;using System.Web.UI;using System.Web.UI.WebControls;using System.Web.UI.WebControls.WebParts;using System.Web.UI.HtmlControls;using Microsoft.Reporting.WebForms;public partialclass Test : System.Web.UI.Page{// Handles initial page loadprotected void Page_Load(object sender, EventArgs e) {if (!IsPostBack) { Session["Season"] =null; } }protected void ConfigureReports() { ReportViewer1.LocalReport.ReportPath = Server.MapPath("reports/Holds.rdlc"); DataSet dataSet = DataSetConfiguration.Holds; ReportDataSource ds =new ReportDataSource("HoldsDataSet_Holds", dataSet.Tables[0]); ReportViewer1.LocalReport.DataSources.Clear(); ReportViewer1.LocalReport.DataSources.Add(ds); ReportViewer1.LocalReport.Refresh(); }// Handles season dropdownlist selectedIndexChangedprotected void ddlSeason_SelectedIndexChanged(object sender, EventArgs e) {if (ddlSeason.SelectedIndex == 0) { Session["Season"] =null; }else { Session["Season"] = ddlSeason.SelectedValue; ConfigureReports(); } } }

DataSetConfiguration.cs is the same.

FYI I found the information I needed at http://www.codeproject.com/aspnet/ReportViewer.asp

Dynamic Transformations

Hi

I have a dts that is creating a table with not a fixed number of columns. The number of colums depend on a couple of factors based on the data that I'm pulling from other tables.

After some processing I need to dump all the data in the "dynamic" table into an excel doc. My problem is with the transformations within the transform data task. I don't know how many fields I will have in my table and this needs to be mapped to columns within the excel doc. Is it possible to programmatically define the transformations within an activeX script or what can I do.

Thanks

Johnnie

SSIS does not support dynamic metadata. The best that could be done is to have a package for each dynamic type (if this is feasible, i.e. there are a finite number of dynamic types) and then have a master package that picks which of these "dynamic" packages to execute.

Matt

Dynamic SQL Syntax H_ll Please Help

This forum has helped me get to this point a couple times with this project BUT I just can't seem to get the syntax correct. Either one of these two statements do exactly what I want them to do:
SELECT @.RtnValue = Column0 FROM MyTable WHERE RowIndex = @.RIndex
SELECT @.RtnValue = (SELECT Column0 FROM MyTable WHERE RowIndex = @.RIndex)

@.RtnValue is the value the program will work with.

The only problem is that Column0 and @.RIndex need to be dynamic so I can index through each Column and Row of the table.

This is the code I am trying to use to do this dynamically, naturally it will be in two while loop to index through each Column and Row

DECLARE @.RtnValue smallint
SET @.RtnValue = 0

DECLARE @.RIndex smallint
SET @.RIndex = 20

DECLARE @.ColumnName varchar(10)
SET @.ColumnName = 'Column0'

DECLARE @.MySelectString varchar(200)
SET @.MySelectString0 = 'SELECT @.RtnValue = ( SELECT ' + @.ColumnName + ' FROM MyTable WHERE RowIndex = ' + @.RIndex + ' )'
--SET @.MySelectString1 = 'SELECT @.RtnValue = ( SELECT ' + @.ColumnName + ' FROM MyTable WHERE RowIndex = 1 )'

EXEC( @.MySelectString0 )
--EXEC( @.MySelectString1 )

@.MySelectString0 produces this error:
Server: Msg 245, Level 16, State 1, Line 23
Syntax error converting the varchar value 'SELECT @.RtnValue = ( SELECT Column0 FROM MyTable WHERE RowIndex = ' to a column of data type smallint.

@.MySelectString1 produces this error:
Server: Msg 137, Level 15, State 1, Line 1
Must declare the variable'@.RtnValue'.

I have tried many different combinations of syntax but can not seem to get the magic combination. Can someone tell me the correct syntax to get this to work.

Thank you in advance.

try something like this :

SET @.MySelectString0 = 'SELECT @.RtnValue = ( SELECT ' + @.ColumnName + ' FROM MyTable WHERE RowIndex = ' + CONVERT(VARCHAR,@.RIndex) + ' )'

|||

Thank You for your responce I will try your solution. I sure hope it works I have been stuck on this problem to long Thanks again

Dynamic SQL Question

Spent couple of hours trying to figure out this issue. How can I pass a
parameter in dynamic SQL?
Here is a simplified example:

CREATE PROCEDURE dbo.spTest
@.LastName varchar(25)
AS

exec('SELECT *
FROM SubmissionLog
WHERE LastName LIKE @.LastName')

exec spTest 'Doe'

Server: Msg 137, Level 15, State 2, Line 3
Must declare the variable '@.LastName'.

Thank's for your help,
Igor

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!mEmENT0m0RI (anonymous@.devdex.com) writes:
> Spent couple of hours trying to figure out this issue. How can I pass a
> parameter in dynamic SQL?
> Here is a simplified example:
>
> CREATE PROCEDURE dbo.spTest
> @.LastName varchar(25)
> AS
> exec ('SELECT *
> FROM SubmissionLog
> WHERE LastName LIKE @.LastName')
>
> exec spTest 'Doe'
>
> Server: Msg 137, Level 15, State 2, Line 3
> Must declare the variable '@.LastName'.

The dynamic SQL constitutes a scope of its own, so you cannot refer
to parameters declared in the surrounding procedure.

This case is best handled with sp_executesql, look at
http://www.sommarskog.se/dynamic_sql.html#sp_executesql.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hey, thanks for answering.
I alreasy coded it without dynamic SQL, but will deffinately try
sp_executesql tomorrow.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!