| Author |
 Topic  |
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
 Posted - 06 September 2005 : 07:57:22
|
quote:
Originally posted by Podge
One observation;
Its going to make for an ugly looking sp unless I can set my screen resolution to 5120x3840
Yeah, I agree with that  . You can insert line breaks at the ';' though. It will still be ugly, but a little less ugly. |
Snitz 3.4 Readme | Like the support? Support Snitz too |
Edited by - ruirib on 06 September 2005 07:57:43 |
 |
|
|
Podge
Support Moderator
Ireland
3776 Posts |
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 06 September 2005 : 12:55:17
|
Good question. I would say it's not, since our previous experience showed that it wasn't.
I do like to do things correctly, though. Call it a mania  . I've seen too many times where we skip a detail and afterwards we add some change that does not work just because of the thing we forgot, or skipped. |
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
Podge
Support Moderator
Ireland
3776 Posts |
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
|
|
Podge
Support Moderator
Ireland
3776 Posts |
Posted - 07 September 2005 : 18:53:52
|
Its working great so far. DTS used to fall over or hang after 3-5 databases. The sp keeps on going.
Next question....
I need to check if a column exists in a table in a database from within the same sp (from above) or another one in the same database. The columns I want to check will be in other databases than the sp.
I've defined the scalar function as described on this page
http://www.msde.biz/sqlfaq.htm
CREATE FUNCTION ColumnAlreadyExists(@TableName NVARCHAR(128),@ColumnName NVARCHAR(128))
RETURNS INTEGER--Returns 0 if column does not exist. Returns 1 if column exists.
AS
BEGIN
--See if the Table already contains the column.
IF EXISTS
(SELECT * FROM SysObjects O INNER JOIN SysColumns C ON O.ID=C.ID
WHERE ObjectProperty(O.ID,'IsUserTable')=1
AND O.Name=@TableName
AND C.Name=@ColumnName)
RETURN 1
--Table does not contain the column.
RETURN 0
END
GO
--Example of using SQL Server 2000 User Defined Scalar Function
--Drop then Add BT_FORMATTED column on the Temp_Hotspot table.
IF .dbo.ColumnAlreadyExists('Temp_Hotspot','BT_FORMATTED')=1
ALTER TABLE Temp_Hotspot DROP COLUMN BT_FORMATTED
ALTER TABLE Temp_Hotspot ADD BT_FORMATTED VARCHAR(500)
I can only check the column in the table in the database for which the function is defined. Is there a way to make this function available to all databases? Ideally I would like to be able to specify the database name as an argument if possible e.g.
IF .dbo.ColumnAlreadyExists('my_database', 'Temp_Hotspot','BT_FORMATTED')=1
Or is there an easier way to do what I want to do? |
Podge.
The Hunger Site - Click to donate free food | My Blog | Snitz 3.4.05 AutoInstall (Beta!)
My Mods: CAPTCHA Mod | GateKeeper Mod
Tutorial: Enable subscriptions on your board
Warning: The post above or below may contain nuts. |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 07 September 2005 : 20:51:43
|
I guess you could use the usual [database].[owner].SysObjects syntax. A quick test showed this syntax to work.
However, it's not possible, AFAIK, to use this syntax in a system defined function (nor in UDF), that could accept the database name as a variable, since that would mean you'd need to insert the 'SELECT * From [@dbname].[owner].SysObjects O...' in an EXEC command, and EXEC commands cannot be used inside UDFs (nor inside SDFs, obviously).
This means you can have a function in a database that checks the existence of a column in another database, with the other database name 'hardcoded', but not a single function that would dothe checking for all databases. |
Snitz 3.4 Readme | Like the support? Support Snitz too |
Edited by - ruirib on 07 September 2005 20:52:38 |
|
|
|
Podge
Support Moderator
Ireland
3776 Posts |
|
|
AnonJr
Moderator
United States
5768 Posts |
Posted - 08 September 2005 : 08:41:05
|
Not to intrude, as I've not had a chance to work with sp's yet, but you might be able to do what you want using ADOX (ADO Extensions) and VBScript. I can't seem to find the link right now, but for my Online Testing program I use ADOX to check if the log table for a test exsists - and write it if it doesn't - since this is not done when a new test is added.
I don't know if this works for non-ms databases though... |
|
|
|
AnonJr
Moderator
United States
5768 Posts |
|
|
Podge
Support Moderator
Ireland
3776 Posts |
Posted - 08 September 2005 : 09:52:45
|
I might be able to do it from within the stored procedure if I do it like this (not tested).
exec ('use ' + @mydatabase + '; if exists (select * from information_schema.columns where table_name = '' + @mytable + '' and column_name = '' + mycolumn +'') print ''column exists'' else alter mytable add whatever int 0'; use master)
And now in readable form;
use MyDatabase
GO
if exists (select * from information_schema.columns
where table_name = 'OGRISH'
and column_name = 'ALEB')
-- build some dynamic query
else
-- build another dynamic query
use myOtherDatabase
GO
|
Podge.
The Hunger Site - Click to donate free food | My Blog | Snitz 3.4.05 AutoInstall (Beta!)
My Mods: CAPTCHA Mod | GateKeeper Mod
Tutorial: Enable subscriptions on your board
Warning: The post above or below may contain nuts. |
Edited by - Podge on 08 September 2005 09:53:55 |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
|
|
Podge
Support Moderator
Ireland
3776 Posts |
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
|
|
Podge
Support Moderator
Ireland
3776 Posts |
|
|
Topic |
|
Topic Locked
.