[MSSQL]Voert query niet goed uit.

[eghie]

Ik heb een stored procedure gemaakt die een aantal tabellen in een database moet aanmaken, maar het gaat niet helemaal goed.

Ik wil eigenlijk een query waarbij de databasenaam een variable is en dat hij in die database een aantal tabellen aanmaakt.

SQL:

CREATE PROCEDURE cms.newCreateTables
(
    @DBName varchar(20)
)
AS

    DECLARE @SQLstr AS varchar(1000)

    SET @SQLstr = 'USE ' + @DBName
    EXECUTE(@SQLstr)

    CREATE TABLE [dtproperties] (
            [id] [int] IDENTITY (1, 1) NOT NULL ,
            [objectid] [int] NULL ,
            [property] [varchar] (64) COLLATE Latin1_General_CI_AS NOT NULL ,
            [value] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
            [uvalue] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL ,
            [lvalue] [image] NULL ,
            [version] [int] NOT NULL CONSTRAINT [DF__dtpropert__versi__77BFCB91] DEFAULT (0),
            CONSTRAINT [pk_dtproperties] PRIMARY KEY  CLUSTERED 
            (
                [id],
                [property]
            )  ON [PRIMARY] 
        ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

Hierbij gebruik in een varchar om die use statement aan te maken, omdat de databasenaam een variable is. Alleen het probleem hierbij is dat hij de use niet eerst uitvoert en dat hij dan de tabellen in z'n eigen database (waar stored procedure is opgeslagen) wil gaan aanmaken. Volgens de check syntax knopje is mn syntax verder wel goed. Nu zal je zeggen dat je een GO ertussen moet stoppen, zodat hij dan wel eerst die use uitvoert, maar dat mag niet van de enterprise manager. Dan geeft hij de fout aan: "Error 2714: there is already an object named 'dtproperties' in the database.", maar dat is al bij het opslaan van de stored procedure, nog niet eens bij het uitvoeren.

Je kunt ook niet zeggen van:

SQL:

USE @DBname

Want dat mag niet, omdat @DBName een varchar is.

Hoe krijg ik dit werkend.

Ik las net trouwens dat GO een batch delimiter is (tijdens het zoeken) en dat je die dus niet tussendoor ff in een stored procedure kan gebruiken.

[ Voor 4% gewijzigd door eghie op 06-07-2004 11:48 ]

[whoami]

heb je ipv EXECUTE al eens sp_execute_sql geprobeerd?

[eghie]

whoami schreef op 06 juli 2004 @ 11:53:
heb je ipv EXECUTE al eens sp_execute_sql geprobeerd?

Zelfs dan voert hij hem nog niet uit. Terwijl hierbij de syntax ook gewoon goed is. Het is trouwens sp_executesql (voor degene die zegt dat hij niet bestaat).

Oja en zo gebruik ik hem:

code:

EXECUTE sp_executesql @SQLstr

Ik heb overigens ook @SQLstr als nvarchar hierbij gedeclareerd, omdat hij anders niet werkt met sp_executesql.

Hier heb ik trouwens een site die uitlegt hoe je dynamisch sql kunt implenteren en die opties (die op mij van toepassing zijn) heb ik ook geprobeerd en het werkt niet.
de site: http://www.sqlteam.com/item.asp?ItemID=4619

[ Voor 43% gewijzigd door eghie op 06-07-2004 12:10 ]

[whoami]

Je kan natuurlijk ook in je CREATE TABLE statement aangeven in welke DB die table gecreeërd moet worden.

[curry684]

Books Online:

Using EXECUTE with a Character String
[...]

Changes in database context last only until the end of the EXECUTE statement.

Says it all lijkt me

[eghie]

whoami schreef op 06 juli 2004 @ 12:09:
Je kan natuurlijk ook in je CREATE TABLE statement aangeven in welke DB die table gecreeërd moet worden.

Dit had ik al geprobeert, maar toch nog ff een keer geprobeert en toen zag ik ineens dat ik de gebruiker vergeten was die wel rechten heeft, want de gebruiker die ik gebruik voor de stored procedure had daar geen rechten voor, dus heb ik het nu zo opgelost: databasenaam.gebruiker.tabel.

Maar vind eigenlijk vreemd waarom hij niet gewoon die USE eerst uitvoert en dan gewoon de create table, want dat zou in gewone query toch ook werken?

[curry684]

eghie schreef op 06 juli 2004 @ 12:33:
[...]

Maar vind eigenlijk vreemd waarom hij niet gewoon die USE eerst uitvoert en dan gewoon de create table, want dat zou in gewone query toch ook werken?

[rml]curry684 in "[ MSSQL]Voert query niet goed uit."[/rml]

[eghie]

curry684 schreef op 06 juli 2004 @ 12:31:
Books Online:

[...]

Says it all lijkt me

Ik snap die zin niet helemaal en ook een collega van mij snapt die niet helemaal, kun je mischien iets anders verwoorden/uitleggen? Ik kan hem wel voor het grootste gedeelte vertalen, maar dan snap ik hem nog niet.

[whoami]

Je veranderd de DB context mbhv een USE statement dat je door een EXECUTE laat uitvoeren.
Van zodra de EXECUTE zijn taak gedaan heeft, veranderd de db-context opnieuw naar wat het daarvoor was.
Aangezien je dus slechts 1 statement binnen dat execute statement laat uitvoeren (dat USE statement dus), en daarna die tabel aanmaakt, wordt de db-context na de execute 'teruggedraaid'.
Hmm, ik hoop dat ik het een beetje duidelijk uitleg.

[curry684]

Uh er staat heel exact dat een database context change in een EXECUTE op een character string maar geldig is tot het einde van de execute statement. Dus:

SQL:

declare @str varchar(50);
set @str = 'use mydb';
use otherdb;
execute @str;

-- de Execute-context is hier exit, dus is otherdb weer actief!
select * from atable;  -- Deze select vind plaats binnen otherdb!

[ Voor 16% gewijzigd door curry684 op 06-07-2004 13:28 ]

[eghie]

Ow nu snap ik hem, dus elke keer als ik dus zo'n execute wil uitvoeren en ik wil voor allemaal dat use statement gebruiken, dan moet ik dus die use statement bij elke execute invoeren.

Ik heb namelijk nu een SQL statement van (jaja) 71.185 tekens (72 pagina's in word) die verschillende stored procedurs aanmaakt. Die moet ik dus gaan opsplitsen in verschillende variablen, omdat de database waarin ze gemaakt worden dynamisch is. Daarbij leek met de use statement handig dan al die CREATE PROCEDURE statements te gaan aanpassen.

[ Voor 50% gewijzigd door eghie op 06-07-2004 14:43 ]