Automating XML from Web to SQL Server

I have a web service, which returns XML to an http request. I need to automate the insert of this XML into SQL server.
I have already designed a procedure which used OPENXML to insert the XML packet, but I need to figure out a way to automate going to the page and retrieving the XML and calling the procedure.
I lack experience on the application development side of SQL Server, and would appriciate any quick & simple ways to perform this. The simpler the better...
Thanks in advance for your ideas.
Michael D.
"sorengi" <sorengi@.discussions.microsoft.com> wrote in message
news:5EEC9F63-7457-4343-A145-5FDCC3615AB3@.microsoft.com...
>I have a web service, which returns XML to an http request. I need to
>automate the insert of this XML into SQL server.
> I have already designed a procedure which used OPENXML to insert the XML
> packet, but I need to figure out a way to automate going to the page and
> retrieving the XML and calling the procedure.
The easiest way to do this would be to create a DTS package in SQL Server
that retrevies the XML and passes it into your procedure. You could then
schedule the package to execute at a specific interval.
See the SQL Server Books Online for information about DTS packages and
scheduling. A simple example of DTS can be found here:
http://sqlxml.org/faqs.aspx?faq=10
Bryant

automating data refresh

Frequently we get request of data refresh. Its curbersome process everytime
to take backup from prod and restore in development/test region. I am
thinking to automate this process using stored procedure. But I need to pass
various parameters to SP to tell where is source, destination, domain, etc.,
Anyone has better ideas/suggestions. Do you have any script reg.
Thanks,
RamuIf you’re using SQL 2005 there are more options obviously with data mirror
and then snapshot. If you want a straight backup and restore then use a
stored proc on the destination server dev or test to use a UNC to grab last
night’s backup file and copy it locally. Then do the restore. Do the copy
with a batch file or better yet a powershell script and then create another
proc that accepts the parameters you need for the restore. Then create a job
with no schedule and give the developers access to the job. The job would
have the step of executing the batch file then executing the restore. You ca
n
also use snapshot replication to refresh dev and test on a schedule when
things are slow, however if you did this make sure the either the dev or tes
t
system are the distributor to offline some of the over head of replication.
If that’s what you want.
John Vandervliet
"Ramu" wrote:

> Frequently we get request of data refresh. Its curbersome process everytim
e
> to take backup from prod and restore in development/test region. I am
> thinking to automate this process using stored procedure. But I need to pa
ss
> various parameters to SP to tell where is source, destination, domain, etc
.,
> Anyone has better ideas/suggestions. Do you have any script reg.
> Thanks,
> Ramu
>|||The "Copy SQL Server Objects Task" in DTS is your tool.
Quentin
"Ramu" <Ramu@.discussions.microsoft.com> wrote in message
news:EDCBFBCD-55C0-44D9-BB19-190EBFBFD7F4@.microsoft.com...
> Frequently we get request of data refresh. Its curbersome process
> everytime
> to take backup from prod and restore in development/test region. I am
> thinking to automate this process using stored procedure. But I need to
> pass
> various parameters to SP to tell where is source, destination, domain,
> etc.,
> Anyone has better ideas/suggestions. Do you have any script reg.
> Thanks,
> Ramu
>

automating data refresh

Frequently we get request of data refresh. Its curbersome process everytime
to take backup from prod and restore in development/test region. I am
thinking to automate this process using stored procedure. But I need to pass
various parameters to SP to tell where is source, destination, domain, etc.,
Anyone has better ideas/suggestions. Do you have any script reg.
Thanks,
Ramu
If you’re using SQL 2005 there are more options obviously with data mirror
and then snapshot. If you want a straight backup and restore then use a
stored proc on the destination server dev or test to use a UNC to grab last
night’s backup file and copy it locally. Then do the restore. Do the copy
with a batch file or better yet a powershell script and then create another
proc that accepts the parameters you need for the restore. Then create a job
with no schedule and give the developers access to the job. The job would
have the step of executing the batch file then executing the restore. You can
also use snapshot replication to refresh dev and test on a schedule when
things are slow, however if you did this make sure the either the dev or test
system are the distributor to offline some of the over head of replication.
If that’s what you want.
John Vandervliet
"Ramu" wrote:

> Frequently we get request of data refresh. Its curbersome process everytime
> to take backup from prod and restore in development/test region. I am
> thinking to automate this process using stored procedure. But I need to pass
> various parameters to SP to tell where is source, destination, domain, etc.,
> Anyone has better ideas/suggestions. Do you have any script reg.
> Thanks,
> Ramu
>
|||The "Copy SQL Server Objects Task" in DTS is your tool.
Quentin
"Ramu" <Ramu@.discussions.microsoft.com> wrote in message
news:EDCBFBCD-55C0-44D9-BB19-190EBFBFD7F4@.microsoft.com...
> Frequently we get request of data refresh. Its curbersome process
> everytime
> to take backup from prod and restore in development/test region. I am
> thinking to automate this process using stored procedure. But I need to
> pass
> various parameters to SP to tell where is source, destination, domain,
> etc.,
> Anyone has better ideas/suggestions. Do you have any script reg.
> Thanks,
> Ramu
>

automating data refresh

Frequently we get request of data refresh. Its curbersome process everytime
to take backup from prod and restore in development/test region. I am
thinking to automate this process using stored procedure. But I need to pass
various parameters to SP to tell where is source, destination, domain, etc.,
Anyone has better ideas/suggestions. Do you have any script reg.
Thanks,
RamuIf youâ're using SQL 2005 there are more options obviously with data mirror
and then snapshot. If you want a straight backup and restore then use a
stored proc on the destination server dev or test to use a UNC to grab last
nightâ's backup file and copy it locally. Then do the restore. Do the copy
with a batch file or better yet a powershell script and then create another
proc that accepts the parameters you need for the restore. Then create a job
with no schedule and give the developers access to the job. The job would
have the step of executing the batch file then executing the restore. You can
also use snapshot replication to refresh dev and test on a schedule when
things are slow, however if you did this make sure the either the dev or test
system are the distributor to offline some of the over head of replication.
If thatâ's what you want.
John Vandervliet
"Ramu" wrote:
> Frequently we get request of data refresh. Its curbersome process everytime
> to take backup from prod and restore in development/test region. I am
> thinking to automate this process using stored procedure. But I need to pass
> various parameters to SP to tell where is source, destination, domain, etc.,
> Anyone has better ideas/suggestions. Do you have any script reg.
> Thanks,
> Ramu
>|||The "Copy SQL Server Objects Task" in DTS is your tool.
Quentin
"Ramu" <Ramu@.discussions.microsoft.com> wrote in message
news:EDCBFBCD-55C0-44D9-BB19-190EBFBFD7F4@.microsoft.com...
> Frequently we get request of data refresh. Its curbersome process
> everytime
> to take backup from prod and restore in development/test region. I am
> thinking to automate this process using stored procedure. But I need to
> pass
> various parameters to SP to tell where is source, destination, domain,
> etc.,
> Anyone has better ideas/suggestions. Do you have any script reg.
> Thanks,
> Ramu
>

Automatically Create Linked Server If Not Exists

I was hoping this would be simple but like everything lately there
seems to be some catch.
What I am trying to do is if one of my SP makes a request to another
system, if the Linked Server does not exists, it will create it on the
fly. However I get this error
Msg 911, Level 16, State 1, Line 11
Could not locate entry in sysdatabases for database 'EXEC master'. No
entry found with that name. Make sure that the name is entered
correctly.
I don't understand why its not executing the entire SQL string at one
time, its weird.
Any Help would be appreciated. I am also running this on SQL 2005
server.
Thanks
-Matt-
[Code]
DECLARE @.SQLServerName NVARCHAR(50)
SET @.SQLServerName = 'Test'
IF NOT EXISTS (
SELECT srv.name
FROM sys.servers srv
WHERE srv.server_id != 0
AND srv.name = @.SQLServerName)
DECLARE @.SQLCMD NVARCHAR(MAX)
SET @.SQLCMD = N'EXEC master.dbo.sp_addlinkedserver @.server = N''' +
@.SQLServerName + N''', @.srvproduct=N''SQL Server'''
PRINT @.SQLCMD
EXEC @.SQLCMD
[/Code]Last line should be
EXEC (@.SQLCMD)
Denis the SQL Menace
http://sqlservercode.blogspot.com/
Matthew wrote:
> I was hoping this would be simple but like everything lately there
> seems to be some catch.
> What I am trying to do is if one of my SP makes a request to another
> system, if the Linked Server does not exists, it will create it on the
> fly. However I get this error
> Msg 911, Level 16, State 1, Line 11
> Could not locate entry in sysdatabases for database 'EXEC master'. No
> entry found with that name. Make sure that the name is entered
> correctly.
> I don't understand why its not executing the entire SQL string at one
> time, its weird.
> Any Help would be appreciated. I am also running this on SQL 2005
> server.
> Thanks
> -Matt-
> [Code]
> DECLARE @.SQLServerName NVARCHAR(50)
> SET @.SQLServerName = 'Test'
> IF NOT EXISTS (
> SELECT srv.name
> FROM sys.servers srv
> WHERE srv.server_id != 0
> AND srv.name = @.SQLServerName)
> DECLARE @.SQLCMD NVARCHAR(MAX)
> SET @.SQLCMD = N'EXEC master.dbo.sp_addlinkedserver @.server = N''' +
> @.SQLServerName + N''', @.srvproduct=N''SQL Server'''
> PRINT @.SQLCMD
> EXEC @.SQLCMD
> [/Code]|||Oh Man, Now I do feel dumb. LOL.
Thanks
-Matt-
SQL Menace wrote:[vbcol=seagreen]
> Last line should be
> EXEC (@.SQLCMD)
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
> Matthew wrote:|||BTW, any reason you do need dynamic SQL?
DECLARE @.SQLServerName NVARCHAR(50)
SET @.SQLServerName = N'Test'
EXEC master.dbo.sp_addlinkedserver @.server = @.SQLServerName,
@.srvproduct=N'SQL Server'
Denis the SQL Menace
http://sqlservercode.blogspot.com/
Matthew wrote:[vbcol=seagreen]
> Oh Man, Now I do feel dumb. LOL.
> Thanks
> -Matt-
>
> SQL Menace wrote:sql

Automatically Create Linked Server If Not Exists

I was hoping this would be simple but like everything lately there
seems to be some catch.
What I am trying to do is if one of my SP makes a request to another
system, if the Linked Server does not exists, it will create it on the
fly. However I get this error
Msg 911, Level 16, State 1, Line 11
Could not locate entry in sysdatabases for database 'EXEC master'. No
entry found with that name. Make sure that the name is entered
correctly.
I don't understand why its not executing the entire SQL string at one
time, its weird.
Any Help would be appreciated. I am also running this on SQL 2005
server.
Thanks
-Matt-
[Code]
DECLARE @.SQLServerName NVARCHAR(50)
SET @.SQLServerName = 'Test'
IF NOT EXISTS (
SELECT srv.name
FROM sys.servers srv
WHERE srv.server_id != 0
AND srv.name = @.SQLServerName)
DECLARE @.SQLCMD NVARCHAR(MAX)
SET @.SQLCMD = N'EXEC master.dbo.sp_addlinkedserver @.server = N''' +
@.SQLServerName + N''', @.srvproduct=N''SQL Server'''
PRINT @.SQLCMD
EXEC @.SQLCMD
[/Code]
Last line should be
EXEC (@.SQLCMD)
Denis the SQL Menace
http://sqlservercode.blogspot.com/
Matthew wrote:
> I was hoping this would be simple but like everything lately there
> seems to be some catch.
> What I am trying to do is if one of my SP makes a request to another
> system, if the Linked Server does not exists, it will create it on the
> fly. However I get this error
> Msg 911, Level 16, State 1, Line 11
> Could not locate entry in sysdatabases for database 'EXEC master'. No
> entry found with that name. Make sure that the name is entered
> correctly.
> I don't understand why its not executing the entire SQL string at one
> time, its weird.
> Any Help would be appreciated. I am also running this on SQL 2005
> server.
> Thanks
> -Matt-
> [Code]
> DECLARE @.SQLServerName NVARCHAR(50)
> SET @.SQLServerName = 'Test'
> IF NOT EXISTS (
> SELECT srv.name
> FROM sys.servers srv
> WHERE srv.server_id != 0
> AND srv.name = @.SQLServerName)
> DECLARE @.SQLCMD NVARCHAR(MAX)
> SET @.SQLCMD = N'EXEC master.dbo.sp_addlinkedserver @.server = N''' +
> @.SQLServerName + N''', @.srvproduct=N''SQL Server'''
> PRINT @.SQLCMD
> EXEC @.SQLCMD
> [/Code]
|||Oh Man, Now I do feel dumb. LOL.
Thanks
-Matt-
SQL Menace wrote:[vbcol=seagreen]
> Last line should be
> EXEC (@.SQLCMD)
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
> Matthew wrote:
|||BTW, any reason you do need dynamic SQL?
DECLARE @.SQLServerName NVARCHAR(50)
SET @.SQLServerName = N'Test'
EXEC master.dbo.sp_addlinkedserver @.server = @.SQLServerName,
@.srvproduct=N'SQL Server'
Denis the SQL Menace
http://sqlservercode.blogspot.com/
Matthew wrote:[vbcol=seagreen]
> Oh Man, Now I do feel dumb. LOL.
> Thanks
> -Matt-
>
> SQL Menace wrote:

Automatically Create Linked Server If Not Exists

I was hoping this would be simple but like everything lately there
seems to be some catch.
What I am trying to do is if one of my SP makes a request to another
system, if the Linked Server does not exists, it will create it on the
fly. However I get this error
Msg 911, Level 16, State 1, Line 11
Could not locate entry in sysdatabases for database 'EXEC master'. No
entry found with that name. Make sure that the name is entered
correctly.
I don't understand why its not executing the entire SQL string at one
time, its weird.
Any Help would be appreciated. I am also running this on SQL 2005
server.
Thanks
-Matt-
[Code]
DECLARE @.SQLServerName NVARCHAR(50)
SET @.SQLServerName = 'Test'
IF NOT EXISTS (
SELECT srv.name
FROM sys.servers srv
WHERE srv.server_id != 0
AND srv.name = @.SQLServerName)
DECLARE @.SQLCMD NVARCHAR(MAX)
SET @.SQLCMD = N'EXEC master.dbo.sp_addlinkedserver @.server = N''' +
@.SQLServerName + N''', @.srvproduct=N''SQL Server'''
PRINT @.SQLCMD
EXEC @.SQLCMD
[/Code]Last line should be
EXEC (@.SQLCMD)
Denis the SQL Menace
http://sqlservercode.blogspot.com/
Matthew wrote:
> I was hoping this would be simple but like everything lately there
> seems to be some catch.
> What I am trying to do is if one of my SP makes a request to another
> system, if the Linked Server does not exists, it will create it on the
> fly. However I get this error
> Msg 911, Level 16, State 1, Line 11
> Could not locate entry in sysdatabases for database 'EXEC master'. No
> entry found with that name. Make sure that the name is entered
> correctly.
> I don't understand why its not executing the entire SQL string at one
> time, its weird.
> Any Help would be appreciated. I am also running this on SQL 2005
> server.
> Thanks
> -Matt-
> [Code]
> DECLARE @.SQLServerName NVARCHAR(50)
> SET @.SQLServerName = 'Test'
> IF NOT EXISTS (
> SELECT srv.name
> FROM sys.servers srv
> WHERE srv.server_id != 0
> AND srv.name = @.SQLServerName)
> DECLARE @.SQLCMD NVARCHAR(MAX)
> SET @.SQLCMD = N'EXEC master.dbo.sp_addlinkedserver @.server = N''' +
> @.SQLServerName + N''', @.srvproduct=N''SQL Server'''
> PRINT @.SQLCMD
> EXEC @.SQLCMD
> [/Code]|||Oh Man, Now I do feel dumb. LOL.
Thanks
-Matt-
SQL Menace wrote:
> Last line should be
> EXEC (@.SQLCMD)
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
> Matthew wrote:
> > I was hoping this would be simple but like everything lately there
> > seems to be some catch.
> >
> > What I am trying to do is if one of my SP makes a request to another
> > system, if the Linked Server does not exists, it will create it on the
> > fly. However I get this error
> >
> > Msg 911, Level 16, State 1, Line 11
> > Could not locate entry in sysdatabases for database 'EXEC master'. No
> > entry found with that name. Make sure that the name is entered
> > correctly.
> >
> > I don't understand why its not executing the entire SQL string at one
> > time, its weird.
> >
> > Any Help would be appreciated. I am also running this on SQL 2005
> > server.
> >
> > Thanks
> >
> > -Matt-
> >
> > [Code]
> > DECLARE @.SQLServerName NVARCHAR(50)
> > SET @.SQLServerName = 'Test'
> > IF NOT EXISTS (
> > SELECT srv.name
> > FROM sys.servers srv
> > WHERE srv.server_id != 0
> > AND srv.name = @.SQLServerName)
> > DECLARE @.SQLCMD NVARCHAR(MAX)
> > SET @.SQLCMD = N'EXEC master.dbo.sp_addlinkedserver @.server = N''' +
> > @.SQLServerName + N''', @.srvproduct=N''SQL Server'''
> > PRINT @.SQLCMD
> > EXEC @.SQLCMD
> > [/Code]|||BTW, any reason you do need dynamic SQL?
DECLARE @.SQLServerName NVARCHAR(50)
SET @.SQLServerName = N'Test'
EXEC master.dbo.sp_addlinkedserver @.server = @.SQLServerName,
@.srvproduct=N'SQL Server'
Denis the SQL Menace
http://sqlservercode.blogspot.com/
Matthew wrote:
> Oh Man, Now I do feel dumb. LOL.
> Thanks
> -Matt-
>
> SQL Menace wrote:
> > Last line should be
> > EXEC (@.SQLCMD)
> >
> > Denis the SQL Menace
> > http://sqlservercode.blogspot.com/
> >
> > Matthew wrote:
> > > I was hoping this would be simple but like everything lately there
> > > seems to be some catch.
> > >
> > > What I am trying to do is if one of my SP makes a request to another
> > > system, if the Linked Server does not exists, it will create it on the
> > > fly. However I get this error
> > >
> > > Msg 911, Level 16, State 1, Line 11
> > > Could not locate entry in sysdatabases for database 'EXEC master'. No
> > > entry found with that name. Make sure that the name is entered
> > > correctly.
> > >
> > > I don't understand why its not executing the entire SQL string at one
> > > time, its weird.
> > >
> > > Any Help would be appreciated. I am also running this on SQL 2005
> > > server.
> > >
> > > Thanks
> > >
> > > -Matt-
> > >
> > > [Code]
> > > DECLARE @.SQLServerName NVARCHAR(50)
> > > SET @.SQLServerName = 'Test'
> > > IF NOT EXISTS (
> > > SELECT srv.name
> > > FROM sys.servers srv
> > > WHERE srv.server_id != 0
> > > AND srv.name = @.SQLServerName)
> > > DECLARE @.SQLCMD NVARCHAR(MAX)
> > > SET @.SQLCMD = N'EXEC master.dbo.sp_addlinkedserver @.server = N''' +
> > > @.SQLServerName + N''', @.srvproduct=N''SQL Server'''
> > > PRINT @.SQLCMD
> > > EXEC @.SQLCMD
> > > [/Code]

Autoimport from xls to SQL 2000?

I have a program setup the exports check request data into a xls file every
week on a shared directory on Windows 2003 server. I'd like a way to import
that data into SQL 2000 on a weekly basis. (Shared directory and SQL on same
server).
Ideas?
just create a dts job to do it.
"Cwhitmore" <Cwhitmore@.discussions.microsoft.com> wrote in message
news:42DDB5A3-0F75-4ABE-B7D5-CBA86251AC6C@.microsoft.com...
>I have a program setup the exports check request data into a xls file every
> week on a shared directory on Windows 2003 server. I'd like a way to
> import
> that data into SQL 2000 on a weekly basis. (Shared directory and SQL on
> same
> server).
> Ideas?
>