How to execute a "batch file" using SQL Server 2005?

Hi all,

As the topic describes, I'd like to run a batch file using sql server.
How do i do it?

Thanks in advanceOne method is with a SQL Server agent CmdExec job step. Another is via
xp_cmdshell. If you choose to use xp_cmdshell, be sure you fully understand
the security implications and don't grant direct execute permissions on the
proc.

--
Happy Holidays

Dan Guzman
SQL Server MVP

"coosa" <coosa76@.gmail.com> wrote in message
news:1135346052.206276.14220@.o13g2000cwo.googlegro ups.com...
> Hi all,
> As the topic describes, I'd like to run a batch file using sql server.
> How do i do it?
> Thanks in advance

how to execute (using sp_executesql or other...) heterogeneous dyn

use SET ANSI_NULLS ON and SET ANSI_WARNINGS ON in dynamic query
"herve maillarda" wrote:

> Hi,
> I need to run execute an heterogeneous dynamic query (I build the query
> using nvarchar variable).
> Code sample :
> CREATE PROCEDURE COPY_DATA (@.ServerName nvarchar(20), @.DbName
> nvarchar(20),@.TableName nvarchar(20), @.DateDeb DateTime, @.DateFin
> DateTime) AS
> -- Build SQL Query --
> Set @.Ch_SQL = +"INSERT INTO " + @.ServerName + "." + @.DbName + ".dbo." +
> @.TableName + " ( Date_Heure, Valeur )"
> Set @.Ch_SQL =@.Ch_SQL + " SELECT Date_Heure,Valeur FROM " + @.DbName +
> ".dbo." + @.TableName + " WHERE (Date_Heure >= CONVERT(DATETIME, '" +
> CAST(@.DateDeb AS nvarchar(20)) + "', 102) AND Date_Heure <=
> CONVERT(DATETIME, '" + CAST(@.DateFin AS nvarchar(20)) +"', 102))"
> -- Execute query
> SET ANSI_NULLS ON
> SET ANSI_WARNINGS ON
> exec sp_executesql @.Ch_SQL
> GO
>
> I've alway an error coming from SQL server..."You must set ANSI_NULLS
> and ANSI_WARNINGS"...
> If I try the query from the Analyzer it works ...
> How can I do ?
> Thanks for your help...
> Herve.
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!
>Won't work...
I still have the same error message coming from the Query Analyzer
"[Microsoft][ODBC SQL Server Driver][SQL Server] heterogeneous querys
require ANSI_NULLS options and ANSI_WARNINGS to be defined for the
connection."
I've try to add the options in my query (as you told me) :
Set @.Ch_SQL = "SET ANSI_NULLS ON SET ANSI_WARNINGS ON INSERT INTO " +
@.ServerName ...
Do I miss something ?
thanks for your help.
Herve MAILLARD
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!|||Hi
Do you have triggers that have not had the settings correctly defined?
http://groups-beta.google.com/group...br />
915f7f68
John
Herve MAILLARD wrote:
> Won't work...
> I still have the same error message coming from the Query Analyzer
> "[Microsoft][ODBC SQL Server Driver][SQL Server] heterogeneous querys
> require ANSI_NULLS options and ANSI_WARNINGS to be defined for the
> connection."
> I've try to add the options in my query (as you told me) :
> Set @.Ch_SQL = "SET ANSI_NULLS ON SET ANSI_WARNINGS ON INSERT INTO "
+
> @.ServerName ...
> Do I miss something ?
> thanks for your help.
>
> Herve MAILLARD
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!sql

How to encrypt sql statements from showing in profiler

I have seen when i run a select statement written by some devs which has
some joins,etc.. , that when i run it in profiler it hides the SQL statement
and instead shows in the textdata column text commented out that states its
encrypted.
So I am curious to know how its done ? I ran the sql statement in Query
Analyzer and then in profiler it shows something like ..
-- the text has been encrypted for security reason.
I found that magical.
Can someone tell me how its done ?
I think that having sp_password anywhere in the text (possibly in a comment) will do this. But this
trick doesn't work for 2005...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Hassan" <hassan@.hotmail.com> wrote in message news:OdWpleMzHHA.5484@.TK2MSFTNGP03.phx.gbl...
>I have seen when i run a select statement written by some devs which has some joins,etc.. , that
>when i run it in profiler it hides the SQL statement and instead shows in the textdata column text
>commented out that states its encrypted.
> So I am curious to know how its done ? I ran the sql statement in Query Analyzer and then in
> profiler it shows something like ..
> -- the text has been encrypted for security reason.
> I found that magical.
> Can someone tell me how its done ?
>
|||On Jul 23, 3:31 am, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:[vbcol=seagreen]
> I think that having sp_password anywhere in the text (possibly in a comment) will do this. But this
> trick doesn't work for 2005...
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
> "Hassan" <has...@.hotmail.com> wrote in messagenews:OdWpleMzHHA.5484@.TK2MSFTNGP03.phx.gbl. ..
>
>
well anything that has --sp_addlogin does the same thing and it
*does* works in 2005

How to encrypt sql statements from showing in profiler

I have seen when i run a select statement written by some devs which has
some joins,etc.. , that when i run it in profiler it hides the SQL statement
and instead shows in the textdata column text commented out that states its
encrypted.
So I am curious to know how its done ? I ran the sql statement in Query
Analyzer and then in profiler it shows something like ..
-- the text has been encrypted for security reason.
I found that magical.
Can someone tell me how its done ?I think that having sp_password anywhere in the text (possibly in a comment) will do this. But this
trick doesn't work for 2005...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Hassan" <hassan@.hotmail.com> wrote in message news:OdWpleMzHHA.5484@.TK2MSFTNGP03.phx.gbl...
>I have seen when i run a select statement written by some devs which has some joins,etc.. , that
>when i run it in profiler it hides the SQL statement and instead shows in the textdata column text
>commented out that states its encrypted.
> So I am curious to know how its done ? I ran the sql statement in Query Analyzer and then in
> profiler it shows something like ..
> -- the text has been encrypted for security reason.
> I found that magical.
> Can someone tell me how its done ?
>|||On Jul 23, 3:31 am, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> I think that having sp_password anywhere in the text (possibly in a comment) will do this. But this
> trick doesn't work for 2005...
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
> "Hassan" <has...@.hotmail.com> wrote in messagenews:OdWpleMzHHA.5484@.TK2MSFTNGP03.phx.gbl...
> >I have seen when i run a select statement written by some devs which has some joins,etc.. , that
> >when i run it in profiler it hides the SQL statement and instead shows in the textdata column text
> >commented out that states its encrypted.
> > So I am curious to know how its done ? I ran the sql statement in Query Analyzer and then in
> > profiler it shows something like ..
> > -- the text has been encrypted for security reason.
> > I found that magical.
> > Can someone tell me how its done ?
well anything that has --sp_addlogin does the same thing and it
*does* works in 2005sql

How to encrypt sql statements from showing in profiler

I have seen when i run a select statement written by some devs which has
some joins,etc.. , that when i run it in profiler it hides the SQL statement
and instead shows in the textdata column text commented out that states its
encrypted.
So I am curious to know how its done ? I ran the sql statement in Query
Analyzer and then in profiler it shows something like ..
-- the text has been encrypted for security reason.
I found that magical.
Can someone tell me how its done ?I think that having sp_password anywhere in the text (possibly in a comment)
will do this. But this
trick doesn't work for 2005...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Hassan" <hassan@.hotmail.com> wrote in message news:OdWpleMzHHA.5484@.TK2MSFTNGP03.phx.gbl...

>I have seen when i run a select statement written by some devs which has so
me joins,etc.. , that
>when i run it in profiler it hides the SQL statement and instead shows in t
he textdata column text
>commented out that states its encrypted.
> So I am curious to know how its done ? I ran the sql statement in Query An
alyzer and then in
> profiler it shows something like ..
> -- the text has been encrypted for security reason.
> I found that magical.
> Can someone tell me how its done ?
>|||On Jul 23, 3:31 am, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:[vbcol=seagreen]
> I think that having sp_password anywhere in the text (possibly in a commen
t) will do this. But this
> trick doesn't work for 2005...
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asph
ttp://sqlblog.com/blogs/tibor_karaszi
> "Hassan" <has...@.hotmail.com> wrote in messagenews:OdWpleMzHHA.5484@.TK2MSF
TNGP03.phx.gbl...
>
>
>
>
well anything that has --sp_addlogin does the same thing and it
*does* works in 2005

How to enable direct catalog changes in sql 2005?

I need to run this sql statement: UPDATE sysobjects SET xtype = 'S' WHERE
name = 'dtproperties'
In Sql server 2000 I had to 'Allow modifications to be made directly to the
system catalog'.
How is this done in Sql server 2005?
Thanks,
Sren
Hi
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/2e6e4eeb-b70b-4f45-a253-28ac4e595d75.htm
"Sren Chrsitensen" <xxxx@.xxxx.com> wrote in message
news:uQFMMmv2GHA.476@.TK2MSFTNGP06.phx.gbl...
>I need to run this sql statement: UPDATE sysobjects SET xtype = 'S' WHERE
>name = 'dtproperties'
> In Sql server 2000 I had to 'Allow modifications to be made directly to
> the system catalog'.
> How is this done in Sql server 2005?
> Thanks,
> Sren
>
|||SQL 2005 does not allow direct updates to system tables. May I ask why you
need to do this?
Hope this helps.
Dan Guzman
SQL Server MVP
"Sren Chrsitensen" <xxxx@.xxxx.com> wrote in message
news:uQFMMmv2GHA.476@.TK2MSFTNGP06.phx.gbl...
>I need to run this sql statement: UPDATE sysobjects SET xtype = 'S' WHERE
>name = 'dtproperties'
> In Sql server 2000 I had to 'Allow modifications to be made directly to
> the system catalog'.
> How is this done in Sql server 2005?
> Thanks,
> Sren
>
|||Sysobjects is not a table in SQL 2005; it is a view.
The underlying table is undocumented , and doesn't even have column called
xtype.
So even if direct updates were allowed (as others have told you they are
not), you would need to do quite a bit of analysis on the definition of the
sysobjects view to figure out what you really wanted to change.
HTH
Kalen Delaney, SQL Server MVP
"Sren Chrsitensen" <xxxx@.xxxx.com> wrote in message
news:uQFMMmv2GHA.476@.TK2MSFTNGP06.phx.gbl...
>I need to run this sql statement: UPDATE sysobjects SET xtype = 'S' WHERE
>name = 'dtproperties'
> In Sql server 2000 I had to 'Allow modifications to be made directly to
> the system catalog'.
> How is this done in Sql server 2005?
> Thanks,
> Sren
>

How to enable direct catalog changes in sql 2005?

I need to run this sql statement: UPDATE sysobjects SET xtype = 'S' WHERE
name = 'dtproperties'
In Sql server 2000 I had to 'Allow modifications to be made directly to the
system catalog'.
How is this done in Sql server 2005?
Thanks,
SørenHi
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/2e6e4eeb-b70b-4f45-a253-28ac4e595d75.htm
"Søren Chrsitensen" <xxxx@.xxxx.com> wrote in message
news:uQFMMmv2GHA.476@.TK2MSFTNGP06.phx.gbl...
>I need to run this sql statement: UPDATE sysobjects SET xtype = 'S' WHERE
>name = 'dtproperties'
> In Sql server 2000 I had to 'Allow modifications to be made directly to
> the system catalog'.
> How is this done in Sql server 2005?
> Thanks,
> Søren
>|||SQL 2005 does not allow direct updates to system tables. May I ask why you
need to do this?
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Søren Chrsitensen" <xxxx@.xxxx.com> wrote in message
news:uQFMMmv2GHA.476@.TK2MSFTNGP06.phx.gbl...
>I need to run this sql statement: UPDATE sysobjects SET xtype = 'S' WHERE
>name = 'dtproperties'
> In Sql server 2000 I had to 'Allow modifications to be made directly to
> the system catalog'.
> How is this done in Sql server 2005?
> Thanks,
> Søren
>|||Sysobjects is not a table in SQL 2005; it is a view.
The underlying table is undocumented , and doesn't even have column called
xtype.
So even if direct updates were allowed (as others have told you they are
not), you would need to do quite a bit of analysis on the definition of the
sysobjects view to figure out what you really wanted to change.
--
HTH
Kalen Delaney, SQL Server MVP
"Søren Chrsitensen" <xxxx@.xxxx.com> wrote in message
news:uQFMMmv2GHA.476@.TK2MSFTNGP06.phx.gbl...
>I need to run this sql statement: UPDATE sysobjects SET xtype = 'S' WHERE
>name = 'dtproperties'
> In Sql server 2000 I had to 'Allow modifications to be made directly to
> the system catalog'.
> How is this done in Sql server 2005?
> Thanks,
> Søren
>sql

How to enable direct catalog changes in sql 2005?

I need to run this sql statement: UPDATE sysobjects SET xtype = 'S' WHERE
name = 'dtproperties'
In Sql server 2000 I had to 'Allow modifications to be made directly to the
system catalog'.
How is this done in Sql server 2005?
Thanks,
SrenHi
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/2e6e4eeb-b70b-4f45-a253-
28ac4e595d75.htm
"Sren Chrsitensen" <xxxx@.xxxx.com> wrote in message
news:uQFMMmv2GHA.476@.TK2MSFTNGP06.phx.gbl...
>I need to run this sql statement: UPDATE sysobjects SET xtype = 'S' WHERE
>name = 'dtproperties'
> In Sql server 2000 I had to 'Allow modifications to be made directly to
> the system catalog'.
> How is this done in Sql server 2005?
> Thanks,
> Sren
>|||SQL 2005 does not allow direct updates to system tables. May I ask why you
need to do this?
Hope this helps.
Dan Guzman
SQL Server MVP
"Sren Chrsitensen" <xxxx@.xxxx.com> wrote in message
news:uQFMMmv2GHA.476@.TK2MSFTNGP06.phx.gbl...
>I need to run this sql statement: UPDATE sysobjects SET xtype = 'S' WHERE
>name = 'dtproperties'
> In Sql server 2000 I had to 'Allow modifications to be made directly to
> the system catalog'.
> How is this done in Sql server 2005?
> Thanks,
> Sren
>|||Sysobjects is not a table in SQL 2005; it is a view.
The underlying table is undocumented , and doesn't even have column called
xtype.
So even if direct updates were allowed (as others have told you they are
not), you would need to do quite a bit of analysis on the definition of the
sysobjects view to figure out what you really wanted to change.
HTH
Kalen Delaney, SQL Server MVP
"Sren Chrsitensen" <xxxx@.xxxx.com> wrote in message
news:uQFMMmv2GHA.476@.TK2MSFTNGP06.phx.gbl...
>I need to run this sql statement: UPDATE sysobjects SET xtype = 'S' WHERE
>name = 'dtproperties'
> In Sql server 2000 I had to 'Allow modifications to be made directly to
> the system catalog'.
> How is this done in Sql server 2005?
> Thanks,
> Sren
>

How to edit this script to make it work right?

After run this command:
select 'sp_spaceused ' + name + 'go' from sysobjects where
type = 'U' order by name
I got results like:
sp_spaceused PSPRCSRQSTFILEgo
sp_spaceused PSPRCSRQSTMETAgo
sp_spaceused PSPRCSRQSTSTRNGgo
How to edit the script to seperate go at the line end and
make it like:
sp_spaceused PSPRCSRQSTFILE
go
sp_spaceused PSPRCSRQSTMETA
go
sp_spaceused PSPRCSRQSTSTRNG
go
Lookup REPLACE() function in SQL Server Books Online.
Anith
|||Instead you can use :
SELECT 'EXEC sp_spaceused ' + name + CHAR(13) + 'GO'
FROM sysobjects
WHERE type = 'U'
ORDER BY name ;
Anith

How to edit this script to make it work right?

After run this command:
select 'sp_spaceused ' + name + 'go' from sysobjects where
type = 'U' order by name
I got results like:
sp_spaceused PSPRCSRQSTFILEgo
sp_spaceused PSPRCSRQSTMETAgo
sp_spaceused PSPRCSRQSTSTRNGgo
How to edit the script to seperate go at the line end and
make it like:
sp_spaceused PSPRCSRQSTFILE
go
sp_spaceused PSPRCSRQSTMETA
go
sp_spaceused PSPRCSRQSTSTRNG
goLookup REPLACE() function in SQL Server Books Online.
--
Anith|||Instead you can use :
SELECT 'EXEC sp_spaceused ' + name + CHAR(13) + 'GO'
FROM sysobjects
WHERE type = 'U'
ORDER BY name ;
--
Anith

How to edit a asp.net2.0/C# project in VS2005, Plz HELP

Hi,

How can i run or edit my ASP.NET 2.0/C# project in VS2005. I have source code files (including database) but it doesn't have .sln file. Please guide me how can i edit or run this in VS2005

Hi,

if you are missing the sln file, you should create a new web project and import the existing files to your solution.

HTH, Jens K. Suessmeyer.

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

Thanks for the help.

which files are necessary, while sending ASP.NET 2.0/C# project (for further development) to the developer, like .sln files, database.

please write the name of other files. and how to open that project in VS2005.

Thanks

vkkv

|||Every file except *user files as they store user specific information.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

How to dynamically pull data for the past month?

I have a query that I want to schedule as a DTS package and have it run on
the first of every month to pull data for the previous month. How can I set
the SQL statement to determine what the last month was and use that for the
query parameters?
Thanks in advance for your help!
Isaac WeathersThe last day of the previous month is
select dateadd(dd, -(datepart(dd,getdate()) ), getdate())
I didn't test this but it gets the current day of the month (say the
12th, ) , then subtracts that many days from the current date, leaving you
at the last day of the prior month...You can then take that date and
subtract the the day -1 from that date, giving you the first day of the
month...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Isaac Weathers" <Isaac@.DrivenHosting.com> wrote in message
news:ecUJFsOqFHA.156@.TK2MSFTNGP11.phx.gbl...
>I have a query that I want to schedule as a DTS package and have it run on
> the first of every month to pull data for the previous month. How can I
> set
> the SQL statement to determine what the last month was and use that for
> the
> query parameters?
> Thanks in advance for your help!
> Isaac Weathers
>|||'====yyyymm format
if len(Month(DateAdd("M", -1, Date()))) = 1 then
DateString = DatePart("YYYY", DateAdd("M", -1, Date())) & "0" &
Month(DateAdd("M", -1, Date()))
Else
DateString = DatePart("YYYY", DateAdd("M", -1, Date())) &
Month(DateAdd("M", -1, Date()))
End If
'=====mm/dd/yyy format
DateString = Month(DateAdd("M", -1, Date()) ) & "/01/" &
DatePart("YYYY", DateAdd("M", -1, Date()))|||This will do the trick:
DateAdd("m",-1,
CAST(CONVERT(nvarchar(2), Month(GetDate()))+ '/1/' +
CONVERT(nvarchar(4), Year(GetDate()))
AS SmallDateTime))
AS FirstDayOfLastMonth,
CAST(CONVERT(nvarchar(12), GetDate() - Day(GetDate())) AS SmallDateTime)
AS LastDayOfLastMonth,
GeoSynch
"Isaac Weathers" <Isaac@.DrivenHosting.com> wrote in message
news:ecUJFsOqFHA.156@.TK2MSFTNGP11.phx.gbl...
>I have a query that I want to schedule as a DTS package and have it run on
> the first of every month to pull data for the previous month. How can I set
> the SQL statement to determine what the last month was and use that for the
> query parameters?
> Thanks in advance for your help!
> Isaac Weathers
>

How to do database restoration??

I have to recover a database and redirect it to another
server with the same database name. Every time I run the
job, I get "Unable to attach to <servername>.
A network connection to the device could not be
established."
What am I missing? How can I redirect a SQL restore to
another server? Thanks!Are you backing up and then trying to attach the database? This will not
work as the MDF is locked while the sql service is running. Trying backing
up the one database and restoring (not attaching) the .bak file that was
generated when you backed it up. See restore in BOL for more details.
HTH
Ray Higdon MCSE, MCDBA, CCNA
--
"Brandon" <anonymous@.discussions.microsoft.com> wrote in message
news:076d01c3ca2c$f344f3e0$a501280a@.phx.gbl...

quote:

---

> I have to recover a database and redirect it to another
> server with the same database name. Every time I run the
> job, I get "Unable to attach to <servername>.
> A network connection to the device could not be
> established."
> What am I missing? How can I redirect a SQL restore to
> another server? Thanks!

---

|||Try the other direction - issue the restore on the machine local to where
you want it to land and tell restore the network location of the file(s) for
recovery. All the usual rules regarding sql service account network access
also apply.
Kevin Connell, MCDBA
----
The views expressed here are my own
and not of my employer.
----
"Brandon" <anonymous@.discussions.microsoft.com> wrote in message
news:076d01c3ca2c$f344f3e0$a501280a@.phx.gbl...

quote:

---

> I have to recover a database and redirect it to another
> server with the same database name. Every time I run the
> job, I get "Unable to attach to <servername>.
> A network connection to the device could not be
> established."
> What am I missing? How can I redirect a SQL restore to
> another server? Thanks!

---