about activeX script error in ssis package in SQL server 2005

when i run activex Script it's shows this error

[ActiveX Script Task] Error: Retrieving the file name for a component failed with error code 0x001B6438

Moving from .NET Framework Data Access and Storage...|||So is there a solution to this?
I've had a similar error when using the "scripting.filesystemobject" from within a script task in ssis.

about activeX script error in ssis package in SQL server 2005

when i run activex Script it's shows this error

[ActiveX Script Task] Error: Retrieving the file name for a component failed with error code 0x001B6438

Moving from .NET Framework Data Access and Storage...|||So is there a solution to this?
I've had a similar error when using the "scripting.filesystemobject" from within a script task in ssis.

about a SQL script

Dear All,
i recently would like to drop a table, then create a new one and then
insert the value to that new table
i have write a script as below:
use test
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Titles]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Titles]
GO
SELECT * INTO [dbo].[Titles]
FROM [other_table].[dbo].[Titles]
GO
Insert TABLE [dbo].[Titles] (name, id) Values ( 'good book',1)
GO
it work fine if it use one database only but my server have 20
databases, and all the database would like to have that modification.
So is there any method to automatically do the modification using a
script?
i really cant figure it out, i hope someone have give me a help
thanks you very much.NEMA,
You could use sp_MSForEachdb (an undocumented stored procedure) as described
at:
http://www.mssqlcity.com/Articles/Undoc/SQL2000UndocSP.htm
RLF
"NEMA" <realjacky@.gmail.com> wrote in message
news:1185463821.216208.166530@.z24g2000prh.googlegroups.com...
> Dear All,
> i recently would like to drop a table, then create a new one and then
> insert the value to that new table
> i have write a script as below:
> use test
> if exists (select * from dbo.sysobjects where id => object_id(N'[dbo].[Titles]') and OBJECTPROPERTY(id, N'IsUserTable') => 1)
> drop table [dbo].[Titles]
> GO
> SELECT * INTO [dbo].[Titles]
> FROM [other_table].[dbo].[Titles]
> GO
> Insert TABLE [dbo].[Titles] (name, id) Values ( 'good book',1)
> GO
> it work fine if it use one database only but my server have 20
> databases, and all the database would like to have that modification.
> So is there any method to automatically do the modification using a
> script?
> i really cant figure it out, i hope someone have give me a help
> thanks you very much.
>|||thanks you Russell
i dont know how to write as the example is all in one statment only.
but i have write a new one using variable but the error is that ' use
@.db_name' is not correct syntax
is anyone how to fix it ?
Declare @.db_count int
Declare @.db_name varchar(100)
Declare @.start int
/* start at 7 which are user databases*/
Set @.start = 7
Set @.db_count = 0
Select @.db_count = count(*)
>From sys.sysdatabases
Where dbid >= @.start
While @.db_count > 0
Begin
Select @.db_name = [name] From sys.sysdatabases Where dbid = @.start
/* avoid delete the table in database test2 as it need use as
template for copy */
If @.db_name <> 'test2'
Begin
use @.db_name
if exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[customer]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
drop table [dbo].[customer]
SELECT * INTO [dbo].[customer]
FROM [test2].[dbo].[customer]
End
Set @.db_count = @.db_count - 1
Set @.start = @.start + 1
End|||NEMA,
The ? substitutes the database name. So, you could do the following I
believe. (I tested a similar script, but I don't actually want to create
these tables on my server.)
exec sp_MSforeachdb
'USE ?
if DB_ID() > = 7
BEGIN
if exists (select * from dbo.sysobjects where id =object_id(N''[dbo].[customer]'') and OBJECTPROPERTY(id, N''IsUserTable'')
= 1)
drop table [dbo].[customer]
SELECT * INTO [dbo].[customer]
FROM [test2].[dbo].[customer]
END'
Or you could use your code, but turn the block of SQL above into Dynamic SQL
(which is what sp_MSForEachDB does) and EXECUTE the prepared strings of SQL.
A good reference is:
http://www.sommarskog.se/dynamic_sql.html
RLF
"NEMA" <realjacky@.gmail.com> wrote in message
news:1185469482.519284.216740@.x40g2000prg.googlegroups.com...
> thanks you Russell
> i dont know how to write as the example is all in one statment only.
> but i have write a new one using variable but the error is that ' use
> @.db_name' is not correct syntax
> is anyone how to fix it ?
> Declare @.db_count int
> Declare @.db_name varchar(100)
> Declare @.start int
> /* start at 7 which are user databases*/
> Set @.start = 7
> Set @.db_count = 0
> Select @.db_count = count(*)
>>From sys.sysdatabases
> Where dbid >= @.start
> While @.db_count > 0
> Begin
> Select @.db_name = [name] From sys.sysdatabases Where dbid = @.start
> /* avoid delete the table in database test2 as it need use as
> template for copy */
> If @.db_name <> 'test2'
> Begin
> use @.db_name
> if exists (select * from dbo.sysobjects where id => object_id(N'[dbo].[customer]') and OBJECTPROPERTY(id, N'IsUserTable')
> = 1)
> drop table [dbo].[customer]
> SELECT * INTO [dbo].[customer]
> FROM [test2].[dbo].[customer]
> End
> Set @.db_count = @.db_count - 1
> Set @.start = @.start + 1
> End
>

about a SQL script

Dear All,
i recently would like to drop a table, then create a new one and then
insert the value to that new table
i have write a script as below:
use test
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Titles]') and OBJECTPROPERTY(id, N'IsUserTable') =
1)
drop table [dbo].[Titles]
GO
SELECT * INTO [dbo].[Titles]
FROM [other_table].[dbo].[Titles]
GO
Insert TABLE [dbo].[Titles] (name, id) Values ( 'good book',1)
GO
it work fine if it use one database only but my server have 20
databases, and all the database would like to have that modification.
So is there any method to automatically do the modification using a
script?
i really cant figure it out, i hope someone have give me a help
thanks you very much.
NEMA,
You could use sp_MSForEachdb (an undocumented stored procedure) as described
at:
http://www.mssqlcity.com/Articles/Undoc/SQL2000UndocSP.htm
RLF
"NEMA" <realjacky@.gmail.com> wrote in message
news:1185463821.216208.166530@.z24g2000prh.googlegr oups.com...
> Dear All,
> i recently would like to drop a table, then create a new one and then
> insert the value to that new table
> i have write a script as below:
> use test
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[Titles]') and OBJECTPROPERTY(id, N'IsUserTable') =
> 1)
> drop table [dbo].[Titles]
> GO
> SELECT * INTO [dbo].[Titles]
> FROM [other_table].[dbo].[Titles]
> GO
> Insert TABLE [dbo].[Titles] (name, id) Values ( 'good book',1)
> GO
> it work fine if it use one database only but my server have 20
> databases, and all the database would like to have that modification.
> So is there any method to automatically do the modification using a
> script?
> i really cant figure it out, i hope someone have give me a help
> thanks you very much.
>
|||thanks you Russell
i dont know how to write as the example is all in one statment only.
but i have write a new one using variable but the error is that ' use
@.db_name' is not correct syntax
is anyone how to fix it ?
Declare @.db_count int
Declare @.db_name varchar(100)
Declare @.start int
/* start at 7 which are user databases*/
Set @.start = 7
Set @.db_count = 0
Select @.db_count = count(*)
>From sys.sysdatabases
Where dbid >= @.start
While @.db_count > 0
Begin
Select @.db_name = [name] From sys.sysdatabases Where dbid = @.start
/* avoid delete the table in database test2 as it need use as
template for copy */
If @.db_name <> 'test2'
Begin
use @.db_name
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[customer]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
drop table [dbo].[customer]
SELECT * INTO [dbo].[customer]
FROM [test2].[dbo].[customer]
End
Set @.db_count = @.db_count - 1
Set @.start = @.start + 1
End
|||NEMA,
The ? substitutes the database name. So, you could do the following I
believe. (I tested a similar script, but I don't actually want to create
these tables on my server.)
exec sp_MSforeachdb
'USE ?
if DB_ID() > = 7
BEGIN
if exists (select * from dbo.sysobjects where id =
object_id(N''[dbo].[customer]'') and OBJECTPROPERTY(id, N''IsUserTable'')
= 1)
drop table [dbo].[customer]
SELECT * INTO [dbo].[customer]
FROM [test2].[dbo].[customer]
END'
Or you could use your code, but turn the block of SQL above into Dynamic SQL
(which is what sp_MSForEachDB does) and EXECUTE the prepared strings of SQL.
A good reference is:
http://www.sommarskog.se/dynamic_sql.html
RLF
"NEMA" <realjacky@.gmail.com> wrote in message
news:1185469482.519284.216740@.x40g2000prg.googlegr oups.com...
> thanks you Russell
> i dont know how to write as the example is all in one statment only.
> but i have write a new one using variable but the error is that ' use
> @.db_name' is not correct syntax
> is anyone how to fix it ?
> Declare @.db_count int
> Declare @.db_name varchar(100)
> Declare @.start int
> /* start at 7 which are user databases*/
> Set @.start = 7
> Set @.db_count = 0
> Select @.db_count = count(*)
> Where dbid >= @.start
> While @.db_count > 0
> Begin
> Select @.db_name = [name] From sys.sysdatabases Where dbid = @.start
> /* avoid delete the table in database test2 as it need use as
> template for copy */
> If @.db_name <> 'test2'
> Begin
> use @.db_name
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[customer]') and OBJECTPROPERTY(id, N'IsUserTable')
> = 1)
> drop table [dbo].[customer]
> SELECT * INTO [dbo].[customer]
> FROM [test2].[dbo].[customer]
> End
> Set @.db_count = @.db_count - 1
> Set @.start = @.start + 1
> End
>

about a SQL script

Dear All,
i recently would like to drop a table, then create a new one and then
insert the value to that new table
i have write a script as below:
use test
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Titles]') and OBJECTPROPERTY(id, N'IsUserTable')
=
1)
drop table [dbo].[Titles]
GO
SELECT * INTO [dbo].[Titles]
FROM [other_table].[dbo].[Titles]
GO
Insert TABLE [dbo].[Titles] (name, id) Values ( 'good book',1)
GO
it work fine if it use one database only but my server have 20
databases, and all the database would like to have that modification.
So is there any method to automatically do the modification using a
script?
i really cant figure it out, i hope someone have give me a help
thanks you very much.NEMA,
You could use sp_MSForEachdb (an undocumented stored procedure) as described
at:
http://www.mssqlcity.com/Articles/U...2000UndocSP.htm
RLF
"NEMA" <realjacky@.gmail.com> wrote in message
news:1185463821.216208.166530@.z24g2000prh.googlegroups.com...
> Dear All,
> i recently would like to drop a table, then create a new one and then
> insert the value to that new table
> i have write a script as below:
> use test
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[Titles]') and OBJECTPROPERTY(id, N'IsUserTable'
) =
> 1)
> drop table [dbo].[Titles]
> GO
> SELECT * INTO [dbo].[Titles]
> FROM [other_table].[dbo].[Titles]
> GO
> Insert TABLE [dbo].[Titles] (name, id) Values ( 'good book',1)
> GO
> it work fine if it use one database only but my server have 20
> databases, and all the database would like to have that modification.
> So is there any method to automatically do the modification using a
> script?
> i really cant figure it out, i hope someone have give me a help
> thanks you very much.
>|||thanks you Russell
i dont know how to write as the example is all in one statment only.
but i have write a new one using variable but the error is that ' use
@.db_name' is not correct syntax
is anyone how to fix it ?
Declare @.db_count int
Declare @.db_name varchar(100)
Declare @.start int
/* start at 7 which are user databases*/
Set @.start = 7
Set @.db_count = 0
Select @.db_count = count(*)
>From sys.sysdatabases
Where dbid >= @.start
While @.db_count > 0
Begin
Select @.db_name = [name] From sys.sysdatabases Where dbid = @.start
/* avoid delete the table in database test2 as it need use as
template for copy */
If @.db_name <> 'test2'
Begin
use @.db_name
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[customer]') and OBJECTPROPERTY(id, N'IsUserTable'
)
= 1)
drop table [dbo].[customer]
SELECT * INTO [dbo].[customer]
FROM [test2].[dbo].[customer]
End
Set @.db_count = @.db_count - 1
Set @.start = @.start + 1
End|||NEMA,
The ? substitutes the database name. So, you could do the following I
believe. (I tested a similar script, but I don't actually want to create
these tables on my server.)
exec sp_MSforeachdb
'USE ?
if DB_ID() > = 7
BEGIN
if exists (select * from dbo.sysobjects where id =
object_id(N''[dbo].[customer]'') and OBJECTPROPERTY(id, N''IsUserTab
le'')
= 1)
drop table [dbo].[customer]
SELECT * INTO [dbo].[customer]
FROM [test2].[dbo].[customer]
END'
Or you could use your code, but turn the block of SQL above into Dynamic SQL
(which is what sp_MSForEachDB does) and EXECUTE the prepared strings of SQL.
A good reference is:
http://www.sommarskog.se/dynamic_sql.html
RLF
"NEMA" <realjacky@.gmail.com> wrote in message
news:1185469482.519284.216740@.x40g2000prg.googlegroups.com...
> thanks you Russell
> i dont know how to write as the example is all in one statment only.
> but i have write a new one using variable but the error is that ' use
> @.db_name' is not correct syntax
> is anyone how to fix it ?
> Declare @.db_count int
> Declare @.db_name varchar(100)
> Declare @.start int
> /* start at 7 which are user databases*/
> Set @.start = 7
> Set @.db_count = 0
> Select @.db_count = count(*)
> Where dbid >= @.start
> While @.db_count > 0
> Begin
> Select @.db_name = [name] From sys.sysdatabases Where dbid = @.start
> /* avoid delete the table in database test2 as it need use as
> template for copy */
> If @.db_name <> 'test2'
> Begin
> use @.db_name
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[customer]') and OBJECTPROPERTY(id, N'IsUserTabl
e')
> = 1)
> drop table [dbo].[customer]
> SELECT * INTO [dbo].[customer]
> FROM [test2].[dbo].[customer]
> End
> Set @.db_count = @.db_count - 1
> Set @.start = @.start + 1
> End
>sql

a visual basic script to DTS

Hello I am trying to convert a VB script someone wrote for my company into a DTS package that will fire automatically whenever the file is updated on the server. I am using vb.net for the firing of the DTS package, but I am having trouble writting the DTS package itself. This is what the code looks like now

If fso.FileExists(cSource) Then
With cne
.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=" & cSource & ";" _
& "Extended Properties=Excel 8.0"
.Open
cSQL = "SELECT * FROM " & sSheet
Set rse = .Execute(cSQL)
End With

While Not rse.EOF
i = i + 1
Me.Label1.Caption = "Updating Sold: " & i
Me.Refresh
If Not IsNull(rse("VEHICLE-STOCK-NO###")) Then
sSearch = "'" & rse("VEHICLE-STOCK-NO###") & "'"
cSQL = "UPDATE Inventory SET SoldDate = '" & rse("FNLZ-DT") & "', "
cSQL = cSQL & "LastUpdate = '" & Now & "' WHERE StockNo = " & sSearch
cSQL = cSQL & " AND SoldDate IS NULL"
cn.Execute (cSQL)
End If
rse.MoveNext
Wend
rse.Close
cne.Close
End If


So basically I have an excel file that is downloaded once a week and then I run this program, well I am having some toubles with making it a DTS

I need to convert this into an active X transfermation code I think
If Not IsNull(rse("VEHICLE-STOCK-NO###")) Then
sSearch = "'" & rse("VEHICLE-STOCK-NO###") & "'"
cSQL = "UPDATE Inventory SET SoldDate = '" & rse("FNLZ-DT") & "', "
cSQL = cSQL & "LastUpdate = '" & Now & "' WHERE StockNo = " & sSearch
cSQL = cSQL & " AND SoldDate IS NULL"
end if

Thanks a lot

DamianSearch SQLDTS (http://www.sqldts.com) website for code examples and more information.

HTHsql

A User connections question

Is there anyway or SQL script to configure the SQL server to automatically
log users out if their connection remains idle for a predetermined period of
time?
The convenience of allowing users to login to Great Plains in the morning
and staying there all day, even if they actually use the application for only
a fraction of the time, is too expensive to cater to.
I want to start by demanding that users logout of Great Plains when they
will not be using the application for a period of time (i.e. during lunch,
during coffee breaks, during meetings, while they are performing tasks not
requiring immediate access to Great Plains, etc.).
Thank You...
Hi
This is one of the things connection pooling would help with, but then you
will not be re-writting the package! I guess you could have a job that checks
the last_batch time in sysprocesses.
John
"clairvoyant316" wrote:

> Is there anyway or SQL script to configure the SQL server to automatically
> log users out if their connection remains idle for a predetermined period of
> time?
> The convenience of allowing users to login to Great Plains in the morning
> and staying there all day, even if they actually use the application for only
> a fraction of the time, is too expensive to cater to.
> I want to start by demanding that users logout of Great Plains when they
> will not be using the application for a period of time (i.e. during lunch,
> during coffee breaks, during meetings, while they are performing tasks not
> requiring immediate access to Great Plains, etc.).
> Thank You...
|||clairvoyant316 wrote:
> Is there anyway or SQL script to configure the SQL server to automatically
> log users out if their connection remains idle for a predetermined period of
> time?
> The convenience of allowing users to login to Great Plains in the morning
> and staying there all day, even if they actually use the application for only
> a fraction of the time, is too expensive to cater to.
> I want to start by demanding that users logout of Great Plains when they
> will not be using the application for a period of time (i.e. during lunch,
> during coffee breaks, during meetings, while they are performing tasks not
> requiring immediate access to Great Plains, etc.).
> Thank You...
Tried to post this yesterday, apparently it didn't "go". Here's a
script (and table) that I've used to handle this very problem in GP.
CREATE TABLE [dbo].[sb_IdleUsersRemoved] (
[iRowID] [int] IDENTITY (1, 1) NOT NULL ,
[iSPID] [int] NULL ,
[vchUserName] [varchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[dtDateRemoved] [datetime] NULL
) ON [PRIMARY]
GO
CREATE PROCEDURE sb_RemoveIdleUsers
AS
DECLARE @.iSPID INT
DECLARE @.vchCommand VARCHAR(255)
/* Create list of SPIDs that have been idle for 12 hours, and are still
listed in dex_session */
SELECT a.spid, a.loginame
INTO #tempSPIDs
FROM master.dbo.sysprocesses a
INNER JOIN tempdb.dbo.dex_session b
ON a.spid = b.sqlsvr_spid
WHERE GETDATE() - a.last_batch > .5
UNION
SELECT spid, loginame
FROM master.dbo.sysprocesses
WHERE dbid IN (7, 16, 17) AND GETDATE() - last_batch > .5
BEGIN TRANSACTION
/* Record the list of spids and logins that are being removed */
INSERT INTO sb_custom.dbo.sb_IdleUsersRemoved
SELECT spid, loginame, GETDATE()
FROM #tempSPIDs
/* Delete records from dex_lock for any idle sessions */
DELETE
FROM tempdb.dbo.dex_lock
WHERE session_id IN (SELECT session_id FROM tempdb.dbo.dex_session WHERE
sqlsvr_spid IN (SELECT spid FROM #tempSPIDs))
/* Delete records from dex_session for any idle sessions */
DELETE
FROM tempdb.dbo.dex_session
WHERE sqlsvr_spid IN (SELECT spid FROM #tempSPIDs)
COMMIT TRANSACTION
/* Kill SQL processes for idle sessions */
SELECT @.iSPID = 0
WHILE EXISTS(SELECT * FROM #tempSPIDs WHERE spid > @.iSPID)
BEGIN
SELECT TOP 1 @.iSPID = spid
FROM #tempSPIDs
WHERE spid > @.iSPID
ORDER BY spid
SELECT @.vchCommand = 'KILL ' + LTRIM(RTRIM(CONVERT(VARCHAR(10),
@.iSPID)))
EXEC (@.vchCommand)
END
DROP TABLE #tempSPIDs
/* Delete records from dex_lock for any records in dex_session that have
no active SQL connection */
DELETE
FROM tempdb.dbo.dex_lock
WHERE session_id IN (SELECT session_id
FROM tempdb.dbo.dex_session
WHERE sqlsvr_spid NOT IN (SELECT spid FROM master.dbo.sysprocesses)
)
/* Delete records from dex_session that have no active SQL connection */
DELETE
FROM tempdb.dbo.dex_session
WHERE sqlsvr_spid NOT IN (SELECT spid FROM master.dbo.sysprocesses)
GO
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||Thanks for your prompt response.
I ran that script and crashed the MS SQL 2000 with timeout as well as
corrupting tempdb. I restored the tempdb from the MS SQL 2000 CD.
It didn't kick users out of the Great Plains at all.
Is there any condition or special configuration that I am not aware of to
run that script?
Thank You
"Tracy McKibben" wrote:

> Tried to post this yesterday, apparently it didn't "go". Here's a
> script (and table) that I've used to handle this very problem in GP.
|||clairvoyant316 wrote:
> Thanks for your prompt response.
> I ran that script and crashed the MS SQL 2000 with timeout as well as
> corrupting tempdb. I restored the tempdb from the MS SQL 2000 CD.
Not likely - the script I sent you simply creates a table and a stored
procedure.

> It didn't kick users out of the Great Plains at all.
> Is there any condition or special configuration that I am not aware of to
> run that script?
>
The script that I gave you creates a stored procedure. YOU must run
that stored procedure before it will remove any users. Surely you
realized that when you reviewed the script? Sounds like you might be
better off just leaving things alone.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||Hi
tempdb is recreated when you restart SQL Server. If it was really corrupted
I would suspect a hardware failure rather than this script. There seems to be
a couple of table creation statements missing from the script and the
database names need changing on the four part names that are not tempdb.
John
"clairvoyant316" wrote:

> Thanks for your prompt response.
> I ran that script and crashed the MS SQL 2000 with timeout as well as
> corrupting tempdb. I restored the tempdb from the MS SQL 2000 CD.
> It didn't kick users out of the Great Plains at all.
> Is there any condition or special configuration that I am not aware of to
> run that script?
> Thank You
> "Tracy McKibben" wrote:
>
|||John Bell wrote:
> Hi
> tempdb is recreated when you restart SQL Server. If it was really corrupted
> I would suspect a hardware failure rather than this script. There seems to be
> a couple of table creation statements missing from the script and the
> database names need changing on the four part names that are not tempdb.
>
Yes, it's just a snippet of code from one of my production systems. I
made the assumption that the OP would be able to adapt it to his needs,
I assumed wrong.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||Hi Tracy
I would personally be wary of killing of processes like this in case there
is the situation you kill off something that you don't want to kill. This may
have also been something that the occurred with the OP. BOL gives a list of
processes that you should not kill such as AWAITING COMMAND, CHECKPOINT
SLEEP, LAZY WRITER, LOCK MONITOR, SELECT,SIGNAL HANDLER which you don't seem
to check in the code,
John
"Tracy McKibben" wrote:

> John Bell wrote:
> Yes, it's just a snippet of code from one of my production systems. I
> made the assumption that the OP would be able to adapt it to his needs,
> I assumed wrong.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>
|||

Quote:

Originally Posted by John Bell

Hi Tracy
I would personally be wary of killing of processes like this in case there
is the situation you kill off something that you don't want to kill. This may
have also been something that the occurred with the OP. BOL gives a list of
processes that you should not kill such as AWAITING COMMAND, CHECKPOINT
SLEEP, LAZY WRITER, LOCK MONITOR, SELECT,SIGNAL HANDLER which you don't seem
to check in the code,
John
"Tracy McKibben" wrote:

> John Bell wrote:
> Yes, it's just a snippet of code from one of my production systems. I
> made the assumption that the OP would be able to adapt it to his needs,
> I assumed wrong.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>

Interesting code, Tracy, but I am curious - what would become of a document or transaction that a user left open? I would guess that any unsaved changes would be lost, but the document itself is still retained. is that correct?
I do a fair amount of work in GP Modifier & VBA, plus SQL for my company. This is something we've considered doing in the past, to reduce the number of occupied licenses for clients.
Thanks! BTW, you have a neat site...I've bookmarked it.

A User connections question

Is there anyway or SQL script to configure the SQL server to automatically
log users out if their connection remains idle for a predetermined period of
time?
The convenience of allowing users to login to Great Plains in the morning
and staying there all day, even if they actually use the application for onl
y
a fraction of the time, is too expensive to cater to.
I want to start by demanding that users logout of Great Plains when they
will not be using the application for a period of time (i.e. during lunch,
during coffee breaks, during meetings, while they are performing tasks not
requiring immediate access to Great Plains, etc.).
Thank You...Hi
This is one of the things connection pooling would help with, but then you
will not be re-writting the package! I guess you could have a job that check
s
the last_batch time in sysprocesses.
John
"clairvoyant316" wrote:

> Is there anyway or SQL script to configure the SQL server to automatical
ly
> log users out if their connection remains idle for a predetermined period
of
> time?
> The convenience of allowing users to login to Great Plains in the morning
> and staying there all day, even if they actually use the application for o
nly
> a fraction of the time, is too expensive to cater to.
> I want to start by demanding that users logout of Great Plains when they
> will not be using the application for a period of time (i.e. during lunch,
> during coffee breaks, during meetings, while they are performing tasks not
> requiring immediate access to Great Plains, etc.).
> Thank You...|||clairvoyant316 wrote:
> Is there anyway or SQL script to configure the SQL server to automatical
ly
> log users out if their connection remains idle for a predetermined period
of
> time?
> The convenience of allowing users to login to Great Plains in the morning
> and staying there all day, even if they actually use the application for o
nly
> a fraction of the time, is too expensive to cater to.
> I want to start by demanding that users logout of Great Plains when they
> will not be using the application for a period of time (i.e. during lunch,
> during coffee breaks, during meetings, while they are performing tasks not
> requiring immediate access to Great Plains, etc.).
> Thank You...
Tried to post this yesterday, apparently it didn't "go". Here's a
script (and table) that I've used to handle this very problem in GP.
CREATE TABLE [dbo].[sb_IdleUsersRemoved] (
[iRowID] [int] IDENTITY (1, 1) NOT NULL ,
[iSPID] [int] NULL ,
[vchUserName] [varchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[dtDateRemoved] [datetime] NULL
) ON [PRIMARY]
GO
CREATE PROCEDURE sb_RemoveIdleUsers
AS
DECLARE @.iSPID INT
DECLARE @.vchCommand VARCHAR(255)
/* Create list of SPIDs that have been idle for 12 hours, and are still
listed in dex_session */
SELECT a.spid, a.loginame
INTO #tempSPIDs
FROM master.dbo.sysprocesses a
INNER JOIN tempdb.dbo.dex_session b
ON a.spid = b.sqlsvr_spid
WHERE GETDATE() - a.last_batch > .5
UNION
SELECT spid, loginame
FROM master.dbo.sysprocesses
WHERE dbid IN (7, 16, 17) AND GETDATE() - last_batch > .5
BEGIN TRANSACTION
/* Record the list of spids and logins that are being removed */
INSERT INTO sb_custom.dbo.sb_IdleUsersRemoved
SELECT spid, loginame, GETDATE()
FROM #tempSPIDs
/* Delete records from dex_lock for any idle sessions */
DELETE
FROM tempdb.dbo.dex_lock
WHERE session_id IN (SELECT session_id FROM tempdb.dbo.dex_session WHERE
sqlsvr_spid IN (SELECT spid FROM #tempSPIDs))
/* Delete records from dex_session for any idle sessions */
DELETE
FROM tempdb.dbo.dex_session
WHERE sqlsvr_spid IN (SELECT spid FROM #tempSPIDs)
COMMIT TRANSACTION
/* Kill SQL processes for idle sessions */
SELECT @.iSPID = 0
WHILE EXISTS(SELECT * FROM #tempSPIDs WHERE spid > @.iSPID)
BEGIN
SELECT TOP 1 @.iSPID = spid
FROM #tempSPIDs
WHERE spid > @.iSPID
ORDER BY spid
SELECT @.vchCommand = 'KILL ' + LTRIM(RTRIM(CONVERT(VARCHAR(10),
@.iSPID)))
EXEC (@.vchCommand)
END
DROP TABLE #tempSPIDs
/* Delete records from dex_lock for any records in dex_session that have
no active SQL connection */
DELETE
FROM tempdb.dbo.dex_lock
WHERE session_id IN (SELECT session_id
FROM tempdb.dbo.dex_session
WHERE sqlsvr_spid NOT IN (SELECT spid FROM master.dbo.sysprocesses)
)
/* Delete records from dex_session that have no active SQL connection */
DELETE
FROM tempdb.dbo.dex_session
WHERE sqlsvr_spid NOT IN (SELECT spid FROM master.dbo.sysprocesses)
GO
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thanks for your prompt response.
I ran that script and crashed the MS SQL 2000 with timeout as well as
corrupting tempdb. I restored the tempdb from the MS SQL 2000 CD.
It didn't kick users out of the Great Plains at all.
Is there any condition or special configuration that I am not aware of to
run that script?
Thank You
"Tracy McKibben" wrote:

> Tried to post this yesterday, apparently it didn't "go". Here's a
> script (and table) that I've used to handle this very problem in GP.|||clairvoyant316 wrote:
> Thanks for your prompt response.
> I ran that script and crashed the MS SQL 2000 with timeout as well as
> corrupting tempdb. I restored the tempdb from the MS SQL 2000 CD.
Not likely - the script I sent you simply creates a table and a stored
procedure.

> It didn't kick users out of the Great Plains at all.
> Is there any condition or special configuration that I am not aware of to
> run that script?
>
The script that I gave you creates a stored procedure. YOU must run
that stored procedure before it will remove any users. Surely you
realized that when you reviewed the script? Sounds like you might be
better off just leaving things alone.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Hi
tempdb is recreated when you restart SQL Server. If it was really corrupted
I would suspect a hardware failure rather than this script. There seems to b
e
a couple of table creation statements missing from the script and the
database names need changing on the four part names that are not tempdb.
John
"clairvoyant316" wrote:

> Thanks for your prompt response.
> I ran that script and crashed the MS SQL 2000 with timeout as well as
> corrupting tempdb. I restored the tempdb from the MS SQL 2000 CD.
> It didn't kick users out of the Great Plains at all.
> Is there any condition or special configuration that I am not aware of to
> run that script?
> Thank You
> "Tracy McKibben" wrote:
>
>|||John Bell wrote:
> Hi
> tempdb is recreated when you restart SQL Server. If it was really corrupte
d
> I would suspect a hardware failure rather than this script. There seems to
be
> a couple of table creation statements missing from the script and the
> database names need changing on the four part names that are not tempdb.
>
Yes, it's just a snippet of code from one of my production systems. I
made the assumption that the OP would be able to adapt it to his needs,
I assumed wrong.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Hi Tracy
I would personally be wary of killing of processes like this in case there
is the situation you kill off something that you don't want to kill. This ma
y
have also been something that the occurred with the OP. BOL gives a list of
processes that you should not kill such as AWAITING COMMAND, CHECKPOINT
SLEEP, LAZY WRITER, LOCK MONITOR, SELECT,SIGNAL HANDLER which you don't seem
to check in the code,
John
"Tracy McKibben" wrote:

> John Bell wrote:
> Yes, it's just a snippet of code from one of my production systems. I
> made the assumption that the OP would be able to adapt it to his needs,
> I assumed wrong.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>

A User connections question

Is there anyway or SQL script to configure the SQL server to automatically
log users out if their connection remains idle for a predetermined period of
time?
The convenience of allowing users to login to Great Plains in the morning
and staying there all day, even if they actually use the application for only
a fraction of the time, is too expensive to cater to.
I want to start by demanding that users logout of Great Plains when they
will not be using the application for a period of time (i.e. during lunch,
during coffee breaks, during meetings, while they are performing tasks not
requiring immediate access to Great Plains, etc.).
Thank You...clairvoyant316 wrote:
> Is there anyway or SQL script to configure the SQL server to automatically
> log users out if their connection remains idle for a predetermined period of
> time?
> The convenience of allowing users to login to Great Plains in the morning
> and staying there all day, even if they actually use the application for only
> a fraction of the time, is too expensive to cater to.
> I want to start by demanding that users logout of Great Plains when they
> will not be using the application for a period of time (i.e. during lunch,
> during coffee breaks, during meetings, while they are performing tasks not
> requiring immediate access to Great Plains, etc.).
> Thank You...
Tried to post this yesterday, apparently it didn't "go". Here's a
script (and table) that I've used to handle this very problem in GP.
CREATE TABLE [dbo].[sb_IdleUsersRemoved] (
[iRowID] [int] IDENTITY (1, 1) NOT NULL ,
[iSPID] [int] NULL ,
[vchUserName] [varchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[dtDateRemoved] [datetime] NULL
) ON [PRIMARY]
GO
CREATE PROCEDURE sb_RemoveIdleUsers
AS
DECLARE @.iSPID INT
DECLARE @.vchCommand VARCHAR(255)
/* Create list of SPIDs that have been idle for 12 hours, and are still
listed in dex_session */
SELECT a.spid, a.loginame
INTO #tempSPIDs
FROM master.dbo.sysprocesses a
INNER JOIN tempdb.dbo.dex_session b
ON a.spid = b.sqlsvr_spid
WHERE GETDATE() - a.last_batch > .5
UNION
SELECT spid, loginame
FROM master.dbo.sysprocesses
WHERE dbid IN (7, 16, 17) AND GETDATE() - last_batch > .5
BEGIN TRANSACTION
/* Record the list of spids and logins that are being removed */
INSERT INTO sb_custom.dbo.sb_IdleUsersRemoved
SELECT spid, loginame, GETDATE()
FROM #tempSPIDs
/* Delete records from dex_lock for any idle sessions */
DELETE
FROM tempdb.dbo.dex_lock
WHERE session_id IN (SELECT session_id FROM tempdb.dbo.dex_session WHERE
sqlsvr_spid IN (SELECT spid FROM #tempSPIDs))
/* Delete records from dex_session for any idle sessions */
DELETE
FROM tempdb.dbo.dex_session
WHERE sqlsvr_spid IN (SELECT spid FROM #tempSPIDs)
COMMIT TRANSACTION
/* Kill SQL processes for idle sessions */
SELECT @.iSPID = 0
WHILE EXISTS(SELECT * FROM #tempSPIDs WHERE spid > @.iSPID)
BEGIN
SELECT TOP 1 @.iSPID = spid
FROM #tempSPIDs
WHERE spid > @.iSPID
ORDER BY spid
SELECT @.vchCommand = 'KILL ' + LTRIM(RTRIM(CONVERT(VARCHAR(10),
@.iSPID)))
EXEC (@.vchCommand)
END
DROP TABLE #tempSPIDs
/* Delete records from dex_lock for any records in dex_session that have
no active SQL connection */
DELETE
FROM tempdb.dbo.dex_lock
WHERE session_id IN (SELECT session_id
FROM tempdb.dbo.dex_session
WHERE sqlsvr_spid NOT IN (SELECT spid FROM master.dbo.sysprocesses)
)
/* Delete records from dex_session that have no active SQL connection */
DELETE
FROM tempdb.dbo.dex_session
WHERE sqlsvr_spid NOT IN (SELECT spid FROM master.dbo.sysprocesses)
GO
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||clairvoyant316 wrote:
> Thanks for your prompt response.
> I ran that script and crashed the MS SQL 2000 with timeout as well as
> corrupting tempdb. I restored the tempdb from the MS SQL 2000 CD.
Not likely - the script I sent you simply creates a table and a stored
procedure.
> It didn't kick users out of the Great Plains at all.
> Is there any condition or special configuration that I am not aware of to
> run that script?
>
The script that I gave you creates a stored procedure. YOU must run
that stored procedure before it will remove any users. Surely you
realized that when you reviewed the script? Sounds like you might be
better off just leaving things alone.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Hi
tempdb is recreated when you restart SQL Server. If it was really corrupted
I would suspect a hardware failure rather than this script. There seems to be
a couple of table creation statements missing from the script and the
database names need changing on the four part names that are not tempdb.
John
"clairvoyant316" wrote:
> Thanks for your prompt response.
> I ran that script and crashed the MS SQL 2000 with timeout as well as
> corrupting tempdb. I restored the tempdb from the MS SQL 2000 CD.
> It didn't kick users out of the Great Plains at all.
> Is there any condition or special configuration that I am not aware of to
> run that script?
> Thank You
> "Tracy McKibben" wrote:
> > Tried to post this yesterday, apparently it didn't "go". Here's a
> > script (and table) that I've used to handle this very problem in GP.
>|||John Bell wrote:
> Hi
> tempdb is recreated when you restart SQL Server. If it was really corrupted
> I would suspect a hardware failure rather than this script. There seems to be
> a couple of table creation statements missing from the script and the
> database names need changing on the four part names that are not tempdb.
>
Yes, it's just a snippet of code from one of my production systems. I
made the assumption that the OP would be able to adapt it to his needs,
I assumed wrong.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Hi Tracy
I would personally be wary of killing of processes like this in case there
is the situation you kill off something that you don't want to kill. This may
have also been something that the occurred with the OP. BOL gives a list of
processes that you should not kill such as AWAITING COMMAND, CHECKPOINT
SLEEP, LAZY WRITER, LOCK MONITOR, SELECT,SIGNAL HANDLER which you don't seem
to check in the code,
John
"Tracy McKibben" wrote:
> John Bell wrote:
> > Hi
> >
> > tempdb is recreated when you restart SQL Server. If it was really corrupted
> > I would suspect a hardware failure rather than this script. There seems to be
> > a couple of table creation statements missing from the script and the
> > database names need changing on the four part names that are not tempdb.
> >
> Yes, it's just a snippet of code from one of my production systems. I
> made the assumption that the OP would be able to adapt it to his needs,
> I assumed wrong.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>

A Second Set of Eyes

This is more of a "does anyone see something I'm missing" post versus a real problem.

What I'm doing is modifying a script I found in BOL. The script iterates through all the tables in a database and performs a SHOWCONTIG on all the tables. For those tables at a certain level of fragmentation, it does an INDEXDEFRAG. What I'd like to add to this is a piece that will iterate through all databases as well.

I'm close but no cigar. I've posted the code below. If anyone has any insight into where I may be going wrong, it would be greatly appreciated!

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

SET NOCOUNT ON

DECLARE @.SQLSTRING VARCHAR(2000)
DECLARE @.DBNAME VARCHAR(64)
DECLARE @.tablename varchar(128)
DECLARE @.execstr varchar(255)
DECLARE @.objectid int
DECLARE @.indexid int
DECLARE @.frag decimal
DECLARE @.maxfrag decimal
DECLARE @.maxextfrag decimal

-- Decide on the maximum fragmentation to allow for.
SELECT @.maxfrag = 30.0
SELECT @.maxextfrag = 40.0

DECLARE db CURSOR FOR
SELECT [NAME]
FROM [master].[dbo].[sysdatabases]
WHERE [NAME] NOT IN
('master', 'model', 'msdb', 'tempdb')

-- Declare a cursor.
--DECLARE tables CURSOR FOR
-- SELECT TABLE_NAME
-- FROM INFORMATION_SCHEMA.TABLES
-- WHERE TABLE_TYPE = 'BASE TABLE'

-- Create the table.
CREATE TABLE #fraglist (
ObjectName char(255),
ObjectId int,
IndexName char(255),
IndexId int,
Lvl int,
CountPages int,
CountRows int,
MinRecSize int,
MaxRecSize int,
AvgRecSize int,
ForRecCount int,
Extents int,
ExtentSwitches int,
AvgFreeBytes int,
AvgPageDensity int,
ScanDensity decimal,
BestCount int,
ActualCount int,
LogicalFrag decimal,
ExtentFrag decimal)

OPEN db

-- Declare a cursor.
DECLARE tables CURSOR FOR
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'

-- Loop through all the databases.
FETCH NEXT
FROM db
INTO @.DBNAME

WHILE @.@.FETCH_STATUS = 0
BEGIN
SELECT @.execstr = 'USE ' + @.dbname + ';' + char(13)
PRINT @.execstr
EXEC (@.execstr)


-- Open the cursor.
OPEN tables

-- Loop through all the tables in the database.
FETCH NEXT
FROM tables
INTO @.tablename

WHILE @.@.FETCH_STATUS = 0
BEGIN
-- Do the showcontig of all indexes of the table
INSERT INTO #fraglist
EXEC ('DBCC SHOWCONTIG (''' + @.tablename + ''')
WITH TABLERESULTS, ALL_INDEXES')
FETCH NEXT
FROM tables
INTO @.tablename
END

-- Close and deallocate the cursor.
CLOSE tables
DEALLOCATE tables

SELECT @.SQLSTRING = 'INSERT INTO DBA_ADMIN.Fragmentation
(DatabaseName,
RunDate,
ObjectName,
ObjectId,
IndexName,
IndexId,
Lvl,
CountPages,
CountRows,
MinRecSize,
MaxRecSize,
AvgRecSize,
ForRecCount,
Extents,
ExtentSwitches,
AvgFreeBytes,
AvgPageDensity,
ScanDensity,
BestCount,
ActualCount,
LogicalFrag,
ExtentFrag)
SELECT '
SELECT @.SQLSTRING = @.SQLSTRING + @.DBNAME
SELECT @.SQLSTRING = @.SQLSTRING + ', getdate(),
ObjectName,
ObjectId,
IndexName,
IndexId,
Lvl,
CountPages,
CountRows,
MinRecSize,
MaxRecSize,
AvgRecSize,
ForRecCount,
Extents,
ExtentSwitches,
AvgFreeBytes,
AvgPageDensity,
ScanDensity,
BestCount,
ActualCount,
LogicalFrag,
ExtentFrag
FROM #fraglist
WHERE LogicalFrag >= @.maxfrag
OR ExtentFrag >= @.maxextfrag'

PRINT @.SQLSTRING

EXEC(@.SQLSTRING)

FETCH NEXT
FROM db
INTO @.DBNAME
END

CLOSE db
DEALLOCATE db

-- Declare the cursor for the list of indexes to be defragged.
DECLARE indexes CURSOR FOR
SELECT ObjectName, ObjectId, IndexId, LogicalFrag
FROM #fraglist
WHERE LogicalFrag >= @.maxfrag
OR ExtentFrag >= @.maxextfrag
AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0

-- Open the cursor.
OPEN indexes

-- Loop through the indexes.
FETCH NEXT
FROM indexes
INTO @.tablename, @.objectid, @.indexid, @.frag

WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT 'Executing DBCC INDEXDEFRAG (0, ' + RTRIM(@.tablename) + ',
' + RTRIM(@.indexid) + ') - fragmentation currently '
+ RTRIM(CONVERT(varchar(15),@.frag)) + '%'
SELECT @.execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@.objectid) + ',
' + RTRIM(@.indexid) + ')'
EXEC (@.execstr)

FETCH NEXT
FROM indexes
INTO @.tablename, @.objectid, @.indexid, @.frag
END

-- Close and deallocate the cursor.
CLOSE indexes
DEALLOCATE indexes
--
-- Delete the temporary table.
DROP TABLE #fraglist

Again, thanks!!there r quite a few problems
1) u cannot change the database context by executing dynamic sql exec('use dbname').
2) the cursor tables is opened outside the loop and closed inside the loop
3) the table Fragmentation is not defined anywhere
ther could be more...|||I addressed the issue withthe tables curosr - works fine now.

The table Fragmentation is actually a permanent table, not a temp table.

..is there any way to actually change database context via SQL besides doing an "in line"|||..is there any way to actually change database context via SQL besides doing an "in line"

Undocumented, but do a search on ms_foreachdb (and ms_foreachtable).

Regards,

hmscott

PS. Undocumented means undocumented, ymmv.|||no. even sp_msforeachdb will not change the context permanently. all that it will do is provide u an option to execute sql in a different db and for all db. the same can be done by

exec ('use mydb select * from mytable')

A script to re-compile stored procedures

Hello all,
Is there anyone who has a script to generate a script to re-compile all
stored procedure under a database? (or a script to re-compile all stored
procedures under a database.)
I have to re-compile all stored procedure under a database.
I will appreciate it if you post it.
Thanks in advance,
Do.
Message posted via http://www.droptable.com
See if this helps:
use northwind
go
declare @.sql nvarchar(4000)
declare @.rs sysname
declare @.rn sysname
declare procedures_cursor cursor local fast_forward
for
select
routine_schema,
routine_name
from
information_schema.routines
where
routine_type = 'procedure'
and objectproperty(object_id(quotename(routine_schema) + '.' +
quotename(routine_name)), 'IsMSShipped') = 0
open procedures_cursor
while 1 = 1
begin
fetch next from procedures_cursor into @.rs, @.rn
if @.@.error != 0 or @.@.fetch_status != 0 break
set @.sql = N'exec sp_recompile ''' + quotename(@.rs) + N'.' + quotename(@.rn)
+ N''''
exec sp_executesql @.sql
end
close procedures_cursor
deallocate procedures_cursor
go
AMB
"Do Park via droptable.com" wrote:

> Hello all,
> Is there anyone who has a script to generate a script to re-compile all
> stored procedure under a database? (or a script to re-compile all stored
> procedures under a database.)
> I have to re-compile all stored procedure under a database.
> I will appreciate it if you post it.
> Thanks in advance,
> Do.
> --
> Message posted via http://www.droptable.com
>

A script to re-compile stored procedures

Hello all,
Is there anyone who has a script to generate a script to re-compile all
stored procedure under a database? (or a script to re-compile all stored
procedures under a database.)
I have to re-compile all stored procedure under a database.
I will appreciate it if you post it.
Thanks in advance,
Do.
Message posted via http://www.droptable.comSee if this helps:
use northwind
go
declare @.sql nvarchar(4000)
declare @.rs sysname
declare @.rn sysname
declare procedures_cursor cursor local fast_forward
for
select
routine_schema,
routine_name
from
information_schema.routines
where
routine_type = 'procedure'
and objectproperty(object_id(quotename(routi
ne_schema) + '.' +
quotename(routine_name)), 'IsMSShipped') = 0
open procedures_cursor
while 1 = 1
begin
fetch next from procedures_cursor into @.rs, @.rn
if @.@.error != 0 or @.@.fetch_status != 0 break
set @.sql = N'exec sp_recompile ''' + quotename(@.rs) + N'.' + quotename(@.rn)
+ N''''
exec sp_executesql @.sql
end
close procedures_cursor
deallocate procedures_cursor
go
AMB
"Do Park via droptable.com" wrote:

> Hello all,
> Is there anyone who has a script to generate a script to re-compile all
> stored procedure under a database? (or a script to re-compile all stored
> procedures under a database.)
> I have to re-compile all stored procedure under a database.
> I will appreciate it if you post it.
> Thanks in advance,
> Do.
> --
> Message posted via http://www.droptable.com
>

A script to re-compile stored procedures

Hello all,
Is there anyone who has a script to generate a script to re-compile all
stored procedure under a database? (or a script to re-compile all stored
procedures under a database.)
I have to re-compile all stored procedure under a database.
I will appreciate it if you post it.
Thanks in advance,
Do.
--
Message posted via http://www.sqlmonster.comSee if this helps:
use northwind
go
declare @.sql nvarchar(4000)
declare @.rs sysname
declare @.rn sysname
declare procedures_cursor cursor local fast_forward
for
select
routine_schema,
routine_name
from
information_schema.routines
where
routine_type = 'procedure'
and objectproperty(object_id(quotename(routine_schema) + '.' +
quotename(routine_name)), 'IsMSShipped') = 0
open procedures_cursor
while 1 = 1
begin
fetch next from procedures_cursor into @.rs, @.rn
if @.@.error != 0 or @.@.fetch_status != 0 break
set @.sql = N'exec sp_recompile ''' + quotename(@.rs) + N'.' + quotename(@.rn)
+ N''''
exec sp_executesql @.sql
end
close procedures_cursor
deallocate procedures_cursor
go
AMB
"Do Park via SQLMonster.com" wrote:
> Hello all,
> Is there anyone who has a script to generate a script to re-compile all
> stored procedure under a database? (or a script to re-compile all stored
> procedures under a database.)
> I have to re-compile all stored procedure under a database.
> I will appreciate it if you post it.
> Thanks in advance,
> Do.
> --
> Message posted via http://www.sqlmonster.com
>

A script to get properties of full text catalog?

Hey, all--

My organization is having a problem with MS-SQL's sporadically stopping full-text catalog population on one of our databases. New content is added to that database constantly, so we have an incremental population schedule set up to update the index once an hour. This works fine for a while and then stops for no apparent reason; we don't notice it having stopped until someone comes along to tell us they can't find something.

We've been trying different things to fix it, but in the meanwhile, is there a way to expose the properties (particularly the Last Population Date) of a full-text catalog to an outside script, so that we could incorporate a check of the catalog status into our daily server health scripts? Or, alternatively, a way to have the database server send out an email when the last incremental update is more than an hour old? It's no good to have our users being the ones to tell us when the indexing has failed.

Thanks much.Hello,

Try

use DatabaseName
select FulltextCatalogProperty(N'CatalogName', N'PopulateCompletionAge')

Its in seconds since 12:00:00 A.M., January 1, 1990.

I'll get you more info after i walk downstairs and get some starbucks :)|||Oh, that's perfect. Thanks so much.

For the reference of future readers of this topic, that first param is actually the catalog name, not the database name. Full Microsoft documentation is here: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_fa-fz_7x15.asp . It's amazing what you can find when you know what you're looking for... thanks again!|||Select dateadd(s,FulltextCatalogProperty(N'CatalogName', N'PopulateCompletionAge'),'1/1/1990')

Alot of times, I'll do a incremental right after the insert. That will only be helpful in certain situations.

Thanks for the note on the Catalog name. It looked fishy but I didnt look into it. I guess my catalog name is the same as my database name ;)

A script to generate a script?

Hi,
I'm using 2005's management studio, and I was wondering if there's
anyway to script the "generate scripts - script wizard" so that when I
want to take a snapshot of my database structure I can just run a quick
script instead of having to use the wizard. The fact that it's a
"wizard" leads me to think that there is a "non-wizard" way of doing
it, but I can't find anything in the docs.
Thanks
Chris.Hmm, just a guess , take a look at SMO object library (former SQL DMO)
"chrisb" <chrisbuckett@.gmail.com> wrote in message
news:1150792870.625977.257100@.h76g2000cwa.googlegroups.com...
> Hi,
> I'm using 2005's management studio, and I was wondering if there's
> anyway to script the "generate scripts - script wizard" so that when I
> want to take a snapshot of my database structure I can just run a quick
> script instead of having to use the wizard. The fact that it's a
> "wizard" leads me to think that there is a "non-wizard" way of doing
> it, but I can't find anything in the docs.
> Thanks
> Chris.
>|||You can use SMO...
check this link.. for a start
http://davidhayden.com/blog/dave/ar...02/09/2795.aspx
--
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||http://www.karaszi.com/SQLServer/in...rate_script.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"chrisb" <chrisbuckett@.gmail.com> wrote in message
news:1150792870.625977.257100@.h76g2000cwa.googlegroups.com...
> Hi,
> I'm using 2005's management studio, and I was wondering if there's
> anyway to script the "generate scripts - script wizard" so that when I
> want to take a snapshot of my database structure I can just run a quick
> script instead of having to use the wizard. The fact that it's a
> "wizard" leads me to think that there is a "non-wizard" way of doing
> it, but I can't find anything in the docs.
> Thanks
> Chris.
>

A script to delete views

Hi,

I need a script that I can run from ASP .Net that will delete all
views that start with "Search". My site creates them on the fly and
they tend to accumulate as more users visit the site. Is there a good
SQL help web site that I can refer to that will be me started?

Thanks,

Bill
Cincinnati, OH USAI need a script that I can run from ASP .Net that will delete all

Quote:

Originally Posted by

views that start with "Search". My site creates them on the fly and
they tend to accumulate as more users visit the site. Is there a good
SQL help web site that I can refer to that will be me started?

The script below will delete all dbo-owned views that begin with 'Search'.
However, creating/deleting objects from normal application code is not
secure and often an indication of an application design flaw.

SET NOCOUNT ON

DECLARE @.DropStatement nvarchar(4000)
DECLARE @.LastError int

DECLARE DropStatements
CURSOR LOCAL FAST_FORWARD READ_ONLY FOR
--views
SELECT
N'DROP VIEW ' +
QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME) AS DropStatement
FROM
INFORMATION_SCHEMA.TABLES
WHERE
TABLE_TYPE = N'VIEW'
AND OBJECTPROPERTY(
OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)),
'IsMSShipped') = 0
AND TABLE_SCHEMA = N'dbo'
AND TABLE_NAME LIKE N'Search%'

OPEN DropStatements
WHILE 1 = 1
BEGIN
FETCH NEXT FROM DropStatements INTO @.DropStatement
IF @.@.FETCH_STATUS = -1 BREAK
BEGIN
EXECUTE sp_ExecuteSQL @.DropStatement
SET @.LastError = @.@.ERROR
IF @.LastError 0
BEGIN
BREAK
END
END
END
CLOSE DropStatements
DEALLOCATE DropStatements

--
Hope this helps.

Dan Guzman
SQL Server MVP

<namewitheldbyrequest@.gmail.comwrote in message
news:1159647523.344352.188610@.c28g2000cwb.googlegr oups.com...

Quote:

Originally Posted by

Hi,
>
I need a script that I can run from ASP .Net that will delete all
views that start with "Search". My site creates them on the fly and
they tend to accumulate as more users visit the site. Is there a good
SQL help web site that I can refer to that will be me started?
>
Thanks,
>
Bill
Cincinnati, OH USA
>

A script to capture Orphan userids

Hello
We've had problems about orphan userids in Development SQL server. Application DBAs restore databases from Production SQL server a lot of time. So I'd like to set up an automation using a script to catch orphan ids.
Is it possible to use a script to capture(or select) orphan ids?
Thanks in advance,
Do.
Message posted via http://www.sqlmonster.com
Hi do Park,
You can use the stored procedure below to get a report
exec sp_change_users_login 'Report'
Details in the following article:
http://support.microsoft.com/default...b;en-us;314546
Yih-Yoon Lee
Do Park via SQLMonster.com wrote:
> Hello
> We've had problems about orphan userids in Development SQL server. Application DBAs restore databases from Production SQL server a lot of time. So I'd like to set up an automation using a script to catch orphan ids.
> Is it possible to use a script to capture(or select) orphan ids?
> Thanks in advance,
> Do.
>
|||There is a script to do this at www.sqlservercentral.com in their script
library...
Brad Feaker
Ex nihilo, nihil fit
"Do Park via SQLMonster.com" wrote:

> Hello
> We've had problems about orphan userids in Development SQL server. Application DBAs restore databases from Production SQL server a lot of time. So I'd like to set up an automation using a script to catch orphan ids.
> Is it possible to use a script to capture(or select) orphan ids?
> Thanks in advance,
> Do.
> --
> Message posted via http://www.sqlmonster.com
>
|||As Yih-Yoon Lee has posted, the solution is to use sp_change_users_login
I have found this article helpful. It contains a couple of stored
procedures that allow you to script logins and their SID. You can use these
to create logins on "downstream" (non-production) servers. As long as users
are created somewhere and their SIDs are carried forward you should not end
up with orphaned users.
HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
http://www.support.microsoft.com/?id=246133
Keith
"Do Park via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:9ba509aa5f3a4496ba55516802843699@.SQLMonster.c om...
> Hello
> We've had problems about orphan userids in Development SQL server.
Application DBAs restore databases from Production SQL server a lot of time.
So I'd like to set up an automation using a script to catch orphan ids.
> Is it possible to use a script to capture(or select) orphan ids?
> Thanks in advance,
> Do.
> --
> Message posted via http://www.sqlmonster.com

A script to capture Orphan userids

Hello
We've had problems about orphan userids in Development SQL server. Applicati
on DBAs restore databases from Production SQL server a lot of time. So I'd l
ike to set up an automation using a script to catch orphan ids.
Is it possible to use a script to capture(or select) orphan ids?
Thanks in advance,
Do.
Message posted via http://www.droptable.comHi do Park,
You can use the stored procedure below to get a report
exec sp_change_users_login 'Report'
Details in the following article:
http://support.microsoft.com/defaul...kb;en-us;314546
Yih-Yoon Lee
Do Park via droptable.com wrote:
> Hello
> We've had problems about orphan userids in Development SQL server. Applica
tion DBAs restore databases from Production SQL server a lot of time. So I'd
like to set up an automation using a script to catch orphan ids.
> Is it possible to use a script to capture(or select) orphan ids?
> Thanks in advance,
> Do.
>|||There is a script to do this at www.sqlservercentral.com in their script
library...
Brad Feaker
Ex nihilo, nihil fit
"Do Park via droptable.com" wrote:

> Hello
> We've had problems about orphan userids in Development SQL server. Applica
tion DBAs restore databases from Production SQL server a lot of time. So I'd
like to set up an automation using a script to catch orphan ids.
> Is it possible to use a script to capture(or select) orphan ids?
> Thanks in advance,
> Do.
> --
> Message posted via http://www.droptable.com
>|||As Yih-Yoon Lee has posted, the solution is to use sp_change_users_login
I have found this article helpful. It contains a couple of stored
procedures that allow you to script logins and their SID. You can use these
to create logins on "downstream" (non-production) servers. As long as users
are created somewhere and their SIDs are carried forward you should not end
up with orphaned users.
HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
http://www.support.microsoft.com/?id=246133
Keith
"Do Park via droptable.com" <forum@.droptable.com> wrote in message
news:9ba509aa5f3a4496ba55516802843699@.SQ
droptable.com...
> Hello
> We've had problems about orphan userids in Development SQL server.
Application DBAs restore databases from Production SQL server a lot of time.
So I'd like to set up an automation using a script to catch orphan ids.
> Is it possible to use a script to capture(or select) orphan ids?
> Thanks in advance,
> Do.
> --
> Message posted via http://www.droptable.com

A script to capture Orphan userids

Hello
We've had problems about orphan userids in Development SQL server. Application DBAs restore databases from Production SQL server a lot of time. So I'd like to set up an automation using a script to catch orphan ids.
Is it possible to use a script to capture(or select) orphan ids?
Thanks in advance,
Do.
--
Message posted via http://www.sqlmonster.comHi do Park,
You can use the stored procedure below to get a report
exec sp_change_users_login 'Report'
Details in the following article:
http://support.microsoft.com/default.aspx?scid=kb;en-us;314546
Yih-Yoon Lee
Do Park via SQLMonster.com wrote:
> Hello
> We've had problems about orphan userids in Development SQL server. Application DBAs restore databases from Production SQL server a lot of time. So I'd like to set up an automation using a script to catch orphan ids.
> Is it possible to use a script to capture(or select) orphan ids?
> Thanks in advance,
> Do.
>|||There is a script to do this at www.sqlservercentral.com in their script
library...
Brad Feaker
Ex nihilo, nihil fit
"Do Park via SQLMonster.com" wrote:
> Hello
> We've had problems about orphan userids in Development SQL server. Application DBAs restore databases from Production SQL server a lot of time. So I'd like to set up an automation using a script to catch orphan ids.
> Is it possible to use a script to capture(or select) orphan ids?
> Thanks in advance,
> Do.
> --
> Message posted via http://www.sqlmonster.com
>|||As Yih-Yoon Lee has posted, the solution is to use sp_change_users_login
I have found this article helpful. It contains a couple of stored
procedures that allow you to script logins and their SID. You can use these
to create logins on "downstream" (non-production) servers. As long as users
are created somewhere and their SIDs are carried forward you should not end
up with orphaned users.
HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
http://www.support.microsoft.com/?id=246133
--
Keith
"Do Park via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:9ba509aa5f3a4496ba55516802843699@.SQLMonster.com...
> Hello
> We've had problems about orphan userids in Development SQL server.
Application DBAs restore databases from Production SQL server a lot of time.
So I'd like to set up an automation using a script to catch orphan ids.
> Is it possible to use a script to capture(or select) orphan ids?
> Thanks in advance,
> Do.
> --
> Message posted via http://www.sqlmonster.com

A question about SqlDataSource

In a web appliction, I want to use sqldatasource and I also want to hide the SQL script used in sqldatasource in the DAL, is anyboyd can tell me how to achieve the target? thanksObjectDataSource?