collation_name dynamisch setzen

[Rya]

(Voranmerkung: Ich hatte schon einmal ein ähnliches Problem hier: http://entwickler-forum.de/showthread.php?t=52115)

Beim Erzeugen einer temporären Tabelle würde ich gerne den collation_name der varchar-Spalten auf den gleichen collation_name setzen, den die anderen varchar-Spalten benutzen. Gebe ich allerdings keinen an wird der default-Wert benutzt, der nicht immer richtig ist.

Ich kann zwar statisch einen collation_name angeben mit dem COLLATE Befehl und ich kann ihn auch auslesen mit:

SELECT TOP 1 COLLATION_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE = 'varchar'

aber wenn ich beides kombiniere also etwa so:

CREATE TABLE #T1 (ModelName varchar(40) COLLATE (SELECT TOP 1 COLLATION_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE = 'varchar'),
DeviceID varchar(40) COLLATE (SELECT TOP 1 COLLATION_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE = 'varchar'),
AutoInc int constraint DF__Auto default (1))

kommt immer die Fehlermeldung "Falsch Syntax in der Nähe von (". Ist es nicht möglich den collation_name so dynamisch zu setzen?

Bisher hatte ich das immer wie folgt gelöst:

SELECT ModelName, DeviceID, DummyIntSpalte as AutoInc
INTO #T1
FROM Devices
WHERE (0 = 1)

(durch Kopieren wird automatisch der collation_name übernommen)
In dem aktuellen Fall geht das aber nicht, da ich so keine echte AutoInc-Spalte erzeugen kann.

Was ist die einfachste Lösung hierfür?

[O. Helper]

Hallo Rya,

auf den Weg geht es nicht, da müsstest Du dynamisches SQL verwenden, wobei Du dann es auf eine Global-temporäre Tabelle abändern musst.
[highlight=SQL]ECLARE @SQL nvarchar(200);
DECLARE @Coll varchar(100);

-- Collation ermitteln (auf merkwürdige weise???)
SELECT TOP 1 @Coll = COLLATION_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE = 'varchar'

-- Temp-Tabelle anlegen ohne Collation
-- mit dynamisches SQL

SET @SQL =
N'CREATE TABLE ##T1
(ModelName varchar(40) COLLATE ' + @Coll + ',
DeviceID varchar(40) COLLATE ' + @Coll + ',
AutoInc int constraint DF__Auto default (1));'

EXEC sp_executesql @stmt = @SQL

SELECT * FROM ##T1

DROP TABLE ##T1[/highlight]

keine echte AutoInc-Spalte erzeugen kann.

Es gibt schon die Möglichkeit per ALTER TABLE anschließend eine IDENTITY Spalte o.ä. in dem Temp-Tabelle einzufügen, das ist kein Problem.

[Rya]

Danke!

Es gibt schon die Möglichkeit per ALTER TABLE anschließend eine IDENTITY Spalte o.ä. in dem Temp-Tabelle einzufügen, das ist kein Problem.

Hm das wäre natürlich noch eine bessere Variante. Allerdings wenn ich Folgendes probiere:

SELECT ModelName, DeviceID
INTO #T1
FROM Devices
WHERE (0 = 1)

ALTER TABLE #T1 ADD AutoInc int constraint DF__Auto default (1)

INSERT INTO #T1 (ModelName, DeviceID)
SELECT Distinct ModelName, DeviceID
FROM [...]

SELECT @DownloadEditStatus = CASE WHEN SUM(AutoInc) IS NULL THEN 0 ELSE SUM(AutoInc) END
FROM #T1

bekomme ich die Fehlermeldung "Ungültiger Spaltenname 'AutoInc'" beim Ausführen.

[O. Helper]

Das
[highlight=SQL]SELECT ModelName, DeviceID
INTO #T1
FROM (SELECT 'Model' AS ModelName, 1 AS DeviceID) AS X

ALTER TABLE #T1 ADD AutoInc int constraint DF__Auto default (1)

INSERT INTO #T1 (ModelName, DeviceID)
SELECT *
FROM (SELECT 'Model' AS ModelName, 2 AS DeviceID) AS X

SELECT CASE WHEN SUM(AutoInc) IS NULL THEN 0 ELSE SUM(AutoInc) END
FROM #T1

SELECT *
FROM #t1

drop table #T1[/highlight]
funktioniert bei mir tadellos.
[highlight=code]ModelName DeviceID AutoInc
--------- ----------- -----------
Model 2 1
Model 1 NULL[/highlight]

[Rya]

Hm seltsam, habe es mit deinem Code probiert und er gibt bei mir weiterhin die Meldung "Ungültiger Spaltenname 'AutoInc'" aus.

[O. Helper]

Ich hatte es unter MS SQL 2005 getestet, da geht es so.
Unter MS SQL 2000 kommt in der Tat die Meldung.

Ändere es mal so ab:
ALTER TABLE #T1 ADD AutoInc int default (1);

dann geht es auch mit 2000.

[Rya]

Hm, bei mir kommt die Meldung immernoch, hier mein kompletter Code:

BEGIN TRANSACTION

SELECT ModelName, DeviceID
INTO #T1
FROM (SELECT 'Model' AS ModelName, 1 AS DeviceID) AS X 

ALTER TABLE #T1 ADD AutoInc int default (1); 

INSERT INTO #T1 (ModelName, DeviceID)
SELECT *
FROM (SELECT 'Model' AS ModelName, 2 AS DeviceID) AS X 

SELECT CASE WHEN SUM(AutoInc) IS NULL THEN 0 ELSE SUM(AutoInc) END
FROM #T1

SELECT *
FROM #T1 

DROP TABLE #T1

COMMIT TRANSACTION

Benutze jetzt erstmal die dynamische SQL Variante, die funktioniert bei mir. Wäre trotzdem interessant woher diese Fehlermeldung kommt. Eventuell gibt es noch irgendwo überbleibsel der alten Tabelle, die kein AutoInc besitzt und nicht richtig gelöscht wird? Hab allerdings mit DROP TABLE #T1 sichergestellt, dass die Tabelle nicht mehr existiert.

[O. Helper]

Nun weiß ich, woran es liegt: Ich hatte es in 2 Schritten ausgeführt, einmal bis einschl. dem ALTER und danach den Rest.

In Query setz mal nach dem ALTER TABLE ein GO.
Bei einer Externen App müsstest Du es auf 2 Commands aufteilen.