Geoff
After I added the ' I get the error as below

Msg 156, Level 15, State 1, Line 37
Incorrect syntax near the keyword 'DEALL DECLARE @sql nvarchar(MAX),
@db sysname,
@sp_executesql nvarchar(512),
@hastable bit

SELECT @sql = 'update XBANKINFO set BankReqSig2 = ''1'',
Signature1 = ''X:\SIGNATURES\NEOSPINE\SIGNER1\DDEAN1.BMP'',
Signature1always = ''1'',
Signature1Limit = ''0'',
Signature2 = ''X:\SIGNATURES\NEOSPINE\SIGNER2\DBELL1.BMP'',
Signature2Limit = ''0.01'',
Signature2Valid = ''1'',
Signature2ValidMsg = ''** Not valid without two signatures **'',
Void = ''1'',
VoidMsg = ''** void after 90 days **''

DECLARE dbcur CURSOR STATIC LOCAL FOR
SELECT name FROM sys.databases

OPEN dbcur

WHILE 1 = 1
BEGIN
FETCH dbcur INTO @db
IF @@fetch_status <> 0
BREAK

SELECT @sp_executesql = quotename(@db) + ..sp_executesql'

EXEC @sp_executesql N'SELECT @id = object_name(''XBANKINFO'')',
N'@id int OUTPUT', @hastable OUTPUT

IF @hastable = 1
EXEC @sp_executesql @sql
END

DEALLOCATE dbcur
OCATE'.



I used the updated query as below


"Geoff Schaller" <geoffx (AT) softxwareobjectives (DOT) com.au> wrote

You're trying to do too much at once :-).

The SQL you provided has all sorts of syntax issues but I don't know
whether that is in the cut and paste to the forum or whether they are
genuine. What happens when you run the query directly in SSMS without
all the wrapper stuff.

Well this is clear isn't it? A syntax error. What is DEALL DECLARE... ?

It isn't in your post so what other syntax issues are you going to make
us find for you when you can't cut/paste properly for us.

Check your syntax. Verify every component in SSMS first.

Geoff




"Sammy" <s_commar (AT) hotmail (DOT) com> wrote

Sammy (s_commar (AT) hotmail (DOT) com) writes:
My posting also included this caveat:

This may work. Well, most likely it does not, because it is not
tested, or even checked for syntax correctness, but it should
get you going.

I also made the effort to explain the idea.

OK, so maybe I should not expect everyone to understand all syntax errors,
but if this is a complaint I have to ask: are you prepared to make the
slightest effort yourself to find a solution?`

Excuse me, if I'm harsh, but I love to help people to help themselves.
It is my hope that each time I make a suggestion that the person that
I help will learn something, and next time a similar problem appears
you are able to find the solution on your own - or at least ask a
question one step later in the process.

But if you are not willing to deal with a syntax error from a stray
character (on my keyboard "ä" and "'" are next to each other), then I
get the uneasy feeling that you want to be spoon-fed. And I will have
to admit that is far less appealing. At least not as long it's a
consultancy gig with a pay-check. (But even when I work as a consultant
I like to see my client to learn something in the process.)

Here is a cleaned-up version which I've tested so that that it compiles
and the loop runs. But since I don't have any XBANKINFO table on my
system, I can't test that part. So again, I urge you: run this on a
test system (copy a few of the Dynamics databases to that server).

If you are unacquainted with dynamic SQL, I have a longer article on my
web site about the topic: http://www.sommarskog.se/dynamic_sql.html.

DECLARE @sql nvarchar(MAX),
@db sysname,
@sp_executesql nvarchar(512),
@hastable bit

SELECT @sql = 'update XBANKINFO set BankReqSig2 = ''1'',
Signature1 = ''X:\SIGNATURES\NEOSPINE\SIGNER1\DDEAN1.BMP'',
Signature1always = ''1'',
Signature1Limit = ''0'',
Signature2 = ''X:\SIGNATURES\NEOSPINE\SIGNER2\DBELL1.BMP'',
Signature2Limit = ''0.01'',
Signature2Valid = ''1'',
Signature2ValidMsg = ''** Not valid without two signatures **'',
Void = ''1'',
VoidMsg = ''** void after 90 days **'''

DECLARE dbcur CURSOR STATIC LOCAL FOR
SELECT name FROM sys.databases
WHERE state_desc = 'ONLINE'

OPEN dbcur

WHILE 1 = 1
BEGIN
FETCH dbcur INTO @db
IF @@fetch_status <> 0
BREAK

SELECT @sp_executesql = quotename(@db) + '..sp_executesql'

EXEC @sp_executesql N'SELECT @id = object_id(''XBANKINFO'')',
N'@id int OUTPUT', @hastable OUTPUT

IF @hastable = 1
EXEC @sp_executesql @sql
END

DEALLOCATE dbcur



--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

Thanks so much Tom. That worked perfectly. Really appreciate your help on
this

"Tom Cooper" <tomcooper (AT) comcast (DOT) net> wrote

I have a question I have a similiar issue. I am trying to run this below on 200+ db on the same server as well. I tried to add the sp_msForEachDb but its erroring out with
Cannot find the user 'DYNGRP', because it does not exist or you do not have permission.

If I do one by one it works fine. Thanks in advance

/*Count : 1 */

declare @cStatement varchar(255)

declare G_cursor CURSOR for select 'grant select,update,insert,delete on [' + convert(varchar(64),name) + '] to DYNGRP' from sysobjects
where (type = 'U' or type = 'V') and uid = 1

set nocount on
OPEN G_cursor
FETCH NEXT FROM G_cursor INTO @cStatement
WHILE (@@FETCH_STATUS <> -1)
begin
EXEC (@cStatement)
FETCH NEXT FROM G_cursor INTO @cStatement
end
DEALLOCATE G_cursor

declare G_cursor CURSOR for select 'grant execute on [' + convert(varchar(64),name) + '] to DYNGRP' from sysobjects
where type = 'P'

set nocount on
OPEN G_cursor
FETCH NEXT FROM G_cursor INTO @cStatement
WHILE (@@FETCH_STATUS <> -1)
begin
EXEC (@cStatement)
FETCH NEXT FROM G_cursor INTO @cStatement
end
DEALLOCATE G_cursor