Scripting a database template

The problem I am having is that I want to replace the sql script language in
a script and call individual files containing that language from our source
control.
How can I call/run multiple .sql files against a database from a single
script file?It depends on what tool you are using to execute the script. OSQL and SQLCMD
has the :r option. The
sub-script cannot have GO in it, though.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Thomas Mick" <nospam@.nospam.com> wrote in message news:12o3bl2hfjlod55@.corp.supernews.com..
.
> The problem I am having is that I want to replace the sql script language
in a script and call
> individual files containing that language from our source control.
> How can I call/run multiple .sql files against a database from a single sc
ript file?
>

Scripting a database template

The problem I am having is that I want to replace the sql script language in
a script and call individual files containing that language from our source
control.
How can I call/run multiple .sql files against a database from a single
script file?It depends on what tool you are using to execute the script. OSQL and SQLCMD has the :r option. The
sub-script cannot have GO in it, though.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Thomas Mick" <nospam@.nospam.com> wrote in message news:12o3bl2hfjlod55@.corp.supernews.com...
> The problem I am having is that I want to replace the sql script language in a script and call
> individual files containing that language from our source control.
> How can I call/run multiple .sql files against a database from a single script file?
>sql

script to grant permision to role

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

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

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

Script the Profile

I am new to SQL Server. I have read on this site that there is a way to
convert the Profile Template to a script so that I can call it with an Agent
at predetermined times. I cannot seem to script the Template; the choices
between 2000 and sql7 are grayed out.
The only thing I can think of is that you must have a trace actually running
to script it.
Peter
"Adam and Eve had many advantages but the principal one was that they
escaped teething."
Mark Twain
"Rich" wrote:

> I am new to SQL Server. I have read on this site that there is a way to
> convert the Profile Template to a script so that I can call it with an Agent
> at predetermined times. I cannot seem to script the Template; the choices
> between 2000 and sql7 are grayed out.
|||From what the Books Online is saying, you can save Trace Template, Trace
File, or Trace Table as a script. It does not indicate that the Trace is
running. I just want to convert the TDF file to a script to call from the
SQL Agent.
"Peter 'Not Peter The Spate' Nolan" wrote:
[vbcol=seagreen]
> The only thing I can think of is that you must have a trace actually running
> to script it.
> Peter
> "Adam and Eve had many advantages but the principal one was that they
> escaped teething."
> Mark Twain
>
> "Rich" wrote:
|||Rich wrote:
> From what the Books Online is saying, you can save Trace Template,
> Trace File, or Trace Table as a script. It does not indicate that
> the Trace is running. I just want to convert the TDF file to a
> script to call from the SQL Agent.
>
You need to run it. You can pause it if you want. The template itself is
not scriptable because there is not server information attached and
hence no SQL Server version. You need to start the trace, paused it,
then use the script menu to generate the T-SQL code.
David Gugick
Imceda Software
www.imceda.com

Script the Profile

I am new to SQL Server. I have read on this site that there is a way to
convert the Profile Template to a script so that I can call it with an Agent
at predetermined times. I cannot seem to script the Template; the choices
between 2000 and sql7 are grayed out.The only thing I can think of is that you must have a trace actually running
to script it.
Peter
"Adam and Eve had many advantages but the principal one was that they
escaped teething."
Mark Twain
"Rich" wrote:
> I am new to SQL Server. I have read on this site that there is a way to
> convert the Profile Template to a script so that I can call it with an Agent
> at predetermined times. I cannot seem to script the Template; the choices
> between 2000 and sql7 are grayed out.|||From what the Books Online is saying, you can save Trace Template, Trace
File, or Trace Table as a script. It does not indicate that the Trace is
running. I just want to convert the TDF file to a script to call from the
SQL Agent.
"Peter 'Not Peter The Spate' Nolan" wrote:
> The only thing I can think of is that you must have a trace actually running
> to script it.
> Peter
> "Adam and Eve had many advantages but the principal one was that they
> escaped teething."
> Mark Twain
>
> "Rich" wrote:
> > I am new to SQL Server. I have read on this site that there is a way to
> > convert the Profile Template to a script so that I can call it with an Agent
> > at predetermined times. I cannot seem to script the Template; the choices
> > between 2000 and sql7 are grayed out.|||Rich wrote:
> From what the Books Online is saying, you can save Trace Template,
> Trace File, or Trace Table as a script. It does not indicate that
> the Trace is running. I just want to convert the TDF file to a
> script to call from the SQL Agent.
>
You need to run it. You can pause it if you want. The template itself is
not scriptable because there is not server information attached and
hence no SQL Server version. You need to start the trace, paused it,
then use the script menu to generate the T-SQL code.
--
David Gugick
Imceda Software
www.imceda.com

Script the Profile

I am new to SQL Server. I have read on this site that there is a way to
convert the Profile Template to a script so that I can call it with an Agent
at predetermined times. I cannot seem to script the Template; the choices
between 2000 and sql7 are grayed out.The only thing I can think of is that you must have a trace actually running
to script it.
Peter
"Adam and Eve had many advantages but the principal one was that they
escaped teething."
Mark Twain
"Rich" wrote:

> I am new to SQL Server. I have read on this site that there is a way to
> convert the Profile Template to a script so that I can call it with an Age
nt
> at predetermined times. I cannot seem to script the Template; the choices
> between 2000 and sql7 are grayed out.|||From what the Books Online is saying, you can save Trace Template, Trace
File, or Trace Table as a script. It does not indicate that the Trace is
running. I just want to convert the TDF file to a script to call from the
SQL Agent.
"Peter 'Not Peter The Spate' Nolan" wrote:
[vbcol=seagreen]
> The only thing I can think of is that you must have a trace actually runni
ng
> to script it.
> Peter
> "Adam and Eve had many advantages but the principal one was that they
> escaped teething."
> Mark Twain
>
> "Rich" wrote:
>|||Rich wrote:
> From what the Books Online is saying, you can save Trace Template,
> Trace File, or Trace Table as a script. It does not indicate that
> the Trace is running. I just want to convert the TDF file to a
> script to call from the SQL Agent.
>
You need to run it. You can pause it if you want. The template itself is
not scriptable because there is not server information attached and
hence no SQL Server version. You need to start the trace, paused it,
then use the script menu to generate the T-SQL code.
David Gugick
Imceda Software
www.imceda.com

script file from stored procedure

Hi
Can anybody tell me how I call a sql script file(or any file for that
matter) from within a stored procedure? e.g. file on c:\filename.sql
I want the procedure to retrieve the file and also to run it.
Thanks in advance
The simplest way to do this is using xp_cmdshell to call ISQL or OSQL.
There is no direct TSQL command that allows you to run a script stored in a
file.

Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"jonjo" <jonjo@.discussions.microsoft.com> wrote in message
news:21A98D8B-C58A-46E3-B8A4-D6D115AE9744@.microsoft.com...
> Hi
> Can anybody tell me how I call a sql script file(or any file for that
> matter) from within a stored procedure? e.g. file on c:\filename.sql
> I want the procedure to retrieve the file and also to run it.
> Thanks in advance
|||You can use xp_cmdshell and oSql to do this.
Andrew J. Kelly SQL MVP
"jonjo" <jonjo@.discussions.microsoft.com> wrote in message
news:21A98D8B-C58A-46E3-B8A4-D6D115AE9744@.microsoft.com...
> Hi
> Can anybody tell me how I call a sql script file(or any file for that
> matter) from within a stored procedure? e.g. file on c:\filename.sql
> I want the procedure to retrieve the file and also to run it.
> Thanks in advance

script file from stored procedure

Hi
Can anybody tell me how I call a sql script file(or any file for that
matter) from within a stored procedure? e.g. file on c:\filename.sql
I want the procedure to retrieve the file and also to run it.
Thanks in advanceThe simplest way to do this is using xp_cmdshell to call ISQL or OSQL.
There is no direct TSQL command that allows you to run a script stored in a
file.
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"jonjo" <jonjo@.discussions.microsoft.com> wrote in message
news:21A98D8B-C58A-46E3-B8A4-D6D115AE9744@.microsoft.com...
> Hi
> Can anybody tell me how I call a sql script file(or any file for that
> matter) from within a stored procedure? e.g. file on c:\filename.sql
> I want the procedure to retrieve the file and also to run it.
> Thanks in advance|||You can use xp_cmdshell and oSql to do this.
Andrew J. Kelly SQL MVP
"jonjo" <jonjo@.discussions.microsoft.com> wrote in message
news:21A98D8B-C58A-46E3-B8A4-D6D115AE9744@.microsoft.com...
> Hi
> Can anybody tell me how I call a sql script file(or any file for that
> matter) from within a stored procedure? e.g. file on c:\filename.sql
> I want the procedure to retrieve the file and also to run it.
> Thanks in advance

script file from stored procedure

Hi
Can anybody tell me how I call a sql script file(or any file for that
matter) from within a stored procedure? e.g. file on c:\filename.sql
I want the procedure to retrieve the file and also to run it.
Thanks in advanceThe simplest way to do this is using xp_cmdshell to call ISQL or OSQL.
There is no direct TSQL command that allows you to run a script stored in a
file.
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"jonjo" <jonjo@.discussions.microsoft.com> wrote in message
news:21A98D8B-C58A-46E3-B8A4-D6D115AE9744@.microsoft.com...
> Hi
> Can anybody tell me how I call a sql script file(or any file for that
> matter) from within a stored procedure? e.g. file on c:\filename.sql
> I want the procedure to retrieve the file and also to run it.
> Thanks in advance|||You can use xp_cmdshell and oSql to do this.
--
Andrew J. Kelly SQL MVP
"jonjo" <jonjo@.discussions.microsoft.com> wrote in message
news:21A98D8B-C58A-46E3-B8A4-D6D115AE9744@.microsoft.com...
> Hi
> Can anybody tell me how I call a sql script file(or any file for that
> matter) from within a stored procedure? e.g. file on c:\filename.sql
> I want the procedure to retrieve the file and also to run it.
> Thanks in advance

Script component to send email

Is there a way I can on any exception, call my .NET code using System.Mail in SSIS 2005 and use my custom code to send emails rather than the Mail component? The mail component sucks, you can't change the look & feel of the email being sent using CSS but if I can fire off my own code to send the email, then that would work great.

To ensure your script task fails on any error, you would create an On Error event handler and add the task in there.

The script task can use just about any .Net component but the system stuff is there already. Try System.Net.Mail for a start and see if that does what you want. If not you may have to get a third-party component, then just add a reference to it. For new components you will need to place them in the framework folder to be able to add a reference e.g. C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\

|||So I would just do an OnError then a script component to call my .Net email function? Then just pass through the System variables to the script variable? cool.|||Yes, event handlers are great for this. No more messing about with workflow constraints for this type of package (container) wide handling.

script component isn't finding a reference dll

I have created this c# dll for one of my packages and I was planning on calling it from the script component, but for some reason when I try to call it I get the following error.

Could not load file or assembly 'VRS.Utilities.Dates, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null' or one of its dependencies. The system cannot find the file specified.

I've dropped the dll file in the WINDOWS\Microsoft.NET\Framework\v2.0.50727 folder and it shows up when I go to add the reference however when I try to implement it I get the error.

Any idea's on how to fix this?

Thanks for the help
Saitham8

Add the DLL to GAC.

At run time the script task will check the DLL in GAC, at design time script task will check the DLL in folder WINDOWS\Microsoft.NET\Framework\v2.0.50727

|||That seemed to be the solution thanks for the help

script component isn't finding a reference dll

I have created this c# dll for one of my packages and I was planning on calling it from the script component, but for some reason when I try to call it I get the following error.

Could not load file or assembly 'VRS.Utilities.Dates, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null' or one of its dependencies. The system cannot find the file specified.

I've dropped the dll file in the WINDOWS\Microsoft.NET\Framework\v2.0.50727 folder and it shows up when I go to add the reference however when I try to implement it I get the error.

Any idea's on how to fix this?

Thanks for the help
Saitham8

Add the DLL to GAC.

At run time the script task will check the DLL in GAC, at design time script task will check the DLL in folder WINDOWS\Microsoft.NET\Framework\v2.0.50727

|||That seemed to be the solution thanks for the help

script calling script

can a SQL Script refer to and call another sql script?
is there a synatax to run another sql script from within a sql script?
how do you refer to and locate the script you want to run from within another?you could do it by invoking sqlcmd or osql via xp_cmdshell. but why?|||e.g.
C:\tmp>type s1.sql
select 'strt_time'=getdate() into #t1
select count(*) as rows from master..syscolumns where name like '%[a-zA-Z]%'
:r s2.sql
select count(*) as rows from master..sysobjects where name like '%[a-zA-Z]%'
:r s2.sql
drop table #t1
go

C:\tmp>type s2.sql
select 'elapsed(ms)'=datediff(ms,strt_time,getdate()) from #t1
go

C:\tmp>osql -n -Usa -Spdsrv -i s1.sql
Password:
(1 row affected)
rows
----
5080

(1 row affected)
elapsed(ms)
----
46

(1 row affected)
rows
----
1400

(1 row affected)
elapsed(ms)
----
76

(1 row affected)

SCOPE_IDENTITY returns decimal from SQL command

Hi,

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

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

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

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

So I changed to this:

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

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

swaino:

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

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

|||

It should already be an integer.

|||

Welldecimal. provides loads of conversion functions.

Also ExecuteScalardoes return an object not an integer..