Scripted delete rows

Hi,
I need to delete rows from my user tables dependant upon there non
existence from another table:

delete student
where student_id not in (select student_id from tblStudent)

The reasons is convoluted, simplest explanation is that our operational
system allows the change of business keys. This wreaks havoc in the
data warehouse.
So, I'm look for help on how I can delete rows from tables that have a
column STUDENT_ID. I'd like the script to search for the tables, then
perform the delete.
I don't know where information about user tables are stored, nor how to
loop through the results to do the delete.

Any Ideas are appreciated."rcamarda" <rcamarda@.cablespeed.com> wrote in message
news:1114613428.538202.213970@.f14g2000cwb.googlegr oups.com...
> Hi,
> I need to delete rows from my user tables dependant upon there non
> existence from another table:
> delete student
> where student_id not in (select student_id from tblStudent)
> The reasons is convoluted, simplest explanation is that our operational
> system allows the change of business keys. This wreaks havoc in the
> data warehouse.
> So, I'm look for help on how I can delete rows from tables that have a
> column STUDENT_ID. I'd like the script to search for the tables, then
> perform the delete.
> I don't know where information about user tables are stored, nor how to
> loop through the results to do the delete.
> Any Ideas are appreciated.

You can use a query like this to generate a script, then review it before
executing it:

select 'delete from ' +
TABLE_SCHEMA + '.' + TABLE_NAME +
' where not exists (select student_id from dbo.tblStudent ts where ' +
TABLE_SCHEMA + '.' + TABLE_NAME +
'.student_id = ts.student_id)'
from
INFORMATION_SCHEMA.COLUMNS
where
COLUMN_NAME = 'student_id' and
objectproperty(object_id(TABLE_SCHEMA + '.' + TABLE_NAME), 'IsTable') = 1

See the INFORMATION_SCHEMA views in Books Online, as well as syscolumns,
sysobjects, and "Meta Data Functions".

Simon|||Simon,
Works like a champ and I learned something new!
Thanks
Robsql

script to grant permision to role

Hi,
is there any script (qucik way) to grant all Select, Update, Insert,
Delete to a role which i call it "WebAccess"
I would like not to spend too much time creating a role called "WebAccess"
and go to that role and check on every Update, Select, Insert, and Delete fo
r
each object
Thanks
EdEd,
Try adding the role "WebAccess" to the fixed databse roles db_datareader and
db_datawriter.
EXEC sp_addrolemember 'db_datareader', 'WebAccess'
EXEC sp_addrolemember 'db_datawriter', 'WebAccess'
AMB
"Ed" wrote:

> Hi,
> is there any script (qucik way) to grant all Select, Update, Insert,
> Delete to a role which i call it "WebAccess"
> I would like not to spend too much time creating a role called "WebAcces
s"
> and go to that role and check on every Update, Select, Insert, and Delete
for
> each object
> Thanks
> Ed|||Do Datareader and DataWrite have rights to Select, Insert, Update, Delete an
d
Exec the stored procedure and UDF?
One more question, do i need to grant permission all stored procedure
starting with dt_xxxx
Thanks again
Ed
"Alejandro Mesa" wrote:
> Ed,
> Try adding the role "WebAccess" to the fixed databse roles db_datareader a
nd
> db_datawriter.
> EXEC sp_addrolemember 'db_datareader', 'WebAccess'
> EXEC sp_addrolemember 'db_datawriter', 'WebAccess'
>
> AMB
>
> "Ed" wrote:
>|||Ed,

> Do Datareader and DataWrite have rights to Select, Insert, Update, Delete
and
> Exec the stored procedure and UDF?
db_datareader - Can select all data from any user table in the database.
db - datawriter - Can modify any data in any user table in the database.
For udfs and sps you have to grant EXECUTE (sp and udf) and REFERENCES (udf)
.
AMB
"Ed" wrote:
> Do Datareader and DataWrite have rights to Select, Insert, Update, Delete
and
> Exec the stored procedure and UDF?
> One more question, do i need to grant permission all stored procedure
> starting with dt_xxxx
> Thanks again
> Ed
>
> "Alejandro Mesa" wrote:
>|||is that necessary to grant permission to stored procedure starting with dt_x
xxx
Ed
"Alejandro Mesa" wrote:
> Ed,
>
> db_datareader - Can select all data from any user table in the database.
> db - datawriter - Can modify any data in any user table in the database.
> For udfs and sps you have to grant EXECUTE (sp and udf) and REFERENCES (ud
f) .
>
> AMB
> "Ed" wrote:
>|||No.
AMB
"Ed" wrote:
> is that necessary to grant permission to stored procedure starting with dt
_xxxx
> Ed
> "Alejandro Mesa" wrote:
>

Script to convert char to int please help....

I ran the following simple select statement :
select * from tcase where ltrim(rtrim(amount)) > 66 and dateposted =
'20041123'
Then I got the following error message after several thousand records were
fetched.
Syntax error converting the varchar value 'ZAER "' to a column of data
type int.
What I like to do is convert that column which is char to int or select
everything with the value 'ZAER' and copy to another table and then convert.
I did the first and updated, copied the data back and ran the select and
still got the same error. Can someone help please? Thank you.
James
How do you plan to convert the value 'ZAER' to an INT? And why do you have
non-integer strings mixed in with integer amounts in your 'amount' column?
I would recommend that you clean up your data and re-define the column as
INT to avoid these types of issues in the future.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"James Juno" <JamesJuno@.discussions.microsoft.com> wrote in message
news:EB3DA6E5-7224-4E94-8FA5-6AB637662A05@.microsoft.com...
> I ran the following simple select statement :
> select * from tcase where ltrim(rtrim(amount)) > 66 and dateposted =
> '20041123'
> Then I got the following error message after several thousand records were
> fetched.
> Syntax error converting the varchar value 'ZAER "' to a column of data
> type int.
> What I like to do is convert that column which is char to int or select
> everything with the value 'ZAER' and copy to another table and then
convert.
> I did the first and updated, copied the data back and ran the select and
> still got the same error. Can someone help please? Thank you.
> James
|||What I wanted to do was remove all the non-integer strings first with an
update to '0' before converting. Do you know what I can do?
James.
"Adam Machanic" wrote:

> How do you plan to convert the value 'ZAER' to an INT? And why do you have
> non-integer strings mixed in with integer amounts in your 'amount' column?
> I would recommend that you clean up your data and re-define the column as
> INT to avoid these types of issues in the future.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "James Juno" <JamesJuno@.discussions.microsoft.com> wrote in message
> news:EB3DA6E5-7224-4E94-8FA5-6AB637662A05@.microsoft.com...
> convert.
>
>
|||Here's what I would do:
UPDATE tcase
SET amount= '0'
WHERE PATINDEX('%[^0-9]%', RTRIM(LTRIM(amount))) > 0
OR amount IS NULL
ALTER TABLE tcase
ALTER COLUMN amount INT NOT NULL
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"James Juno" <JamesJuno@.discussions.microsoft.com> wrote in message
news:506B7C12-D36E-4F82-A2A1-5C545CD643CC@.microsoft.com...[vbcol=seagreen]
> What I wanted to do was remove all the non-integer strings first with an
> update to '0' before converting. Do you know what I can do?
> James.
>
> "Adam Machanic" wrote:
have[vbcol=seagreen]
column?[vbcol=seagreen]
as[vbcol=seagreen]
were[vbcol=seagreen]
data[vbcol=seagreen]
select[vbcol=seagreen]
and[vbcol=seagreen]

Script to convert char to int please help....

I ran the following simple select statement :
select * from tcase where ltrim(rtrim(amount)) > 66 and dateposted = '20041123'
Then I got the following error message after several thousand records were
fetched.
Syntax error converting the varchar value 'ZAER "' to a column of data
type int.
What I like to do is convert that column which is char to int or select
everything with the value 'ZAER' and copy to another table and then convert.
I did the first and updated, copied the data back and ran the select and
still got the same error. Can someone help please? Thank you.
JamesHow do you plan to convert the value 'ZAER' to an INT? And why do you have
non-integer strings mixed in with integer amounts in your 'amount' column?
I would recommend that you clean up your data and re-define the column as
INT to avoid these types of issues in the future.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"James Juno" <JamesJuno@.discussions.microsoft.com> wrote in message
news:EB3DA6E5-7224-4E94-8FA5-6AB637662A05@.microsoft.com...
> I ran the following simple select statement :
> select * from tcase where ltrim(rtrim(amount)) > 66 and dateposted => '20041123'
> Then I got the following error message after several thousand records were
> fetched.
> Syntax error converting the varchar value 'ZAER "' to a column of data
> type int.
> What I like to do is convert that column which is char to int or select
> everything with the value 'ZAER' and copy to another table and then
convert.
> I did the first and updated, copied the data back and ran the select and
> still got the same error. Can someone help please? Thank you.
> James|||What I wanted to do was remove all the non-integer strings first with an
update to '0' before converting. Do you know what I can do?
James.
"Adam Machanic" wrote:
> How do you plan to convert the value 'ZAER' to an INT? And why do you have
> non-integer strings mixed in with integer amounts in your 'amount' column?
> I would recommend that you clean up your data and re-define the column as
> INT to avoid these types of issues in the future.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "James Juno" <JamesJuno@.discussions.microsoft.com> wrote in message
> news:EB3DA6E5-7224-4E94-8FA5-6AB637662A05@.microsoft.com...
> > I ran the following simple select statement :
> >
> > select * from tcase where ltrim(rtrim(amount)) > 66 and dateposted => > '20041123'
> > Then I got the following error message after several thousand records were
> > fetched.
> >
> > Syntax error converting the varchar value 'ZAER "' to a column of data
> > type int.
> >
> > What I like to do is convert that column which is char to int or select
> > everything with the value 'ZAER' and copy to another table and then
> convert.
> > I did the first and updated, copied the data back and ran the select and
> > still got the same error. Can someone help please? Thank you.
> >
> > James
>
>|||Here's what I would do:
UPDATE tcase
SET amount= '0'
WHERE PATINDEX('%[^0-9]%', RTRIM(LTRIM(amount))) > 0
OR amount IS NULL
ALTER TABLE tcase
ALTER COLUMN amount INT NOT NULL
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"James Juno" <JamesJuno@.discussions.microsoft.com> wrote in message
news:506B7C12-D36E-4F82-A2A1-5C545CD643CC@.microsoft.com...
> What I wanted to do was remove all the non-integer strings first with an
> update to '0' before converting. Do you know what I can do?
> James.
>
> "Adam Machanic" wrote:
> > How do you plan to convert the value 'ZAER' to an INT? And why do you
have
> > non-integer strings mixed in with integer amounts in your 'amount'
column?
> > I would recommend that you clean up your data and re-define the column
as
> > INT to avoid these types of issues in the future.
> >
> >
> > --
> > Adam Machanic
> > SQL Server MVP
> > http://www.sqljunkies.com/weblog/amachanic
> > --
> >
> >
> > "James Juno" <JamesJuno@.discussions.microsoft.com> wrote in message
> > news:EB3DA6E5-7224-4E94-8FA5-6AB637662A05@.microsoft.com...
> > > I ran the following simple select statement :
> > >
> > > select * from tcase where ltrim(rtrim(amount)) > 66 and dateposted => > > '20041123'
> > > Then I got the following error message after several thousand records
were
> > > fetched.
> > >
> > > Syntax error converting the varchar value 'ZAER "' to a column of
data
> > > type int.
> > >
> > > What I like to do is convert that column which is char to int or
select
> > > everything with the value 'ZAER' and copy to another table and then
> > convert.
> > > I did the first and updated, copied the data back and ran the select
and
> > > still got the same error. Can someone help please? Thank you.
> > >
> > > James
> >
> >
> >

Script Task Variables

script task: there should be another way to select variables than the comma seperated list

One has to type in a whole list of variables, hoping not to make any mistakes

IntelliSense for example?

But hey, I'm not complaining...

greets

There is! You can do it in code.

Writing to a variable from a script task
(http://blogs.conchango.com/jamiethomson/archive/2005/02/09/964.aspx)

Still no intellisense though!!

I highly recommend you use the code option because this minimises the risk of variable locking. I plan to blog about this soon and have written about it in an upcoming article in SQL Server Standard.

-Jamie

|||

ok, I'll do it your way

thanks

|||

Dear Jamie,

Your method works fine for 1 variable.
I assume you know this, but for other readers of this thread:

ms-help://MS.VSCC.v80/MS.VSIPCC.v80/MS.SQLSVR.v9.en/dtsref9mref/html/T_Microsoft_SqlServer_Dts_Runtime_VariableDispenser.htm

quoting:

There are two scenarios for using the variable dispenser.

You want just one variable. In this scenario, call LockOneForRead or LockOneForWrite, and a collection with one element is returned.

You want several variables. In this scenario, call LockForRead and LockForWrite several times, one for each variable. This builds up two lists, one list that contains variables for reading and a list of variables for writing. Next, call GetVariables, which gives you a collection that contains all of the locked variables. If GetVariables succeeds, the two lock lists, which are the lists of variable names, not actual locks, is cleared.

To clear the locks, call Unlock on the collection when finished to explicitly release the locks. This unlocks the variables themselves. If GetVariables fails, the lists remain unchanged, and you can call GetVariables again. If you still do not succeed, call Reset to clear the lists and bring the variable dispenser back to its initial state.

Cheers,

Tom

Script Table as SELECT To...

I have only recetly started using SQL server 2005 in anger and have
rather amusingly found that Microsoft have fixed something I always
considered a bug but have managed to make it worse. I wonder if maybe
I am missing something.
In Query Analyzer (SQL Server 2000) you were able to right click on a
table and select
Script Object to New Window As..Select
and you would get something like
SELECT [FIELDS] FROM [Table]
except usually you were dealing with a real table so you would get
SELECT [FIELD1], [FIELD2], [FIELD3], [FIELD4], [FIELD5], [FIELD6],
[FIELD7], [FIELD8], [FIELD9], [FIELD10] etc FROM [Table]
In this case, the table name would be off the edge of the screen to
the right. I was in a habit of hitting the End key and hitting CR
before the FROM which pushed the table name onto the second line.
i.e.
SELECT [FIELD1], [FIELD2], etc...
FROM [Table]
I always found this a bit annoying and wished there was a way to
change this behavior.
Now with SQL 2005 and Management Studio, you can right click on a
table and select
Script Table as ... Select To ... New Query Editor Window
and you get something like
SELECT [FIELD1]
, [FIELD2]
, [FIELD3]
, [FIELD4]
, [FIELD5] etc
FROM [Table]
So Microsoft have changed the behavior, but now the table name
dissapears off the bottom of the screen, I find this even MORE
frustrating as I have to scroll down to the bottom to see which table
I am using, also
sometimes I want to have several SELECT statements open, and this
means selecting all the fields and replacing with * or deleting the CR
for each line.
Does anyone have any comments or know of a way of changing the defalt
scripting behavior of Management Studio?<benb@.atwuk.com> wrote in message
news:e92d760a-90fc-4692-b8e8-ce4beacfaff2@.v17g2000hsa.googlegroups.com...
>I have only recetly started using SQL server 2005 in anger and have
> rather amusingly found that Microsoft have fixed something I always
> considered a bug but have managed to make it worse. I wonder if maybe
> I am missing something.
> In Query Analyzer (SQL Server 2000) you were able to right click on a
> table and select
> Script Object to New Window As..Select
> and you would get something like
> SELECT [FIELDS] FROM [Table]
> except usually you were dealing with a real table so you would get
> SELECT [FIELD1], [FIELD2], [FIELD3], [FIELD4], [FIELD5], [FIELD6],
> [FIELD7], [FIELD8], [FIELD9], [FIELD10] etc FROM [Table]
> In this case, the table name would be off the edge of the screen to
> the right. I was in a habit of hitting the End key and hitting CR
> before the FROM which pushed the table name onto the second line.
> i.e.
> SELECT [FIELD1], [FIELD2], etc...
> FROM [Table]
> I always found this a bit annoying and wished there was a way to
> change this behavior.
> Now with SQL 2005 and Management Studio, you can right click on a
> table and select
> Script Table as ... Select To ... New Query Editor Window
> and you get something like
> SELECT [FIELD1]
> , [FIELD2]
> , [FIELD3]
> , [FIELD4]
> , [FIELD5] etc
> FROM [Table]
> So Microsoft have changed the behavior, but now the table name
> dissapears off the bottom of the screen, I find this even MORE
> frustrating as I have to scroll down to the bottom to see which table
> I am using, also
> sometimes I want to have several SELECT statements open, and this
> means selecting all the fields and replacing with * or deleting the CR
> for each line.
> Does anyone have any comments or know of a way of changing the defalt
> scripting behavior of Management Studio?
>
If you drag the Columns node into the editing window it will insert a list
of column names in one line. All you have to do is type SELECT and FROM.
--
David Portas|||On 31 Jan, 19:28, "David Portas"
<REMOVE_BEFORE_REPLYING_dpor...@.acm.org> wrote:
> <b...@.atwuk.com> wrote in message
> news:e92d760a-90fc-4692-b8e8-ce4beacfaff2@.v17g2000hsa.googlegroups.com...
>
>
> >I have only recetly started using SQL server 2005 in anger and have
> > rather amusingly found that Microsoft have fixed something I always
> > considered a bug but have managed to make it worse. I wonder if maybe
> > I am missing something.
> > In Query Analyzer (SQL Server 2000) you were able to right click on a
> > table and select
> > Script Object to New Window As..Select
> > and you would get something like
> > SELECT [FIELDS] FROM [Table]
> > except usually you were dealing with a real table so you would get
> > SELECT [FIELD1], [FIELD2], [FIELD3], [FIELD4], [FIELD5], [FIELD6],
> > [FIELD7], [FIELD8], [FIELD9], [FIELD10] etc FROM [Table]
> > In this case, the table name would be off the edge of the screen to
> > the right. I was in a habit of hitting the End key and hitting CR
> > before the FROM which pushed the table name onto the second line.
> > i.e.
> > SELECT [FIELD1], [FIELD2], etc...
> > FROM [Table]
> > I always found this a bit annoying and wished there was a way to
> > change this behavior.
> > Now with SQL 2005 and Management Studio, you can right click on a
> > table and select
> > Script Table as ... Select To ... New Query Editor Window
> > and you get something like
> > SELECT [FIELD1]
> > =A0 =A0 =A0 =A0 =A0 =A0, [FIELD2]
> > =A0 =A0 =A0 =A0 =A0 =A0, [FIELD3]
> > =A0 =A0 =A0 =A0 =A0 =A0, [FIELD4]
> > =A0 =A0 =A0 =A0 =A0 =A0, [FIELD5] etc
> > FROM [Table]
> > So Microsoft have changed the behavior, but now the table name
> > dissapears off the bottom of the screen, I find this even MORE
> > frustrating as I have to scroll down to the bottom to see which table
> > I am using, also
> > sometimes I want to have several SELECT statements open, and this
> > means selecting all the fields and replacing with * or deleting the CR
> > for each line.
> > Does anyone have any comments or know of a way of changing the defalt
> > scripting behavior of Management Studio?
> If you drag the Columns node into the editing window it will insert a list=
> of column names in one line. All you have to do is type SELECT and FROM.
> --
> David Portas-
So no one knows of any way to customise the scripts generated when
scripting Script Table as .. ?|||<<So no one knows of any way to customise the scripts generated when
scripting Script Table as .. ?>>
AFAIK, no such customization is possible.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<benb@.atwuk.com> wrote in message
news:16aeba87-35c6-4373-902c-12bd5720d132@.e23g2000prf.googlegroups.com...
On 31 Jan, 19:28, "David Portas"
<REMOVE_BEFORE_REPLYING_dpor...@.acm.org> wrote:
> <b...@.atwuk.com> wrote in message
> news:e92d760a-90fc-4692-b8e8-ce4beacfaff2@.v17g2000hsa.googlegroups.com...
>
>
> >I have only recetly started using SQL server 2005 in anger and have
> > rather amusingly found that Microsoft have fixed something I always
> > considered a bug but have managed to make it worse. I wonder if maybe
> > I am missing something.
> > In Query Analyzer (SQL Server 2000) you were able to right click on a
> > table and select
> > Script Object to New Window As..Select
> > and you would get something like
> > SELECT [FIELDS] FROM [Table]
> > except usually you were dealing with a real table so you would get
> > SELECT [FIELD1], [FIELD2], [FIELD3], [FIELD4], [FIELD5], [FIELD6],
> > [FIELD7], [FIELD8], [FIELD9], [FIELD10] etc FROM [Table]
> > In this case, the table name would be off the edge of the screen to
> > the right. I was in a habit of hitting the End key and hitting CR
> > before the FROM which pushed the table name onto the second line.
> > i.e.
> > SELECT [FIELD1], [FIELD2], etc...
> > FROM [Table]
> > I always found this a bit annoying and wished there was a way to
> > change this behavior.
> > Now with SQL 2005 and Management Studio, you can right click on a
> > table and select
> > Script Table as ... Select To ... New Query Editor Window
> > and you get something like
> > SELECT [FIELD1]
> > , [FIELD2]
> > , [FIELD3]
> > , [FIELD4]
> > , [FIELD5] etc
> > FROM [Table]
> > So Microsoft have changed the behavior, but now the table name
> > dissapears off the bottom of the screen, I find this even MORE
> > frustrating as I have to scroll down to the bottom to see which table
> > I am using, also
> > sometimes I want to have several SELECT statements open, and this
> > means selecting all the fields and replacing with * or deleting the CR
> > for each line.
> > Does anyone have any comments or know of a way of changing the defalt
> > scripting behavior of Management Studio?
> If you drag the Columns node into the editing window it will insert a list
> of column names in one line. All you have to do is type SELECT and FROM.
> --
> David Portas-
So no one knows of any way to customise the scripts generated when
scripting Script Table as .. ?

script in 2005 cannot be run on sql server 2000

for e.g.
in 2005
SELECT * FROM sys.foreign_keys....(no error)
in sql server 2000
SELECT * FROM sys.foreign_keys....(Invalid object name 'sys.foreign_keys'.)
SELECT * FROM sys.indexes WHERE object_id = .......
(no problem in sql server 2005, but have problem in sql server 2000)
so how to handle these case?
Is it possible to write script which can be run on both sql server 2005 and
sql server 2000?Yes, it is possible to write those scripts to they can run on both SQL
Server 2000 and SQL Server 2005. But you need to look for names like
sysindexes and sysforeignkeys. In the documentation (BOL) they are called
system tables in SQL Server 2000 and compatibility views on SQL Server 2005.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"kei" wrote:
> for e.g.
> in 2005
> SELECT * FROM sys.foreign_keys....(no error)
> in sql server 2000
> SELECT * FROM sys.foreign_keys....(Invalid object name 'sys.foreign_keys'.)
> SELECT * FROM sys.indexes WHERE object_id = .......
> (no problem in sql server 2005, but have problem in sql server 2000)
> so how to handle these case?
> Is it possible to write script which can be run on both sql server 2005 and
> sql server 2000?|||so what should I do now to convert the already written sql 2005 script to run
on sql 2000 server?
"Ben Nevarez" wrote:
> Yes, it is possible to write those scripts to they can run on both SQL
> Server 2000 and SQL Server 2005. But you need to look for names like
> sysindexes and sysforeignkeys. In the documentation (BOL) they are called
> system tables in SQL Server 2000 and compatibility views on SQL Server 2005.
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "kei" wrote:
> > for e.g.
> > in 2005
> > SELECT * FROM sys.foreign_keys....(no error)
> > in sql server 2000
> > SELECT * FROM sys.foreign_keys....(Invalid object name 'sys.foreign_keys'.)
> >
> > SELECT * FROM sys.indexes WHERE object_id = .......
> > (no problem in sql server 2005, but have problem in sql server 2000)
> >
> > so how to handle these case?
> > Is it possible to write script which can be run on both sql server 2005 and
> > sql server 2000?|||This sounds like going backwards, that is, moving from SQL Server 2005 to
SQL Server 2000. For SQL Server 2005, Microsoft recommends to use the new
catalog views (like sys.indexes) and the compatibility views (like
sysindexes) are for backward compatibility only.
So, what I would do is to leave the existing scripts for SQL Server 2005
unchanged and just create similar ones just for SQL Server 2000.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"kei" wrote:
> so what should I do now to convert the already written sql 2005 script to run
> on sql 2000 server?
> "Ben Nevarez" wrote:
> >
> > Yes, it is possible to write those scripts to they can run on both SQL
> > Server 2000 and SQL Server 2005. But you need to look for names like
> > sysindexes and sysforeignkeys. In the documentation (BOL) they are called
> > system tables in SQL Server 2000 and compatibility views on SQL Server 2005.
> >
> > Hope this helps,
> >
> > Ben Nevarez
> > Senior Database Administrator
> > AIG SunAmerica
> >
> >
> >
> > "kei" wrote:
> >
> > > for e.g.
> > > in 2005
> > > SELECT * FROM sys.foreign_keys....(no error)
> > > in sql server 2000
> > > SELECT * FROM sys.foreign_keys....(Invalid object name 'sys.foreign_keys'.)
> > >
> > > SELECT * FROM sys.indexes WHERE object_id = .......
> > > (no problem in sql server 2005, but have problem in sql server 2000)
> > >
> > > so how to handle these case?
> > > Is it possible to write script which can be run on both sql server 2005 and
> > > sql server 2000?|||NO, I really want to write a script that can be run on both sql server 2000
and 2005, not 1 script for each version, so how can I change the sql server
2005 script to script that can be run on sql server 2000 and sql server 2005?
SELECT * FROM sys.foreign_keys WHERE object_id = ......
SELECT * FROM sys.indexes WHERE object_id =........
how to change the above statement? any concrete example of how to change?
thx!!
"Ben Nevarez" wrote:
> This sounds like going backwards, that is, moving from SQL Server 2005 to
> SQL Server 2000. For SQL Server 2005, Microsoft recommends to use the new
> catalog views (like sys.indexes) and the compatibility views (like
> sysindexes) are for backward compatibility only.
> So, what I would do is to leave the existing scripts for SQL Server 2005
> unchanged and just create similar ones just for SQL Server 2000.
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "kei" wrote:
> > so what should I do now to convert the already written sql 2005 script to run
> > on sql 2000 server?
> >
> > "Ben Nevarez" wrote:
> >
> > >
> > > Yes, it is possible to write those scripts to they can run on both SQL
> > > Server 2000 and SQL Server 2005. But you need to look for names like
> > > sysindexes and sysforeignkeys. In the documentation (BOL) they are called
> > > system tables in SQL Server 2000 and compatibility views on SQL Server 2005.
> > >
> > > Hope this helps,
> > >
> > > Ben Nevarez
> > > Senior Database Administrator
> > > AIG SunAmerica
> > >
> > >
> > >
> > > "kei" wrote:
> > >
> > > > for e.g.
> > > > in 2005
> > > > SELECT * FROM sys.foreign_keys....(no error)
> > > > in sql server 2000
> > > > SELECT * FROM sys.foreign_keys....(Invalid object name 'sys.foreign_keys'.)
> > > >
> > > > SELECT * FROM sys.indexes WHERE object_id = .......
> > > > (no problem in sql server 2005, but have problem in sql server 2000)
> > > >
> > > > so how to handle these case?
> > > > Is it possible to write script which can be run on both sql server 2005 and
> > > > sql server 2000?|||I am affraid that there is no automatic way to convert the scripts. Check on
the SQL Server documentation (Books Online) for the description of both the
sysindexes and sysforeignkeys system tables/compatibility views.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"kei" wrote:
> NO, I really want to write a script that can be run on both sql server 2000
> and 2005, not 1 script for each version, so how can I change the sql server
> 2005 script to script that can be run on sql server 2000 and sql server 2005?
> SELECT * FROM sys.foreign_keys WHERE object_id = ......
> SELECT * FROM sys.indexes WHERE object_id =........
> how to change the above statement? any concrete example of how to change?
> thx!!
> "Ben Nevarez" wrote:
> >
> > This sounds like going backwards, that is, moving from SQL Server 2005 to
> > SQL Server 2000. For SQL Server 2005, Microsoft recommends to use the new
> > catalog views (like sys.indexes) and the compatibility views (like
> > sysindexes) are for backward compatibility only.
> >
> > So, what I would do is to leave the existing scripts for SQL Server 2005
> > unchanged and just create similar ones just for SQL Server 2000.
> >
> > Hope this helps,
> >
> > Ben Nevarez
> > Senior Database Administrator
> > AIG SunAmerica
> >
> >
> >
> > "kei" wrote:
> >
> > > so what should I do now to convert the already written sql 2005 script to run
> > > on sql 2000 server?
> > >
> > > "Ben Nevarez" wrote:
> > >
> > > >
> > > > Yes, it is possible to write those scripts to they can run on both SQL
> > > > Server 2000 and SQL Server 2005. But you need to look for names like
> > > > sysindexes and sysforeignkeys. In the documentation (BOL) they are called
> > > > system tables in SQL Server 2000 and compatibility views on SQL Server 2005.
> > > >
> > > > Hope this helps,
> > > >
> > > > Ben Nevarez
> > > > Senior Database Administrator
> > > > AIG SunAmerica
> > > >
> > > >
> > > >
> > > > "kei" wrote:
> > > >
> > > > > for e.g.
> > > > > in 2005
> > > > > SELECT * FROM sys.foreign_keys....(no error)
> > > > > in sql server 2000
> > > > > SELECT * FROM sys.foreign_keys....(Invalid object name 'sys.foreign_keys'.)
> > > > >
> > > > > SELECT * FROM sys.indexes WHERE object_id = .......
> > > > > (no problem in sql server 2005, but have problem in sql server 2000)
> > > > >
> > > > > so how to handle these case?
> > > > > Is it possible to write script which can be run on both sql server 2005 and
> > > > > sql server 2000?|||I am willing to modify it manually, but don't know how to change manually, do
you have any idea?
"Ben Nevarez" wrote:
> I am affraid that there is no automatic way to convert the scripts. Check on
> the SQL Server documentation (Books Online) for the description of both the
> sysindexes and sysforeignkeys system tables/compatibility views.
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "kei" wrote:
> > NO, I really want to write a script that can be run on both sql server 2000
> > and 2005, not 1 script for each version, so how can I change the sql server
> > 2005 script to script that can be run on sql server 2000 and sql server 2005?
> > SELECT * FROM sys.foreign_keys WHERE object_id = ......
> > SELECT * FROM sys.indexes WHERE object_id =........
> > how to change the above statement? any concrete example of how to change?
> > thx!!
> >
> > "Ben Nevarez" wrote:
> >
> > >
> > > This sounds like going backwards, that is, moving from SQL Server 2005 to
> > > SQL Server 2000. For SQL Server 2005, Microsoft recommends to use the new
> > > catalog views (like sys.indexes) and the compatibility views (like
> > > sysindexes) are for backward compatibility only.
> > >
> > > So, what I would do is to leave the existing scripts for SQL Server 2005
> > > unchanged and just create similar ones just for SQL Server 2000.
> > >
> > > Hope this helps,
> > >
> > > Ben Nevarez
> > > Senior Database Administrator
> > > AIG SunAmerica
> > >
> > >
> > >
> > > "kei" wrote:
> > >
> > > > so what should I do now to convert the already written sql 2005 script to run
> > > > on sql 2000 server?
> > > >
> > > > "Ben Nevarez" wrote:
> > > >
> > > > >
> > > > > Yes, it is possible to write those scripts to they can run on both SQL
> > > > > Server 2000 and SQL Server 2005. But you need to look for names like
> > > > > sysindexes and sysforeignkeys. In the documentation (BOL) they are called
> > > > > system tables in SQL Server 2000 and compatibility views on SQL Server 2005.
> > > > >
> > > > > Hope this helps,
> > > > >
> > > > > Ben Nevarez
> > > > > Senior Database Administrator
> > > > > AIG SunAmerica
> > > > >
> > > > >
> > > > >
> > > > > "kei" wrote:
> > > > >
> > > > > > for e.g.
> > > > > > in 2005
> > > > > > SELECT * FROM sys.foreign_keys....(no error)
> > > > > > in sql server 2000
> > > > > > SELECT * FROM sys.foreign_keys....(Invalid object name 'sys.foreign_keys'.)
> > > > > >
> > > > > > SELECT * FROM sys.indexes WHERE object_id = .......
> > > > > > (no problem in sql server 2005, but have problem in sql server 2000)
> > > > > >
> > > > > > so how to handle these case?
> > > > > > Is it possible to write script which can be run on both sql server 2005 and
> > > > > > sql server 2000?|||I believe Ben's trying to say that you should go to BOL and look for SQL
Server 2000 commands that is equal to the ones you used in your SQL Server
2005 scripts and go that way.
You can check if it's a SQL Server 2000 or 2005 before starting other
commands in your script and then you could use 2000 or 2005 commands
according to the version of the SQL Server that you run your script.
--
Ekrem Ã?nsoy
"kei" <kei@.discussions.microsoft.com> wrote in message
news:6942143B-33F0-476F-A030-3BE4DF2AC220@.microsoft.com...
>I am willing to modify it manually, but don't know how to change manually,
>do
> you have any idea?
> "Ben Nevarez" wrote:
>> I am affraid that there is no automatic way to convert the scripts. Check
>> on
>> the SQL Server documentation (Books Online) for the description of both
>> the
>> sysindexes and sysforeignkeys system tables/compatibility views.
>> Hope this helps,
>> Ben Nevarez
>> Senior Database Administrator
>> AIG SunAmerica
>>
>> "kei" wrote:
>> > NO, I really want to write a script that can be run on both sql server
>> > 2000
>> > and 2005, not 1 script for each version, so how can I change the sql
>> > server
>> > 2005 script to script that can be run on sql server 2000 and sql server
>> > 2005?
>> > SELECT * FROM sys.foreign_keys WHERE object_id = ......
>> > SELECT * FROM sys.indexes WHERE object_id =........
>> > how to change the above statement? any concrete example of how to
>> > change?
>> > thx!!
>> >
>> > "Ben Nevarez" wrote:
>> >
>> > >
>> > > This sounds like going backwards, that is, moving from SQL Server
>> > > 2005 to
>> > > SQL Server 2000. For SQL Server 2005, Microsoft recommends to use the
>> > > new
>> > > catalog views (like sys.indexes) and the compatibility views (like
>> > > sysindexes) are for backward compatibility only.
>> > >
>> > > So, what I would do is to leave the existing scripts for SQL Server
>> > > 2005
>> > > unchanged and just create similar ones just for SQL Server 2000.
>> > >
>> > > Hope this helps,
>> > >
>> > > Ben Nevarez
>> > > Senior Database Administrator
>> > > AIG SunAmerica
>> > >
>> > >
>> > >
>> > > "kei" wrote:
>> > >
>> > > > so what should I do now to convert the already written sql 2005
>> > > > script to run
>> > > > on sql 2000 server?
>> > > >
>> > > > "Ben Nevarez" wrote:
>> > > >
>> > > > >
>> > > > > Yes, it is possible to write those scripts to they can run on
>> > > > > both SQL
>> > > > > Server 2000 and SQL Server 2005. But you need to look for names
>> > > > > like
>> > > > > sysindexes and sysforeignkeys. In the documentation (BOL) they
>> > > > > are called
>> > > > > system tables in SQL Server 2000 and compatibility views on SQL
>> > > > > Server 2005.
>> > > > >
>> > > > > Hope this helps,
>> > > > >
>> > > > > Ben Nevarez
>> > > > > Senior Database Administrator
>> > > > > AIG SunAmerica
>> > > > >
>> > > > >
>> > > > >
>> > > > > "kei" wrote:
>> > > > >
>> > > > > > for e.g.
>> > > > > > in 2005
>> > > > > > SELECT * FROM sys.foreign_keys....(no error)
>> > > > > > in sql server 2000
>> > > > > > SELECT * FROM sys.foreign_keys....(Invalid object name
>> > > > > > 'sys.foreign_keys'.)
>> > > > > >
>> > > > > > SELECT * FROM sys.indexes WHERE object_id = .......
>> > > > > > (no problem in sql server 2005, but have problem in sql server
>> > > > > > 2000)
>> > > > > >
>> > > > > > so how to handle these case?
>> > > > > > Is it possible to write script which can be run on both sql
>> > > > > > server 2005 and
>> > > > > > sql server 2000?

Script databsae does not compatible with SQL2000

I've created a databse on SQL2005, and I did not use any new data type for columns. I script the database, and select SQL2000 for "Script for Server version", howvever, the script generated cannot be run on SQL2000, it can be only run on SQL2005.

The script generated by SQL2005 is not backward compatible? like SQL2000 can generated script compatible wtih SQL7.

CREATE TABLE [dbo].[Table1](
[Col1] [nvarchar](16) NOT NULL,
[Col2] [nvarchar](100) NOT NULL,
[Col3] [nvarchar](10) NULL,
[Col4] [datetime] NULL,
[Col5] [nvarchar](10) NULL,
[Col6] [datetime] NULL,
[Col7] [bit] NULL CONSTRAINT [DF_Table1_Col7] DEFAULT ((0)),
[Col8] [bit] NULL CONSTRAINT [DF_Table1_Col8] DEFAULT ((0)),
[Col9] [int] NULL CONSTRAINT [DF_Table1_Col9] DEFAULT ((0)),
CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
(
[Col1] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

Error detected on the line "IGNORE_DUP_KEY = OFF".

This is a known bug. From the feedback center:

http://lab.msdn.microsoft.com/productfeedback/viewfeedback.aspx?feedbackid=a6510471-9c40-4184-9611-5656457864d8

Edit: I have tried it and it does seem to be fixed in the SP1 CTM

Louis

|||Generally, you should not rely on the scripting capabilities other than for ad-hoc stuff. It is better to maintain your scripts in a source code control system and use it for reference / maintenance. There are other problems with scripting other than syntax issue with the CONSTRAINT in your example. For example, resolving dependencies is not accurate since the engine itself doesn't guarantee it for all cases. There are also issues with expressions which are normalized / modified by the engine so if you try to compare the script generated from the engine with your source it will not match even though they produce identical results.|||I'm sorry, but I can't hold back. The condescension in your reply to this stranded user is almost palpable. These types of responses are absolutely maddening, especially when the bugs in the SQL Server 2005 tools are so *painfully* obvious upon even casual observation. People don't need lectures on why you shouldn't be using a feature YOU included in the server which doesn't work because YOU did incredibly inadequate testing before you shipped. Seriously, just about any script you create from SQL Mgmt Studio which you want to run on 2000 will not work because it uses sys.objects. That bug should have never been included in the release, and frankly is a sign of a real management problem inside your team which needs to be addressed.|||

I won't dispute the 2005 tools have errors comment, and neither did he (in fact I would sing in that chorus with you if need be). However, he post was not a "well, you shouldn't be using it anyway" comment, I don't think. He goes on to explain that some parts of scripting out objects is just really quite hard (like maintaining precedence of which script to run first.)

As a side note, the sys.objects issue has been changed in the CTP as well for the 2000 compatible scripting.

All he was trying to say was that the best way to use the tools and do development is to maintain scripts of your work, and not rely on the tools to script out a database. I doubt that he was suggesting to never use the scripting tools for any reason. (especially since he advocated it for ad-hoc stuff.) I know I use it quite often, if for no other reason than to post a table structure to a discussion. But for a production system you should generally should have scripts for all objects that were created outside of using the SSMS tools and then scripting the objects.

It is very common to include a mini-lecture with any post where someone sees that a person *might* be abusing a feature. I learned a ton from my early days in the newsgroups because a friendly user or two (and an unfriendly albeit, very intelligent, jerk) lectured me on how something should be done.

|||Thanks, ***. Fix your God damned product or don't ship it. SQL 2005 is ***.

Script Conversion

Hey,
Here is an SQL script that works in Sybase and I need a script that does the same thing but works for DB2. Does anyone know what it is?
SELECT type, price, advance FROM test_table ORDER BY type COMPUTE SUM(price), SUM(advance) BY type
Thanks for you helpNever mind...

I discovered that DB2 doesn't support "Compute" rows

__________________________
Help Cure Cancer (http://www.scuzzy.refhost.net/HCC)

scopeing problem in an aggregate function

I have a matrix with two column groups. I need to be able have the user
select from my boolean parameter and change the scope on my aggregate
function. I already have my boolean parameter setup. the two groups are
called matrix1_Year and matrix1_Hub. i used the following expression in my
textbox
=Count(Fields!DeliveryNumber.Value)/Count(Fields!DeliveryNumber.Value,IIF(Parameters!Scope1.Value,"matrix1_Year","matrix1_hub"))
when i preview the report it gives me the following error
The Value expression for the textbox â'textbox5â' has a scope parameter that
is not valid for an aggregate function. The scope parameter must be set to a
string constant that is equal to either the name of a containing group, the
name of a containing data region, or the name of a data set.
If i take the IIF statement out and put in either of the group names, the
report works and i get the values i would expect, but i need to be able to do
this by passing in a parameter, i don't want to create two report just for
this one problem.Have you tried adding the IIF to the outside... ie
=IIf (Parameters@.Scope1.Value,Big Expression1, Big Expression2)
I do not know if this will help, but it is possible that the problem is
related to WHEN the expression is evaluated..
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"gacar101" wrote:
> I have a matrix with two column groups. I need to be able have the user
> select from my boolean parameter and change the scope on my aggregate
> function. I already have my boolean parameter setup. the two groups are
> called matrix1_Year and matrix1_Hub. i used the following expression in my
> textbox
> =Count(Fields!DeliveryNumber.Value)/Count(Fields!DeliveryNumber.Value,IIF(Parameters!Scope1.Value,"matrix1_Year","matrix1_hub"))
> when i preview the report it gives me the following error
> The Value expression for the textbox â'textbox5â' has a scope parameter that
> is not valid for an aggregate function. The scope parameter must be set to a
> string constant that is equal to either the name of a containing group, the
> name of a containing data region, or the name of a data set.
> If i take the IIF statement out and put in either of the group names, the
> report works and i get the values i would expect, but i need to be able to do
> this by passing in a parameter, i don't want to create two report just for
> this one problem.

SCOPE_IDENTITY returns decimal from SQL command

Hi,

I have an sqlCommand return the ID from the last inserted row. I use SELECT SCOPE_IDENTITY within the SQL.

When I call the function it returns a Decimal instead of an integer??

So I want to convert this to an integer, however this is not liked by the compiler since the overloaded method for the decimal conversion is obviously a decimal, but the ExecuteScalar parameter is of type object.

iReturnVal = decimal.ToInt32(_sqlCommand.ExecuteScalar()); <-- compiler complains...

So I changed to this:

iReturnValue = decimal.ToInt32((decimal)_sqlCommand.ExecuteScalar());

Can someone tell me if this is the right way to do it? Seems messy code to me...

swaino:

iReturnVal = decimal.ToInt32(_sqlCommand.ExecuteScalar()); <-- compiler complains...

Decimal.toint32? I thought it was convert.toint32?

|||

It should already be an integer.

|||

Welldecimal. provides loads of conversion functions.

Also ExecuteScalardoes return an object not an integer..

SCOPE_IDENTITY on Subscriber

We are doing immediate updating transactional replication between a
single publisher and a single subscriber.
When we try to select SCOPE_IDENTITY or @.@.IDENTITY on the Subscriber
we always receive NULL as the result. When we call them on the
Publisher we receive the correct value.
We have the Identity fields setup on the Publisher but on the
Subscriber they are only basic data types as described in the SQL
Server books online.
So my question is, is this functionality by design? And if so, how
can we obtain the Identity values of newly inserted records on the
subscriber?
Or should we be receiving the Identity values back on the subscriber
but something configured incorrectly.
Thanks
Joseph Palermo
this sounds correct.
The way it works is that an update/insert/delete which occurs on the
subscriber is first applied on the publisher and then the publisher - so the
publisher sets the identity value.
"Joseph Palermo" <joe.groups@.bigrawr.com> wrote in message
news:3d78705f.0405051554.65b69c62@.posting.google.c om...
> We are doing immediate updating transactional replication between a
> single publisher and a single subscriber.
> When we try to select SCOPE_IDENTITY or @.@.IDENTITY on the Subscriber
> we always receive NULL as the result. When we call them on the
> Publisher we receive the correct value.
> We have the Identity fields setup on the Publisher but on the
> Subscriber they are only basic data types as described in the SQL
> Server books online.
> So my question is, is this functionality by design? And if so, how
> can we obtain the Identity values of newly inserted records on the
> subscriber?
> Or should we be receiving the Identity values back on the subscriber
> but something configured incorrectly.
> Thanks
> Joseph Palermo
|||Also, if you want the identity values to be created on the
subscriber, then you can use queued updating subscribers,
in which case the identity ranges will be managed on each
subscriber separately. This would give you access to the
@.@.identity value (or scope_identity()).
HTH,
Paul Ibison