April 2, 2009 at 9:10 am
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
April 2, 2009 at 10:08 am
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
April 2, 2009 at 3:35 pm
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.
April 2, 2009 at 8:53 pm
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.
_____________
Code for TallyGenerator
April 3, 2009 at 2:05 am
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
April 3, 2009 at 2:51 am
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
April 3, 2009 at 5:14 am
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
April 3, 2009 at 5:55 am
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
April 3, 2009 at 6:43 am
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
April 3, 2009 at 7:12 am
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
April 3, 2009 at 7:34 am
Hi Mike,
Thank you very much, once again.
I understood why DDL statements can cause the locks.
But in this case, because the procedure was coded like 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 <>'')
..............................................................................................................
..............................................................................................................
.............................................................................................................
if @@trancount >0
begin
commit transaction
end
else
rollback transaction
set nocount off
Is it possible to happen that? - if the alter table fails, the transaction will be opened?
I think that this procedure because of the xact_abort set to ON, works like this:
If any instruction (update, insert, delete, alter table, .....) inside the
"Begin transaction" fails, it goes to the rolback transaction and no transaction will be open. correct?
I think that the xact_abort works like, if there's any errors inside the stored procedure, it will stop immidialty from executing , and because i have the @@trancount>0 it will roll back the transaction and the system, tables will be released , correct?
April 3, 2009 at 9:11 am
Pedro,
Sorry. I wasn't clear. The locks on systems tables will be released if your procedure fails. Don't worry, you can't leave any locks lying around.
But your error handling code isn't quite right. Your understanding of XAct_Abort is correct but you should really test @@ERROR after every DML statement . SQL Server 2005 has the much better TRY..CATCH blocks.
You procedure should look something more like:
alter proc VALIDADOR2
as
DECLARE@iErrorint
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 <>'')
SET @iError = @@ERROR
IF @iError = 0
BEGIN
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))
SET @iError = @@ERROR
END
IF @iError = 0
BEGIN
update bulk_provincia set operacao_bulk ='UPDATE' where operacao_bulk is null and valido_bulk=1
SET @iError = @@ERROR
END
IF @iError = 0
BEGIN
delete from bulk_provincia where valido_bulk is null
SET @iError = @@ERROR
END
IF @iError = 0
BEGIN
declare @maxprov as int
set @maxprov=(select max(codprov)+1 from provincia)
exec('ALTER TABLE bulk_provincia add CODIGOMAXIMO INT IDENTITY ('+@maxprov+',1)')
SET @iError = @@ERROR
END
IF @iError = 0
BEGIN
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))
SET @iError = @@ERROR
END
IF @iError = 0
BEGIN
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))
IF @iError = 0
BEGIN
update bulk_repfiscal set operacao_bulk ='UPDATE' where operacao_bulk is null and valido_bulk=1
SET @iError = @@ERROR
END
IF @iError = 0
BEGIN
delete from bulk_repfiscal where valido_bulk is null
SET @iError = @@ERROR
END
IF @iError = 0
BEGIN
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')
SET @iError = @@ERROR
END
IF @iError = 0
BEGIN
declare @maxRep as int
set @maxrep=(select max(codrf)+1 from repfiscal)
exec('ALTER TABLE bulk_repfiscal add CODIGOMAXIMO INT IDENTITY ('+@maxrep+',1)')
SET @iError = @@ERROR
END
if @iError = 0
commit transaction
else
rollback transaction
set nocount off -- not needed but no harm it being here
April 3, 2009 at 5:28 pm
Pedro, if you use format file for BULK INSERT you may create staging table with IDENTITY column.
Format File allows you to insert into specific columns.
Check BOL for details.
_____________
Code for TallyGenerator
April 3, 2009 at 5:59 pm
And again, I strongly suggest you not to change DB schema on fly.
You may create #table and BULK INSERT into it as well as into static table.
It's not any worse than static tables for joins, data processing, etc., but it's locked within the scope of your connection and it's dropped automatically when connection is closed or suddenly broken.
You're probably not gonna have problems with that while in DEV, but as soon as you deploy to PROD they'll start to call you in the middle of the night in order to fix you c..p.
😉
_____________
Code for TallyGenerator
April 4, 2009 at 5:19 am
Hi- thank you very mutch for reply.
In this case, i think that there is no problem.
I could create as you say, a temporary table (#temp) wich would be just in use will my session is ON.
But, i think i don't need to... This tables , to where i'm gonna load my data (trought bulk_insert) are just staging tables. No body but me will use it.
In the end of the process (the app) deletes this tables.
Its execly as a temporary table, because in the end is deleted, but instead of being in my TempDB, they will be in my User database.
Users (in production) will only access the DB, trought the APP.
This app will only use this tables when it imports the data and no people will be working. In the end of the process , the stating tables are deleted.
Do you stiil think that is better to use temporary tables on tempdb? why?
tks for your help,
Pedro
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply