Script to stop SQL server services and SQL instances.

What is the best way to write a scrip to Stop SQL Server service or SQL
Instance servies.
Thanks for the help.net stop mssqlserver
<msnews.microsoft.com> wrote in message
news:u7wBZIX8HHA.3624@.TK2MSFTNGP05.phx.gbl...
> What is the best way to write a scrip to Stop SQL Server service or SQL
> Instance servies.
> Thanks for the help.
>

script to do full or differential backups for all databases

Can someone help me write a script to do full backups or differential
backups for all user databases on a server ?
ThanksHi,
Script to backup all databases
CREATE PROCEDURE BACKUP_SP @.Folder VARCHAR(100)
AS
begin
DECLARE @.NAME VARCHAR(100),
@.DBNAME VARCHAR(100)
DECLARE BACKUP_CUR CURSOR FOR
SELECT name FROM sysdatabases where name not
in('model','pubs','tempdb','no_rthwind')
OPEN BACKUP_CUR
FETCH NEXT FROM BACKUP_CUR INTO @.DBNAME
WHILE @.@.FETCH_STATUS=0
BEGIN
SELECT
@.NAME=ltrim(rtrim(@.folder))+@.@._SERVERNAM
E+'_'+@.DBNAME+'_'+ltr_im(rtrim(conve
rt
(char,getdate(),105)))+'Dump.b_ak'
BACKUP DATABASE @.DBNAME TO DISK = @.NAME WITH INIT , NOUNLOAD , NAME =
@.DBNAME, NOSKIP , STATS = 10
FETCH NEXT FROM BACKUP_CUR INTO @.DBNAME
END
CLOSE BACKUP_CUR
DEALLOCATE BACKUP_CUR
end
How to schedule
--
Enterprise Manager - Management -- SQL Agent -- Jobs -- Right click and
create new job.
Give a name to the Job and in Job step menthon this procedure with
foldername as parameter and
scdule the job to be executed based on requirement
Thanks
Hari
SQL Server MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:uzarO8fRFHA.3156@.TK2MSFTNGP15.phx.gbl...
> Can someone help me write a script to do full backups or differential
> backups for all user databases on a server ?
> Thanks
>|||Hassan
Another one using undocumented stored procedure
EXEC sp_MSforeachdb
'IF N''?'' NOT IN(''model'' ,''tempdb'', ''distribution'', ''Northwind'',
''pubs'')
BEGIN
DECLARE @.sql varchar(1000);
set @.sql = ''BACKUP DATABASE '' + (''?'') + '' TO DISK =
''''D:\MSSQL2000\MSSQL\BACKUP'' + (''?'') + ''.bak'''' WITH INIT''
EXEC (@.sql)
END'
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:uzarO8fRFHA.3156@.TK2MSFTNGP15.phx.gbl...
> Can someone help me write a script to do full backups or differential
> backups for all user databases on a server ?
> Thanks
>|||Hassan,
Here is a link to one I know of.
http://www.planet-source-code.com/v...zysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET
resources...

Script to copy entire table

I'm a newbie to script writing. I'm trying to write a script to copy all
data from a table to the same table in a 2nd database. Both databases are
on the same server and are identical in design. I can do this with DTS but
wanted a script I could email to a user to run in Query Analyzer.

Example:
Copy entire table called 'Customers' in the 'Data01' database to
table 'Customers' in the 'Data02' database

I want to overwrite all data in the destination table.

Thanks"sqlnewbie" <sqlnewbie@.yahoo.com> wrote in message
news:k4PYb.27374$vs5.13502@.newssvr25.news.prodigy. com...
> I'm a newbie to script writing. I'm trying to write a script to copy all
> data from a table to the same table in a 2nd database. Both databases are
> on the same server and are identical in design. I can do this with DTS
but
> wanted a script I could email to a user to run in Query Analyzer.
> Example:
> Copy entire table called 'Customers' in the 'Data01' database to
> table 'Customers' in the 'Data02' database
> I want to overwrite all data in the destination table.
> Thanks

/* Replace all data in the destination table */

use Data02
go

truncate table dbo.Customers
insert into dbo.Customers (col1, col2, ...)
select col1, col2, ...
from Data01.dbo.Customers

/* Insert only data which isn't already there */

use Data02
go

insert into dbo.Customers (col1, col2, ...)
select col1, col2, ...
from Data01.dbo.Customers c1
where not exists (select *
from dbo.Customers c2
where c1.PrimaryKeyCol = c2.PrimaryKeyCol)

Note that TRUNCATE TABLE requires certain permissions (see Books Online),
and won't work if the table is referenced by foreign keys. In this case, you
can use "DELETE FROM dbo.Customers".

I would be careful about sending scripts to users, as they often seem to run
them in the wrong place at the wrong time - moving data should really be a
DBA's task (although I appreciate that not everyone has a DBA available).
You may want to back up the database first, just in case.

Simonsql

Script component, dumb question

How do you tell the script task NOT to write a particular row?

This code currently writes 0 and blank when the type <> 4, i'm looking NOT to write the row at all.

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Public Class ScriptMain
Inherits UserComponent

Private iType, iRest As String
Private rawAmount As Double

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
iType = Row.Type
iRest = Row.rest
If iType = "4" Then
Row.ani = iRest.Substring(112, 10)
rawAmount = CInt(iRest.Substring(41, 4))
If rawAmount <> 0 Then
Row.amount = rawAmount / 100
End If
End If
End Sub

End Class

Also, I'm writting these columns to a destination excel, and event hough the spreadsheet cells are formated for an nn.nn numeric, every cell has an error that my data is text and I get that green wedge asking me to convert it. If I manually enter what I'm sending(example 45.5, it takes it just fine and turns it into 45.50 numeric). What can I do about this? anything additional I can send excel to tell it treat numerics like numerics.. maybe something like: mso-number-format or something.

Thanks for any help or information.To have a script task not write a particular row, create two outputs, and direct desired rows to one output, undesired rows to the other.

By default, for a given script component, a single output is created. So, create a second output, named "Output 1" by default. Ensure the SynchrousInputID property of the new output is equal to that of the original. Set the ExclusionGroup on both outputs to 1, meaning they are filtered outputs.

In the component, direct rows as needed, connecting the desired output to downstream inputs. Unconnected ouputs are effectvely discarded (technically they are accessible on the disregarded output). Some will naturally reply, you can do similar operations with a conditional split, and that's true enough.

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper

Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Public Class ScriptMain

Inherits UserComponent

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

If True Then

Row.DirectRowToOutput0()

Else

Row.DirectRowToOutput1()

End If

End Sub

End Class

|||

Having problems with this. I have a script component in a data flow.

when I add the additional output, the synchronousInput id defaults to 0, and I can't change it to 1133 which is what Output 0 has.

In the code, when I attempt to add code like this

Row.DirectRowToOutput1()Row.DirectRowToOutput1()

property DirectRow* is not available. I must be missing something.

Also, while on this, how does one insert additional rows?

|||

You can't insert new rows into a synchronous output, only into an asynchronous one.

If the script component is set to be a transform (you're prompted for this when you first add the script component), then when you add the new output, the SynchronousInputID should be a dropdown that allows you to select the Input.

|||

re "property DirectRow* is not available. I must be missing something."

I had this problem too - resolved it by setting Exclusion Group from 0 to 1 on both outputs

Script component, dumb question

How do you tell the script task NOT to write a particular row?

This code currently writes 0 and blank when the type <> 4, i'm looking NOT to write the row at all.

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Public Class ScriptMain
Inherits UserComponent

Private iType, iRest As String
Private rawAmount As Double

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
iType = Row.Type
iRest = Row.rest
If iType = "4" Then
Row.ani = iRest.Substring(112, 10)
rawAmount = CInt(iRest.Substring(41, 4))
If rawAmount <> 0 Then
Row.amount = rawAmount / 100
End If
End If
End Sub

End Class

Also, I'm writting these columns to a destination excel, and event hough the spreadsheet cells are formated for an nn.nn numeric, every cell has an error that my data is text and I get that green wedge asking me to convert it. If I manually enter what I'm sending(example 45.5, it takes it just fine and turns it into 45.50 numeric). What can I do about this? anything additional I can send excel to tell it treat numerics like numerics.. maybe something like: mso-number-format or something.

Thanks for any help or information.

To have a script task not write a particular row, create two outputs, and direct desired rows to one output, undesired rows to the other.

By default, for a given script component, a single output is created. So, create a second output, named "Output 1" by default. Ensure the SynchrousInputID property of the new output is equal to that of the original. Set the ExclusionGroup on both outputs to 1, meaning they are filtered outputs.

In the component, direct rows as needed, connecting the desired output to downstream inputs. Unconnected ouputs are effectvely discarded (technically they are accessible on the disregarded output). Some will naturally reply, you can do similar operations with a conditional split, and that's true enough.

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper

Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Public Class ScriptMain

Inherits UserComponent

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

If True Then

Row.DirectRowToOutput0()

Else

Row.DirectRowToOutput1()

End If

End Sub

End Class

|||

Having problems with this. I have a script component in a data flow.

when I add the additional output, the synchronousInput id defaults to 0, and I can't change it to 1133 which is what Output 0 has.

In the code, when I attempt to add code like this

Row.DirectRowToOutput1()Row.DirectRowToOutput1()

property DirectRow* is not available. I must be missing something.

Also, while on this, how does one insert additional rows?

|||

You can't insert new rows into a synchronous output, only into an asynchronous one.

If the script component is set to be a transform (you're prompted for this when you first add the script component), then when you add the new output, the SynchronousInputID should be a dropdown that allows you to select the Input.

|||

re "property DirectRow* is not available. I must be missing something."

I had this problem too - resolved it by setting Exclusion Group from 0 to 1 on both outputs

Script Component Task: function that returns last word in a string

I have a field called CustomerName Varchar 100 and I wish to write a function that can do the following in a script component task

create a function called CleanString (ByVal CustomerName as String) As String

CleanString Returns the last word of a Customer name if the CustomerName field contains more than one word or if the CustomerName field does not contain Corp or Ltd

ie parse 'Mr John Tools' and the function returns 'Tools'

ie parse 'TechnicalBooks' and the function returns 'TechnicalBooks'

ie parse 'Microsoft Corp' return 'Microsoft Corp'

ie parse 'Digidesign Ltd' return 'Digidesign Ltd'

Any idea of a regular expression or existing piece of existing code I can have

thanks in advance

dave

I believe you'd want something along the lines of:

\s*(\w+(\s+(Corp|Ltd))?)$

Public Function CleanString(ByVal CustomerName As String) As String

Dim re As New Regex("\s*(\w+(\s+(Corp|Ltd))?)$")

Dim m As Match

m = re.Match(CustomerName)

' insert sanity to make sure we had a match

' group 0 would be the full string

' group 1 is the one we want

Dim g As Group = m.Groups(1)

Dim cc As CaptureCollection = g.Captures

CleanString = cc(0).Value

End Function

|||

Matt,

Thanks the code almost worked--I had to rewrite it like this. However I need help to tweak the regular expression Regex("\s*(\w+(\s+(Corp|Ltd))?)$") Im getting the following results In/Out

Input: Digidesign Australasia Pty Ltd Output: Pty Ltd --wrong should be Ltd

Input: Ms S Aiten Output: Aiten --correct

Function CleanString(ByVal CustomerName As String) As String

m = re.Match(CustomerName)

' group 0 would be the full string

' group 1 is the one we want

CleanString = m.Groups(1).Value

End Function

'Called by

Public Class ScriptMain

Inherits UserComponent

Dim re As Regex = New Regex("\s*(\w+(\s+(Corp|Ltd))?)$")

Dim m As Match

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

If Len(Row.CUSTOMERNAME) > 0 Then

Output0Buffer.oCUSTOMERNAME = Row.CUSTOMERNAME.ToString

Output0Buffer.oSearchName = CleanString(Row.CUSTOMERNAME.ToString)

End If

|||I don't think you can easily do this with a single regex. You might want to pre-process the string to remove the Pty first, or have a special case that captures the words you want to the left and right of "Pty" whenever the string appears in your customer name.

Script

Hi All,
I need to write a script that would check the size of the db file and the size of transaction log file and it they are getting bigger than a certain amount I get notified. Any suggestions?
Thanks.start with sysfiles. the size number might not be totally accurate, you may want use sp_spaceused with the updateuseage argument first.|||Do you have a sample script?|||In fact I do have something somewhere that just goes and tallies the size of the database and log files. It does not however update usage because I was just looking for a quick and dirty number. This however will not meet your needs. I suggest you read up on sp_spaceused and take a look at the system catalog starting with sysfiles. This is an easy thing and it is all layed out for you in BOL if you know where to look.

In BOL, go to Contents\Transact SQL reference and than take a look at system stored procedure and system tables.|||Why wouldn't you just use alerts?|||We don't have a necessary software to do that. To get notified I use mailsend command.

SCOPE_IDENTITY plzzzzz help me

this is my code, i really want a basic easy way without using procedures to do a simple task to response.write(last record added) using scope_identity...
asp.net/VB
===================================================================================
Sub click_addnew(sender as object, e as system.eventargs)

Dim oDR as System.Data.SQLClient.SQLDataReader
Dim oCom As System.Data.SQLClient.SqlCommand
Dim oConn as System.Data.SQLClient.SQLConnection
Dim recnumber as integer
try
oConn = New System.Data.SQLClient.SQLConnection ("server=xxx.xxx.xx; initial catalog=xxx;uid=xxx;pwd=xxx")
oConn.Open()
oCom = New System.Data.SQLClient.SqlCommand()
oCom.Connection = oConn

oCom.CommandText = "INSERT INTO hooliganproducts (hooligantitle, hooliganprice, hooligandescription, hooliganpartno, hooligancata) VALUES ('test' , '100' , 'test' , 'test' , 'test') select scope_identity"
oDR = oCom.ExecuteReader()
response.Write(oDR) 'THIS IS WHERE I WANT TO DISPLAY THE NUMBER OF THE LAST RECORD

catch
Response.Write("Error:" & err.Description)
Finally
oDR = Nothing
oCom = Nothing
oConn.Close()
oConn = Nothing
end try

End Sub
=============================================================================

Please can someone help, feel like i have been banging my head up a brick wall all day, have spent the whole day trying to find an answer.
Thank you in advance
Darren

Multiple SQL commands in a batch should be separated by semicolons (;). Try putting a semicolon between theINSERT statement and theselect scope_identity statement.
|||(1) You need brackets after SCOPE_IDENTITY as in SCOPE_IDENTITY().
(2) You might want to use ExecuteScalar since you are only getting backone value. So you can also avoid the overhead of creating a datareader.
So your code would look something like :
oConn = New System.Data.SQLClient.SQLConnection ("server=xxx.xxx.xx; initial catalog=xxx;uid=xxx;pwd=xxx")
'oConn.Open() - OPEN ONLY WHEN YOU NEED AND CLOSE IMMEDIATELY.
oCom = New System.Data.SQLClient.SqlCommand()
oCom.Connection = oConn
oCom.CommandText= "INSERT INTO hooliganproducts (hooligantitle, hooliganprice,hooligandescription, hooliganpartno, hooligancata) VALUES ('test' ,'100' , 'test' , 'test' , 'test') select SCOPE_IDENTITY()"
Dim newId as Integer
try
oConn.Open()
newId = oCom.ExecuteScalar()
response.Write(newId) 'THIS IS WHERE I WANT TO DISPLAY THE NUMBER OF THE LAST RECORD

catch
Response.Write("Error:" & err.Description)
Finally
oCom = Nothing
oConn.Close()
oConn = Nothing
end try
|||Thank you both. feel much better now, got it working.

scope statement in the session scope?

How to write [scope] statements in the session cope,not in the cube-mdx-script?

In the book called "Microsoft Analysis Services 2005" written by developers from microsft's ssas2005 team, there is a senctence:

"because scope statements and assignment operators work ony in the context of a cube or session..."

How to do that ?

Thanx.

You can't do a scope statement from Management Studio because Management Studio doesn't set the current cube on the connection string.

If you look at the MDX Script Performance tool (http://www.codeplex.com/mdxscriptperf) that Chris Webb wrote you will see an example of running a scope statement within a session. It specifies the cube in the connection string. And it replays the calc script one statement at a time which is an example of using "scope" in the context of a session.

Hope that helps.

|||

Thanx for your answer,which is right absolutely.

But I doubt about the Performance Analyzer according the inner working rule , which can be found in the book called "Microsoft Analysis Services 2005" that is written by developers from SSAS2005.