Showing posts with label old. Show all posts
Showing posts with label old. Show all posts

Wednesday, March 21, 2012

Executing .sql file using SMO

Hi all,

Is it possible to execute a .sql file using SMO. like in old days we used to use osql.exe to execute the sql files

Thanks in advance

Rujith

Hi,

you don′t need SMO for that,executing scripts can be done via a *normal* sqlconnection (untested script, just wroite down in notepad)

static void Main(string[] args)
{

if (args[0] == null)
{
Console.WriteLine("No file passed to the program.");
return;
}

if (!File.Exists(args[0].ToString()))
{
Console.WriteLine("File does not exist.");
return;
}

try
{
FileStream file = new FileStream(args[0].ToString(), FileMode, FileAccess);
StreamReader sr = new StreamReader(file);
string Commands = sr.ReadToEnd();
sr.Close();
file.Close();

SqlCommand cmd = new SqlCommand(Commands);
cmd.Connection.ConnectionString = "Data Source=.;Integrated Authentication=true";
cmd.Connection.Open();
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
Console.WriteLine(string.Format("Exeception occured: {0}", ex.Message));
}

}

HTH, Jens Suessmeyer.

|||

Hi

This code exevuted for some .sql file

but not for all .

how to do that ?

?

|||

Hi,

what do you mean by *all* ?

-Jens.

|||

I mean

that i do have some script file

One for TAble

One for View

One for Stored procedures and so on...

I also have developed same type of code.

But this code runs for only that table script file only

but not for othres;

specially

Code gives error where GO command is there in script;

|||Either replace the GOs in the script or slit the string into a string array which can be executed one by one.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||

Also check out ExecuteNonQuery function:

server.ConnectionContext.ExecuteNonQuery(sqlStatements);

This function "understands" GO batch separators. You would of course need to load the text from a file to a string.

Artur Laksberg
SQL Server Team
Microsoft

|||

Thnx Artur laksberg

but u have not mentioned what is this "server" object.

please tell about class to which this object does belongs to.

Waiting for yor reply

uday (Emersion)

|||Hi ermersion,
take a look inte namespace

Microsoft.SqlServer.Management.Smo
There is a server class which hold the method Arthur was talking about.
The namespace can be used by referencing the assembly directly (installed in the GAC) or by a file reference on
Microsoft.SqlServer.Smo.dll
SomeTime Ago, I created a small app for a user mapping, the server class is also used there, perhaps you can get a feeling from this:
http://www.sqlserver2005.de/SharedFiles/UserMappingwithSMO.zip
HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Artur laksberg MSFT wrote:

Also check out ExecuteNonQuery function:

server.ConnectionContext.ExecuteNonQuery(sqlStatements);

This function "understands" GO batch separators. You would of course need to load the text from a file to a string.

Artur Laksberg
SQL Server Team
Microsoft

That's what we did. It read the script line by line. When GO was encountered, it submitted what was accumulated to SQL Server using an ADO SQL command object ExecuteNonQuery method. It works fine.

Not familiar with the object you're talking about. We'll have to check that out because it would be more effecient to send scripts that contain multiple batches. However, that requires our customers to upgrade to SQL 2005 (or install the add-on's) to get SMO.

Joe

Executing .sql file using SMO

Hi all,

Is it possible to execute a .sql file using SMO. like in old days we used to use osql.exe to execute the sql files

Thanks in advance

Rujith

Hi,

you don′t need SMO for that,executing scripts can be done via a *normal* sqlconnection (untested script, just wroite down in notepad)

static void Main(string[] args)
{

if (args[0] == null)
{
Console.WriteLine("No file passed to the program.");
return;
}

if (!File.Exists(args[0].ToString()))
{
Console.WriteLine("File does not exist.");
return;
}

try
{
FileStream file = new FileStream(args[0].ToString(), FileMode, FileAccess);
StreamReader sr = new StreamReader(file);
string Commands = sr.ReadToEnd();
sr.Close();
file.Close();

SqlCommand cmd = new SqlCommand(Commands);
cmd.Connection.ConnectionString = "Data Source=.;Integrated Authentication=true";
cmd.Connection.Open();
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
Console.WriteLine(string.Format("Exeception occured: {0}", ex.Message));
}

}

HTH, Jens Suessmeyer.

|||

Hi

This code exevuted for some .sql file

but not for all .

how to do that ?

?

|||

Hi,

what do you mean by *all* ?

-Jens.

|||

I mean

that i do have some script file

One for TAble

One for View

One for Stored procedures and so on...

I also have developed same type of code.

But this code runs for only that table script file only

but not for othres;

specially

Code gives error where GO command is there in script;

|||Either replace the GOs in the script or slit the string into a string array which can be executed one by one.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||

Also check out ExecuteNonQuery function:

server.ConnectionContext.ExecuteNonQuery(sqlStatements);

This function "understands" GO batch separators. You would of course need to load the text from a file to a string.

Artur Laksberg
SQL Server Team
Microsoft

|||

Thnx Artur laksberg

but u have not mentioned what is this "server" object.

please tell about class to which this object does belongs to.

Waiting for yor reply

uday (Emersion)

|||Hi ermersion,
take a look inte namespace

Microsoft.SqlServer.Management.Smo
There is a server class which hold the method Arthur was talking about.
The namespace can be used by referencing the assembly directly (installed in the GAC) or by a file reference on
Microsoft.SqlServer.Smo.dll
SomeTime Ago, I created a small app for a user mapping, the server class is also used there, perhaps you can get a feeling from this:
http://www.sqlserver2005.de/SharedFiles/UserMappingwithSMO.zip
HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Artur laksberg MSFT wrote:

Also check out ExecuteNonQuery function:

server.ConnectionContext.ExecuteNonQuery(sqlStatements);

This function "understands" GO batch separators. You would of course need to load the text from a file to a string.

Artur Laksberg
SQL Server Team
Microsoft

That's what we did. It read the script line by line. When GO was encountered, it submitted what was accumulated to SQL Server using an ADO SQL command object ExecuteNonQuery method. It works fine.

Not familiar with the object you're talking about. We'll have to check that out because it would be more effecient to send scripts that contain multiple batches. However, that requires our customers to upgrade to SQL 2005 (or install the add-on's) to get SMO.

Joe

Friday, February 17, 2012

EXECUTE msdb.dbo.sp_sqlagent_get_perf_counters

Hi All,
We are running a job for archiving old data. The job calls a stored
procedure. The data gets copied successfully to a different database but when
a query is run to delete the data from the source database, it does not get
completed.
What is it trying to do ? Why does it not move forward?
The profiles shows the following continuously...
EXECUTE msdb.dbo.sp_sqlagent_get_perf_counters
-- sp_sqlagent_get_perf_counters
SET NOCOUNT ON
-- sp_sqlagent_get_perf_counters
CREATE TABLE #temp
(
performance_condition NVARCHAR(1024) COLLATE database_default NOT NULL
)
-- sp_sqlagent_get_perf_counters
INSERT INTO #temp VALUES (N'dummy')
IF (@.all_counters = 0)
INSERT INTO #temp
SELECT DISTINCT SUBSTRING(performance_condition, 1, CHARINDEX('|',
performance_condition, PATINDEX('%[_|_]%', performance_condition) + 1) - 1)
FROM msdb.dbo.sysalerts
WHERE (performance_condition IS NOT NULL)
AND (enabled = 1)
SELECT 'object_name' = RTRIM(SUBSTRING(spi1.object_name, 1, 50)),
'counter_name' = RTRIM(SUBSTRING(spi1.counter_name, 1, 50)),
'instance_name' = CASE spi1.instance_name
WHEN N'' THEN NULL
ELSE RTRIM(spi1.instance_name)
END,
'value' = CASE spi1.cntr_type
WHEN 537003008 -- A ratio
THEN CONVERT(FLOAT, spi1.cntr_value) / (SELECT CASE
spi2.cntr_value WHEN 0 THEN 1 ELSE spi2.cntr_value END
FROM
master.dbo.sysperfinfo spi2
WHERE
(spi1.counter_name + ' ' = SUBSTRING(spi2.counter_name, 1, PATINDEX('%
Base%', spi2.counter_name)))
AND
(spi1.instance_name = spi2.instance_name)
AND
(spi2.cntr_type = 1073939459))
ELSE spi1.cntr_value
END
FROM master.dbo.sysperfinfo spi1,
#temp tmp
WHERE (spi1.cntr_type <> 1073939459) -- Divisors
AND ((@.all_counters = 1) OR
(tmp.performance_condition = RTRIM(spi1.object_name) + '|' +
RTRIM(spi1.counter_name)))
sp_verify_job_identifiers '@.job_name',
'@.job_id',
@.job_name OUTPUT,
@.job_id OUTPUT,
'NO_TEST'
sp_sqlagent_get_perf_counters is a system procedure which feeds alerts for
SQL Agent service. There might be an entry in the SQL Server registry under
the key PerformanceSamplingInterval in MSSQLServer\SQLServerAgent which sets
the sampling interval. You can reduce this default by changing the value
there.
Alternatively you can totally avoid them by removing all the alerts set in
SQL Server( some of them are demo alerts set by SQL Server install by
default). Goto EM, under management, SQL Server Agent, delete all the
alerts. If you have no alerts the procedure will not run.
Anith
|||Hi Anith,
Thanks for a quick reply Do these alerts affect the performance of any
other query running in parallel ? One of our queries to delete bulk records
is taking a helll lot of time and is not returning, profiler traces show that
the query has started and then a large number of these
sp_sqlagent_get_perf_counters are shown, can you give some more inputs?
Regards
Sachin
"Anith Sen" wrote:

> sp_sqlagent_get_perf_counters is a system procedure which feeds alerts for
> SQL Agent service. There might be an entry in the SQL Server registry under
> the key PerformanceSamplingInterval in MSSQLServer\SQLServerAgent which sets
> the sampling interval. You can reduce this default by changing the value
> there.
> Alternatively you can totally avoid them by removing all the alerts set in
> SQL Server( some of them are demo alerts set by SQL Server install by
> default). Goto EM, under management, SQL Server Agent, delete all the
> alerts. If you have no alerts the procedure will not run.
> --
> Anith
>
>
|||>> Do these alerts affect the performance of any other query running in[vbcol=seagreen]
Generally, it should be negligible. However, you can check the trace file to
see how often this procedure is being run. If it is being run every 5 sec,
or 10 sec depending on the polling interval, then in heavy transaction
oriented systems, it might have some impact.
[vbcol=seagreen]
I do not have a first hand experience of how it impacts huge deletes, but on
a related note, do you have a profiler trace running 24/7 on the production
machine? In transaction-heavy systems, that itself can have dampening effect
on the overall performance.
Anith
|||No we do not have profiler running on production. Its just on the test
environment.
Not sure what is it trying to do?
And yes, I checked for msdb.dbo.sp_sqlagent_get_perf_counters ? Its taking
around 15-16 ms
Please find below some other traces as well.
-- EXECUTE msdb.dbo.sp_sqlagent_get_perf_counters
-- SELECT N'Testing Connection...'
-- EXECUTE msdb.dbo.sp_sqlagent_get_perf_counters
-- SELECT N'Testing Connection...'
-- EXECUTE msdb.dbo.sp_sqlagent_get_perf_counters
...
...
-- SET TEXTSIZE 64512
-- select @.@.microsoftversion
-- select convert(sysname, serverproperty(N'servername'))
-- SELECT ISNULL(SUSER_SNAME(), SUSER_NAME())
-- EXECUTE msdb.dbo.sp_help_jobstep @.job_id =
0x63314D2F34B5AC449E29CF142843F50F
-- EXECUTE @.retval = sp_verify_job_identifiers '@.job_name',
'@.job_id',
@.job_name OUTPUT,
@.job_id OUTPUT,
'NO_TEST'
-- EXEC dbo.sp_MSdistribution_cleanup @.min_distretention = 0,
@.max_distretention = 72
-- exec @.retcode = dbo.sp_MSsubscription_cleanup @.cutoff_time
Thanks,
Sachin
"Anith Sen" wrote:

> Generally, it should be negligible. However, you can check the trace file to
> see how often this procedure is being run. If it is being run every 5 sec,
> or 10 sec depending on the polling interval, then in heavy transaction
> oriented systems, it might have some impact.
>
> I do not have a first hand experience of how it impacts huge deletes, but on
> a related note, do you have a profiler trace running 24/7 on the production
> machine? In transaction-heavy systems, that itself can have dampening effect
> on the overall performance.
> --
> Anith
>
>