anyone know the best way to do this

Hi I am currently performing the following tasks manually and was wondering
if anyone might suggest if a script,dts or possibly .net application could do
this.
1. get file name from user
2. copy file from one server to another (path does not change so could be
hardcoded)
3. insert the name of the file in a database table (SQL2000).
thanks.
--
Paul G
Software engineer.More info needed here - what interface does the user have to the
application - where are they giving the file name to you? In a SQL
context or Application context?
If assumptions are kept simple.....
1. depends on the application, but shouldn't be too hard
2. USE Master
GO
EXEC xp_cmdshell 'copy "\\server1\filepath\filename.xyz"
"\\server2\filepath\filename.xyz"'
3. USE Userdb
GO
INSERT table
select 'filename'
Maybe write a SP with a filename parameter and execute #2 & #3 with the
same SP.|||Hi thanks for the information. Sounds like you can perform a copy with the
command you provided.Think I will just use a .net application that calls a
stored procedure passing the name of the file as an input to the procedure.
will use this command as you provided.
EXEC xp_cmdshell 'copy "\\server1\filepath\filename.xyz"
"\\server2\filepath\filename.xyz"'
--
Paul G
Software engineer.
"unc27932@.yahoo.com" wrote:
> More info needed here - what interface does the user have to the
> application - where are they giving the file name to you? In a SQL
> context or Application context?
> If assumptions are kept simple.....
> 1. depends on the application, but shouldn't be too hard
> 2. USE Master
> GO
> EXEC xp_cmdshell 'copy "\\server1\filepath\filename.xyz"
> "\\server2\filepath\filename.xyz"'
> 3. USE Userdb
> GO
> INSERT table
> select 'filename'
> Maybe write a SP with a filename parameter and execute #2 & #3 with the
> same SP.
>|||Yes - check out BOL on xp_cmdshell. Beware though - the logged in user
(or application) will have to have the correct OS permissions on the
aforementioned directories (I believe)....Can anyone else confirm?
Also - I wouldn't give your users a chance to input a command...just
give them the ability to enter a filepath and you hardcode the
command/sp in your application. Otherwise, they could do nasty things
with xp_cmdshell, as it's an open window into the OS.|||ok thanks was thinking of hardcoding the path and then building the rest of
the string with the file name that is passed into the stored procedure. Was
also thinking of somehow testing the filename input (probably in the .net
app) and not call the stored procedure unless a valid filename is supplied.
Will most likely only be 1 user other than myself but also thinking they
would have to have permissions to the directories, source and destination.
--
Paul G
Software engineer.
"unc27932@.yahoo.com" wrote:
> Yes - check out BOL on xp_cmdshell. Beware though - the logged in user
> (or application) will have to have the correct OS permissions on the
> aforementioned directories (I believe)....Can anyone else confirm?
> Also - I wouldn't give your users a chance to input a command...just
> give them the ability to enter a filepath and you hardcode the
> command/sp in your application. Otherwise, they could do nasty things
> with xp_cmdshell, as it's an open window into the OS.
>sql

anyone know the best way to do this

Hi I am currently performing the following tasks manually and was wondering
if anyone might suggest if a script,dts or possibly .net application could d
o
this.
1. get file name from user
2. copy file from one server to another (path does not change so could be
hardcoded)
3. insert the name of the file in a database table (SQL2000).
thanks.
--
Paul G
Software engineer.More info needed here - what interface does the user have to the
application - where are they giving the file name to you? In a SQL
context or Application context?
If assumptions are kept simple.....
1. depends on the application, but shouldn't be too hard
2. USE Master
GO
EXEC xp_cmdshell 'copy "\\server1\filepath\filename.xyz"
"\\server2\filepath\filename.xyz"'
3. USE Userdb
GO
INSERT table
select 'filename'
Maybe write a SP with a filename parameter and execute #2 & #3 with the
same SP.|||Hi thanks for the information. Sounds like you can perform a copy with the
command you provided.Think I will just use a .net application that calls a
stored procedure passing the name of the file as an input to the procedure.
will use this command as you provided.
EXEC xp_cmdshell 'copy "\\server1\filepath\filename.xyz"
"\\server2\filepath\filename.xyz"'
Paul G
Software engineer.
"unc27932@.yahoo.com" wrote:

> More info needed here - what interface does the user have to the
> application - where are they giving the file name to you? In a SQL
> context or Application context?
> If assumptions are kept simple.....
> 1. depends on the application, but shouldn't be too hard
> 2. USE Master
> GO
> EXEC xp_cmdshell 'copy "\\server1\filepath\filename.xyz"
> "\\server2\filepath\filename.xyz"'
> 3. USE Userdb
> GO
> INSERT table
> select 'filename'
> Maybe write a SP with a filename parameter and execute #2 & #3 with the
> same SP.
>|||Yes - check out BOL on xp_cmdshell. Beware though - the logged in user
(or application) will have to have the correct OS permissions on the
aforementioned directories (I believe)....Can anyone else confirm?
Also - I wouldn't give your users a chance to input a command...just
give them the ability to enter a filepath and you hardcode the
command/sp in your application. Otherwise, they could do nasty things
with xp_cmdshell, as it's an open window into the OS.|||ok thanks was thinking of hardcoding the path and then building the rest of
the string with the file name that is passed into the stored procedure. Was
also thinking of somehow testing the filename input (probably in the .net
app) and not call the stored procedure unless a valid filename is supplied.
Will most likely only be 1 user other than myself but also thinking they
would have to have permissions to the directories, source and destination.
--
Paul G
Software engineer.
"unc27932@.yahoo.com" wrote:

> Yes - check out BOL on xp_cmdshell. Beware though - the logged in user
> (or application) will have to have the correct OS permissions on the
> aforementioned directories (I believe)....Can anyone else confirm?
> Also - I wouldn't give your users a chance to input a command...just
> give them the ability to enter a filepath and you hardcode the
> command/sp in your application. Otherwise, they could do nasty things
> with xp_cmdshell, as it's an open window into the OS.
>

anyone know the best way to do this

Hi I am currently performing the following tasks manually and was wondering
if anyone might suggest if a script,dts or possibly .net application could do
this.
1. get file name from user
2. copy file from one server to another (path does not change so could be
hardcoded)
3. insert the name of the file in a database table (SQL2000).
thanks.
Paul G
Software engineer.
More info needed here - what interface does the user have to the
application - where are they giving the file name to you? In a SQL
context or Application context?
If assumptions are kept simple.....
1. depends on the application, but shouldn't be too hard
2. USE Master
GO
EXEC xp_cmdshell 'copy "\\server1\filepath\filename.xyz"
"\\server2\filepath\filename.xyz"'
3. USE Userdb
GO
INSERT table
select 'filename'
Maybe write a SP with a filename parameter and execute #2 & #3 with the
same SP.
|||Hi thanks for the information. Sounds like you can perform a copy with the
command you provided.Think I will just use a .net application that calls a
stored procedure passing the name of the file as an input to the procedure.
will use this command as you provided.
EXEC xp_cmdshell 'copy "\\server1\filepath\filename.xyz"
"\\server2\filepath\filename.xyz"'
Paul G
Software engineer.
"unc27932@.yahoo.com" wrote:

> More info needed here - what interface does the user have to the
> application - where are they giving the file name to you? In a SQL
> context or Application context?
> If assumptions are kept simple.....
> 1. depends on the application, but shouldn't be too hard
> 2. USE Master
> GO
> EXEC xp_cmdshell 'copy "\\server1\filepath\filename.xyz"
> "\\server2\filepath\filename.xyz"'
> 3. USE Userdb
> GO
> INSERT table
> select 'filename'
> Maybe write a SP with a filename parameter and execute #2 & #3 with the
> same SP.
>
|||Yes - check out BOL on xp_cmdshell. Beware though - the logged in user
(or application) will have to have the correct OS permissions on the
aforementioned directories (I believe)....Can anyone else confirm?
Also - I wouldn't give your users a chance to input a command...just
give them the ability to enter a filepath and you hardcode the
command/sp in your application. Otherwise, they could do nasty things
with xp_cmdshell, as it's an open window into the OS.
|||ok thanks was thinking of hardcoding the path and then building the rest of
the string with the file name that is passed into the stored procedure. Was
also thinking of somehow testing the filename input (probably in the .net
app) and not call the stored procedure unless a valid filename is supplied.
Will most likely only be 1 user other than myself but also thinking they
would have to have permissions to the directories, source and destination.
Paul G
Software engineer.
"unc27932@.yahoo.com" wrote:

> Yes - check out BOL on xp_cmdshell. Beware though - the logged in user
> (or application) will have to have the correct OS permissions on the
> aforementioned directories (I believe)....Can anyone else confirm?
> Also - I wouldn't give your users a chance to input a command...just
> give them the ability to enter a filepath and you hardcode the
> command/sp in your application. Otherwise, they could do nasty things
> with xp_cmdshell, as it's an open window into the OS.
>

Anyone has the script to verify the constraints and indexes?

Anyone has the script to verify the constraints and
indexes? Thanks.
Bill
Can you provide some more details? What do you mean by verify?
Anith
|||Thanks for the reply. I mean how to find out the primary
key and foreign key information from a database.
|||For a single table you can use the system procedures sp_pkeys & sp_fkeys to
find out the primary keys & foriegn keys respectively.
To get the list of all the primary keys in a database, you can use the
INFORMATION_SCHEMA views or system tables, perhaps with a couple of
meta-data functions. For instance, to get the list of foriegn keys you could
do:
SELECT s4.name AS "FK Name",
s3.name AS "Referencing Table",
s6.name AS "Referencing Column",
s1.name AS "Referenced Table",
s5.name AS "Referenced Column"
FROM sysobjects s1
INNER JOIN sysforeignkeys s2
ON s1.id = s2.rkeyid
INNER JOIN sysobjects s3
ON s3.id = s2.fkeyid
INNER JOIN sysobjects s4
ON s4.id = s2.constid
INNER JOIN syscolumns s5
ON s5.id = s1.id AND s2.rkey = s5.colid
INNER JOIN syscolumns s6
ON s3.id = s6.id AND s2.fkey = s6.colid ;
A simpler approach woule be to use the system table sysforeignkeys like :
SELECT OBJECT_NAME( constid ) AS "FK Name",
OBJECT_NAME( fkeyid ) AS "Referencing table",
COL_NAME( fkeyid, fkey ) AS "Referencing column",
OBJECT_NAME( rkeyid ) AS "Referenced Table",
COL_NAME( rkeyid, rkey ) AS "Referenced Column"
FROM sysforeignkeys ;
Similarly to get the primary keys, you could do something like:
SELECT TABLE_NAME,
CONSTRAINT_NAME AS "PK Name",
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE CONSTRAINT_TYPE = 'PRIMARY KEY'
-- AND TABLE_SCHEMA = 'dbo';
Or there are several ways to do this as well using the system tables
directly. You can download a copy of the system table map from :
http://www.microsoft.com/sql/techinf.../systables.asp
Also, you can get a copy of the INFORMATION_SCHEMA view maps from:
http://www.dbmaint.com/download/info_schema/
Anith

Anyone has the script to verify the constraints and indexes?

Anyone has the script to verify the constraints and
indexes? Thanks.
BillCan you provide some more details? What do you mean by verify?
--
Anith|||Thanks for the reply. I mean how to find out the primary
key and foreign key information from a database.|||For a single table you can use the system procedures sp_pkeys & sp_fkeys to
find out the primary keys & foriegn keys respectively.
To get the list of all the primary keys in a database, you can use the
INFORMATION_SCHEMA views or system tables, perhaps with a couple of
meta-data functions. For instance, to get the list of foriegn keys you could
do:
SELECT s4.name AS "FK Name",
s3.name AS "Referencing Table",
s6.name AS "Referencing Column",
s1.name AS "Referenced Table",
s5.name AS "Referenced Column"
FROM sysobjects s1
INNER JOIN sysforeignkeys s2
ON s1.id = s2.rkeyid
INNER JOIN sysobjects s3
ON s3.id = s2.fkeyid
INNER JOIN sysobjects s4
ON s4.id = s2.constid
INNER JOIN syscolumns s5
ON s5.id = s1.id AND s2.rkey = s5.colid
INNER JOIN syscolumns s6
ON s3.id = s6.id AND s2.fkey = s6.colid ;
A simpler approach woule be to use the system table sysforeignkeys like :
SELECT OBJECT_NAME( constid ) AS "FK Name",
OBJECT_NAME( fkeyid ) AS "Referencing table",
COL_NAME( fkeyid, fkey ) AS "Referencing column",
OBJECT_NAME( rkeyid ) AS "Referenced Table",
COL_NAME( rkeyid, rkey ) AS "Referenced Column"
FROM sysforeignkeys ;
Similarly to get the primary keys, you could do something like:
SELECT TABLE_NAME,
CONSTRAINT_NAME AS "PK Name",
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE CONSTRAINT_TYPE = 'PRIMARY KEY'
-- AND TABLE_SCHEMA = 'dbo';
Or there are several ways to do this as well using the system tables
directly. You can download a copy of the system table map from :
http://www.microsoft.com/sql/techinfo/productdoc/2000/systables.asp
Also, you can get a copy of the INFORMATION_SCHEMA view maps from:
http://www.dbmaint.com/download/info_schema/
--
Anith

Any way to generate SQL Script for application role?

Is there any way to generate the SQL to create an application role and all t
he associated grants for table and stored procedure access? Or does anyone h
ave a suggestion for how to migrate an application role from one environment
to another? The developers
originally create the role by clicking on things.The easiest way is to script the existing application role permissions
and edit the script for the new environment. See sp_addapprole in SQL
BOL for the syntax for creating a new application role.
--Mary
On Fri, 16 Apr 2004 10:01:10 -0700, Charlotte
<anonymous@.discussions.microsoft.com> wrote:

>Is there any way to generate the SQL to create an application role and all the asso
ciated grants for table and stored procedure access? Or does anyone have a suggestio
n for how to migrate an application role from one environment to another? The develo
per
s originally create the role by clicking on things.sql

any way to check the duplicated rows in destination before loading data?

Hi. As the title, I am try to figure out how to write script to prevent duplicated rows before loading data from couple csv files to the OLE database table.
Another quick question, when I use Data Conversion to convert data from string to datetime or decimal type, it always return error like potential data loss.

For your first question, probably an easier approach is to use a SORT transform to remove duplicate records before loading data into your destination.

For the other question, I think it's a matter of which format you used in your source strings. Firstly pls be aware we use locale information when doing converting strings to date types or decimals. Secondly, when converting string to date types, you have two options: normal conversion and fast-parse conversion. Normal conversion supports standard oledb formats while fastparse supports ISO 8601. (fastparse option is on the DataConversion output columns)

You'll need to get more detailed helps on this from SQLServer Books On Line. e.g. For fastparse, pls refer ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/extran9/html/bed6e2c1-791a-4fa1-b29f-cbfdd1fa8d39.htm

thanks

wenyang

|||Thanks for your fast response. My first question is to load date from CSV files to the table, but don't insert the duplicated rows which are already existed in the table.|||

I see. you want to avoid inserting rows which'll duplicate rows in your existing destination table. In that case, you can do a lookup first, then leading only those "failing" rows to destination. Remember to set Lookup's error flow handling to Redirect.

thanks

wenyang

Any way to call a Package from a Script Component?

Just wondering if it's possible to call a package from within a script component. I'd think so, but not quite sure how to.

Thanks,

Jeff Tolman
E&M Electric

Do you mean you want to call a package for each input row?

There is no built-in support for this, but you can execute package using DTEXEC.EXE utility. Use System.Diagnostics.Process class to start the process.|||

Yes, after a row is processed I'd like to run another package. The DTEXEC.EXE utility probably would not work since it would be run in another thread space and there probably would be no way to monitor when that process completed.

The SSIS Script component (VSA) editor doesn't seem to make it easy to reuse pieces of code between packages, otherwise I wouldn't need to call a package. I read in the Help files that if you need to use code across packages then it would best to create a user component, but unfortunately I don't have the time to learn how to do that.

Thanks for your help Michael.

Jeff

|||

Check out sample @. http://mystutter.blogspot.com/2006/03/ssis-2005-returning-values-to-calling.html

The author calls another DTSX package from the script task component.

Thanks,
Loonysan

Any way to avoid using a cursor and a script on this one?

A while back a db expert I was talking to expressed the opinion that
cursors were overused, mostly by programmers who were thinking like
programmers instead of db people.
I have a task to do and I'm not sure I can do it without a cursor.
I'm curious to see if anyone can think of a way around it.
In a nutshell I want to query the database to find all tables that have
a particular field( "comment"), include only results where that field
has one of 20 substrings in the content, and then print it all out.
So I have
(substring1, substring2 substring3 ...substring20)
and I would like to get output like
table_name comment substring
-- -- --
If I can find another way than using cursors or brute force ( many
cut-n-pasted tsql statements ) I would be grateful.
Thanks in advance for any thoughts
SteveWell this sounds like a one of a kind request. If it isn't you have some
serious design flaws. A cursor is useful for something like that where you
need to navigate multiple objects dynamically. What you should not use a
cursor for are things that can get the results via a SET based approach. For
instance you would not create a cursor to navigate each row of the table to
search for yoru string. You would do that in a SET based fashion.
Andrew J. Kelly SQL MVP
"Steve" <stevesusenet@.yahoo.com> wrote in message
news:1139531021.868263.148350@.g47g2000cwa.googlegroups.com...
>A while back a db expert I was talking to expressed the opinion that
> cursors were overused, mostly by programmers who were thinking like
> programmers instead of db people.
> I have a task to do and I'm not sure I can do it without a cursor.
> I'm curious to see if anyone can think of a way around it.
> In a nutshell I want to query the database to find all tables that have
> a particular field( "comment"), include only results where that field
> has one of 20 substrings in the content, and then print it all out.
> So I have
> (substring1, substring2 substring3 ...substring20)
> and I would like to get output like
> table_name comment substring
> -- -- --
> If I can find another way than using cursors or brute force ( many
> cut-n-pasted tsql statements ) I would be grateful.
> Thanks in advance for any thoughts
> Steve
>|||Hello, Steve
Let's take it one step at a time. First, we need a list of tables that
have a column named "comment":
SELECT o.name FROM syscolumns c INNER JOIN sysobjects o ON c.id=o.id
WHERE o.xtype='U' AND c.name='comment'
Then, for each of the above tables, we would want to do something like
this:
SELECT 'Some table' as table_name, comment, sub_string
FROM [Some table] t INNER JOIN (
SELECT 'substring1' sub_string
UNION SELECT 'substring2'
UNION SELECT 'substring3'
/* ... */
) x ON t.comment LIKE '%'+sub_string+'%'
Instead of writing the substrings like above, we can use a temporary
table to store them:
CREATE TABLE #substrings (sub_string varchar(50) PRIMARY KEY)
INSERT INTO #substrings VALUES ('substring1')
INSERT INTO #substrings VALUES ('substring2')
INSERT INTO #substrings VALUES ('substring3')
/* ... */
Now we can generate the SELECT-s for all the tables, using the
following query:
SELECT '
SELECT '''+o.name+''' as table_name, comment, sub_string
FROM ['+o.name+'] t INNER JOIN #substrings x
ON t.comment LIKE ''%''+sub_string+''%''
' FROM syscolumns c INNER JOIN sysobjects o ON c.id=o.id
WHERE o.xtype='U' AND c.name='comment'
After executing the above query, copy the results into another query
window and execute them.
We can stop here, but let's make it more automatic: we can use a WHILE
loop to execute each statement (yes, this resembles a cursor very
much...) that puts the results in a temporary table:
CREATE TABLE #results (table_name sysname, comment ntext, sub_string
varchar(50))
create table #statements (id int identity primary key, sql
nvarchar(4000))
insert into #statements
SELECT '
INSERT INTO #results SELECT '''+o.name+''' as table_name, comment,
sub_string
FROM ['+o.name+'] t INNER JOIN #substrings x
ON t.comment LIKE ''%''+sub_string+''%''
' FROM syscolumns c INNER JOIN sysobjects o ON c.id=o.id
WHERE o.xtype='U' AND c.name='comment'
declare @.id int, @.sql nvarchar(4000)
set @.id=0
while 1=1 begin
set @.id=(select min(id) from #statements where id>@.id)
if @.id is null break
select @.sql=sql from #statements where id=@.id
exec (@.sql)
end
drop table #statements
select * from #results
drop table #results
If you don't like the WHILE loop, we can write something shorter, using
an undocumented stored procedure: sp_execresultset (of course, using
undocumented features is not recommended, but this is a one-time job,
isn't it?)
CREATE TABLE #results (table_name sysname, comment ntext, sub_string
varchar(50))
EXEC sp_execresultset '
SELECT ''
INSERT INTO #results SELECT '''+o.name+''' as table_name,
comment, sub_string
FROM [''+o.name+''] t INNER JOIN #substrings x
ON t.comment LIKE ''''%''''+sub_string+''''%''''
'' FROM syscolumns c INNER JOIN sysobjects o ON c.id=o.id
WHERE o.xtype=''U'' AND c.name=''comment''
'
select * from #results
drop table #results
Razvan|||Thanks for the code Razvan!|||Steve wrote:
> A while back a db expert I was talking to expressed the opinion that
> cursors were overused, mostly by programmers who were thinking like
> programmers instead of db people.
> I have a task to do and I'm not sure I can do it without a cursor.
> I'm curious to see if anyone can think of a way around it.
> In a nutshell I want to query the database to find all tables that have
> a particular field( "comment"), include only results where that field
> has one of 20 substrings in the content, and then print it all out.
> So I have
> (substring1, substring2 substring3 ...substring20)
> and I would like to get output like
> table_name comment substring
> -- -- --
> If I can find another way than using cursors or brute force ( many
> cut-n-pasted tsql statements ) I would be grateful.
> Thanks in advance for any thoughts
> Steve
A strange thing to want to do at runtime. Why don't you know what
columns exist in your database? Sensible data manipulation requirements
against a known, static database schema can indeed be done 99.99% of
the time without using cursors.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Any setting that would reduce locking during a SQL script?

I am running an upgrade script to update a database from one release to
another. There are a lot of SQL scripts that run during this process.
While this process is running - there should be no other users accessing the
database.
With this in mind - I am hoping to reduce the amount of locking that occurs.
Since no one else will be accessing the system - I don't need all of the
locks at the granular level (I am fine with table locks). Is there some way
that I could make it so that I get table locks instead of row-level locking?
Thanks in advance.You can specify the TABLOCKX hint to acquire an exclusive table lock. For
example:
INSERT INTO MyTable WITH (TABLOCKX)
SELECT * FROM MyOtherTable WITH (TABLOCKX)
Hope this helps.
Dan Guzman
SQL Server MVP
"TJTODD" <Thxomasx.Toddy@.Siemensx.com> wrote in message
news:%23yBrPIH4DHA.2380@.TK2MSFTNGP10.phx.gbl...

quote:

---

> I am running an upgrade script to update a database from one release to
> another. There are a lot of SQL scripts that run during this process.
> While this process is running - there should be no other users accessing

---

the

quote:

---

> database.
> With this in mind - I am hoping to reduce the amount of locking that

---

occurs.

quote:

---

> Since no one else will be accessing the system - I don't need all of the
> locks at the granular level (I am fine with table locks). Is there some

---

way

quote:

---

> that I could make it so that I get table locks instead of row-level

---

locking?

quote:

---

> Thanks in advance.
>

---

|||One to look at will be setting the database into single user mode.
I'm not sure whether it causes locks to escalate, but admin work such as
you're performing is one of it's indended usage scenarios.
You can set a db into single_user by using the alter database command, eg:
alter database [dbname] set single_user
Regards,
Greg Linwood
"TJTODD" <Thxomasx.Toddy@.Siemensx.com> wrote in message
news:%23yBrPIH4DHA.2380@.TK2MSFTNGP10.phx.gbl...

quote:

---

> I am running an upgrade script to update a database from one release to
> another. There are a lot of SQL scripts that run during this process.
> While this process is running - there should be no other users accessing

---

the

quote:

---

> database.
> With this in mind - I am hoping to reduce the amount of locking that

---

occurs.

quote:

---

> Since no one else will be accessing the system - I don't need all of the
> locks at the granular level (I am fine with table locks). Is there some

---

way

quote:

---

> that I could make it so that I get table locks instead of row-level

---

locking?

quote:

---

> Thanks in advance.
>

---

Any setting that would reduce locking during a SQL script?

I am running an upgrade script to update a database from one release to
another. There are a lot of SQL scripts that run during this process.
While this process is running - there should be no other users accessing the
database.
With this in mind - I am hoping to reduce the amount of locking that occurs.
Since no one else will be accessing the system - I don't need all of the
locks at the granular level (I am fine with table locks). Is there some way
that I could make it so that I get table locks instead of row-level locking?
Thanks in advance.You can specify the TABLOCKX hint to acquire an exclusive table lock. For
example:
INSERT INTO MyTable WITH (TABLOCKX)
SELECT * FROM MyOtherTable WITH (TABLOCKX)
--
Hope this helps.
Dan Guzman
SQL Server MVP
"TJTODD" <Thxomasx.Toddy@.Siemensx.com> wrote in message
news:%23yBrPIH4DHA.2380@.TK2MSFTNGP10.phx.gbl...
> I am running an upgrade script to update a database from one release to
> another. There are a lot of SQL scripts that run during this process.
> While this process is running - there should be no other users accessing
the
> database.
> With this in mind - I am hoping to reduce the amount of locking that
occurs.
> Since no one else will be accessing the system - I don't need all of the
> locks at the granular level (I am fine with table locks). Is there some
way
> that I could make it so that I get table locks instead of row-level
locking?
> Thanks in advance.
>|||One to look at will be setting the database into single user mode.
I'm not sure whether it causes locks to escalate, but admin work such as
you're performing is one of it's indended usage scenarios.
You can set a db into single_user by using the alter database command, eg:
alter database [dbname] set single_user
Regards,
Greg Linwood
"TJTODD" <Thxomasx.Toddy@.Siemensx.com> wrote in message
news:%23yBrPIH4DHA.2380@.TK2MSFTNGP10.phx.gbl...
> I am running an upgrade script to update a database from one release to
> another. There are a lot of SQL scripts that run during this process.
> While this process is running - there should be no other users accessing
the
> database.
> With this in mind - I am hoping to reduce the amount of locking that
occurs.
> Since no one else will be accessing the system - I don't need all of the
> locks at the granular level (I am fine with table locks). Is there some
way
> that I could make it so that I get table locks instead of row-level
locking?
> Thanks in advance.
>

Any Faster Way To Process This?

Hello All.

I have this script that I need to run every Saturday evening and it takes more than 5 hours to complete. Is there a better way to structure my script? Please advise. Thank you.

update Table_A set financial_yr = t2.Master_financial_year,
financial_period = t2.Master_financial_period
from Master_financial_Table t2
where frst_requested_date >= t2.Start_date and frst_requested_date <= t2.End_date
and financial_year>2000

There are a total of 4.6 million records (and growing weekly) in Table_A and 42 records in Master_financial_table (standard)

Indexes have been created for these tables.Maybe this will be faster
You don't read 4 million times the Master_financial_Table

Declare @.Master_financial_year DateTime
Declare @.Master_financial_period DateTime
Declare @.Start_date DateTime
Declare @.End_date DateTime

Select @.Master_financial_year=Master_financial_year,
@.Master_financial_period=Master_financial_period,
@.Start_date=Start_date,
@.End_date=End_date
From Master_financial_Table

Update Table_A
Set financial_yr=@.Master_financial_year,
financial_period = @.Master_financial_period
Where frst_requested_date between @.Start_date and @.End_date and
financial_year>2000|||I suspect that Table_A will have been given a non-clustered index on frst_requested_date. Given that where frst_requested_date >= t2.Start_date and frst_requested_date <= t2.End_date is going to select a very large number of records, if Table_A has clustered index, you should consider removing it.

In SQL Server 2000, if a table has a clustered index, the leaf nodes of all non-clustered indexes contain the key values of the clustered index corresponding to the index match. So, when a non-clustered index match is found it is followed by a bookmark-lookup on the tables clustered index. On *very* large tables even a highly specific index can return many thousands of records, thereby inducing thousands of clustered index seeks in order to resolve the secondary bookmark lookup. SQL Server should be smart enough to realize a table scan is going to be better, but often it doesnt, and so very large clustered tables exhibit unpleasant performance when a query is conducted through a non-clustered index.

If a table is non-clustered (or heaped), the leaf nodes of all its indexes consist of offset pointers directly into the data blob. This has a significant performance risk in that any index page splitting becomes expensive to fixup on insert or update, but all indexes recover their data by jumping directly to the data row without thousands or millions of bookmark lookups or full blown table scans.

If you can generate a query plan and discover many bookmark lookups are taking place, its worth considering unclustering Table_A.|||If a table is non-clustered (or heaped), the leaf nodes of all its indexes consist of offset pointers directly into the data blob. This has a significant performance risk in that any index page splitting becomes expensive to fixup on insert or update, but all indexes recover their data by jumping directly to the data row without thousands or millions of bookmark lookups or full blown table scans.

I disagree. If the table is a heap the pointers point to the ROWID, which takes into place dbname, table, page, etc. Not sure what you mean by data blob.

and so very large clustered tables exhibit unpleasant performance when a query is conducted through a non-clustered index.

I disagree again. SQL will choose to do a clustered index scan if a table scan is more efficient, a clustered index seek is actually using the index and SQL will usually choose to ignore the NCI and just scan the table (clustered index scan) Also, while you mention pagesplitting, a heap reclaims empty space, this is horrible as it takes time for SQL to find the empty space. Also, choosing the right clustered key will keep your inserts fast and your NCI lookups fast as your key should be small (smaller than the ROWID lookup)

I have found there are very rare occasions when a clustered index should not be on a table.

For the poster - mess with your indexes and play with the "set statistics IO on" command, this will show you how many page reads your query costs you and allow you the ability to tell if removing or adding an index will really benefit you or hurt you.

HTH|||Originally posted by rhigdon
I disagree. If the table is a heap the pointers point to the ROWID, which takes into place dbname, table, page, etc. Not sure what you mean by data blob.



I disagree again. SQL will choose to do a clustered index scan if a table scan is more efficient, a clustered index seek is actually using the index and SQL will usually choose to ignore the NCI and just scan the table (clustered index scan) Also, while you mention pagesplitting, a heap reclaims empty space, this is horrible as it takes time for SQL to find the empty space. Also, choosing the right clustered key will keep your inserts fast and your NCI lookups fast as your key should be small (smaller than the ROWID lookup)

I have found there are very rare occasions when a clustered index should not be on a table.

For the poster - mess with your indexes and play with the "set statistics IO on" command, this will show you how many page reads your query costs you and allow you the ability to tell if removing or adding an index will really benefit you or hurt you.

HTH

You are quite welcome to disagree. However very large data tables behave differently from more modest deployments.

I quote from http://www.sql-server-performance.com/jc_sql_server_quantative_analysis5d.asp

"The key observation for multi-row select queries is that there can be a very wide discrepancy between the point where query optimizer switches the execution plan to a Table Scan and the actually observed cross-over point."

"Other important points include the following. Bookmark Lookups are less expensive for heap organized tables than tables with a clustered index. It is frequently recommended that tables have a clustered index. If clustering only benefits a small fraction of the queries (weighted by the number of rows involved), then it may be better to leave the table a heap."|||"Other important points include the following. Bookmark Lookups are less expensive for heap organized tables than tables with a clustered index. It is frequently recommended that tables have a clustered index. If clustering only benefits a small fraction of the queries (weighted by the number of rows involved), then it may be better to leave the table a heap."

I agree if you will not be having a lot of inserts or deletes. The reclaiming of empty space is non-optimal if either are occuring. The other benefit of more efficient key locks with a clustered index than row locks should be taken into consideration if you will be joining to the table. I would be real interested to hear the posters logical IO when using or not using a clustered index. There are very few tables I work with that are read-only or used solely for querying, the only place I really have that is in a warehouse that I use cubes for anyway rather than SQL to query.

Looks like an interesting article, will have a read.|||Karolyn, limteckboon's query is not going to make four million passes through the table. That's ridiculous. Your solution is functionally equivalent to his, but with extra coding.

limteckboon,
It's unclear whether fields frst_requested_date, and financial_year are in Table_A or Master_financial_Table. It makes a difference in how the query should be written. Please clarify. If they are in Table_A then possibly a JOIN instead of a WHERE clause would improve efficiency:

update Table_A
set financial_yr = t2.Master_financial_year,
financial_period = t2.Master_financial_period
from Table_A
inner join Master_financial_Table t2
on Table_A frst_requested_date between t2.Start_date and t2.End_date
and Table_A.financial_year>2000

In the meantime, you may get better performance if you DROP the indexes on Table_A prior to your update, especially any indexes involving columns financial_yr and financial_period. Add the indexes back in at the end of the process.

blindman|||my query is not equivalent...

there's no JOINs on my query
so the UPDATE query will be excuted faster|||reindexing a 4-million-table must cost a lot...|||It costs less than continuously reshuffling the index pages.|||Hello All.

Thank you very much for all your most valuable advises. Greatly appreciated. Now, I need time to digest them. Will give a try on the 2 suggested codes to see which is more suitable for me but I am very sure both set of codes will definitely give better performance than mine.

Once again Thanks a million to all.

Best regards|||i don't see a difference between Karolyn and blindy's code, except for "extra coding" which actually eliminates a whole table out of the update (that's actually a plus, blindy)

limteckboon:
consider non-conventional data modifications. i guarantee that we can shrink the execution time down to under 60 minutes, maybe even less than 30 if you stop listening to blabbering about indexing and inner joins.

did i get your attention?|||ms_sql_dba posted in another thread

"i've done this type of update on larger number of rows without using update. the trick? use queryout on bcp with values that you want to have, then truncate and bcp data back in."

to his idea
--> use Bulk Insert to put back in the data is faster than Bcp
--> and set the option 'select into/bulkcopy' to True with this command

exec sp_option 'DataBaseName', 'select into/bulkcopy','True'|||simple... *** admiration ***|||If you're going to examine 4 million rows, so a table scan is going to be done (almost certainly), but I doubt that the scanning time is going to be an issue. What will certainly be an issue is the fact that you are updating every single one of them, every single time!

Once you've processed 4 million records this week, how many of them are really going to change by next week? Your query should select, and only update the status of, those records which have changed (or that do not have a known status yet). It seems to me that if you are examining the "first-requested" date, not too many records will ever change status a second time! Take full advantage of that!
If you must make the change to all the records, write a script that processes 1,000 records at a time, then commits. Otherwise, the server is prepared to roll back every one of those changes!
Any change to an indexed column will cause the index to be updated every time. This should be avoided.
Just as an afterthought: can these be calculated fields? I mean, with only twenty-something date ranges, total ...

No matter how efficient a computer or a piece of software may be, the best way to get good performance out of it is to ask it to do absolutely no more than it has to. I think that the root cause of the problem is that you are making the server do far too much work.