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 )

Easy Transact SQL Question

I'm a novice with SQL Querying, and can't figure out an update command.
My SELECT statement is as follows:
SELECT * FROM table1, table2
WHERE table1.keyfield = table2.keyfield and table2.field is not null
How do I convert this to an update statement on a field in table1 while
maintaining the restriction based on the field in table2?
Thanks for the help!Cindy Mikeworth wrote:
> I'm a novice with SQL Querying, and can't figure out an update command.
> My SELECT statement is as follows:
> SELECT * FROM table1, table2
> WHERE table1.keyfield = table2.keyfield and table2.field is not null
> How do I convert this to an update statement on a field in table1 while
> maintaining the restriction based on the field in table2?
> Thanks for the help!
For example:
UPDATE table1
SET col1 = 1234
WHERE EXISTS
(SELECT *
FROM table2
WHERE table2.keycol = table1.keycol
AND table2.col IS NOT NULL) ;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||First learn that rows are NOT anythign like records, nor are columns
anything like a field. it is VITAL to have the right mindset in SQL
SELECT *
FROM Table1, Table2
WHERE table1.keyfield = table2.keyfield
AND table2.field IS NOT NULL;
You don't do it at all!! One of the MANY differences between a field
and column is that a column can have constraints on it. An SQL
programmer woudl have done this in the DDL (do you know what DDL is? If
not, you are sooooo screwed).
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it. Could you program from what you posted? HOW?!
If you have a key, in ANY table is BY DEFINITION NOT NULL, so your|||Cindy Mikeworth (CindyMikeworth@.newsgroups.nospam) writes:
> I'm a novice with SQL Querying, and can't figure out an update command.
> My SELECT statement is as follows:
> SELECT * FROM table1, table2
> WHERE table1.keyfield = table2.keyfield and table2.field is not null
> How do I convert this to an update statement on a field in table1 while
> maintaining the restriction based on the field in table2?
UPDATE table1
SET field = ...
FROM table1, table2
WHERE table1.keyfield = table2.keyfield
and table2.field is not null
This uses a non-standard extension of the UPDATE statement that is
proprietary to SQL Server and Sybase. As long as one is careful that
the join produces a unique value for the row to update, this is a very
practical method, not the least because it's so easy to transform a
SELECT into an UPDATE.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||UPDATE t1
SET t1.column1 = t2.value
FROM table1 t1
INNER JOIN table2 t2
ON t1.keyfield = t2.keyfield
WHERE t2.field IS NOT NULL

Easy IF statement (Beginner)

Im not really keen on If statements, so im trying to figure out how to write an if statement in a matrix. I want it to list the values that begin with 4's as 2004 the values that start with 5's as 2005 and the values that begin with 6's as 2006. How do i do this? Whats the IF statement code, i know how to put one option. but now all.

=IIF(Fields!ICNNo.Value=Left(1),2004,) ? I know thats wrong..

Please Help.

One way you could do this is using nested IIF statements. Like this:

=iif(left(Fields!ICNNo.Value, 1) = "4", "2004",
iif(left(Fields!ICNNo.Value, 1) = "5", "2005",
iif(left(Fields!ICNNo.Value, 1) = "6", "2006", "OtherValue")))

Hope this helps.

Jarret

|||It worked!!! THANKS ALOT!!!! It worked perfectly!

Dynamically creating SSIS package for each flat file

Trying to figure out the best method of reading in a number of flat files, all with different number of columns and data types and outputting them to a database.

Here's the problem: They are EBCDIC encoded and some of the columns are packed decimal. I've set up one package that takes the flat file, unpacks the decimal (Using UnpackDecimal component) and then sending the rest through a second component to go from EBCDIC -> ASCII.

What I need is a way to do this for every flat file based on the schema for that flat file. One current solution is to write a script/app to create the .dtsx XML file and then execute that for each flat file. It appears like this may be possible, but I haven't gotten far enough to know for sure. So my questions are this:

1) Is there an easier way to do this (ie somehow feed the schema to the package and use it to dynamically set up the column makers and determine which columns get fed to the unpack decimal component.

2) If there isn't a better way, will dynamically creating the .dtsx XML file based on the necessary input/output columns for each flat file work? If so, what is a good source of information on this (information about how the .dtsx XML file is set up, what needs to be changed/what doesn't, etc).

Thanks,

Travis

Trav2003 wrote:

1) Is there an easier way to do this (ie somehow feed the schema to the package and use it to dynamically set up the column makers and determine which columns get fed to the unpack decimal component.

2) If there isn't a better way, will dynamically creating the .dtsx XML file based on the necessary input/output columns for each flat file work? If so, what is a good source of information on this (information about how the .dtsx XML file is set up, what needs to be changed/what doesn't, etc).

Thanks,

Travis

1) No. SSIS can't handle dynamic columns. The best it can do it dynamically create a child package, which is no different than #2.
2) Yes, it will work. You probably don't want to create XML directly, but instead use the API to generate the package. The updated samples contain one showing how to create a basic package. You may also find this tool helpful for reverse engineering packages.
|||you might also check out http://www.aminosoftware.com they have a custom source component that will read in many forms of ebcdic (including packed, zoned, etc) and output it into ASCII with only a single pass through the data file.

Dynamic SQL weird stuff

OK im not an expert SQL user or anything and im trying to figure out how to
do stuff purely from BOL...Im still learning how to use dynamic SQL and the
sp_executesql. im trying to run some dynamic sql code on every column in
every user table in a database... the general structure of the code i got
(sorry im sure its terrible) is like this:
declare @.colname sysname
declare @.colcurs cursor for
select syscolumns.name from syscolumns inner join sysobjects on syscolumns.i
d
= sysobjects.id
where sysobjects.xtype = 'u' and sysobjects.name != 'dtproperties'
open colcurs
fetch next from colcurs into @.colname
while @.@.fetchstatus = 0
begin
{dynamic SQL code using @.colname - im not putting this in yet until i see
that this whole thing works as i expect}
fetch next
end
close colcurs
deallocate colcurs
ok, if I just run the select, I get the rowset im looking for. WHen i try th
e
whole thing with the cursor, I get 'incorrect syntax near the keyword 'for''
.
what am I doing wrong? something wrong with this cursor?
thanks dearly
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200601/1sorry haha ok i figured it out, @.colcurs should just be colcurs
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200601/1|||>> OK im not an expert SQL user or anything and im trying to figure out how
to do stuff purely from BOL...Im still learning how to use dynamic SQL and t
he sp_executesql. <<
This is much like learning to cook or have sex from a book, isn't it?
:)
The first thing is that you want to avoid dynamic SQL in favor of
compiled SQL code and stored procedure.
Dynamic SQL says, "My understanding of the problem and my Software
Engineering skills are sooooo poor that a random future user knows more
on the fly than I do after all my research!!"
Cursors are even worse!! They say that you have absolutely no
understanding of declarative code and are reverting to 1950's
procedural code. In over 20 years of SQL, I have written five cursors
and i know that if I had CASE expressions, I could have easily avoided
three of them.
Dynamic SQL **and** a cursor is soooooooo damn wrong!!
Do you want to do this right or have code that runs 2 to 3 order of
magnitude slower than it could?|||Your comments about dynamic SQL are just plain wrong.
Dynamic SQL is compiled, it does not run slower than static SQL, in fact the
execution plan is kept from the first invocation (just like static SQL) and
also that execution plan can be parameterised, SQL Server does some of this
for you.
Dynamic SQL can be used to great effect where you have multiple optional
parameters and you cannot build an efficient query plan, the only other way
would be to have countless IF ELSE statements for each possible parameter
combination or to have masses of stored procedures again for each possible
combination - that would be maintanence hell.
Cursors are bad if there is a set based solution available, but there isn't
always one available, also, there might be reasons for using a cursor - data
cleansing, creating export files; there is no real need to learn another 3gl
language when we have a very simple Transact SQL language that will do the
job and its done inside the engine, in SQL 2005 we have more power with CLR
intergration.
I would dearly love to see your performance statistics and benchmarks to
back up your claim that dynamic SQL is '2 to 3 order of magnitude slower',
this is several times i've asked you to post but you never do; perhaps you
should go and do some testing and come back once you've actually tried it
rather than making things up to try and bolster your position.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1137468711.008805.10380@.o13g2000cwo.googlegroups.com...
> This is much like learning to cook or have sex from a book, isn't it?
> :)
> The first thing is that you want to avoid dynamic SQL in favor of
> compiled SQL code and stored procedure.
> Dynamic SQL says, "My understanding of the problem and my Software
> Engineering skills are sooooo poor that a random future user knows more
> on the fly than I do after all my research!!"
> Cursors are even worse!! They say that you have absolutely no
> understanding of declarative code and are reverting to 1950's
> procedural code. In over 20 years of SQL, I have written five cursors
> and i know that if I had CASE expressions, I could have easily avoided
> three of them.
> Dynamic SQL **and** a cursor is soooooooo damn wrong!!
> Do you want to do this right or have code that runs 2 to 3 order of
> magnitude slower than it could?
>

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!

Dynamic SQL Help

I am getting the following error when trying to use dynamic sql. I've tried
several ways to fix it, but have not been able to figure it out. Can someone
show me how to fix this? Thanks in advance for any help.
Error for the following stored procedure:
Invalid operator for data type. Operator equals add, type equals text.
ALTER PROC UpdateTable1Field(
@.tableID uniqueidentifier,
@.fieldUpdated varchar(50),
@.fieldText text)
AS
DECLARE @.sql nvarchar(4000)
SET @.sql = N'DECLARE @.ptrVal binary(16)
SELECT @.ptrVal = TEXTPTR(' + @.fieldUpdated +
') FROM Table1 WHERE TableID = ''' + CAST(@.tableID AS varchar(40)) + '''
WRITETEXT Table1.' + @.fieldUpdated + ' @.ptrVal ''' + @.fieldText + ''''
EXEC sp_executesql @.sqlTry,
SET @.sql = N'
DECLARE @.ptrVal binary(16)
SELECT @.ptrVal = TEXTPTR(' + @.fieldUpdated + N')
FROM Table1 WHERE TableID = @.TableID
WRITETEXT Table1.' + @.fieldUpdated + ' @.ptrVal @.fieldText'
EXEC sp_executesql @.sql, N'@.tableID uniqueidentifier, @.fieldText text',
@.tableID, @.fieldText
go
AMB
"Mike Collins" wrote:

> I am getting the following error when trying to use dynamic sql. I've trie
d
> several ways to fix it, but have not been able to figure it out. Can someo
ne
> show me how to fix this? Thanks in advance for any help.
> Error for the following stored procedure:
> Invalid operator for data type. Operator equals add, type equals text.
> ALTER PROC UpdateTable1Field(
> @.tableID uniqueidentifier,
> @.fieldUpdated varchar(50),
> @.fieldText text)
> AS
> DECLARE @.sql nvarchar(4000)
> SET @.sql = N'DECLARE @.ptrVal binary(16)
> SELECT @.ptrVal = TEXTPTR(' + @.fieldUpdated +
> ') FROM Table1 WHERE TableID = ''' + CAST(@.tableID AS varchar(40)) + '''
> WRITETEXT Table1.' + @.fieldUpdated + ' @.ptrVal ''' + @.fieldText + ''''
> EXEC sp_executesql @.sql
>|||TEXT datatype cannot be used with '+' operator, try changing @.fieldText to
varchar()
- Sha Anand
"Mike Collins" wrote:

> I am getting the following error when trying to use dynamic sql. I've trie
d
> several ways to fix it, but have not been able to figure it out. Can someo
ne
> show me how to fix this? Thanks in advance for any help.
> Error for the following stored procedure:
> Invalid operator for data type. Operator equals add, type equals text.
> ALTER PROC UpdateTable1Field(
> @.tableID uniqueidentifier,
> @.fieldUpdated varchar(50),
> @.fieldText text)
> AS
> DECLARE @.sql nvarchar(4000)
> SET @.sql = N'DECLARE @.ptrVal binary(16)
> SELECT @.ptrVal = TEXTPTR(' + @.fieldUpdated +
> ') FROM Table1 WHERE TableID = ''' + CAST(@.tableID AS varchar(40)) + '''
> WRITETEXT Table1.' + @.fieldUpdated + ' @.ptrVal ''' + @.fieldText + ''''
> EXEC sp_executesql @.sql
>|||Hello, Mike
You can (but shoudn't) use something like this:
ALTER PROC UpdateTable1Field(
@.tableID uniqueidentifier,
@.fieldUpdated varchar(50),
@.fieldText text)
AS
DECLARE @.sql nvarchar(4000)
SET @.sql = N'DECLARE @.ptrVal binary(16)
SELECT @.ptrVal=TEXTPTR(' + @.fieldUpdated +
') FROM Table1 WHERE TableID = @.tableID
WRITETEXT Table1.' + @.fieldUpdated + ' @.ptrVal @.fieldText'
EXEC sp_executesql @.sql, N'@.tableID uniqueidentifier, @.fieldText text',
@.tableID, @.fieldText
However, you should know that:
1. Using TEXTPTR and WRITETEXT this way assumes that there was a value
in that text column (i.e. it was not NULL), before executing the
procedure.
2. Dynamic SQL is usually a bad idea. If a stored procedure contains
Dynamic SQL, a lot of the reasons for using a stored procedure
(security, performance, maintanability) are now voided. Read the
following article by Erland Sommarskog, SQL Server MVP:
http://www.sommarskog.se/dynamic_sql.html
especially the part about "Common Cases when to (Not) Use Dynamic SQL".
Razvan|||Thanks, that works except I need to figure out what the following error mean
s.
NULL textptr (text, ntext, or image pointer) passed to WriteText function.
"Alejandro Mesa" wrote:
> Try,
> SET @.sql = N'
> DECLARE @.ptrVal binary(16)
> SELECT @.ptrVal = TEXTPTR(' + @.fieldUpdated + N')
> FROM Table1 WHERE TableID = @.TableID
> WRITETEXT Table1.' + @.fieldUpdated + ' @.ptrVal @.fieldText'
> EXEC sp_executesql @.sql, N'@.tableID uniqueidentifier, @.fieldText text',
> @.tableID, @.fieldText
> go
>
> AMB
>
> "Mike Collins" wrote:
>|||What about when my data is too long for a varchar data type? Although not in
all, in many cases it will be too long.
"Sha Anand" wrote:
> TEXT datatype cannot be used with '+' operator, try changing @.fieldText
to
> varchar()
> - Sha Anand
> "Mike Collins" wrote:
>|||Thanks...will this article point me to an alternative to what I am trying to
do? I will not know the column to update ahead of time.
"Razvan Socol" wrote:

> Hello, Mike
> You can (but shoudn't) use something like this:
> ALTER PROC UpdateTable1Field(
> @.tableID uniqueidentifier,
> @.fieldUpdated varchar(50),
> @.fieldText text)
> AS
> DECLARE @.sql nvarchar(4000)
> SET @.sql = N'DECLARE @.ptrVal binary(16)
> SELECT @.ptrVal=TEXTPTR(' + @.fieldUpdated +
> ') FROM Table1 WHERE TableID = @.tableID
> WRITETEXT Table1.' + @.fieldUpdated + ' @.ptrVal @.fieldText'
> EXEC sp_executesql @.sql, N'@.tableID uniqueidentifier, @.fieldText text',
> @.tableID, @.fieldText
> However, you should know that:
> 1. Using TEXTPTR and WRITETEXT this way assumes that there was a value
> in that text column (i.e. it was not NULL), before executing the
> procedure.
> 2. Dynamic SQL is usually a bad idea. If a stored procedure contains
> Dynamic SQL, a lot of the reasons for using a stored procedure
> (security, performance, maintanability) are now voided. Read the
> following article by Erland Sommarskog, SQL Server MVP:
> http://www.sommarskog.se/dynamic_sql.html
> especially the part about "Common Cases when to (Not) Use Dynamic SQL".
> Razvan
>|||Mike,
The error is because the column was not initialized. Put some value or empty
string when you insert the row. See WRITETEXT in BOL for more info.
create table dbo.t1 (
c1 int not null identity,
c2 text
)
go
create procedure dbo.p1
@.c1 int,
@.c2 text
as
set nocount on
declare @.sql nvarchar(4000)
SET @.sql = N'
DECLARE @.ptrval binary(16)
SELECT @.ptrval = TEXTPTR(c2)
FROM dbo.t1 WHERE c1 = @.c1
WRITETEXT dbo.t1.c2 @.ptrval @.c2'
EXEC sp_executesql @.sql, N'@.c1 int, @.c2 text', @.c1, @.c2
go
insert into dbo.t1(c2) values('uno')
go
exec dbo.p1 1, 'dos'
go
select * from dbo.t1
go
drop procedure dbo.p1
go
drop table dbo.t1
go
AMB
"Mike Collins" wrote:
> Thanks, that works except I need to figure out what the following error me
ans.
> NULL textptr (text, ntext, or image pointer) passed to WriteText function.
> "Alejandro Mesa" wrote:
>|||Thanks...it works great now.
"Alejandro Mesa" wrote:
> Mike,
> The error is because the column was not initialized. Put some value or emp
ty
> string when you insert the row. See WRITETEXT in BOL for more info.
> create table dbo.t1 (
> c1 int not null identity,
> c2 text
> )
> go
> create procedure dbo.p1
> @.c1 int,
> @.c2 text
> as
> set nocount on
> declare @.sql nvarchar(4000)
> SET @.sql = N'
> DECLARE @.ptrval binary(16)
> SELECT @.ptrval = TEXTPTR(c2)
> FROM dbo.t1 WHERE c1 = @.c1
> WRITETEXT dbo.t1.c2 @.ptrval @.c2'
> EXEC sp_executesql @.sql, N'@.c1 int, @.c2 text', @.c1, @.c2
> go
> insert into dbo.t1(c2) values('uno')
> go
> exec dbo.p1 1, 'dos'
> go
> select * from dbo.t1
> go
> drop procedure dbo.p1
> go
> drop table dbo.t1
> go
>
> AMB
>
> "Mike Collins" wrote:
>|||Mike Collins (MikeCollins@.discussions.microsoft.com) writes:
> Thanks...will this article point me to an alternative to what I am
> trying to do? I will not know the column to update ahead of time.
Yes, exactly this case is covered in the article.
And if you don't know the column to update ahead of time, there is
some fishy going on anyway. A stored procedure in a well-designed
data model would always operate on known tables and known columns.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Dynamic SQL Concatenation

In FIGURE 2, I'm successfully building a dynamic statement on Northwind.
However, in FIGURE 1, I'm trying to add a WHERE subquery that should return
FIGURE 2's results but only for the last 10 days. I'm getting a strange
error in QA. Can someone help me with my syntax in FIGURE 1 near the ERROR
comment?
-- FIGURE 1:
declare @.SQL varchar(1000), @.ShipVia int, @.Country nvarchar(15), @.typeID
int, @.timeID int, @.dtMaxDate datetime
SET @.Country = N'Germany'
SET @.ShipVia = '1'
set @.typeID = '1'
SET @.timeID = '1'
set @.dtMaxDate = (SELECT MAX(OrderDate) FROM Orders)
SET @.SQL = 'SELECT Customers.CompanyName, '
IF @.typeID = 1
SET @.SQL = @.SQL + 'SUM(Orders.Freight) AS SumOfFreight '
IF @.typeID = 2
SET @.SQL = @.SQL + 'COUNT(*) AS SalesCount '
SET @.SQL = @.SQL + '
FROM Customers
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
WHERE Orders.ShipVia = ' + CAST(@.ShipVia AS VARCHAR(55))
IF @.timeID = 1 -- ERROR IS IN NEXT 2 LINES
SET @.SQL = @.SQL + ' AND Orders.OrderDate IN (SELECT DISTINCT TOP 10
Orders.OrderDate
FROM Orders WHERE Orders.OrderDate <= ' +
CAST(@.dtMaxDate AS VARCHAR(55))
SET @.SQL = @.SQL + ' ORDER BY Orders.OrderDate DESC) '
SET @.SQL = @.SQL + 'GROUP BY Customers.CompanyName'
EXEC(@.SQL)
-- FIGURE 2:
declare @.SQL varchar(1000), @.ShipVia int, @.Country nvarchar(15), @.typeID int
SET @.Country = N'Germany'
SET @.ShipVia = '1'
set @.typeID = '1'
SET @.SQL = 'SELECT Customers.CompanyName, '
IF @.typeID = 1
SET @.SQL = @.SQL + 'SUM(Orders.Freight) AS SumOfFreight '
IF @.typeID = 2
SET @.SQL = @.SQL + 'COUNT(*) AS SalesCount '
SET @.SQL = @.SQL + '
FROM
Customers
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
WHERE
Orders.ShipVia = ' + CAST(@.ShipVia AS VARCHAR(55)) + '
GROUP BY
Customers.CompanyName'What's the error you're getting?
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"scott" <sbailey@.mileslumber.com> wrote in message
news:OVCoeAt7FHA.204@.TK2MSFTNGP10.phx.gbl...
> In FIGURE 2, I'm successfully building a dynamic statement on Northwind.
> However, in FIGURE 1, I'm trying to add a WHERE subquery that should
> return FIGURE 2's results but only for the last 10 days. I'm getting a
> strange error in QA. Can someone help me with my syntax in FIGURE 1 near
> the ERROR comment?
> -- FIGURE 1:
> declare @.SQL varchar(1000), @.ShipVia int, @.Country nvarchar(15), @.typeID
> int, @.timeID int, @.dtMaxDate datetime
> SET @.Country = N'Germany'
> SET @.ShipVia = '1'
> set @.typeID = '1'
> SET @.timeID = '1'
> set @.dtMaxDate = (SELECT MAX(OrderDate) FROM Orders)
> SET @.SQL = 'SELECT Customers.CompanyName, '
> IF @.typeID = 1
> SET @.SQL = @.SQL + 'SUM(Orders.Freight) AS SumOfFreight '
> IF @.typeID = 2
> SET @.SQL = @.SQL + 'COUNT(*) AS SalesCount '
> SET @.SQL = @.SQL + '
> FROM Customers
> INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
> WHERE Orders.ShipVia = ' + CAST(@.ShipVia AS VARCHAR(55))
> IF @.timeID = 1 -- ERROR IS IN NEXT 2 LINES
> SET @.SQL = @.SQL + ' AND Orders.OrderDate IN (SELECT DISTINCT TOP 10
> Orders.OrderDate
> FROM Orders WHERE Orders.OrderDate <= ' +
> CAST(@.dtMaxDate AS VARCHAR(55))
> SET @.SQL = @.SQL + ' ORDER BY Orders.OrderDate DESC) '
> SET @.SQL = @.SQL + 'GROUP BY Customers.CompanyName'
> EXEC(@.SQL)
> -- FIGURE 2:
> declare @.SQL varchar(1000), @.ShipVia int, @.Country nvarchar(15), @.typeID
> int
> SET @.Country = N'Germany'
> SET @.ShipVia = '1'
> set @.typeID = '1'
> SET @.SQL = 'SELECT Customers.CompanyName, '
> IF @.typeID = 1
> SET @.SQL = @.SQL + 'SUM(Orders.Freight) AS SumOfFreight '
> IF @.typeID = 2
> SET @.SQL = @.SQL + 'COUNT(*) AS SalesCount '
> SET @.SQL = @.SQL + '
> FROM
> Customers
> INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
> WHERE
> Orders.ShipVia = ' + CAST(@.ShipVia AS VARCHAR(55)) + '
> GROUP BY
> Customers.CompanyName'
>|||scott (sbailey@.mileslumber.com) writes:
> In FIGURE 2, I'm successfully building a dynamic statement on Northwind.
> However, in FIGURE 1, I'm trying to add a WHERE subquery that should
> return
> FIGURE 2's results but only for the last 10 days. I'm getting a strange
> error in QA. Can someone help me with my syntax in FIGURE 1 near the ERROR
> comment?
Please, next time include the error message you get.
Here is what I think is suspect:

> CAST(@.dtMaxDate AS VARCHAR(55))
Use "convert(char(8), @.dtMaxDate, 112)" to get a date format that
is not subject to different interpretation.
Or even, better throw this code away entirely. I don't know what you
are trying to achieve, but if this is an exercise with dynamic SQL,
please to it the proper way and use sp_executesql instead. Then you
don't have to deal with cast and convert to interpolate variable values.
Please see sp_executesql in Books Online.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Server: Msg 170, Level 15, State 1, Line 7
Line 7: Incorrect syntax near '6'.
What's confusing is their is no '6' in my code.
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:uadSxFt7FHA.2176@.TK2MSFTNGP14.phx.gbl...
> What's the error you're getting?
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "scott" <sbailey@.mileslumber.com> wrote in message
> news:OVCoeAt7FHA.204@.TK2MSFTNGP10.phx.gbl...
>|||Below in FIGURE 1 is my code using the new date format. I'm getting the
below error below. Can you help me solve this? I hear what you're saying
about the other method, but want to get this one fixed due to time
constraints.
ERROR:
Server: Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type datetime.
-- FIGURE 1:
declare @.SQL varchar(1000), @.ShipVia int, @.Country nvarchar(15), @.typeID
int, @.timeID int, @.dtMaxDate datetime
SET @.Country = N'Germany'
SET @.ShipVia = '1'
set @.typeID = '1'
SET @.timeID = '1'
set @.dtMaxDate = (SELECT MAX(OrderDate) FROM Orders)
SET @.SQL = 'SELECT Customers.CompanyName, '
IF @.typeID = 1
SET @.SQL = @.SQL + 'SUM(Orders.Freight) AS SumOfFreight '
IF @.typeID = 2
SET @.SQL = @.SQL + 'COUNT(*) AS SalesCount '
SET @.SQL = @.SQL + '
FROM Customers
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
WHERE
Orders.ShipVia = ' + CAST(@.ShipVia AS VARCHAR(55))
IF @.timeID = 1
SET @.SQL = @.SQL + ' AND Orders.OrderDate IN (SELECT DISTINCT TOP 10
Orders.OrderDate
FROM Orders WHERE Orders.OrderDate <= ' +
convert(char(8), @.dtMaxDate, 112)
SET @.SQL = @.SQL + ' ORDER BY Orders.OrderDate DESC) '
SET @.SQL = @.SQL + 'GROUP BY Customers.CompanyName'
EXEC(@.SQL)
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9715E9F9B8E8FYazorman@.127.0.0.1...
> scott (sbailey@.mileslumber.com) writes:
> Please, next time include the error message you get.
> Here is what I think is suspect:
>
> Use "convert(char(8), @.dtMaxDate, 112)" to get a date format that
> is not subject to different interpretation.
> Or even, better throw this code away entirely. I don't know what you
> are trying to achieve, but if this is an exercise with dynamic SQL,
> please to it the proper way and use sp_executesql instead. Then you
> don't have to deal with cast and convert to interpolate variable values.
> Please see sp_executesql in Books Online.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||CONVERT (varchar(12), @.dtMaxDate, 101) fixed it. thanks for your input.
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9715E9F9B8E8FYazorman@.127.0.0.1...
> scott (sbailey@.mileslumber.com) writes:
> Please, next time include the error message you get.
> Here is what I think is suspect:
>
> Use "convert(char(8), @.dtMaxDate, 112)" to get a date format that
> is not subject to different interpretation.
> Or even, better throw this code away entirely. I don't know what you
> are trying to achieve, but if this is an exercise with dynamic SQL,
> please to it the proper way and use sp_executesql instead. Then you
> don't have to deal with cast and convert to interpolate variable values.
> Please see sp_executesql in Books Online.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||Scott (sbailey@.mileslumber.com) writes:
> Server: Msg 170, Level 15, State 1, Line 7
> Line 7: Incorrect syntax near '6'.
> What's confusing is their is no '6' in my code.
It's imperative when you work with dynamic SQL to include things like:
IF @.debug = 1 PRINT @.sql
so that you can see what you actually have produced.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns971668A0F963EYazorman@.127.0.0.1...
> It's imperative when you work with dynamic SQL to include things like:
> IF @.debug = 1 PRINT @.sql
How about, instead, using a macro and putting it in debug mode? :)
http://www.datamanipulation.net/tsqlmacro
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--

> so that you can see what you actually have produced.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||On Mon, 21 Nov 2005 18:19:56 -0600, Scott wrote:

>CONVERT (varchar(12), @.dtMaxDate, 101) fixed it. thanks for your input.
Hi Scott,
I severely doubt it. You're not getting errors anymore, but your results
are incorrect.
If you had followed Erlands original advice to print the SQL string you
are executing, you'd have seen that your original query created code
like this:
WHERE Orders.OrderDate <= 20051123
Since 20051123 is a numeric and OrderDate is a datetime, SQL Server
attempts to convertthe numeric 20051123 to datetime. Conversion rule
says that the number is the number of days since jan 1st 1900. If I
didn't miscalculate, adding 20,051,123 to the start of 1900 takes us
some 55,000 years in the future - well beyond the maximum capacity of
the datetime datatype. That's whhy you got the error.
Your "fix" now generates code like this:
WHERE Orders.OrderDate <= 11/23/2005
This will first divide 11 by 23 (using integer division, yielding 0),
then divide that by 2005 (result still 0). This will then be converted
to datetime ==> 0 days after jan 1st 1900 is jan 1st 1900. Regardless of
the value of @.dtMaxDate, you'll be comparing to 1900-01-01.
The correct way to pass a date literal is to use the yyyymmdd format,
and to enclose it in single quotes:
WHERE Orders.OrderDate <= '20051123'
Or to fix this particular line of your code:
SET @.SQL = @.SQL + ' AND Orders.OrderDate IN (SELECT DISTINCT TOP 10
Orders.OrderDate
FROM Orders WHERE Orders.OrderDate <= ''' +
convert(char(8), @.dtMaxDate, 112) + ''''
(Note that a single quotes within single quotes must be doubled!)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||thanks, that fixed it.
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:4vd7o19vos75ef7eumvphndgcafspqkjv7@.
4ax.com...
> On Mon, 21 Nov 2005 18:19:56 -0600, Scott wrote:
>
> Hi Scott,
> I severely doubt it. You're not getting errors anymore, but your results
> are incorrect.
> If you had followed Erlands original advice to print the SQL string you
> are executing, you'd have seen that your original query created code
> like this:
> WHERE Orders.OrderDate <= 20051123
> Since 20051123 is a numeric and OrderDate is a datetime, SQL Server
> attempts to convertthe numeric 20051123 to datetime. Conversion rule
> says that the number is the number of days since jan 1st 1900. If I
> didn't miscalculate, adding 20,051,123 to the start of 1900 takes us
> some 55,000 years in the future - well beyond the maximum capacity of
> the datetime datatype. That's whhy you got the error.
>
> Your "fix" now generates code like this:
> WHERE Orders.OrderDate <= 11/23/2005
> This will first divide 11 by 23 (using integer division, yielding 0),
> then divide that by 2005 (result still 0). This will then be converted
> to datetime ==> 0 days after jan 1st 1900 is jan 1st 1900. Regardless of
> the value of @.dtMaxDate, you'll be comparing to 1900-01-01.
>
> The correct way to pass a date literal is to use the yyyymmdd format,
> and to enclose it in single quotes:
> WHERE Orders.OrderDate <= '20051123'
> Or to fix this particular line of your code:
> SET @.SQL = @.SQL + ' AND Orders.OrderDate IN (SELECT DISTINCT TOP 10
> Orders.OrderDate
> FROM Orders WHERE Orders.OrderDate <= ''' +
> convert(char(8), @.dtMaxDate, 112) + ''''
> (Note that a single quotes within single quotes must be doubled!)
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)