Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

t-sql not committed Expand / Collapse
Author
Message
Posted Thursday, April 02, 2009 9:10 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, May 31, 2009 10:19 AM
Points: 103, Visits: 982
HI - Masters,
I have the t-sql below in my query analiser:



update bulk_provincia set VALIDO_BULK = 1
where (numprov is not null and idprov is not null and numprov <> '' and idprov <>'')
--go
update bulk_provincia set operacao_bulk ='INSERT' where numprov in
(select numprov from bulk_provincia where valido_bulk=1 and numprov not in(select numprov from provincia))
--go
update bulk_provincia set operacao_bulk ='UPDATE' where operacao_bulk is null and valido_bulk=1
--go
delete from bulk_provincia where valido_bulk is null
--go
CREATE clustered INDEX IDX_ORDENA on bulk_provincia (operacao_bulk)
--go
declare @maxprov as int
set @maxprov=(select max(codprov)+1 from provincia)
exec('ALTER TABLE bulk_provincia add CODIGOMAXIMO INT IDENTITY ('+@maxprov+',1)')
--go
update bulk_repfiscal set VALIDO_BULK = 1
where (numrf is not null and idrf is not null and cod_provincia is not null
and numrf <> '' and idrf <>'' and cod_provincia <>'' and cod_provincia in
(select numprov from bulk_provincia union select numprov from provincia))
--go
update bulk_repfiscal set operacao_bulk ='INSERT' where numrf in
(select numrf from bulk_repfiscal where valido_bulk=1 and numrf not in(select numrf from repfiscal))
--go
update bulk_repfiscal set operacao_bulk ='UPDATE' where operacao_bulk is null and valido_bulk=1
--go
delete from bulk_repfiscal where valido_bulk is null
--go
update bulk_repfiscal set bulk_repfiscal.cod_provincia_bulk=r.codigo
from
(select codprov as codigo, numprov as numero from provincia
union
select codigomaximo as codigo, numprov as numero from bulk_provincia where valido_bulk=1
and operacao_bulk='INSERT') as r
where bulk_repfiscal.cod_provincia =r.numero


When i execute it (t-sql) with the "GO" instruction between each update or insert, it works well.
But when i put the "GO" like "--GO", so that the GO does not execute, it reases me (query analiser) the folowing error:

"Invalid column name 'codigomaximo'"

I don't understand why.
I need to remove the "GO" because i what to put all of this (t-sql) in a single stored procedure.
Can you please help?

tks,

Pedro




Post #689034
Posted Thursday, April 02, 2009 10:08 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Monday, October 07, 2013 8:15 AM
Points: 770, Visits: 1,177
Your thread subject is kinda wrong

You do NOT need the GO statements, everything should run fine
except probably the CREATE CLUSTERED INDEX part (you need a GO after I believe)
Are you sure you want that inside a Stored Procedure, create an index every call?

Also investigate the error, like column 'codigomaximo' does exist in the [bulk_provincia] table?


UPDATE  bulk_provincia
SET VALIDO_BULK = 1
WHERE (numprov IS NOT NULL
AND idprov IS NOT NULL
AND numprov <> ''
AND idprov <> ''
)
--go
UPDATE bulk_provincia
SET operacao_bulk = 'INSERT'
WHERE numprov IN (SELECT numprov
FROM bulk_provincia
WHERE valido_bulk = 1
AND numprov NOT IN (SELECT numprov
FROM provincia))
--go
UPDATE bulk_provincia
SET operacao_bulk = 'UPDATE'
WHERE operacao_bulk IS NULL
AND valido_bulk = 1
--go
DELETE FROM bulk_provincia
WHERE valido_bulk IS NULL
--go
CREATE CLUSTERED INDEX IDX_ORDENA ON bulk_provincia (operacao_bulk)
--go
DECLARE @maxprov AS INT
SET @maxprov = (
SELECT MAX(codprov) + 1
FROM provincia
)
EXEC
('ALTER TABLE bulk_provincia add CODIGOMAXIMO INT IDENTITY (' + @maxprov
+ ',1)'
)
--go
UPDATE bulk_repfiscal
SET VALIDO_BULK = 1
WHERE (numrf IS NOT NULL
AND idrf IS NOT NULL
AND cod_provincia IS NOT NULL
AND numrf <> ''
AND idrf <> ''
AND cod_provincia <> ''
AND cod_provincia IN (SELECT numprov
FROM bulk_provincia
UNION
SELECT numprov
FROM provincia)
)
--go
UPDATE bulk_repfiscal
SET operacao_bulk = 'INSERT'
WHERE numrf IN (SELECT numrf
FROM bulk_repfiscal
WHERE valido_bulk = 1
AND numrf NOT IN (SELECT numrf
FROM repfiscal))
--go
UPDATE bulk_repfiscal
SET operacao_bulk = 'UPDATE'
WHERE operacao_bulk IS NULL
AND valido_bulk = 1
--go
DELETE FROM bulk_repfiscal
WHERE valido_bulk IS NULL
--go
UPDATE bulk_repfiscal
SET bulk_repfiscal.cod_provincia_bulk = r.codigo
FROM (
SELECT codprov AS codigo
,numprov AS numero
FROM provincia
UNION
SELECT codigomaximo AS codigo
,numprov AS numero
FROM bulk_provincia
WHERE valido_bulk = 1
AND operacao_bulk = 'INSERT'
) AS r
WHERE bulk_repfiscal.cod_provincia = r.numero



SQLServerNewbie

MCITP: Database Administrator SQL Server 2005
Post #689114
Posted Thursday, April 02, 2009 3:35 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, May 31, 2009 10:19 AM
Points: 103, Visits: 982
HI- jerry tks for reply.

yes the table as the column.

You can see in the lines of the stored procedure (in read) the line:

"('ALTER TABLE bulk_provincia add CODIGOMAXIMO INT IDENTITY (' + @maxprov

+ ',1)'"


this line alters the table, addind the column codigomaximo.
But then, when i make a select to it (same lines below the creation of the column), the proc raises the error.....


If i put "GO" between each line, instead of having in the stored proc, it woks fine. It simes like the Alter table does not function without a "GO"


Can you please help?

tks,

Pedro


P.s - as to the index a allready removeit from the procedure.


Post #689415
Posted Thursday, April 02, 2009 8:53 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: 2 days ago @ 9:51 PM
Points: 4,570, Visits: 8,315
When you run any program it must be compiled first.

When SQL Server compiles your script without "GO" you table does not have that IDENTITY column yet, and compilation fails because of that.

When you separate scripts by "GO" each batch is compiled separately. So, when you compile the batch which accesses that IDENTITY column it's already created by one of previous batches.

Little hint - compiler would not mind if there would be no table at all. But it will fail if the table has incorrect structure.

And a major advice - don't modify database schema on fly. NEVER. I mean NEVER!!!
Remember - SQL Server is a multiuser environment. You may have 2 or more users executing different parts of the same code at the same time.

If there is no other way to solve the problem but alter tables ask here or Google for a solution.
Post #689561
Posted Friday, April 03, 2009 2:05 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, May 31, 2009 10:19 AM
Points: 103, Visits: 982
Hi - thank you very mutch for your reply.

In this case i can change the structure of the table because this is only a staging table.
This are tables created to do some validations to witch only i will access.

As to the problem. I need to have everything on the same stored procedure.

How can i resolve this? does anyone have ideias of how i can resolve my problem?

thank you,

Pedro
Post #689639
Posted Friday, April 03, 2009 2:51 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, February 24, 2014 1:01 AM
Points: 503, Visits: 610
Try executing the update statement which is failing as a dynamic SQL string. That way at the time it is executed the column will exist on your table.

Mike
Post #689657
Posted Friday, April 03, 2009 5:14 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, May 31, 2009 10:19 AM
Points: 103, Visits: 982
It worked. Now i can have everything on the same procedure.

But i don't understand why it worked....

let me see if it's because of this:

It worked because with dynamic SQL statements the stored procedure doesn't go and see if the columns of that table allready exist. Correct?

Then the stored procedure is compailed and during the compilation the identity column is created.

After that, when i send an execute comand, so that the stored proc is executed, the column is allready there (created during compilation) and therefour, it works fine.

Is this correct?






Other question about the same procedure:
The procedure is below:


alter proc VALIDADOR2
as

set xact_abort on
set nocount on

begin transaction


update bulk_provincia set VALIDO_BULK = 1
where (numprov is not null and idprov is not null and numprov <> '' and idprov <>'')

update bulk_provincia set operacao_bulk ='INSERT' where numprov in
(select numprov from bulk_provincia where valido_bulk=1 and numprov not in(select numprov from provincia))

update bulk_provincia set operacao_bulk ='UPDATE' where operacao_bulk is null and valido_bulk=1

delete from bulk_provincia where valido_bulk is null

declare @maxprov as int
set @maxprov=(select max(codprov)+1 from provincia)
exec('ALTER TABLE bulk_provincia add CODIGOMAXIMO INT IDENTITY ('+@maxprov+',1)')

update bulk_repfiscal set VALIDO_BULK = 1
where (numrf is not null and idrf is not null and cod_provincia is not null
and numrf <> '' and idrf <>'' and cod_provincia <>'' and cod_provincia in
(select numprov from bulk_provincia union select numprov from provincia))

update bulk_repfiscal set operacao_bulk ='INSERT' where numrf in
(select numrf from bulk_repfiscal where valido_bulk=1 and numrf not in(select numrf from repfiscal))

update bulk_repfiscal set operacao_bulk ='UPDATE' where operacao_bulk is null and valido_bulk=1

delete from bulk_repfiscal where valido_bulk is null

exec('update bulk_repfiscal set bulk_repfiscal.cod_provincia_bulk=r.codigo
from
(select codprov as codigo, numprov as numero from provincia
union
select codigomaximo as codigo, numprov as numero from bulk_provincia where valido_bulk=1
and operacao_bulk=''INSERT'') as r
where bulk_repfiscal.cod_provincia =r.numero')

declare @maxRep as int
set @maxrep=(select max(codrf)+1 from repfiscal)
exec('ALTER TABLE bulk_repfiscal add CODIGOMAXIMO INT IDENTITY ('+@maxrep+',1)')



if @@trancount >0
begin
commit transaction
end
else
rollback transaction

set nocount off


go


exec validador2






because of this statement below is now a dynamic sql statement:



exec('update bulk_repfiscal set bulk_repfiscal.cod_provincia_bulk=r.codigo
from
(select codprov as codigo, numprov as numero from provincia
union
select codigomaximo as codigo, numprov as numero from bulk_provincia where valido_bulk=1
and operacao_bulk=''INSERT'') as r
where bulk_repfiscal.cod_provincia =r.numero')


will the end of the stored procedure stiill work fine?
I ask this, because i don't know if the fact that i have dynamic SQL statements, creates other transactions.

As you can see in the above stored proc, i started the stored proc with the "Begin transaction"
And end it commiting that transaction if there's no errors. Otherwise , the transaction is rolled back.

Does the fact that this SQL statement is dynamic, afects the number of transaction inside my stored proc? Or it runs as a unique transaction?

tks,

Pedro












Post #689739
Posted Friday, April 03, 2009 5:55 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, February 24, 2014 1:01 AM
Points: 503, Visits: 610
You’re almost correct. When compiling a stored procedure, SQL Server does not try to parse strings in EXEC statements. That happens when the stored procedure executes the string. Hence there is a slight degradation in performance when using dynamic SQL. You don't have a pre-compiled query plan for the SQL statement.

>> Then the stored procedure is compiled and during the compilation the identity column is created.
>> After that, when I send an execute command, so that the stored proc is executed, the column is already there (created during compilation) and therefore, it works fine

No. The identity column is created when the EXEC statement is executed. So, having changed that UPDATE statement to dynamic SQL, again all the parsing and syntax checking is done when the EXEC statement runs. At the time that is done, your ALTER TABLE command has executed and created the column on the table, so the UPDATE statement now works.

Adding the dynamic SQL does not in anyway affect the transaction handling you have added. It simply runs within the same transaction. No new transactions are started so it will work as you intend it to.

On the subject of transactions, I’d be wary about running DDL statements inside of transactions, eg. create table, alter table etc. You may have to do it in your case but remember you are putting locks on system tables. Though maybe it’s because I’m an old timer and first started using SQL Server back in 1995 with version 4.21a. Back then, and up to version 6.0 I believe, you couldn’t perform such operations inside of transaction, SQL Server would actually throw an error. So, because I grew up with it, I stick with it. But if you are having no problems, then I guess you can leave it as it is, though I’d be curious to know why the column isn’t on the table in the first place and why you are adding it at run time. I’m sure there’s a good reason, but I can honestly say, it’s not something I’ve had to do before.

Hope that answers all you questions.

Mike
Post #689757
Posted Friday, April 03, 2009 6:43 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, May 31, 2009 10:19 AM
Points: 103, Visits: 982
Hi - Mike,

First of all, thank you for your wonderful help.

AS to your explanations, i undestud almost everything.
The only part that i did not undestud it's when you say that the create table or alter table inside, or any other DDL statement can cause Locks on system tables.

Is that so? why? In witch system tables?


About your curiosity of why am i creating this identity column inside the Stored Proc, below is my explanation:



1) I have a table, the table name is:

Bulk_provincia (it's a staging table, loaded by a bulk_insert statement)


2)

i update the table:

update bulk_provincia set VALIDO_BULK = 1
where (numprov is not null and idprov is not null and numprov <> '' and idprov <>'')


then, i make a verification of wich records will be updated or inserted (on my destination table) and update the staging table:

update bulk_provincia set operacao_bulk ='INSERT' where numprov in
(select numprov from bulk_provincia where valido_bulk=1 and numprov not in(select numprov
from provincia))

update bulk_provincia set operacao_bulk ='UPDATE' where operacao_bulk is null and
valido_bulk=1


then i delete the records that i will not import to my destination table:

delete from bulk_provincia where valido_bulk is null


At the end, i create the identity column based on a column (one of the columns of this staging table as an clustered index), so that the insert statements will be the first
to have numbers of the identity field:

declare @maxprov as int
set @maxprov=(select max(codprov)+1 from provincia)
exec('ALTER TABLE bulk_provincia add CODIGOMAXIMO INT IDENTITY ('+@maxprov+',1)')


The objective of this, is to make an insert to the destination table (the identity values will go too) and to use (in the procedure this identity fields that are not commited yet to make same comparations).

tks,

Pedro
Post #689805
Posted Friday, April 03, 2009 7:12 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, February 24, 2014 1:01 AM
Points: 503, Visits: 610
I'll probably miss some out, but when creating a table rows are added to sysobjects, syscolumns and sysindexes. There are probably more. So running a create table inside a transaction will put locks on those tables and any others it accesses. Until you commit, those locks will be held and could get in the way of other operations. You can always experiment by running a create table or alter table inside a transaction using query analyser. Do something like

begin transaction
create table test
(
col1 int
)

then run sp_lock . That will return all the object ids and database ids of locked objects.

As for why you are doing it, I think I see what you are doing but I haven't fully understood it just yet. But if it works and you are happy with it then it'll be okay.

Mike
Post #689852
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse