|
Assuming you are using sql server (2000 or 2005)
old way
if not exists (select 1 from syscolumns where name = 'column1' and id = object_id('table1'))<br />
begin<br />
alter table table1<br />
add column1 int<br />
end
or, using the views that come as standard
if not exists(select 1 from information_schema.columns where table_name = 'table1' and column_name = 'column1')<br />
begin<br />
alter table table1<br />
add column1 int<br />
end
Ensure you do a USE {database} first as these only work in the current database
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Could somebody kindly point me to an article about performing a join that excludes the results from another table? I believe that it is something resembling WHERE tblFirstTable.Key...NOT IN tblSecondTable.Key or something.
modified on Tuesday, January 29, 2008 5:19:13 PM
|
|
|
|
|
I have written a small query to illustrate how to do this.
As you were polite in asking for an article and not the code (to be honest it's quicker for me to write the code than find an article).
This example shows how to find all the animals in the zoo table that are not on the feeding table - I guess some animals are going to go hungry. 
<br />
select distinct z.animal<br />
from zoo z<br />
left join feeding f<br />
on z.animal= f.animal<br />
and f.animal is null<br />
Regards
Guy
You always pass failure on the way to success.
|
|
|
|
|
I am running in a problem of not able to restore a differential database backup through a scheduled job. The Restoration command is :
declare @str nvarchar(4000)
declare @spid int
while exists (select spid from sysprocesses where dbid= db_id('SGOTA'))
begin
set @spid = (select min(spid) from sysprocesses where dbid= db_id('SGOTA'))
set @str= 'kill ' + convert(nvarchar,@spid)
execute sp_executesql @str
end
RESTORE DATABASE SGOTA from disk='d:\FTP\DB Backup\OTA\sg_Differential_Backup.BAK'
with standby ='C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\UNDO_SGOTA.DAT'
The error I am receiving is:
Executed as user: NT AUTHORITY\SYSTEM. The preceding restore operation did not specify WITH NORECOVERY or WITH STANDBY. Restart the restore sequence, specifying WITH NORECOVERY or WITH STANDBY for all but the final step. [SQLSTATE 42000] (Error 4306) RESTORE DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.
I have taken a full backup earlier. Both the databases have a Bulk-Logged Recovery model and the destination database is in read-only mode.
I am in an urgent need of the solution. Kindly help.
-----Have A Nice Day-----
|
|
|
|
|
i hv created a database named Sample.....
In this database i have 3 tables named Department,Division,Location..All tables are having data also....nw i need to delete the data from all the three table at a time through query.....
is it posiible?.
if so,den what is the query for that?....
any kind of information is appreciatable...
please help me...
|
|
|
|
|
try over referential integrity using primaty and foreign key, with cascade delete oprion on tables
when u delete one row(Col001) from MyTableOne cascade delete will delete all rows in other tables that have referential integrity with table MyTableOne on Col001.
|
|
|
|
|
sorry i didnt get u.....am new to sqlserver.....can u pls tel me how to do this
|
|
|
|
|
from BookOnLine
Enforcing Referential Integrity Between Tables
Referential integrity between tables is enforced by default when you create a relationship in your database diagram. An enforced relationship ensures each value entered in a foreign key column matches an existing value in the related primary key column.
You can change the conditions under which referential integrity is enforced by editing the relationship's properties.
To change referential integrity options for a new relationship
In your database diagram, create a relationship. For details, see Creating a Relationship Between Tables.
In the Create Relationship dialog box, clear or select one or more of the options.
For information about the options available for SQL Server databases, see Database Designer Considerations for SQL Server Databases.
To change referential integrity options for an existing relationship
In your database diagram, select the relationship line.
Right-click the relationship line and select Properties.
Choose the Relationships tab.
Select the relationship from the Selected relationship list.
Clear or select one or more of the options.
For information about the options available for SQL Server databases, see Database Designer Considerations for SQL Server Databases.
The relationship is updated in the database when you save the diagram or either of the related tables.
|
|
|
|
|
Hi all,
I want to get a multicolumn selection but with the behavior of the DISTINCT cmd only on the first column, something like that
SELECT DISTINCT order_id, date FROM Orders WHERE .....
but the distinction should only refer on the "order_id" and not even on the "date" .
How does the sql-cmd looks like ?
thanks in advance
Frank
|
|
|
|
|
Can you give us a sample of what data exists in the table, and then what data you want after the query has been run? Because what you have doesn't seem to make sense...if your data looks like this:
order_id - date
1 - 1/1/2008
2 - 1/1/2008
2 - 1/2/2008
then which date are you going to want to display? If you distinct on only the order_id (which isn't really possible using a distinct, you would have to use a group by) then the SQL doesn't know which date you want...(which is why it's not possible using Distinct)....so please supply us some more info so we can help.
|
|
|
|
|
Hi Kschuler,
my problem is that I have to show the date and I have to show the order_id but only once.
I know, in most cases it doesn't makes sense. But for my application it doesn't matters which date has to be displayed when double entries exists. The central point is that all entries have to show a date multiple entries or not.
This is the crux.
Now I think there is no SQL solution for that problem.
Now I filter the recordset.
Thank you for your help
|
|
|
|
|
You can accomplish it with something like this:
SELECT order_id, MAX(date) FROM tablename GROUP BY order_id
The idea is that you will return one order_id and the largest date with that order_id. I know it would work for a number, I'm just not sure how a date reacts to the MAX() function...you could also use MIN if you want the smallest date...and if you did this
SELECT order_id, MAX(date), COUNT(*) FROM tablename GROUP BY order_id
It would also display how many dates exist for that order id.
So maybe you need to do some research on GROUP BY and how it affects date datatypes.
Hope this helps.
|
|
|
|
|
Great Kschuler,
it works !!!! thanks
It works because you are using agregat functions as a second and/or third parameter after the select.
That helps for the future.
Frank
|
|
|
|
|
This doesn't make sense. Either all of the data is distinct or none of it is. If you want a single order_id, then you can only retrieve one date otherwise this order will span multiple lines.
|
|
|
|
|
Hi all
I have the following the statements
SET @TempMenuId= 'SELECT @res1 = coalesce(@res1 + ',', '') + CAST([Tb001_Id] AS Varchar(8)) FROM [admin].[cm_tb_005] WHERE' + @Role_Idv +'=1'
EXEC @TempMenuId
Iam Passing @Role_Idv dynamicall.But its showing error message like this
The name 'SELECT @res1 = coalesce(@res1 + ',', ') + CAST([Tb' is not a valid identifier.
Please help me on this.
Regard's
Veeresh
i want to join this group
|
|
|
|
|
this is working !
DECLARE @IDNAL VARCHAR(36)
DECLARE @ID_STAVKE VARCHAR(36)
DECLARE @IDDOC VARCHAR(36)
DECLARE @IDVL VARCHAR(36)
DECLARE @IDLOK VARCHAR(36)
DECLARE @NEVALJA CHAR(1)
DECLARE @RECENICA AS VARCHAR(5000)
DECLARE @LOCAL_KONTO AS VARCHAR(36)
DECLARE @ID_FORMULE AS VARCHAR(36)
DECLARE @FORMULA AS VARCHAR(500)
SET @MySTRING='
UPDATE #A SET
#A.col001=#A.col001+COALESCE((SELECT ('+@FORMULA+') FROM #LOCAL_STAVKE
WHERE #LOCAL_STAVKE.VD='''+@IDDOC+'''
AND #LOCAL_STAVKE.KONTO= CASE '''+@LOCAL_KONTO+'''
WHEN '''' THEN #LOCAL_STAVKE.KONTO ELSE '''+@LOCAL_KONTO+''' END
AND #LOCAL_STAVKE.ID=#A.IDNALOGA ),0)'
EXECUTE(@MySTRING)
|
|
|
|
|
|
If your using SQL Server 2005 you could use the Day() / Month() / Year() - Methods to get the specified part of the date. I don't know exactly whether these functions are available in SQL Server 2000, too.
|
|
|
|
|
thanks
You get the best out of others when you give the best of yourself.
|
|
|
|
|
Don't delete your original message. It is rude!
|
|
|
|
|
Is there a clear cut guide, tool, etc... for doing a migration from MySQL to SQL Server 2000 or 2005?
I have to migrate a small DB and am looking for an easy way to do it if such a thing exists.
|
|
|
|
|
Hi,
Our website provide users with a forum where they can enter messages. So, typically, a user would enter a message, click submit, and their message would be displayed on the forum.
The db that stores these msgs uses a varchar field. Now, we're adding a facility to convert any urls that are entered to links by adding 'a href' tags, so that when users see messages with urls, those urls appear as links, and they can click on them. The problem is that if a user enters a message of 8K chars, then we add 'a href' tags to any urls they have, that makes the message length > 8K, preventing us from storing it in our db. (Please note that we do need to add HTML tags for other reasons as well, not just to convert urls to links.)
We thought about adding 'a href' tags during pre-render, thus avoiding storing html in the db, but found that doing such string manipulations, while a user is waiting to see their post displayed on the forum, was expensive. Timing tests revealed that the time to render such pages more than doubled.
I have solved such problems previously by adding an extra field in the db to store any overflow data, that is, data that appears after the first 8K chars.
However, I learnt of sql server 2005's varchar(max) datatype, and 'am wondering if the best way to solve this problem would be to replace the varchar field with varchar(max).
Thanks in advance for any advice.
Shefali
|
|
|
|
|
shefali_sinha wrote: We thought about adding 'a href' tags during pre-render, thus avoiding storing html in the db, but found that doing such string manipulations, while a user is waiting to see their post displayed on the forum, was expensive. Timing tests revealed that the time to render such pages more than doubled.
To be honest, that sounds odd. If you were using a regular expression to detect URIs then you might want to switch to a cheaper option, or consider using a precompiled regex.
shefali_sinha wrote: However, I learnt of sql server 2005's varchar(max) datatype, and 'am wondering if the best way to solve this problem would be to replace the varchar field with varchar(max).
Yes, that would be a solution.
Be aware that memo-style fields are stored differently (physically) to standard fields. I doubt this will make a difference to a typical forum database though.
|
|
|
|
|
Thanks a lot.
Haven't had a chance to look into pre-compiles regex, but will look into it.
I simply converted a varchar(8000) field to varchar(max) and another ntext field to varchar(max), the latter conversion was for the sake of better perfromance of a varchar(max) vs. ntext, and my world stayed perfect. Testing reveled no problems. To do the conversion in sql server 2005, I used the statement:
alter table alter column <column name=""> varchar(max).
Just thought I should document this here in case it helps someone else.
Shefali
|
|
|
|
|
ntext -> nvarchar(max). Otherwise you'll blow away any unicode data....
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
