Precompiling checking problem

i have a stored procedure i want to save
the commands all execute fine if i execute them one at a time, but when i
try to create a sp out of the script, it fails
problem... my sp adds a coulmn to a table in the begining, does some
processing and uses that column, then drops it.
the process that saves the sp tries to validate each command individually
instead of as a progression so obviously a select statement with the new
column is not valid on its own and it wont process the script.
is their any directive in QA that can turn off this checking?
i have a work around, build the query dynamically, but this is a pain as far
as im concerned.
any suggestions?
not sure I understand completely, BUT I think your problem may be resolved
by placing GO statements after each step in the sproc.
try that (if you havent already) and script the thing out and paste it in
here if that still does not work..
that we we can all have a look at it and get through the guesswork.
Greg Jackson
PDX, Oregon
|||example:
table1 has column1, column2, column3
i create a script to make a stored procedure in query analyzer:
create proc test1 as
alter table to add column4
update table1 to set column4 = to something
update table1 to set other columns to something
alter table to drop column4
go
1) execute each internal line seperately, each runs without error
2) execute script to create the proc, ERROR cause it checks each command
before creating the proc and since the second line sets column4 to
something, and column4 is not there right now, its an invalid command and
the proc isnt created
3) cant put go's between the commands in a stored procedure and even if you
could, the system still wont save the procedure cause line2 is still invalid
at this moment in time.
|||Claude Hebert wrote:
> example:
> table1 has column1, column2, column3
> i create a script to make a stored procedure in query analyzer:
> create proc test1 as
> alter table to add column4
> update table1 to set column4 = to something
> update table1 to set other columns to something
> alter table to drop column4
>
Without trying to understand what you are doing, the problem is that
each of those statements must be in its own batch. You have to use
dynamic sql to do what you want:
create table dbo.test1 (col1 int)
go
insert into dbo.test1 values (1)
create proc dbo.test2 as
begin
EXEC ('alter table dbo.test1 add column4 int')
select * from dbo.test1
EXEC ('update dbo.test1 set column4 = 5')
select * from dbo.test1
EXEC ('alter table dbo.test1 drop column column4')
select * from dbo.test1
end
go
exec test2
drop proc dbo.test2
create table dbo.test1
David Gugick
Imceda Software
www.imceda.com
|||i know that works, and this is a very simple case...
because of the size of our project, the number of procedures, the number of
lines in those procedures that would have to be moved into EXEC statements,
i was trying to find another way...
i was just hopping there was a set something that i could do in QA to allow
the code to run without all the EXEC's
im guessing at this point, the answer is NO
|||Hi Greg
GO is a batch separator for the client, it is not a SQL statement. GO tells
the CLIENT to send a set of commands to SQL Server separately (that is what
a batch is).
GO is not possible inside a stored procedure, which always executes within a
single batch. In fact, if you are trying to create a stored proc in the
Query Analyzer, as soon as a GO is entered, that is the end of the
procedure.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:eG51le%23VFHA.2616@.TK2MSFTNGP14.phx.gbl...
> not sure I understand completely, BUT I think your problem may be resolved
> by placing GO statements after each step in the sproc.
> try that (if you havent already) and script the thing out and paste it in
> here if that still does not work..
> that we we can all have a look at it and get through the guesswork.
>
> Greg Jackson
> PDX, Oregon
>
|||yep my bad.
I didnt read carefully enough. I though he was talking about a SQL Script.
GAJ

Pre snapshot to apply permissions

Hi.
I would like to apply the objects' permissions after to apply an snapshot.
To do it, I need a script file, with the grant commands to apply that
permissions.
Could I get that script just before applying the snapshot with any t-sql
command to generate and save the script file with these permissions?.
So if the objects' permissions have change from the last time I applied the
snapshot or i have created new objects, I can be sure that the permissions
transfered are accurate.
Thank you
Jorge,
here is a script I use which can be adapted for your needs. srvname is the
subscriber and delivery is the name of my database. I already have the
logins and users, and if you don't have them, then I'll leave you to create
that script
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
SELECT 'Grant ' +
case
when action = 193 then 'SELECT'
when action = 195 then 'INSERT'
when action = 196 then 'DELETE'
when action = 197 then 'UPDATE'
when action = 224 then 'EXECUTE'
end as Grant1,
' ON [' + delivery.dbo.sysobjects.name + '] TO ' +
delivery.dbo.sysusers.name as Grant2
--,ss.srvname
--,delivery.dbo.syspublications.name AS Publication
FROM delivery.dbo.syssubscriptions
INNER JOIN delivery.dbo.sysextendedarticlesview ON
delivery.dbo.syssubscriptions.artid =
delivery.dbo.sysextendedarticlesview.artid
INNER JOIN delivery.dbo.sysobjects ON
delivery.dbo.sysextendedarticlesview.objid =
delivery.dbo.sysobjects.id
INNER JOIN delivery.dbo.syspublications ON
delivery.dbo.sysextendedarticlesview.pubid =
delivery.dbo.syspublications.pubid
INNER JOIN master..sysservers ss on ss.srvid =
delivery.dbo.syssubscriptions.srvid
left outer JOIN delivery.dbo.sysprotects on
delivery.dbo.sysprotects.id = delivery.dbo.sysobjects.id
left outer JOIN delivery.dbo.sysusers on
delivery.dbo.sysprotects.uid = delivery.dbo.sysusers.uid
where action in (193,195,196,197,224)
--and srvname = 'xxx\PDSCOPY'
and srvname = 'yyy\PDSCOPY'
order by ss.srvname, delivery.dbo.syspublications.name,
delivery.dbo.sysobjects.name, delivery.dbo.sysusers.name
"Jorge" <Jorge@.discussions.microsoft.com> wrote in message
news:18488D2B-1F12-4D30-91C7-894EDE31D557@.microsoft.com...
> Hi.
> I would like to apply the objects' permissions after to apply an snapshot.
> To do it, I need a script file, with the grant commands to apply that
> permissions.
> Could I get that script just before applying the snapshot with any t-sql
> command to generate and save the script file with these permissions?.
> So if the objects' permissions have change from the last time I applied
the
> snapshot or i have created new objects, I can be sure that the permissions
> transfered are accurate.
> Thank you
|||Very good, Paul.
It's exactly what I was looking for
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> escribi en el mensaje
news:eXmbfaX2EHA.2192@.TK2MSFTNGP14.phx.gbl...
> Jorge,
> here is a script I use which can be adapted for your needs. srvname is the
> subscriber and delivery is the name of my database. I already have the
> logins and users, and if you don't have them, then I'll leave you to
> create
> that script
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
> SELECT 'Grant ' +
> case
> when action = 193 then 'SELECT'
> when action = 195 then 'INSERT'
> when action = 196 then 'DELETE'
> when action = 197 then 'UPDATE'
> when action = 224 then 'EXECUTE'
> end as Grant1,
> ' ON [' + delivery.dbo.sysobjects.name + '] TO ' +
> delivery.dbo.sysusers.name as Grant2
> --,ss.srvname
> --,delivery.dbo.syspublications.name AS Publication
> FROM delivery.dbo.syssubscriptions
> INNER JOIN delivery.dbo.sysextendedarticlesview ON
> delivery.dbo.syssubscriptions.artid =
> delivery.dbo.sysextendedarticlesview.artid
> INNER JOIN delivery.dbo.sysobjects ON
> delivery.dbo.sysextendedarticlesview.objid =
> delivery.dbo.sysobjects.id
> INNER JOIN delivery.dbo.syspublications ON
> delivery.dbo.sysextendedarticlesview.pubid =
> delivery.dbo.syspublications.pubid
> INNER JOIN master..sysservers ss on ss.srvid =
> delivery.dbo.syssubscriptions.srvid
> left outer JOIN delivery.dbo.sysprotects on
> delivery.dbo.sysprotects.id = delivery.dbo.sysobjects.id
> left outer JOIN delivery.dbo.sysusers on
> delivery.dbo.sysprotects.uid = delivery.dbo.sysusers.uid
> where action in (193,195,196,197,224)
> --and srvname = 'xxx\PDSCOPY'
> and srvname = 'yyy\PDSCOPY'
> order by ss.srvname, delivery.dbo.syspublications.name,
> delivery.dbo.sysobjects.name, delivery.dbo.sysusers.name
>
> "Jorge" <Jorge@.discussions.microsoft.com> wrote in message
> news:18488D2B-1F12-4D30-91C7-894EDE31D557@.microsoft.com...
> the
>