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 when running sp at package

Dear all

i have a stored procedure which run in package. I find out that the stored

procedure stop at the running after 1 seconds, but the pacakage reports the

success.

this stored procedure can run at query analyzer normally.

I tried to adjust the command time-out at the execute sql task properties

object to 9999 or 0 or 1800. However, I find out it is useless to extend the

executive time.

The case happens in two sql server 2000 which is installed in win 2k server

and nt server 5.0. So, I guess it is a sql server bug.

Please give me some suggestion. whether a sql server has this bug? how can i

fix it?

thx
alex

my code is as follow.

CREATE PROCEDURE usp_UpdateRGNUPC
AS
declare @.upcVar varchar(13), @.skuVar varchar(9), @.strStore varchar(7), @.tempSql varchar(1000)

declare varTempShop cursor
local
static
FOR select distinct Shop from strmst
OPEN varTempShop
fetch next from varTempShop into @.strStore
while @.@.FETCH_STATUS = 0
begin
declare varTemp cursor
FOR SELECT r.upc, r.sku
FROM rgnupc r
INNER JOIN strmst s ON r.rgnid = s.rgnid
WHERE s.Shop = @.strStore
OPEN varTemp
fetch next from varTemp into @.upcVar, @.skuVar
while @.@.FETCH_STATUS = 0
begin
set @.tempSql = 'select * from ' + @.strStore + '.dbo.invupc where upc = ''' + @.upcVar + ''''
exec(@.tempSql)

if (@.@.rowcount>0)
begin
set @.tempSql = 'Update ' + @.strStore + '.dbo.invupc set sku = ''' + @.skuVar + ''' where upc = ''' + @.upcVar + ''''
exec(@.tempSql)
end
else
begin
set @.tempSql = 'Insert into ' + @.strStore + '.dbo.invupc (upc, sku) values (''' + @.upcVar + ''',''' + @.skuVar + ''')'
exec(@.tempSql)
end

fetch next from varTemp into @.upcVar, @.skuVar
end
close varTemp
deallocate varTemp

fetch next from varTempShop into @.strStore
end
close varTempShop
deallocate varTempShop
GO

one sql server 2000 is service pack 3. another is service pack 4.

Alex

Bug in jdbc driver...

Error:
SQLException caught: [Microsoft][SQLServer 2000 Driver for JDBC]Column index
7 is out of range.
SQLException caught:.
-----
The procedure follows (slightly modified for security reasons):

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE dbo.xprHSPMilestoneData (@.templateid int, @.groupid int) AS
DECLARE @.DataSQL varchar(6000)
DECLARE @.HeadCount int
DECLARE @.CurrentColumn datetime
DECLARE @.ColorColumn varchar(11)
BEGIN
DECLARE Head_cursor CURSOR FOR
SELECT milestone
FROM dbo.vwTrackingGroupDurMS
WHERE templateid = @.templateid AND groupid = @.groupid
OPEN Head_cursor
SET @.DataSQL = 'SELECT ''DSN Workflow Milestones'' AS MSLabel,
NULL'
SET @.HeadCount = 1
FETCH NEXT FROM Head_cursor INTO @.CurrentColumn
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.DataSQL = @.DataSQL + ',''' +
convert(char(10),@.CurrentColumn,101) + ''' as Col' + CONVERT(varchar(2),
@.HeadCount) + 'MS'
SET @.HeadCount = @.HeadCount + 1
FETCH NEXT FROM Head_cursor INTO @.CurrentColumn
END
CLOSE Head_cursor
DEALLOCATE Head_cursor
--Loop thru the colors for each milestone
DECLARE Head_cursor CURSOR FOR
SELECT color
FROM dbo.vwTrackingGroupDurMX
WHERE templateid = @.templateid AND groupid = @.groupid
OPEN Head_cursor
SET @.HeadCount = 1
FETCH NEXT FROM Head_cursor INTO @.ColorColumn
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.DataSQL = @.DataSQL + ',''' + @.ColorColumn +
''' as Col' + CONVERT(varchar(2), @.HeadCount) + 'Color'
SET @.HeadCount = @.HeadCount + 1
FETCH NEXT FROM Head_cursor INTO @.ColorColumn
END
CLOSE Head_cursor
DEALLOCATE Head_cursor
EXECUTE (@.DataSQL)
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Microsoft Support Information for this error:
-----
FIX: "Column index <index> out of range" error when you try to read a field
from a JDBC ResultSet object
View products that this article applies to.
Article ID : 838610
Last Review : March 29, 2005
Revision : 2.0
SYMPTOMS
You have a Java application that uses the Java Database Connectivity (JDBC)
API to retrieve a ResultSet
object by using the Microsoft SQL Server 2000 Driver for JDBC. When you try
to read a field from the
ResultSet object by using the methods in the JDBC API, you may receive an
error message
that is similar to the following:
java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]Column
index <index> is out of range.
Note <index> is a placeholder for the index of the column that is being
retrieved.
This problem occurs when all the following conditions are true:
? The Select Transact-SQL statement that is used to retrieve the
data from the SQL Server tables to the
ResultSet object contains a JOIN between different tables in the SQL
Server database.
? The Select Transact-SQL statement uses asterisk (*) to retrieve
all fields from the respective tables.
RESOLUTION
A supported hotfix is now available from Microsoft, but it is only intended
to correct the problem that is described in
this article. Only apply it to systems that are experiencing this specific
problem. This hotfix may receive
additional testing. Therefore, if you are not severely affected by this
problem, Microsoft recommends
that you wait for the next service pack that contains this hotfix.
How do I obtain this hotfix?
Michael Benedict wrote:

> Error:
>
> SQLException caught: [Microsoft][SQLServer 2000 Driver for JDBC]Column index
> 7 is out of range.
I looked at your procedure. Are you absolutely sure
your constructed SQL string got to selecting 7 fields?
I would have the procedure return the SQL string, just for
debugging, and/or do the typical resul set metadata processing
to see how many columns were really returned. The bug you cite
seems to be about *any* row index, under conditions your
procedure doesn't have...
Joe Weinstein at BEA

> SQLException caught:.
> -----
> The procedure follows (slightly modified for security reasons):
>
> ----
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS OFF
> GO
> CREATE PROCEDURE dbo.xprHSPMilestoneData (@.templateid int, @.groupid int) AS
>
> DECLARE @.DataSQL varchar(6000)
> DECLARE @.HeadCount int
> DECLARE @.CurrentColumn datetime
> DECLARE @.ColorColumn varchar(11)
>
> BEGIN
> DECLARE Head_cursor CURSOR FOR
> SELECT milestone
> FROM dbo.vwTrackingGroupDurMS
> WHERE templateid = @.templateid AND groupid = @.groupid
>
> OPEN Head_cursor
> SET @.DataSQL = 'SELECT ''DSN Workflow Milestones'' AS MSLabel,
> NULL'
> SET @.HeadCount = 1
> FETCH NEXT FROM Head_cursor INTO @.CurrentColumn
>
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> SET @.DataSQL = @.DataSQL + ',''' +
> convert(char(10),@.CurrentColumn,101) + ''' as Col' + CONVERT(varchar(2),
> @.HeadCount) + 'MS'
> SET @.HeadCount = @.HeadCount + 1
> FETCH NEXT FROM Head_cursor INTO @.CurrentColumn
> END
> CLOSE Head_cursor
> DEALLOCATE Head_cursor
>
> --Loop thru the colors for each milestone
> DECLARE Head_cursor CURSOR FOR
> SELECT color
> FROM dbo.vwTrackingGroupDurMX
> WHERE templateid = @.templateid AND groupid = @.groupid
>
> OPEN Head_cursor
>
> SET @.HeadCount = 1
> FETCH NEXT FROM Head_cursor INTO @.ColorColumn
>
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> SET @.DataSQL = @.DataSQL + ',''' + @.ColorColumn +
> ''' as Col' + CONVERT(varchar(2), @.HeadCount) + 'Color'
> SET @.HeadCount = @.HeadCount + 1
> FETCH NEXT FROM Head_cursor INTO @.ColorColumn
> END
>
> CLOSE Head_cursor
> DEALLOCATE Head_cursor
>
> EXECUTE (@.DataSQL)
> END
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
>
> ----
> Microsoft Support Information for this error:
> -----
>
> FIX: "Column index <index> out of range" error when you try to read a field
> from a JDBC ResultSet object
> View products that this article applies to.
>
> Article ID : 838610
> Last Review : March 29, 2005
> Revision : 2.0
> SYMPTOMS
>
> You have a Java application that uses the Java Database Connectivity (JDBC)
> API to retrieve a ResultSet
> object by using the Microsoft SQL Server 2000 Driver for JDBC. When you try
> to read a field from the
> ResultSet object by using the methods in the JDBC API, you may receive an
> error message
> that is similar to the following:
>
> java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]Column
> index <index> is out of range.
> Note <index> is a placeholder for the index of the column that is being
> retrieved.
>
> This problem occurs when all the following conditions are true:
>
> ? The Select Transact-SQL statement that is used to retrieve the
> data from the SQL Server tables to the
> ResultSet object contains a JOIN between different tables in the SQL
> Server database.
> ? The Select Transact-SQL statement uses asterisk (*) to retrieve
> all fields from the respective tables.
> RESOLUTION
>
> A supported hotfix is now available from Microsoft, but it is only intended
> to correct the problem that is described in
> this article. Only apply it to systems that are experiencing this specific
> problem. This hotfix may receive
> additional testing. Therefore, if you are not severely affected by this
> problem, Microsoft recommends
> that you wait for the next service pack that contains this hotfix.
> How do I obtain this hotfix?

Bug in a Stored Procedure [sys].[sp_dbmmonitorupdate]

Hi guys,

I found an annoying bug in the system sproc [sys].[sp_dbmmonitorupdate]. There in line
308 : set @.database_name = db_name( @.database_id )
it should basically state
set @.database_name = QUOTENAME(db_name( @.database_id ))
because if a database has a space or point in the name this causes the sproc to fail with the error:
"Incorrect syntax near '.'."

has someone any idea how to change this sys sproc?
or an idea for a workaround?

thanks...
darkook, let me ask the other way around:

i cannot change the sys sproc, can I? in that case I need a sproc which is deployed in the msdb (there is a check in the sp_dbmmonitorupdate which says it must be executed in the context of the msdb). how ever, I understand that no sprocs should be deployed in msdb. any ideas how to solve this problem?

thx!

buffer pool only has data pages ?

Or does the buffer pool include procedure cache as well ?
I know in sql 2000, they have this mem to leave concept that I have heard is
no longer referred to in 2005. So whats it called in 2005?
ThanksCome on now Hassan this is getting beyond petty. Do you really expect
everyone to spoon feed you everything about SQL Server just because you
don't want to read a book or do a simple Google search. The answer to this
is certainly something you should already know by now but can be found in at
least 20 places with little trouble. Heck even BooksOnLine can answer that
question easily. 95% of the questions you ask here can be found in "Inside
SQL Server 2005" alone. Do everyone including yourself a favor and buy the
book and take a little time to read it. Or at least the sections that are
relevant to what you need to know at the time. You won't regret it.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Hassan" <hassan@.test.com> wrote in message
news:Om2ZjtcZIHA.4696@.TK2MSFTNGP05.phx.gbl...
> Or does the buffer pool include procedure cache as well ?
> I know in sql 2000, they have this mem to leave concept that I have heard
> is no longer referred to in 2005. So whats it called in 2005?
> Thanks|||Andrew J. Kelly wrote:
> Come on now Hassan this is getting beyond petty. Do you really expect
> everyone to spoon feed you everything about SQL Server just because
> you don't want to read a book or do a simple Google search. The answer
> to this is certainly something you should already know by now but can
> be found in at least 20 places with little trouble. Heck even
> BooksOnLine can answer that question easily. 95% of the questions you
> ask here can be found in "Inside SQL Server 2005" alone. Do everyone
> including yourself a favor and buy the book and take a little time to
> read it. Or at least the sections that are relevant to what you need
> to know at the time. You won't regret it.
>
And neither will we :)

Bring Database Online

Hi,

One of our database size 90 Gb was backed up and restored on a standyby server with NoRecovery option .A Stored Procedure was written to do production Database transaction log Backup every 15 min and restore the Tlog File on Standby server.

Once the last transaction log file is restored on standby server the tlog file is deleted.

Now due to some reason the restore tlog job failed but the tlog file got deleted.

Now till we will not restore the last database backup we will not be able to start the restore tlog job.

Now the database is in restoring mode.Is there any methord to get that database online without any transactionlog.

Regards

Sufian

Inorder to bring the db online try the following,

Restore database DBName with Recovery....just let me know if the db has come online.......

|||You could just bring it online, however, a better option may be to perform a differential backup and restore that backup with norecovery. This will include all of the transactions since the last backup, and should allow you to resume your trans log backups once it has been applied.
Tim

both .net transaction and stored procedure transaction together

Hi ALL

I want to know that is it possible that i use the .net transaction and inside of that i use tha stored procedure transaction ,nested transaction with .net and stored procedure?

is it like the nested transaction in stored procedure that we use both 2 transactions in stored procedure or not ?

thanks

To use .NET code in SQL you need to enable CLR Integration via the Surface Area Configuration Utility. This need only be done once.

Next you need to compile your .NET assembly to a DLL. This DLL will be called into MS SQL.

From within MS SQL you load the assembly by using the statement:

CREATE ASSEMBLY <ass_name>
FROM '<path to dll>'

CREATE function <function name> (<any parameters>)
returns <any return value>
[with returns null on null input]
external name <ass_name>.[<dll ns.class>].<dll method>

The .NET assembly can be removed once it is pulled into MS SQL since the binary is from that point onwards referenced internally.

|||Shouldn't a transaction be atomic? Why would you use 2 transactions?|||

The transaction is atomic. Once you import the CLR code into the assembly (a one time processes) and you define the T-SQL function to call the imported code you have an atomic function.

The only difference is that MS SQL's internal .NET Framework will be running the code, rather than the T-SQL interpretur.

Hope this helps.

Al

|||

Thanks, but I was answering on the original question ;-)

|||SQL Server has no support for nested transactions. Nesting of transactions only increments @.@.TRANCOUNT and it is the final commit that has control over the outcome of the entire transaction. You should however take a look at the new TransactionScope class in .NET Framework 2.0 which has promotable transactions concept. It is not clear if you want to link the .NET transaction with the SQL transaction and this can be done by using the TransactionScope class i.e., if multiple resource managers are involved then transaction automatically becomes a distributed transaction.