buggy sql like command with wildcards to xml

I have this stored procedure, witch works nice under the query mode
create procedure MKR
@.mkrvar nvarchar(50)
AS
SELECT *
FROM QUADRA
WHERE (SUBSTRING(QUADRA, 3, 9) LIKE '%@.mkrvar%')
GO
so i call it like this on my browser :
http://mywebsite/?sql=exec MKR '57' FOR XML NESTED&root=root
is not working...oh !
http://mywebsite/?sql=SELECT * FROM QUADRA WHERE (SUBSTRING(QUADRA, 3, 9)
LIKE '%57%')FOR XML NESTED&root=root
it doesnt work too
so i trought that might be something buggy...donno...so i tried this
http://mywebsite/?sql=SELECT * FROM QUADRA WHERE (QUADRA LIKE '%57%') FOR
XML RAW&root=root
ok...it doesnt work...why ?
but this work
http://mywebsite/?sql=SELECT * FROM QUADRA WHERE (QUADRA LIKE '57') FOR XML
RAW&root=root
so...why i cant use wildcards to generate an xml ?
i need a fix or a workarround for this asap, if someone could help :D
thanks
max
% is an escape character in a URL. For example you will see spaces
converted to %20.
Try %%57%% instead.
"Antonio Max" <maxspam@.bol.com.br> wrote in message
news:eiuMJVxKEHA.3292@.TK2MSFTNGP11.phx.gbl...
>I have this stored procedure, witch works nice under the query mode
> create procedure MKR
> @.mkrvar nvarchar(50)
> AS
> SELECT *
> FROM QUADRA
> WHERE (SUBSTRING(QUADRA, 3, 9) LIKE '%@.mkrvar%')
> GO
> so i call it like this on my browser :
> http://mywebsite/?sql=exec MKR '57' FOR XML NESTED&root=root
> is not working...oh !
> http://mywebsite/?sql=SELECT * FROM QUADRA WHERE (SUBSTRING(QUADRA, 3, 9)
> LIKE '%57%')FOR XML NESTED&root=root
> it doesnt work too
> so i trought that might be something buggy...donno...so i tried this
>
> http://mywebsite/?sql=SELECT * FROM QUADRA WHERE (QUADRA LIKE '%57%') FOR
> XML RAW&root=root
> ok...it doesnt work...why ?
>
> but this work
> http://mywebsite/?sql=SELECT * FROM QUADRA WHERE (QUADRA LIKE '57') FOR
> XML
> RAW&root=root
> so...why i cant use wildcards to generate an xml ?
> i need a fix or a workarround for this asap, if someone could help :D
> thanks
> max
>
>
|||thanks a lot
forgot this;;.. eerr
anyway...it looks like this now
http://mywebsite/?sql=SELECT%20*%20F...DRA%20WHERE%20(QUADRA%20LIKE%20
'%2557%25')%20FOR%20XML%20RAW&root=root
where %25 is the % sign on url encoding char table
thanks
max
"Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
news:#T#EIIzKEHA.892@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> % is an escape character in a URL. For example you will see spaces
> converted to %20.
> Try %%57%% instead.
>
> "Antonio Max" <maxspam@.bol.com.br> wrote in message
> news:eiuMJVxKEHA.3292@.TK2MSFTNGP11.phx.gbl...
9)[vbcol=seagreen]
FOR
>

BUG: Severe error occurred on the current command...

select top 10 count(*) peerTotal, B.peerCountryCode, B.peerCountry,
(select top 1 peerCity from Peers where peerCity != 'Determining' AND peerCountry != 'NULL' and peerCountryCode = B.peerCountryCode group by peerCity order by count(peerCity) desc) peerTopCity
from Peers B where peerCountryCode != 'NULL' and peerCountry != 'NULL' group by peerCountryCode, peerCountry order by peerTotal desc

This causes the error

but if you take the order by at teh end out, it works. The table is partitioned acrosss 3 bladesBy the way, I am using SP2 CTP|||Source,Severity,Message
01/09/2007 09:06:09,Server,Unknown,A user request from the session with SPID 59 generated a fatal exception. SQL Server is terminating this session. Contact Product Support Services with the dump produced in the log directory.
01/09/2007 09:06:09,Server,Unknown,Error: 17310<c/> Severity: 20<c/> State: 1.
01/09/2007 09:06:09,spid59,Unknown,External dump process return code 0x20000001.<nl/>External dump process returned no errors.
01/09/2007 09:06:09,spid59,Unknown,Stack Signature for the dump is 0x0684EA45
01/09/2007 09:06:09,spid59,Unknown,78132A36 Module(MSVCR80+00002A36)
01/09/2007 09:06:09,spid59,Unknown,781329AA Module(MSVCR80+000029AA)
01/09/2007 09:06:09,spid59,Unknown,0126872D Module(sqlservr+0026872D)
01/09/2007 09:06:09,spid59,Unknown,01268590 Module(sqlservr+00268590)
01/09/2007 09:06:09,spid59,Unknown,01268AE0 Module(sqlservr+00268AE0)
01/09/2007 09:06:09,spid59,Unknown,01267AEF Module(sqlservr+00267AEF)
01/09/2007 09:06:09,spid59,Unknown,0100B1D5 Module(sqlservr+0000B1D5)
01/09/2007 09:06:09,spid59,Unknown,0100B4C5 Module(sqlservr+0000B4C5)
01/09/2007 09:06:09,spid59,Unknown,0100B39F Module(sqlservr+0000B39F)
01/09/2007 09:06:09,spid59,Unknown,0102F3DB Module(sqlservr+0002F3DB)
01/09/2007 09:06:09,spid59,Unknown,01030CC2 Module(sqlservr+00030CC2)
01/09/2007 09:06:09,spid59,Unknown,0102CDE6 Module(sqlservr+0002CDE6)
01/09/2007 09:06:09,spid59,Unknown,012DC2DD Module(sqlservr+002DC2DD)
01/09/2007 09:06:09,spid59,Unknown,013ADAA5 Module(sqlservr+003ADAA5)
01/09/2007 09:06:09,spid59,Unknown,012DB9B7 Module(sqlservr+002DB9B7)
01/09/2007 09:06:09,spid59,Unknown,012DC028 Module(sqlservr+002DC028)
01/09/2007 09:06:09,spid59,Unknown,012DD905 Module(sqlservr+002DD905)
01/09/2007 09:06:09,spid59,Unknown,012DD7A0 Module(sqlservr+002DD7A0)
01/09/2007 09:06:09,spid59,Unknown,012DCEA7 Module(sqlservr+002DCEA7)
01/09/2007 09:06:09,spid59,Unknown,012DDBA3 Module(sqlservr+002DDBA3)
01/09/2007 09:06:09,spid59,Unknown,012E13A8 Module(sqlservr+002E13A8)
01/09/2007 09:06:09,spid59,Unknown,013C92D9 Module(sqlservr+003C92D9)
01/09/2007 09:06:09,spid59,Unknown,013C8AE9 Module(sqlservr+003C8AE9)
01/09/2007 09:06:09,spid59,Unknown,01415594 Module(sqlservr+00415594)
01/09/2007 09:06:09,spid59,Unknown,013C8AE9 Module(sqlservr+003C8AE9)
01/09/2007 09:06:09,spid59,Unknown,01454E39 Module(sqlservr+00454E39)
01/09/2007 09:06:09,spid59,Unknown,013C8AE9 Module(sqlservr+003C8AE9)
01/09/2007 09:06:09,spid59,Unknown,0141A804 Module(sqlservr+0041A804)
01/09/2007 09:06:09,spid59,Unknown,013C8AE9 Module(sqlservr+003C8AE9)
01/09/2007 09:06:09,spid59,Unknown,01418E23 Module(sqlservr+00418E23)
01/09/2007 09:06:09,spid59,Unknown,013C9B43 Module(sqlservr+003C9B43)
01/09/2007 09:06:09,spid59,Unknown,013C8AE9 Module(sqlservr+003C8AE9)
01/09/2007 09:06:09,spid59,Unknown,0141B6BE Module(sqlservr+0041B6BE)
01/09/2007 09:06:09,spid59,Unknown,013C8AE9 Module(sqlservr+003C8AE9)
01/09/2007 09:06:09,spid59,Unknown,01415594 Module(sqlservr+00415594)
01/09/2007 09:06:09,spid59,Unknown,013C8AE9 Module(sqlservr+003C8AE9)
01/09/2007 09:06:09,spid59,Unknown,01415E6E Module(sqlservr+00415E6E)
01/09/2007 09:06:09,spid59,Unknown,013C9B43 Module(sqlservr+003C9B43)
01/09/2007 09:06:09,spid59,Unknown,013C8AE9 Module(sqlservr+003C8AE9)
01/09/2007 09:06:09,spid59,Unknown,01EBFF7D Module(sqlservr+00EBFF7D)
01/09/2007 09:06:09,spid59,Unknown,0151F41F Module(sqlservr+0051F41F)
01/09/2007 09:06:09,spid59,Unknown,013B6A49 Module(sqlservr+003B6A49)
01/09/2007 09:06:09,spid59,Unknown,013B6891 Module(sqlservr+003B6891)
01/09/2007 09:06:09,spid59,Unknown,* Short Stack Dump
01/09/2007 09:06:09,spid59,Unknown,* -
01/09/2007 09:06:09,spid59,Unknown,* *******************************************************************************
01/09/2007 09:06:09,spid59,Unknown,* SegSs: 00000023:
01/09/2007 09:06:09,spid59,Unknown,* Esp: 63CCB9FC: 63CC1102 21E257A8 21E7D930 21C9E3E8 63CCBA40 02490881
01/09/2007 09:06:09,spid59,Unknown,* EFlags: 00010246: 006F0064 00730077 004E005F 00000054 00610050 00680074
01/09/2007 09:06:09,spid59,Unknown,* SegCs: 0000001B:
01/09/2007 09:06:09,spid59,Unknown,* Ebp: 63CCBA18: 63CCBA4C 013B6A49 21E7D930 00000000 21E257A8 63CC1156
01/09/2007 09:06:09,spid59,Unknown,* Eip: 013B6891: 5BD9A5F3 3843893C C7404389 FFFFFC45 C38BFFFF 64F44D8B
01/09/2007 09:06:09,spid59,Unknown,* Edx: 00000000:
01/09/2007 09:06:09,spid59,Unknown,* Ecx: 00000008:
01/09/2007 09:06:09,spid59,Unknown,* Ebx: 21E7D930: 013B68C0 00000000 0000006D 21E257A8 00000000 00000000
01/09/2007 09:06:09,spid59,Unknown,* Eax: 00000000:
01/09/2007 09:06:09,spid59,Unknown,* Esi: 00000000:
01/09/2007 09:06:09,spid59,Unknown,* Edi: 21E7D948: 00000000 00000000 00000000 00000000 00000000 00000000
01/09/2007 09:06:09,spid59,Unknown,*
01/09/2007 09:06:09,spid59,Unknown,* dbghelp 64330000 64444FFF 00115000
01/09/2007 09:06:09,spid59,Unknown,* xplog70 63B30000 63B32FFF 00003000
01/09/2007 09:06:09,spid59,Unknown,* xplog70 63B10000 63B1BFFF 0000c000
01/09/2007 09:06:09,spid59,Unknown,* xpstar90 63AE0000 63B05FFF 00026000
01/09/2007 09:06:09,spid59,Unknown,* odbcint 63AC0000 63AD6FFF 00017000
01/09/2007 09:06:09,spid59,Unknown,* ATL80 7C630000 7C64AFFF 0001b000
01/09/2007 09:06:09,spid59,Unknown,* BatchParser90 63990000 639AEFFF 0001f000
01/09/2007 09:06:09,spid59,Unknown,* ODBC32 63950000 6398CFFF 0003d000
01/09/2007 09:06:09,spid59,Unknown,* SQLSCM90 63930000 63938FFF 00009000
01/09/2007 09:06:09,spid59,Unknown,* xpstar90 638D0000 63918FFF 00049000
01/09/2007 09:06:09,spid59,Unknown,* xpsqlbot 638B0000 638B5FFF 00006000
01/09/2007 09:06:09,spid59,Unknown,* msftepxy 633C0000 633D4FFF 00015000
01/09/2007 09:06:09,spid59,Unknown,* SQLNCLIR 00770000 007A2FFF 00033000
01/09/2007 09:06:09,spid59,Unknown,* comdlg32 762B0000 762F9FFF 0004a000
01/09/2007 09:06:09,spid59,Unknown,* COMCTL32 77530000 775C6FFF 00097000
01/09/2007 09:06:09,spid59,Unknown,* sqlncli 63010000 63233FFF 00224000
01/09/2007 09:06:09,spid59,Unknown,* CLBCatQ 777B0000 77832FFF 00083000
01/09/2007 09:06:09,spid59,Unknown,* xpsp2res 62D40000 63004FFF 002c5000
01/09/2007 09:06:09,spid59,Unknown,* ntdsapi 766F0000 76704FFF 00015000
01/09/2007 09:06:09,spid59,Unknown,* SAMLIB 62D30000 62D3EFFF 0000f000
01/09/2007 09:06:09,spid59,Unknown,* NTMARTA 77E00000 77E21FFF 00022000
01/09/2007 09:06:09,spid59,Unknown,* wshtcpip 71AE0000 71AE7FFF 00008000
01/09/2007 09:06:09,spid59,Unknown,* hnetcfg 62CD0000 62D28FFF 00059000
01/09/2007 09:06:09,spid59,Unknown,* dssenh 68100000 68123FFF 00024000
01/09/2007 09:06:09,spid59,Unknown,* imagehlp 76C10000 76C38FFF 00029000
01/09/2007 09:06:09,spid59,Unknown,* WINTRUST 76BB0000 76BDAFFF 0002b000
01/09/2007 09:06:09,spid59,Unknown,* dbghelp 62950000 62A64FFF 00115000
01/09/2007 09:06:09,spid59,Unknown,* msfte 626F0000 62948FFF 00259000
01/09/2007 09:06:09,spid59,Unknown,* security 61F50000 61F53FFF 00004000
01/09/2007 09:06:09,spid59,Unknown,* rasadhlp 76F80000 76F84FFF 00005000
01/09/2007 09:06:09,spid59,Unknown,* WLDAP32 76F10000 76F3DFFF 0002e000
01/09/2007 09:06:09,spid59,Unknown,* winrnr 76F70000 76F76FFF 00007000
01/09/2007 09:06:09,spid59,Unknown,* DNSAPI 76ED0000 76EF8FFF 00029000
01/09/2007 09:06:09,spid59,Unknown,* RESUTILS 344B0000 344C2FFF 00013000
01/09/2007 09:06:09,spid59,Unknown,* CLUSAPI 34490000 344A1FFF 00012000
01/09/2007 09:06:09,spid59,Unknown,* OLEAUT32 77D00000 77D8BFFF 0008c000
01/09/2007 09:06:09,spid59,Unknown,* WSOCK32 71BB0000 71BB8FFF 00009000
01/09/2007 09:06:09,spid59,Unknown,* VERSION 77B90000 77B97FFF 00008000
01/09/2007 09:06:09,spid59,Unknown,* MTXCLU 34470000 34488FFF 00019000
01/09/2007 09:06:09,spid59,Unknown,* msvcp60 780C0000 78120FFF 00061000
01/09/2007 09:06:09,spid59,Unknown,* MSDTCPRX 343F0000 34467FFF 00078000
01/09/2007 09:06:09,spid59,Unknown,* XOLEHLP 343E0000 343E5FFF 00006000
01/09/2007 09:06:09,spid59,Unknown,* COMRES 77010000 770D5FFF 000c6000
01/09/2007 09:06:09,spid59,Unknown,* schannel 76750000 76776FFF 00027000
01/09/2007 09:06:09,spid59,Unknown,* cryptdll 766E0000 766EBFFF 0000c000
01/09/2007 09:06:09,spid59,Unknown,* kerberos 34320000 34377FFF 00058000
01/09/2007 09:06:09,spid59,Unknown,* iphlpapi 76CF0000 76D09FFF 0001a000
01/09/2007 09:06:09,spid59,Unknown,* msv1_0 76C90000 76CB6FFF 00027000
01/09/2007 09:06:09,spid59,Unknown,* ole32 77670000 777A3FFF 00134000
01/09/2007 09:06:09,spid59,Unknown,* MSCOREE 340C0000 34104FFF 00045000
01/09/2007 09:06:09,spid59,Unknown,* AUTHZ 76C40000 76C53FFF 00014000
01/09/2007 09:06:09,spid59,Unknown,* rsaenh 68000000 6802EFFF 0002f000
01/09/2007 09:06:09,spid59,Unknown,* SQLOS 344D0000 344D4FFF 00005000
01/09/2007 09:06:09,spid59,Unknown,* sqlevn70 4F610000 4F7B8FFF 001a9000
01/09/2007 09:06:09,spid59,Unknown,* instapi 48060000 48069FFF 0000a000
01/09/2007 09:06:09,spid59,Unknown,* psapi 76B70000 76B7AFFF 0000b000
01/09/2007 09:06:09,spid59,Unknown,* comctl32 77420000 77522FFF 00103000
01/09/2007 09:06:09,spid59,Unknown,* SHLWAPI 77DA0000 77DF1FFF 00052000
01/09/2007 09:06:09,spid59,Unknown,* SHELL32 7C8D0000 7D0D2FFF 00803000
01/09/2007 09:06:09,spid59,Unknown,* NETAPI32 71C40000 71C97FFF 00058000
01/09/2007 09:06:09,spid59,Unknown,* opends60 333E0000 333E6FFF 00007000
01/09/2007 09:06:09,spid59,Unknown,* USERENV 76920000 769E3FFF 000c4000
01/09/2007 09:06:09,spid59,Unknown,* WS2HELP 71BF0000 71BF7FFF 00008000
01/09/2007 09:06:09,spid59,Unknown,* WS2_32 71C00000 71C16FFF 00017000
01/09/2007 09:06:09,spid59,Unknown,* MSWSOCK 71B20000 71B60FFF 00041000
01/09/2007 09:06:09,spid59,Unknown,* Secur32 76F50000 76F62FFF 00013000
01/09/2007 09:06:09,spid59,Unknown,* MSASN1 76190000 761A1FFF 00012000
01/09/2007 09:06:09,spid59,Unknown,* CRYPT32 761B0000 76242FFF 00093000
01/09/2007 09:06:09,spid59,Unknown,* GDI32 77C00000 77C47FFF 00048000
01/09/2007 09:06:09,spid59,Unknown,* USER32 77380000 77411FFF 00092000
01/09/2007 09:06:09,spid59,Unknown,* RPCRT4 77C50000 77CEEFFF 0009f000
01/09/2007 09:06:09,spid59,Unknown,* ADVAPI32 77F50000 77FEBFFF 0009c000
01/09/2007 09:06:09,spid59,Unknown,* MSVCP80 7C420000 7C4A6FFF 00087000
01/09/2007 09:06:09,spid59,Unknown,* msvcrt 77BA0000 77BF9FFF 0005a000
01/09/2007 09:06:09,spid59,Unknown,* MSVCR80 78130000 781CAFFF 0009b000
01/09/2007 09:06:09,spid59,Unknown,* kernel32 77E40000 77F41FFF 00102000
01/09/2007 09:06:09,spid59,Unknown,* ntdll 7C800000 7C8BFFFF 000c0000
01/09/2007 09:06:09,spid59,Unknown,* sqlservr 01000000 02C09FFF 01c0a000
01/09/2007 09:06:09,spid59,Unknown,* MODULE BASE END SIZE
01/09/2007 09:06:09,spid59,Unknown,*
01/09/2007 09:06:09,spid59,Unknown,*
01/09/2007 09:06:09,spid59,Unknown,* ry order by peerTotal desc
01/09/2007 09:06:09,spid59,Unknown,* != 'NULL' and peerCountry != 'NULL' group by peerCountryCode<c/> peerCount
01/09/2007 09:06:09,spid59,Unknown,* y) desc) peerTopCity from divinityTorrentPeers B where peerCountryCode
01/09/2007 09:06:09,spid59,Unknown,* CountryCode = B.peerCountryCode group by peerCity order by count(peerCit
01/09/2007 09:06:09,spid59,Unknown,* Peers where peerCity != 'Determining' AND peerCountry != 'NULL' and peer
01/09/2007 09:06:09,spid59,Unknown,* ountryCode<c/> B.peerCountry<c/> (select top 1 peerCity from Torrent
01/09/2007 09:06:09,spid59,Unknown,* SET XACT_ABORT ON; select top 10 count(*) peerTotal<c/> B.peerC
01/09/2007 09:06:09,spid59,Unknown,* Input Buffer 510 bytes -
01/09/2007 09:06:09,spid59,Unknown,* Access Violation occurred reading address 00000000
01/09/2007 09:06:09,spid59,Unknown,* Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
01/09/2007 09:06:09,spid59,Unknown,* Exception Address = 013B6891 Module(sqlservr+003B6891)
01/09/2007 09:06:09,spid59,Unknown,*
01/09/2007 09:06:09,spid59,Unknown,*
01/09/2007 09:06:09,spid59,Unknown,* Private server build.
01/09/2007 09:06:09,spid59,Unknown,* 01/09/07 09:06:09 spid 59
01/09/2007 09:06:09,spid59,Unknown,* BEGIN STACK DUMP:
01/09/2007 09:06:09,spid59,Unknown,*
01/09/2007 09:06:09,spid59,Unknown,* *******************************************************************************|||Hmm.. I am also getting this error, even after installing the new Sp2 CTP patch.... this is bad that no one has even responded to this yet..

Bug with GetFloat?

I have field defined as float (price).

when I do the following command I get invalid cast.

reader.GetFloat(reader.GetOrdinal("price"));

I have to pull it with GetDouble.

reader.GetDouble (reader.getOrdinal("price"));

which works.

since it's defined as a float would that not make this an error.. or am I missing something?

GetFloat and GetDouble don't do any conversions. GetFloat expects a single precision and GetDouble a double precision value. Since the SQL Float is double precision you'll get an casting error with GetFloat. You could use GetFloat with a real field.

See "SQL Server Data Types and Their .NET Framework Equivalents" in books online for more info.

|||
When dealing with currency, why not create price as a sql type 'money'?

reader.GetDecimal (reader.getOrdinal("price")) would then work for you without throwing any casting exceptions.

Bug with GetFloat?

I have field defined as float (price).

when I do the following command I get invalid cast.

reader.GetFloat(reader.GetOrdinal("price"));

I have to pull it with GetDouble.

reader.GetDouble (reader.getOrdinal("price"));

which works.

since it's defined as a float would that not make this an error.. or am I missing something?

GetFloat and GetDouble don't do any conversions. GetFloat expects a single precision and GetDouble a double precision value. Since the SQL Float is double precision you'll get an casting error with GetFloat. You could use GetFloat with a real field.

See "SQL Server Data Types and Their .NET Framework Equivalents" in books online for more info.

|||
When dealing with currency, why not create price as a sql type 'money'?

reader.GetDecimal (reader.getOrdinal("price")) would then work for you without throwing any casting exceptions.

Bug in the OLE DB command?

This command has no error:

update art_anz set anz = anz where artnr = 'xxxxxxx'

declare @.artnr as varchar(10)
set @.artnr = ?

But this command has an error:

--update art_anz set anz = anz where artnr = 'xxxxxxx'

declare @.artnr as varchar(10)
set @.artnr = ?

The difference is the first line. When you use parameters (?) in the OLE DB-Command, the very first line has to be a Non-Select SQL-Statement.

The SQL-Statement do nothing!

When you have no parameters, you can write normal T-SQL-Code and you get no error.

I think, this is a bug!!

It certainly sounds like it could be a bug. Could you log it at the feedback centre: http://lab.msdn.microsoft.com/productfeedback/default.aspx

-Jamie

bug in string processing if the GO keyword is inside the string

I encoutered a strange behavior using the exec command and I could repreduce
the behavior with the print command:
the command:
print '1
2
3'
is doing it's jub, but if i add go inside the string I get the floowing
error:
print '1
2
go
3'
Server: Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark before the character string '1
2
'.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '1
2
'.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '3'.
Server: Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark before the character string '
'.
can someone explain that or point me to a fix I'm using SQL 2000 SP4 on
windows 2003 server with service pack 1
this problem also occurs is the string is sent as a parameter to a stored
procedure using the exec command.
please helpMartin,
This is not a bug. Go is a command that tells SQL Server that this is the
end of a batch of T-SQL statements. If you execute some code in Query
Analyzer and one line has the go word alone, SQL Server will take this as th
e
Go command. For example, try this
print '1
2
go 3
4'
Anyway, I would avoid the go word at the beginning of a line, if possible.
Ben Nevarez, MCDBA, OCP
Database Administrator
"martin" wrote:

> I encoutered a strange behavior using the exec command and I could repredu
ce
> the behavior with the print command:
> the command:
> print '1
> 2
> 3'
> is doing it's jub, but if i add go inside the string I get the floowing
> error:
> print '1
> 2
> go
> 3'
> Server: Msg 105, Level 15, State 1, Line 1
> Unclosed quotation mark before the character string '1
> 2
> '.
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near '1
> 2
> '.
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near '3'.
> Server: Msg 105, Level 15, State 1, Line 1
> Unclosed quotation mark before the character string '
> '.
>
> can someone explain that or point me to a fix I'm using SQL 2000 SP4 on
> windows 2003 server with service pack 1
> this problem also occurs is the string is sent as a parameter to a stored
> procedure using the exec command.
> please help
>
>|||well, ben,
of course it's a bug
if I get a string as input from a user of a web application, and encode the
string as required like replacing a single apostrophe ' with 2 '' in order
to not break the SQL syntax the same goes with the GO keyword or other
keyword like SELECT.
what exactly should I do in order to pass this kind of a parameter to a
stored procedure? encode it with some way to it's numric ascii
representation?
just image that I will ask you to avoid the END keyword in the beggining of
a sentence in your reply to me, would that not be considered as a bug?
not all string passes as a parameter to a stored procedure are in my control
at all. most of them are not.
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:968CB5C7-A808-4767-BAAC-C2F59114E390@.microsoft.com...
> Martin,
> This is not a bug. Go is a command that tells SQL Server that this is the
> end of a batch of T-SQL statements. If you execute some code in Query
> Analyzer and one line has the go word alone, SQL Server will take this as
> the
> Go command. For example, try this
> print '1
> 2
> go 3
> 4'
> Anyway, I would avoid the go word at the beginning of a line, if possible.
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "martin" wrote:
>|||martin wrote:
> well, ben,
> of course it's a bug
> if I get a string as input from a user of a web application, and encode th
e
> string as required like replacing a single apostrophe ' with 2 '' in order
> to not break the SQL syntax the same goes with the GO keyword or other
> keyword like SELECT.
>
If you accept string input in that manner and use it for dynamic SQL
then your web application is buggy, dangerous and insecure. This
problem is called SQL Injection and is one important reason why you
should never create dynamic strings out of unverified,
non-parameterized user input. The proper and safe way to do it is to
use parameters in your client code (the ADO parameters collection if
you are using ADO for example).
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
--|||martin
> what exactly should I do in order to pass this kind of a parameter to a
> stored procedure? encode it with some way to it's numric ascii
> representation?
It is not a bug, please post exactly what are you doing in order to help
you?
"martin" <news.microsoft.com> wrote in message
news:eAcI3PCbGHA.3812@.TK2MSFTNGP04.phx.gbl...
> well, ben,
> of course it's a bug
> if I get a string as input from a user of a web application, and encode
> the string as required like replacing a single apostrophe ' with 2 '' in
> order to not break the SQL syntax the same goes with the GO keyword or
> other keyword like SELECT.
> what exactly should I do in order to pass this kind of a parameter to a
> stored procedure? encode it with some way to it's numric ascii
> representation?
> just image that I will ask you to avoid the END keyword in the beggining
> of a sentence in your reply to me, would that not be considered as a bug?
> not all string passes as a parameter to a stored procedure are in my
> control at all. most of them are not.
>
>
>
>
>
> "Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
> news:968CB5C7-A808-4767-BAAC-C2F59114E390@.microsoft.com...
>|||well,
so please explain that:
my app does uses ADO.NET and ado.net succeedes to execute SQL statements
that fails to execute in query analyzer.
is there some magic here?
after executing stored procedure with command object, adding a string
parameter
in the profiler I see the following SQL statement executed from ADO.NET:
declare @.P1 int
set @.P1=33
exec sp_insert_string '748F4655-9106-4D45-A0A2-1AA95C4C8912', 2, N'test',
N'--test
go
-- test
', N'asd', N'James', NULL, @.P1 output
select @.P1
the same stored procedure fails to execute from query analyzer.
I thought the answer will be in some changes to the default behavior of
ADO.NET so I also executed the line performed by ADO.NET to set default
execution variables, with no help:
-- network protocol: TCP/IP
set quoted_identifier on
set implicit_transactions off
set cursor_close_on_commit off
set ansi_warnings on
set ansi_padding on
set ansi_nulls on
set concat_null_yields_null on
set language us_english
set dateformat mdy
set datefirst 7
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1146383014.820122.76100@.j33g2000cwa.googlegroups.com...
> martin wrote:
> If you accept string input in that manner and use it for dynamic SQL
> then your web application is buggy, dangerous and insecure. This
> problem is called SQL Injection and is one important reason why you
> should never create dynamic strings out of unverified,
> non-parameterized user input. The proper and safe way to do it is to
> use parameters in your client code (the ADO parameters collection if
> you are using ADO for example).
> --
> 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
> --
>|||I've just checked this out on SSMS the SQL 2005 replacement for Query
Analyser.
The problem has been fixed as the print command works perfectly.
The are problems with the Query Analyser SQL interpreter. In a nutshell,
avoid placing Go on it's own at the begginning of the line.
You could change your calling code to do something like
Print '1
2
' + 'Go
3
4'
It's a bodge I know, but it should work ok.
To "Fix" the problem you should upgrade to SQL2005, as I doubt that there
will be another SP for SQL2000.
As mentioned by one of the other posters, you need to make sure that you are
not suseptable to a SQL Injection attack.
OK, you might say that all user input must go through your front end, and
that's 100% secure. But consider the what if scenario.
The simplest way to avoid injection attacks is to use Parameterized queries,
or stored procedures.
The important thing is that any Dynamic SQL uses the sp_executesql command
and uses the parameters properly. i.e. Do not build your query like it's
adhoc sql.
Colin.
"martin" <news.microsoft.com> wrote in message
news:%23GW81yBbGHA.3916@.TK2MSFTNGP03.phx.gbl...
>I encoutered a strange behavior using the exec command and I could
>repreduce the behavior with the print command:
> the command:
> print '1
> 2
> 3'
> is doing it's jub, but if i add go inside the string I get the floowing
> error:
> print '1
> 2
> go
> 3'
> Server: Msg 105, Level 15, State 1, Line 1
> Unclosed quotation mark before the character string '1
> 2
> '.
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near '1
> 2
> '.
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near '3'.
> Server: Msg 105, Level 15, State 1, Line 1
> Unclosed quotation mark before the character string '
> '.
>
> can someone explain that or point me to a fix I'm using SQL 2000 SP4 on
> windows 2003 server with service pack 1
> this problem also occurs is the string is sent as a parameter to a stored
> procedure using the exec command.
> please help
>|||martin wrote:
> well,
> so please explain that:
> my app does uses ADO.NET and ado.net succeedes to execute SQL statements
> that fails to execute in query analyzer.
> is there some magic here?
> after executing stored procedure with command object, adding a string
> parameter
> in the profiler I see the following SQL statement executed from ADO.NET:
> declare @.P1 int
> set @.P1=33
> exec sp_insert_string '748F4655-9106-4D45-A0A2-1AA95C4C8912', 2, N'test',
> N'--test
> go
> -- test
> ', N'asd', N'James', NULL, @.P1 output
> select @.P1
> the same stored procedure fails to execute from query analyzer.
>
GO is not a T-SQL statement. It is a batch separator used by Query
Analyzer and the other client utilities so this behaviour is correct.
BTW you sould not use the sp_ prefix for user procs. sp_ is reserved
for system procs and may adversely affect performance and reliability
if used in databases other than Master.
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
--|||Ben Nevarez (BenNevarez@.discussions.microsoft.com) writes:
> This is not a bug. Go is a command that tells SQL Server that this is the
> end of a batch of T-SQL statements.
No. GO is just an identifier as far as SQL Server is concerned. That is,
it is not a command or anything.
However, it is a command that is used by many client-tools to signify
the end of batch, that's true.
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