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
Showing posts with label service. Show all posts
Showing posts with label service. Show all posts
Thursday, March 29, 2012
Automating SQL Server Service Pack / Patch / Hot fix apply
In one of my task I have to apply the latest SQL Server Service Pack /
Patch / Hot fix on all the SQL Server Boxes in our client environment. There
are more than 400 SQL Server boxes with SQL Server version 6.5, 7.0 and 2000
running on Window NT, 2000 and 2003 Platforms. We have been given deadline of
one month to apply the SQL Server patches access all the box.
It is also being advised to rollout this task Quarterly on every year
as the Service pack / Patch are being released.
Hereby I am looking for some advise on
1. The best process to follow on for this task:
2. Automating this task:
Is anyone used any third party tools like "Opsware" to automate this
process?
3. Issues and Risks:
Issues and Risks which to be faced as part of this task .
4. Misc:
Any sort of information that would help with this task.
Thanks
S SIVAPRASAD
Have you considered Microsoft Systems Managment Server. It's really designed
just for change and configuration management. It's a great tool for applying
service packs & hotfixes accross the enterprise.
See http://www.microsoft.com/smserver/ev.../default.mspx.
Mike
This posting is provided "AS IS" with no warranties, and confers no rights."
When you include scripts, it should also include "Use of included script
samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm.
"S Siva Prasad [SIVA]" <SSivaPrasadSIVA@.discussions.microsoft.com> wrote in
message news:1C8805CA-3E70-452E-944A-30419598693D@.microsoft.com...
> In one of my task I have to apply the latest SQL Server Service Pack /
> Patch / Hot fix on all the SQL Server Boxes in our client environment.
> There
> are more than 400 SQL Server boxes with SQL Server version 6.5, 7.0 and
> 2000
> running on Window NT, 2000 and 2003 Platforms. We have been given deadline
> of
> one month to apply the SQL Server patches access all the box.
> It is also being advised to rollout this task Quarterly on every year
> as the Service pack / Patch are being released.
> Hereby I am looking for some advise on
> 1. The best process to follow on for this task:
> 2. Automating this task:
> Is anyone used any third party tools like "Opsware" to automate this
> process?
>
> 3. Issues and Risks:
> Issues and Risks which to be faced as part of this task .
>
> 4. Misc:
> Any sort of information that would help with this task.
> --
> Thanks
> S SIVAPRASAD
sql
Patch / Hot fix on all the SQL Server Boxes in our client environment. There
are more than 400 SQL Server boxes with SQL Server version 6.5, 7.0 and 2000
running on Window NT, 2000 and 2003 Platforms. We have been given deadline of
one month to apply the SQL Server patches access all the box.
It is also being advised to rollout this task Quarterly on every year
as the Service pack / Patch are being released.
Hereby I am looking for some advise on
1. The best process to follow on for this task:
2. Automating this task:
Is anyone used any third party tools like "Opsware" to automate this
process?
3. Issues and Risks:
Issues and Risks which to be faced as part of this task .
4. Misc:
Any sort of information that would help with this task.
Thanks
S SIVAPRASAD
Have you considered Microsoft Systems Managment Server. It's really designed
just for change and configuration management. It's a great tool for applying
service packs & hotfixes accross the enterprise.
See http://www.microsoft.com/smserver/ev.../default.mspx.
Mike
This posting is provided "AS IS" with no warranties, and confers no rights."
When you include scripts, it should also include "Use of included script
samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm.
"S Siva Prasad [SIVA]" <SSivaPrasadSIVA@.discussions.microsoft.com> wrote in
message news:1C8805CA-3E70-452E-944A-30419598693D@.microsoft.com...
> In one of my task I have to apply the latest SQL Server Service Pack /
> Patch / Hot fix on all the SQL Server Boxes in our client environment.
> There
> are more than 400 SQL Server boxes with SQL Server version 6.5, 7.0 and
> 2000
> running on Window NT, 2000 and 2003 Platforms. We have been given deadline
> of
> one month to apply the SQL Server patches access all the box.
> It is also being advised to rollout this task Quarterly on every year
> as the Service pack / Patch are being released.
> Hereby I am looking for some advise on
> 1. The best process to follow on for this task:
> 2. Automating this task:
> Is anyone used any third party tools like "Opsware" to automate this
> process?
>
> 3. Issues and Risks:
> Issues and Risks which to be faced as part of this task .
>
> 4. Misc:
> Any sort of information that would help with this task.
> --
> Thanks
> S SIVAPRASAD
sql
Tuesday, March 27, 2012
Automating Daily Database Inserts...Contd
I used thetutorial on creating a windows service. i was able to succesfully create a service. however, after going through the whole tutorial i realized this is something that is always running. i need to run the service only once a day ( usually past midnight).
however, i dont know what i messed up. i have been fiddling with it for some time now. i had already created 2-3 windows applications- one for creating a bunch of html pages , another for opening each of these files in a word app and printing it. now i was trying to merge all these processes into one windoes service. so i cut/pasted some code..etc. now it throws an error :
"
cannot start service from command line or a debugger. A Windows Service must first be installed (using instalutil.exe) and then started with the Server Explorer, Windows Services Administrative tool or the NET START command."
does anyone know how i can configure it so it runs only at a specified time and not always.
thanks.The service will always be running since that's how services work. The service periodically checks every Timer.Internval milliseconds whether something should be run. When it actually runs something, like a report at midnight, is up to your code.
If you are concerned about the resources used by the service you can open up the Windows Task Manager and look under the Processes tab. My own similar scheduler uses a negligible amount of CPU. It barely ticks over a few seconds per 24 hours.|||so how do i set it to run at a certain time..all my calculations r dependent on the date functions...so i need the service to run after 12 midnight. so how do i compare the timer to the time of the day...
do you know of any tutorial or some sample...
thanks McMurdoStation|||The service is always running (that is the beauty of it). You need to code it such that it looks at the time of day and runs your process when required. In your case, you can have it check the date periodically, and then whenever the date changes, run the process.|||
|||thanks both of you. i understand it better now.
// C# but this should give you the general idea// Declare class level variable to hold date process last run
private DateTime lastRunDate = System.DateTime.Today;// then handle the event the timer generates when each Timer.Interval has elapsed
private void timer1_Elapsed(object sender, System.Timers.ElapsedEventArgs e) {
DateTime today= System.DateTime.Today;
if( today > lastRunDate){
lastRunDate= today ;
RunYourStuff();
}
}
however, i dont know what i messed up. i have been fiddling with it for some time now. i had already created 2-3 windows applications- one for creating a bunch of html pages , another for opening each of these files in a word app and printing it. now i was trying to merge all these processes into one windoes service. so i cut/pasted some code..etc. now it throws an error :
"
cannot start service from command line or a debugger. A Windows Service must first be installed (using instalutil.exe) and then started with the Server Explorer, Windows Services Administrative tool or the NET START command."
i am pretty sure its not the code. also when i went to administrative tols -> services -> and browsed for my "Service1"... it shows up in the list but its not started. i was trying to start it. it says "
"The service1 on local computer started ans then stopped. some services stop automatically if they have no work to do, for example, the Performance Logs and Alerts Service."
can you help me figure this out...
thanks in advance|||anyone..|||Did you create the installer for the service? I think that article talks about that too. After you create and run the installer then you can start the service from the Services Manager (if it didn't start automatically).
If the service is crashing still then add some code to write out the event log so that you can figure out where it is when it crashes.|||actually i was able to start the service...i can see it from the admin tools -> services
i tried to schedule it using windows scheduler and when the program ran at the specified time
.. it kept throwing the error :
"cannot start service from command line or a debugger. A Windows Service must first be installed (using instalutil.exe) and then started with the Server Explorer, Windows Services Administrative tool or the NET START command."
i dont understand why we have to create a set up project..
when i schedule the task.. i should select the windowsservice1.exe right ?
i googled around for some time, but most of the articles are too brief..
thanks|||You do not have to schedule the service to run. It is designed to CONSTANTLY run, with or without a user logged in. You need to check to make sure the service continues running (if it stops running, there is a problem in your code) and if it is running, internally in the code, you need to make it do what you want to do periodically (whatever period you need).|||when i tried to build the solution i get this error:
WARNING: This setup does not contain the .NET Framework which must be installed on the target machine by running dotnetfx.exe before this setup will install. You can find dotnetfx.exe on the Visual Studio .NET 'Windows Components Update' media. Dotnetfx.exe can be redistributed with your setup.
i really need some help in getting this running.
doug, like you said i removed it from the scheduled events. i coded it as :
Dim lastrun = System.DateTime.Now.Hour
Private Sub Timer1_Elapsed(ByVal sender As System.Object, ByVal e As System.Timers.ElapsedEventArgs) Handles Timer1.Elapsed
Dim today As DateTime = System.DateTime.Today
If today.Hour > lastrun Then
lastrun = today
'add monthly charges
Call addmonthlycharges()
'create the html statements
Call createstmts()
'print the stmts
Call printstmts()
End If
End Sub
so it will run every hour ( for now ) though it needs to run once a day.
i was trying to debug the program but it keeps throwing back the error :
"cannot start service from command line or a debugger. A Windows Service must first be installed (using instalutil.exe) and then started with the Server Explorer, Windows Services Administrative tool or the NET START command."
thanks|||someone...?|||anyone......|||The warning message about dotnetfx isn't a big deal. Presumably you have the DotNet framework already on your computer so it won't matter. It would only become an issue if you want to deploy your service on a server that doesn't already have DotNet. You can worry about that later...
To install the service follow the directions on that articlehttp://authors.aspalliance.com/hrmalik/articles/2003/200302/20030203.aspx">starting on this page.
You've already done this given the dotnetfx warning message. After it has built the install file (something.msi) right click on the installer project in the solutions explorer and select "Install" from the pop-up list. Either that or navigate to the something.msi file it created an double-click.
Follow the usual instructions for the install wizard.
After the install is done go to the services manager, look up the service you just installed, and start it. In principal, it should then start working and running your update at midnight.|||i can see the status of the service as "started" under services, but its not doing anything.. the prog is actually supposed to create a new folder and a few html files inside the folder and also print them.
heres the entire code :
Public Sub New()
MyBase.New()' This call is required by the Component Designer.
InitializeComponent()' Add any initialization after the InitializeComponent() call
If Not EventLog.SourceExists("MySource") Then
EventLog.CreateEventSource("MySource", "MyNewLog")
End If
EventLog1.Source = "MySource"
EventLog1.Log = "MyNewLog"End Sub
Dim FileExists As Boolean
Dim lblmessage As String = Now()
Dim lastrun = System.DateTime.Now.HourProtected Overrides Sub OnStart(ByVal args() As String)
' Add code here to start your service. This method should set things
' in motion so your service can do its work.
EventLog1.WriteEntry("Starting")
Timer1.Start()
End SubProtected Overrides Sub OnStop()
' Add code here to perform any tear-down necessary to stop your service.
EventLog1.WriteEntry("Stopping")
Timer1.Stop()
End SubPrivate Sub Timer1_Elapsed(ByVal sender As System.Object, ByVal e As System.Timers.ElapsedEventArgs) Handles Timer1.Elapsed
Dim today As DateTime = System.DateTime.Today
If today.Hour > lastrun Then
lastrun = today
'add monthly charges
Call addmonthlycharges()
'create the html statements
Call createstmts()
'print the stmts
Call printstmts()
End If
End Sub
i am sure theres no prob with the code, since i had the same code in a windows application and it runs fine. i was just trying to automate it so it runs by itself everyday...
i'd really appreciate any help in this..
thanks
automatically stop/restart sql service
I would like to be able to automatically stop a sql service running on sql
2005 express, use a script to transfer files over to another server and
restart the service automatically.
Any ideas of how I can do this?
You can use a batch file that does a net stop, copy and net start. Schedule
it with task scheduler.
Jason Massie
Web: http://statisticsio.com
RSS: http://feeds.feedburner.com/statisticsio
"pete0085" <pete0085@.discussions.microsoft.com> wrote in message
news:63C59DBE-8BBF-4967-BFB4-EDEF3DA0B84C@.microsoft.com...
>I would like to be able to automatically stop a sql service running on sql
> 2005 express, use a script to transfer files over to another server and
> restart the service automatically.
> Any ideas of how I can do this?
|||When it does a copy and it's only copying one file, not the entire directory.
The directory has 4 folders and 1 readme file and it's copying the readme
file.
Any ideas?
"Jason Massie" wrote:
> You can use a batch file that does a net stop, copy and net start. Schedule
> it with task scheduler.
>
> --
> Jason Massie
> Web: http://statisticsio.com
> RSS: http://feeds.feedburner.com/statisticsio
> "pete0085" <pete0085@.discussions.microsoft.com> wrote in message
> news:63C59DBE-8BBF-4967-BFB4-EDEF3DA0B84C@.microsoft.com...
>
>
|||xcopy /S
Documentation for the other switches is available by running xcopy /?
Jason Massie
Web: http://statisticsio.com
RSS: http://feeds.feedburner.com/statisticsio
"pete0085" <pete0085@.discussions.microsoft.com> wrote in message
news:752C5BDF-A76C-48E3-8D9F-013739AE87AE@.microsoft.com...[vbcol=seagreen]
> When it does a copy and it's only copying one file, not the entire
> directory.
> The directory has 4 folders and 1 readme file and it's copying the readme
> file.
> Any ideas?
> "Jason Massie" wrote:
sql
2005 express, use a script to transfer files over to another server and
restart the service automatically.
Any ideas of how I can do this?
You can use a batch file that does a net stop, copy and net start. Schedule
it with task scheduler.
Jason Massie
Web: http://statisticsio.com
RSS: http://feeds.feedburner.com/statisticsio
"pete0085" <pete0085@.discussions.microsoft.com> wrote in message
news:63C59DBE-8BBF-4967-BFB4-EDEF3DA0B84C@.microsoft.com...
>I would like to be able to automatically stop a sql service running on sql
> 2005 express, use a script to transfer files over to another server and
> restart the service automatically.
> Any ideas of how I can do this?
|||When it does a copy and it's only copying one file, not the entire directory.
The directory has 4 folders and 1 readme file and it's copying the readme
file.
Any ideas?
"Jason Massie" wrote:
> You can use a batch file that does a net stop, copy and net start. Schedule
> it with task scheduler.
>
> --
> Jason Massie
> Web: http://statisticsio.com
> RSS: http://feeds.feedburner.com/statisticsio
> "pete0085" <pete0085@.discussions.microsoft.com> wrote in message
> news:63C59DBE-8BBF-4967-BFB4-EDEF3DA0B84C@.microsoft.com...
>
>
|||xcopy /S
Documentation for the other switches is available by running xcopy /?
Jason Massie
Web: http://statisticsio.com
RSS: http://feeds.feedburner.com/statisticsio
"pete0085" <pete0085@.discussions.microsoft.com> wrote in message
news:752C5BDF-A76C-48E3-8D9F-013739AE87AE@.microsoft.com...[vbcol=seagreen]
> When it does a copy and it's only copying one file, not the entire
> directory.
> The directory has 4 folders and 1 readme file and it's copying the readme
> file.
> Any ideas?
> "Jason Massie" wrote:
sql
automatically stop/restart sql service
I would like to be able to automatically stop a sql service running on sql
2005 express, use a script to transfer files over to another server and
restart the service automatically.
Any ideas of how I can do this?You can use a batch file that does a net stop, copy and net start. Schedule
it with task scheduler.
Jason Massie
Web: http://statisticsio.com
RSS: http://feeds.feedburner.com/statisticsio
"pete0085" <pete0085@.discussions.microsoft.com> wrote in message
news:63C59DBE-8BBF-4967-BFB4-EDEF3DA0B84C@.microsoft.com...
>I would like to be able to automatically stop a sql service running on sql
> 2005 express, use a script to transfer files over to another server and
> restart the service automatically.
> Any ideas of how I can do this?|||When it does a copy and it's only copying one file, not the entire directory.
The directory has 4 folders and 1 readme file and it's copying the readme
file.
Any ideas?
"Jason Massie" wrote:
> You can use a batch file that does a net stop, copy and net start. Schedule
> it with task scheduler.
>
> --
> Jason Massie
> Web: http://statisticsio.com
> RSS: http://feeds.feedburner.com/statisticsio
> "pete0085" <pete0085@.discussions.microsoft.com> wrote in message
> news:63C59DBE-8BBF-4967-BFB4-EDEF3DA0B84C@.microsoft.com...
> >I would like to be able to automatically stop a sql service running on sql
> > 2005 express, use a script to transfer files over to another server and
> > restart the service automatically.
> >
> > Any ideas of how I can do this?
>
>|||xcopy /S
Documentation for the other switches is available by running xcopy /?
--
Jason Massie
Web: http://statisticsio.com
RSS: http://feeds.feedburner.com/statisticsio
"pete0085" <pete0085@.discussions.microsoft.com> wrote in message
news:752C5BDF-A76C-48E3-8D9F-013739AE87AE@.microsoft.com...
> When it does a copy and it's only copying one file, not the entire
> directory.
> The directory has 4 folders and 1 readme file and it's copying the readme
> file.
> Any ideas?
> "Jason Massie" wrote:
>> You can use a batch file that does a net stop, copy and net start.
>> Schedule
>> it with task scheduler.
>>
>> --
>> Jason Massie
>> Web: http://statisticsio.com
>> RSS: http://feeds.feedburner.com/statisticsio
>> "pete0085" <pete0085@.discussions.microsoft.com> wrote in message
>> news:63C59DBE-8BBF-4967-BFB4-EDEF3DA0B84C@.microsoft.com...
>> >I would like to be able to automatically stop a sql service running on
>> >sql
>> > 2005 express, use a script to transfer files over to another server and
>> > restart the service automatically.
>> >
>> > Any ideas of how I can do this?
>>
2005 express, use a script to transfer files over to another server and
restart the service automatically.
Any ideas of how I can do this?You can use a batch file that does a net stop, copy and net start. Schedule
it with task scheduler.
Jason Massie
Web: http://statisticsio.com
RSS: http://feeds.feedburner.com/statisticsio
"pete0085" <pete0085@.discussions.microsoft.com> wrote in message
news:63C59DBE-8BBF-4967-BFB4-EDEF3DA0B84C@.microsoft.com...
>I would like to be able to automatically stop a sql service running on sql
> 2005 express, use a script to transfer files over to another server and
> restart the service automatically.
> Any ideas of how I can do this?|||When it does a copy and it's only copying one file, not the entire directory.
The directory has 4 folders and 1 readme file and it's copying the readme
file.
Any ideas?
"Jason Massie" wrote:
> You can use a batch file that does a net stop, copy and net start. Schedule
> it with task scheduler.
>
> --
> Jason Massie
> Web: http://statisticsio.com
> RSS: http://feeds.feedburner.com/statisticsio
> "pete0085" <pete0085@.discussions.microsoft.com> wrote in message
> news:63C59DBE-8BBF-4967-BFB4-EDEF3DA0B84C@.microsoft.com...
> >I would like to be able to automatically stop a sql service running on sql
> > 2005 express, use a script to transfer files over to another server and
> > restart the service automatically.
> >
> > Any ideas of how I can do this?
>
>|||xcopy /S
Documentation for the other switches is available by running xcopy /?
--
Jason Massie
Web: http://statisticsio.com
RSS: http://feeds.feedburner.com/statisticsio
"pete0085" <pete0085@.discussions.microsoft.com> wrote in message
news:752C5BDF-A76C-48E3-8D9F-013739AE87AE@.microsoft.com...
> When it does a copy and it's only copying one file, not the entire
> directory.
> The directory has 4 folders and 1 readme file and it's copying the readme
> file.
> Any ideas?
> "Jason Massie" wrote:
>> You can use a batch file that does a net stop, copy and net start.
>> Schedule
>> it with task scheduler.
>>
>> --
>> Jason Massie
>> Web: http://statisticsio.com
>> RSS: http://feeds.feedburner.com/statisticsio
>> "pete0085" <pete0085@.discussions.microsoft.com> wrote in message
>> news:63C59DBE-8BBF-4967-BFB4-EDEF3DA0B84C@.microsoft.com...
>> >I would like to be able to automatically stop a sql service running on
>> >sql
>> > 2005 express, use a script to transfer files over to another server and
>> > restart the service automatically.
>> >
>> > Any ideas of how I can do this?
>>
Sunday, March 25, 2012
automatically grow file
SQL Server 7.00, Service Pack 6
I had an insert statement that stopped working the other day. I figured out
that it had to do with the fact that "space available" on the database was
very small. I had "automatically grow file" on the db set to 10%, and I had
plenty of disk space. I think that when my client called the insert
statement, the db was trying to automatically grow, but it was taking so
long that the client timed out.
I finally called:
ALTER DATABASE xxx
MODIFY FILE
(NAME = 'xxx_data',
SIZE = 3000MB)
which took a minute and a half, and then the insert statement began working
quickly again.
I have a couple of half-formed ideas on how to deal with this over the long
term.
My db is now 3GB. If I set "automatically grow file" to 1%, instead of 10%,
does that mean that the automatic growing next time will be quicker? Is
the time it takes proportional to the amount of space we are adding?
Maybe I could create a weekly job that looks to see if the space available
on a database is less than 50MB or so, and if so, expands the database size.
I'm not sure how to write this. I can call sp_spaceused, but I don't know
how to stick "unallocated space" into a variable, since sp_spaceused returns
two datasets.
Any other ideas?
Thanks,
GeorgeThere is probably a simpler way to do this, but...
I would follow your weekly idea.
If you run sp_helptext sp_spaceused you will get back the queries used to
run the sp_spaceused command.
I would probably take that code and create a new sproc. In the new sproc,
instead of returning the results, you can check them there and make the
appropriate changes.
HTH
Rick Sawtell
MCT, MCSD, MCDBA|||Sp_helptext is useful. Thanks.
I'm mostly there, but now I am having trouble passing a parameter into the
alter database command.
declare @.newsize varchar(8)
...
ALTER DATABASE xyz MODIFY FILE
(NAME = xyz_data, SIZE = @.newsize)
Line 5: Incorrect syntax near '@.newsize'.
Thanks,
George|||Seems you can't use a variable for the size, quite simply. Try using dynamic SQL to EXEC the
statement instead.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"George Wynne" <george@.nssco.com> wrote in message news:O5hyQ6FkEHA.3724@.TK2MSFTNGP11.phx.gbl...
> Sp_helptext is useful. Thanks.
> I'm mostly there, but now I am having trouble passing a parameter into the
> alter database command.
> declare @.newsize varchar(8)
> ...
> ALTER DATABASE xyz MODIFY FILE
> (NAME = xyz_data, SIZE = @.newsize)
> Line 5: Incorrect syntax near '@.newsize'.
> Thanks,
> George
>sql
I had an insert statement that stopped working the other day. I figured out
that it had to do with the fact that "space available" on the database was
very small. I had "automatically grow file" on the db set to 10%, and I had
plenty of disk space. I think that when my client called the insert
statement, the db was trying to automatically grow, but it was taking so
long that the client timed out.
I finally called:
ALTER DATABASE xxx
MODIFY FILE
(NAME = 'xxx_data',
SIZE = 3000MB)
which took a minute and a half, and then the insert statement began working
quickly again.
I have a couple of half-formed ideas on how to deal with this over the long
term.
My db is now 3GB. If I set "automatically grow file" to 1%, instead of 10%,
does that mean that the automatic growing next time will be quicker? Is
the time it takes proportional to the amount of space we are adding?
Maybe I could create a weekly job that looks to see if the space available
on a database is less than 50MB or so, and if so, expands the database size.
I'm not sure how to write this. I can call sp_spaceused, but I don't know
how to stick "unallocated space" into a variable, since sp_spaceused returns
two datasets.
Any other ideas?
Thanks,
GeorgeThere is probably a simpler way to do this, but...
I would follow your weekly idea.
If you run sp_helptext sp_spaceused you will get back the queries used to
run the sp_spaceused command.
I would probably take that code and create a new sproc. In the new sproc,
instead of returning the results, you can check them there and make the
appropriate changes.
HTH
Rick Sawtell
MCT, MCSD, MCDBA|||Sp_helptext is useful. Thanks.
I'm mostly there, but now I am having trouble passing a parameter into the
alter database command.
declare @.newsize varchar(8)
...
ALTER DATABASE xyz MODIFY FILE
(NAME = xyz_data, SIZE = @.newsize)
Line 5: Incorrect syntax near '@.newsize'.
Thanks,
George|||Seems you can't use a variable for the size, quite simply. Try using dynamic SQL to EXEC the
statement instead.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"George Wynne" <george@.nssco.com> wrote in message news:O5hyQ6FkEHA.3724@.TK2MSFTNGP11.phx.gbl...
> Sp_helptext is useful. Thanks.
> I'm mostly there, but now I am having trouble passing a parameter into the
> alter database command.
> declare @.newsize varchar(8)
> ...
> ALTER DATABASE xyz MODIFY FILE
> (NAME = xyz_data, SIZE = @.newsize)
> Line 5: Incorrect syntax near '@.newsize'.
> Thanks,
> George
>sql
automatically grow file
SQL Server 7.00, Service Pack 6
I had an insert statement that stopped working the other day. I figured out
that it had to do with the fact that "space available" on the database was
very small. I had "automatically grow file" on the db set to 10%, and I had
plenty of disk space. I think that when my client called the insert
statement, the db was trying to automatically grow, but it was taking so
long that the client timed out.
I finally called:
ALTER DATABASE xxx
MODIFY FILE
(NAME = 'xxx_data',
SIZE = 3000MB)
which took a minute and a half, and then the insert statement began working
quickly again.
I have a couple of half-formed ideas on how to deal with this over the long
term.
My db is now 3GB. If I set "automatically grow file" to 1%, instead of 10%,
does that mean that the automatic growing next time will be quicker? Is
the time it takes proportional to the amount of space we are adding?
Maybe I could create a weekly job that looks to see if the space available
on a database is less than 50MB or so, and if so, expands the database size.
I'm not sure how to write this. I can call sp_spaceused, but I don't know
how to stick "unallocated space" into a variable, since sp_spaceused returns
two datasets.
Any other ideas?
Thanks,
George
There is probably a simpler way to do this, but...
I would follow your weekly idea.
If you run sp_helptext sp_spaceused you will get back the queries used to
run the sp_spaceused command.
I would probably take that code and create a new sproc. In the new sproc,
instead of returning the results, you can check them there and make the
appropriate changes.
HTH
Rick Sawtell
MCT, MCSD, MCDBA
|||Sp_helptext is useful. Thanks.
I'm mostly there, but now I am having trouble passing a parameter into the
alter database command.
declare @.newsize varchar(8)
...
ALTER DATABASE xyz MODIFY FILE
(NAME = xyz_data, SIZE = @.newsize)
Line 5: Incorrect syntax near '@.newsize'.
Thanks,
George
|||Seems you can't use a variable for the size, quite simply. Try using dynamic SQL to EXEC the
statement instead.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"George Wynne" <george@.nssco.com> wrote in message news:O5hyQ6FkEHA.3724@.TK2MSFTNGP11.phx.gbl...
> Sp_helptext is useful. Thanks.
> I'm mostly there, but now I am having trouble passing a parameter into the
> alter database command.
> declare @.newsize varchar(8)
> ...
> ALTER DATABASE xyz MODIFY FILE
> (NAME = xyz_data, SIZE = @.newsize)
> Line 5: Incorrect syntax near '@.newsize'.
> Thanks,
> George
>
I had an insert statement that stopped working the other day. I figured out
that it had to do with the fact that "space available" on the database was
very small. I had "automatically grow file" on the db set to 10%, and I had
plenty of disk space. I think that when my client called the insert
statement, the db was trying to automatically grow, but it was taking so
long that the client timed out.
I finally called:
ALTER DATABASE xxx
MODIFY FILE
(NAME = 'xxx_data',
SIZE = 3000MB)
which took a minute and a half, and then the insert statement began working
quickly again.
I have a couple of half-formed ideas on how to deal with this over the long
term.
My db is now 3GB. If I set "automatically grow file" to 1%, instead of 10%,
does that mean that the automatic growing next time will be quicker? Is
the time it takes proportional to the amount of space we are adding?
Maybe I could create a weekly job that looks to see if the space available
on a database is less than 50MB or so, and if so, expands the database size.
I'm not sure how to write this. I can call sp_spaceused, but I don't know
how to stick "unallocated space" into a variable, since sp_spaceused returns
two datasets.
Any other ideas?
Thanks,
George
There is probably a simpler way to do this, but...
I would follow your weekly idea.
If you run sp_helptext sp_spaceused you will get back the queries used to
run the sp_spaceused command.
I would probably take that code and create a new sproc. In the new sproc,
instead of returning the results, you can check them there and make the
appropriate changes.
HTH
Rick Sawtell
MCT, MCSD, MCDBA
|||Sp_helptext is useful. Thanks.
I'm mostly there, but now I am having trouble passing a parameter into the
alter database command.
declare @.newsize varchar(8)
...
ALTER DATABASE xyz MODIFY FILE
(NAME = xyz_data, SIZE = @.newsize)
Line 5: Incorrect syntax near '@.newsize'.
Thanks,
George
|||Seems you can't use a variable for the size, quite simply. Try using dynamic SQL to EXEC the
statement instead.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"George Wynne" <george@.nssco.com> wrote in message news:O5hyQ6FkEHA.3724@.TK2MSFTNGP11.phx.gbl...
> Sp_helptext is useful. Thanks.
> I'm mostly there, but now I am having trouble passing a parameter into the
> alter database command.
> declare @.newsize varchar(8)
> ...
> ALTER DATABASE xyz MODIFY FILE
> (NAME = xyz_data, SIZE = @.newsize)
> Line 5: Incorrect syntax near '@.newsize'.
> Thanks,
> George
>
automatically grow file
SQL Server 7.00, Service Pack 6
I had an insert statement that stopped working the other day. I figured out
that it had to do with the fact that "space available" on the database was
very small. I had "automatically grow file" on the db set to 10%, and I had
plenty of disk space. I think that when my client called the insert
statement, the db was trying to automatically grow, but it was taking so
long that the client timed out.
I finally called:
ALTER DATABASE xxx
MODIFY FILE
(NAME = 'xxx_data',
SIZE = 3000MB)
which took a minute and a half, and then the insert statement began working
quickly again.
I have a couple of half-formed ideas on how to deal with this over the long
term.
My db is now 3GB. If I set "automatically grow file" to 1%, instead of 10%,
does that mean that the automatic growing next time will be quicker? Is
the time it takes proportional to the amount of space we are adding?
Maybe I could create a weekly job that looks to see if the space available
on a database is less than 50MB or so, and if so, expands the database size.
I'm not sure how to write this. I can call sp_spaceused, but I don't know
how to stick "unallocated space" into a variable, since sp_spaceused returns
two datasets.
Any other ideas?
Thanks,
GeorgeThere is probably a simpler way to do this, but...
I would follow your weekly idea.
If you run sp_helptext sp_spaceused you will get back the queries used to
run the sp_spaceused command.
I would probably take that code and create a new sproc. In the new sproc,
instead of returning the results, you can check them there and make the
appropriate changes.
HTH
Rick Sawtell
MCT, MCSD, MCDBA|||Sp_helptext is useful. Thanks.
I'm mostly there, but now I am having trouble passing a parameter into the
alter database command.
declare @.newsize varchar(8)
...
ALTER DATABASE xyz MODIFY FILE
(NAME = xyz_data, SIZE = @.newsize)
Line 5: Incorrect syntax near '@.newsize'.
Thanks,
George|||Seems you can't use a variable for the size, quite simply. Try using dynamic
SQL to EXEC the
statement instead.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"George Wynne" <george@.nssco.com> wrote in message news:O5hyQ6FkEHA.3724@.TK2MSFTNGP11.phx.gb
l...
> Sp_helptext is useful. Thanks.
> I'm mostly there, but now I am having trouble passing a parameter into the
> alter database command.
> declare @.newsize varchar(8)
> ...
> ALTER DATABASE xyz MODIFY FILE
> (NAME = xyz_data, SIZE = @.newsize)
> Line 5: Incorrect syntax near '@.newsize'.
> Thanks,
> George
>
I had an insert statement that stopped working the other day. I figured out
that it had to do with the fact that "space available" on the database was
very small. I had "automatically grow file" on the db set to 10%, and I had
plenty of disk space. I think that when my client called the insert
statement, the db was trying to automatically grow, but it was taking so
long that the client timed out.
I finally called:
ALTER DATABASE xxx
MODIFY FILE
(NAME = 'xxx_data',
SIZE = 3000MB)
which took a minute and a half, and then the insert statement began working
quickly again.
I have a couple of half-formed ideas on how to deal with this over the long
term.
My db is now 3GB. If I set "automatically grow file" to 1%, instead of 10%,
does that mean that the automatic growing next time will be quicker? Is
the time it takes proportional to the amount of space we are adding?
Maybe I could create a weekly job that looks to see if the space available
on a database is less than 50MB or so, and if so, expands the database size.
I'm not sure how to write this. I can call sp_spaceused, but I don't know
how to stick "unallocated space" into a variable, since sp_spaceused returns
two datasets.
Any other ideas?
Thanks,
GeorgeThere is probably a simpler way to do this, but...
I would follow your weekly idea.
If you run sp_helptext sp_spaceused you will get back the queries used to
run the sp_spaceused command.
I would probably take that code and create a new sproc. In the new sproc,
instead of returning the results, you can check them there and make the
appropriate changes.
HTH
Rick Sawtell
MCT, MCSD, MCDBA|||Sp_helptext is useful. Thanks.
I'm mostly there, but now I am having trouble passing a parameter into the
alter database command.
declare @.newsize varchar(8)
...
ALTER DATABASE xyz MODIFY FILE
(NAME = xyz_data, SIZE = @.newsize)
Line 5: Incorrect syntax near '@.newsize'.
Thanks,
George|||Seems you can't use a variable for the size, quite simply. Try using dynamic
SQL to EXEC the
statement instead.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"George Wynne" <george@.nssco.com> wrote in message news:O5hyQ6FkEHA.3724@.TK2MSFTNGP11.phx.gb
l...
> Sp_helptext is useful. Thanks.
> I'm mostly there, but now I am having trouble passing a parameter into the
> alter database command.
> declare @.newsize varchar(8)
> ...
> ALTER DATABASE xyz MODIFY FILE
> (NAME = xyz_data, SIZE = @.newsize)
> Line 5: Incorrect syntax near '@.newsize'.
> Thanks,
> George
>
Automatically enable broker after restore or attach
Is there a way to automatically enable Service Broker on SQL Server 2005 Express Edition after a database has been attached or restored?
My initial idea was to check if broker is disabled whenever the client application starts and enable it from there but the problem with this is that the current user might not have the required permissions. So now I'm looking for another way to enable it right after restore/attach if at all possible. Any ideas would be appreciated.
My initial idea was to check if broker is disabled whenever the client application starts and enable it from there but the problem with this is that the current user might not have the required permissions. So now I'm looking for another way to enable it right after restore/attach if at all possible. Any ideas would be appreciated.
Simplest approach would be to enabled it via a procedure that does have the appropiate permissions (using EXECUTE AS and code signing).
Other possiblity would be to create a server event notification and use an activate procedure in msdb to enable the database:
Code Snippet
use msdb;
go
create queue [dbevents]
create service [dbevents] on queue [dbevents] ([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]);
create event notification [dbbackuprestore]
on server for AUDIT_BACKUP_RESTORE_EVENT
to service 'dbevents', 'current database';
Thursday, March 22, 2012
Automatically add permissions on items for users?
Can someone give me a couple of pointers on how to interact with the web
service (?) to automatically add policies for a user to view folders and
reports rather than adding them manually through Report Manager?
thanksThis is a multi-part message in MIME format.
--=_NextPart_000_00C1_01C4BA63.9F970620
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: 7bit
Rather than authorizing each user in Reporting Services it is
recommended that you create a Windows Group (e.g. Reporting Users),
associate the Group with a Reporting Services Role (e.g. Browser), and
then when you create a new Windows User you make the user a member of
the [Reporting Users] Group.
Garry
--=_NextPart_000_00C1_01C4BA63.9F970620
Content-Type: text/html;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
&
Re: Automatically add permissions on items for users?
Rather than authorizing each user in Reporting =Services it is recommended that you create a Windows Group (e.g. =Reporting Users), associate the Group with a Reporting Services Role =(e.g. Browser), and then when you create a new Windows User you make the =user a member of the [Reporting Users] Group.
Garry
--=_NextPart_000_00C1_01C4BA63.9F970620--|||the reason that I need to add each user is that I am using forms
authentication.
how can I call the web service to add policies for each user?
"Garry Lenz" wrote:
> Rather than authorizing each user in Reporting Services it is
> recommended that you create a Windows Group (e.g. Reporting Users),
> associate the Group with a Reporting Services Role (e.g. Browser), and
> then when you create a new Windows User you make the user a member of
> the [Reporting Users] Group.
> Garry
>
service (?) to automatically add policies for a user to view folders and
reports rather than adding them manually through Report Manager?
thanksThis is a multi-part message in MIME format.
--=_NextPart_000_00C1_01C4BA63.9F970620
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: 7bit
Rather than authorizing each user in Reporting Services it is
recommended that you create a Windows Group (e.g. Reporting Users),
associate the Group with a Reporting Services Role (e.g. Browser), and
then when you create a new Windows User you make the user a member of
the [Reporting Users] Group.
Garry
--=_NextPart_000_00C1_01C4BA63.9F970620
Content-Type: text/html;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
&
Re: Automatically add permissions on items for users?
Rather than authorizing each user in Reporting =Services it is recommended that you create a Windows Group (e.g. =Reporting Users), associate the Group with a Reporting Services Role =(e.g. Browser), and then when you create a new Windows User you make the =user a member of the [Reporting Users] Group.
Garry
--=_NextPart_000_00C1_01C4BA63.9F970620--|||the reason that I need to add each user is that I am using forms
authentication.
how can I call the web service to add policies for each user?
"Garry Lenz" wrote:
> Rather than authorizing each user in Reporting Services it is
> recommended that you create a Windows Group (e.g. Reporting Users),
> associate the Group with a Reporting Services Role (e.g. Browser), and
> then when you create a new Windows User you make the user a member of
> the [Reporting Users] Group.
> Garry
>
Tuesday, March 20, 2012
Automatic Refresh Data
Is there any way in Reporting Service so that It will automatic
refresh data in the report with specified time interval ?
ThanksOn Mar 30, 11:35 pm, sqlpr...@.gmail.com wrote:
> Is there any way in Reporting Service so that It will automatic
> refresh data in the report with specified time interval ?
> Thanks
Yes, in the Reports Project, select the 'Layout' tab. Then select the
'Report' tab at the top and then 'Report Properties...' On the
'General' tab, select 'Auto Refresh' near the bottom and select the
number of seconds between refreshes.
Regards,
Enrique Martinez
Sr. Software Developer|||On Apr 1, 7:46 am, "EMartinez" <emartinez...@.gmail.com> wrote:
> On Mar 30, 11:35 pm, sqlpr...@.gmail.com wrote:
> > Is there any way in Reporting Service so that It will automatic
> > refresh data in the report with specified time interval ?
> > Thanks
> Yes, in the Reports Project, select the 'Layout' tab. Then select the
> 'Report' tab at the top and then 'Report Properties...' On the
> 'General' tab, select 'Auto Refresh' near the bottom and select the
> number of seconds between refreshes.
> Regards,
> Enrique Martinez
> Sr. Software Developer
Thanks
it has done my work.|||On Apr 1, 12:47 am, sqlpr...@.gmail.com wrote:
> On Apr 1, 7:46 am, "EMartinez" <emartinez...@.gmail.com> wrote:
>
> > On Mar 30, 11:35 pm, sqlpr...@.gmail.com wrote:
> > > Is there any way in Reporting Service so that It will automatic
> > > refresh data in the report with specified time interval ?
> > > Thanks
> > Yes, in the Reports Project, select the 'Layout' tab. Then select the
> > 'Report' tab at the top and then 'Report Properties...' On the
> > 'General' tab, select 'Auto Refresh' near the bottom and select the
> > number of seconds between refreshes.
> > Regards,
> > Enrique Martinez
> > Sr. Software Developer
> Thanks
> it has done my work.
You're welcome. Glad I could be of assistance.
Regards,
Enrique Martinez
Sr. Software Developersql
refresh data in the report with specified time interval ?
ThanksOn Mar 30, 11:35 pm, sqlpr...@.gmail.com wrote:
> Is there any way in Reporting Service so that It will automatic
> refresh data in the report with specified time interval ?
> Thanks
Yes, in the Reports Project, select the 'Layout' tab. Then select the
'Report' tab at the top and then 'Report Properties...' On the
'General' tab, select 'Auto Refresh' near the bottom and select the
number of seconds between refreshes.
Regards,
Enrique Martinez
Sr. Software Developer|||On Apr 1, 7:46 am, "EMartinez" <emartinez...@.gmail.com> wrote:
> On Mar 30, 11:35 pm, sqlpr...@.gmail.com wrote:
> > Is there any way in Reporting Service so that It will automatic
> > refresh data in the report with specified time interval ?
> > Thanks
> Yes, in the Reports Project, select the 'Layout' tab. Then select the
> 'Report' tab at the top and then 'Report Properties...' On the
> 'General' tab, select 'Auto Refresh' near the bottom and select the
> number of seconds between refreshes.
> Regards,
> Enrique Martinez
> Sr. Software Developer
Thanks
it has done my work.|||On Apr 1, 12:47 am, sqlpr...@.gmail.com wrote:
> On Apr 1, 7:46 am, "EMartinez" <emartinez...@.gmail.com> wrote:
>
> > On Mar 30, 11:35 pm, sqlpr...@.gmail.com wrote:
> > > Is there any way in Reporting Service so that It will automatic
> > > refresh data in the report with specified time interval ?
> > > Thanks
> > Yes, in the Reports Project, select the 'Layout' tab. Then select the
> > 'Report' tab at the top and then 'Report Properties...' On the
> > 'General' tab, select 'Auto Refresh' near the bottom and select the
> > number of seconds between refreshes.
> > Regards,
> > Enrique Martinez
> > Sr. Software Developer
> Thanks
> it has done my work.
You're welcome. Glad I could be of assistance.
Regards,
Enrique Martinez
Sr. Software Developersql
Thursday, March 8, 2012
Automated backups in SQL Express?
I've realize that the SQL Agent service is no longer available now that i've upgraded from MSDE 2000 to SQL Server Express. 
I would like to find out the easiest way I could go about creating an automated backup solution for SQL Server Express. Previously I created Jobs in the SQL Agent Service using SQL DMO which is not longer possible.
Has anyone any suggestions that would be easy to setup or any samples that I could use? Maybe using SMO or some other method.
Thanks.You could use the Windows Scheduled Tasks to execute a backup script using the SQLCMD program with a T-SQL script.
|||Automating Database maintenance in SQL 2005 Express Edition Part I
Automating Database maintenance in SQL 2005 Express Edition Part II
I should say that it does seem sqlmaint is included with SQLExpress so you could equally use that. Part I deals with creating scheduled tasks and using SQLCMD. Part II use SMO.
|||
I suppose we have to build all of the error handling, alerting, logging now that MS has stripped the SQL Agent Scheduler from Express?
What will probably happen is nothing, leading to a general market trend of disliking any small application using mssql 2005 express since the agent features no longer exist and nobody wants to pay to create something like it, (without notifications, alerts, system event logging, etc., distrust and fear occur).
Automated backups in SQL Express?
I've realize that the SQL Agent service is no longer available now that i've upgraded from MSDE 2000 to SQL Server Express.
I would like to find out the easiest way I could go about creating an automated backup solution for SQL Server Express. Previously I created Jobs in the SQL Agent Service using SQL DMO which is not longer possible.
Has anyone any suggestions that would be easy to setup or any samples that I could use? Maybe using SMO or some other method.
Thanks.You could use the Windows Scheduled Tasks to execute a backup script using the SQLCMD program with a T-SQL script.
|||Automating Database maintenance in SQL 2005 Express Edition Part I
Automating Database maintenance in SQL 2005 Express Edition Part II
I should say that it does seem sqlmaint is included with SQLExpress so you could equally use that. Part I deals with creating scheduled tasks and using SQLCMD. Part II use SMO.
|||
I suppose we have to build all of the error handling, alerting, logging now that MS has stripped the SQL Agent Scheduler from Express?
What will probably happen is nothing, leading to a general market trend of disliking any small application using mssql 2005 express since the agent features no longer exist and nobody wants to pay to create something like it, (without notifications, alerts, system event logging, etc., distrust and fear occur).
Wednesday, March 7, 2012
Automate to get version number for a list of SQL Instances
We have a bunch of SQL Server instances in our domain. Some of them
are in SQL 2000 and some are in 2005. I need to be able to find out
what service pack, edition and version each of the instances are
running on, what will be the best way to go about it. I would prefer
not to do it manually, i have a list of instances all saved in a
table. I would like to loop through this table connect to each
instance and get the result I want and save it in the same table.
My challenge is none of the servers are linked and I am not able to
get openrowset to work with trusted connection, I am able to get what
I need using SQL user but that requires me to add SQL login to each of
the servers before I can go about my script. I am sure other DBA's
have gone through this, can someone please suggest or give ideas.
Any help in this reagrd will be greatly appreciated.
Thanks"shub" <shubtech@.gmail.com> wrote in message
news:1194190723.073489.207550@.z9g2000hsf.googlegroups.com...
> We have a bunch of SQL Server instances in our domain. Some of them
> are in SQL 2000 and some are in 2005. I need to be able to find out
> what service pack, edition and version each of the instances are
> running on, what will be the best way to go about it. I would prefer
> not to do it manually, i have a list of instances all saved in a
> table. I would like to loop through this table connect to each
> instance and get the result I want and save it in the same table.
> My challenge is none of the servers are linked and I am not able to
> get openrowset to work with trusted connection, I am able to get what
> I need using SQL user but that requires me to add SQL login to each of
> the servers before I can go about my script. I am sure other DBA's
> have gone through this, can someone please suggest or give ideas.
> Any help in this reagrd will be greatly appreciated.
> Thanks
>
If you can access the file system on the target servers then you can obtain
the version number via VBScript:
Set objFSO = CreateObject("Scripting.FileSystemObject")
Wscript.Echo objFSO.GetFileVersion("C:\Program Files\Microsoft SQL
Server\MSSQL$SS2K\Binn\sqlservr.exe")
--
David Portas|||Hi
http://dimantdatabasesolutions.blogspot.com/2007/04/whats-version-of-sql-server.html
The below is not reliable script. You will have to go throu each server and
run SERVERPROPRTY to get what you want.
CREATE TABLE #servers(sname VARCHAR(255))
INSERT #servers EXEC master..XP_CMDShell 'OSQL -L'
DELETE #servers WHERE sname='Servers:'
SELECT LTRIM(sname) FROM #servers WHERE sname != 'NULL'
DROP TABLE #servers
"shub" <shubtech@.gmail.com> wrote in message
news:1194190723.073489.207550@.z9g2000hsf.googlegroups.com...
> We have a bunch of SQL Server instances in our domain. Some of them
> are in SQL 2000 and some are in 2005. I need to be able to find out
> what service pack, edition and version each of the instances are
> running on, what will be the best way to go about it. I would prefer
> not to do it manually, i have a list of instances all saved in a
> table. I would like to loop through this table connect to each
> instance and get the result I want and save it in the same table.
> My challenge is none of the servers are linked and I am not able to
> get openrowset to work with trusted connection, I am able to get what
> I need using SQL user but that requires me to add SQL login to each of
> the servers before I can go about my script. I am sure other DBA's
> have gone through this, can someone please suggest or give ideas.
> Any help in this reagrd will be greatly appreciated.
> Thanks
>|||I had to monitor several hundred instances before and I did monitoring each
day by creating linked servers through TSQL right before I executed my
monitoring scripts. After the scripts finished I would drop the link server
so I would not have hundreds of linked servers setting around.
Also try using OSQL to make a connection. You can put build the OSQL
connection string from your table for each server and then paste all the
connection strings into a batch job which points to a script file with your
monitoring code.
"shub" <shubtech@.gmail.com> wrote in message
news:1194190723.073489.207550@.z9g2000hsf.googlegroups.com...
> We have a bunch of SQL Server instances in our domain. Some of them
> are in SQL 2000 and some are in 2005. I need to be able to find out
> what service pack, edition and version each of the instances are
> running on, what will be the best way to go about it. I would prefer
> not to do it manually, i have a list of instances all saved in a
> table. I would like to loop through this table connect to each
> instance and get the result I want and save it in the same table.
> My challenge is none of the servers are linked and I am not able to
> get openrowset to work with trusted connection, I am able to get what
> I need using SQL user but that requires me to add SQL login to each of
> the servers before I can go about my script. I am sure other DBA's
> have gone through this, can someone please suggest or give ideas.
> Any help in this reagrd will be greatly appreciated.
> Thanks
>|||The ideal approach is not to do this in T-SQL, but in a little client app
written in a real programming language. Typically, you would want to collect
a lot more info than just versions and build numbers. Having a little client
app gives you ultimate flexibility in whatever inventory information you may
fancy to collect.
Linchi
"shub" wrote:
> We have a bunch of SQL Server instances in our domain. Some of them
> are in SQL 2000 and some are in 2005. I need to be able to find out
> what service pack, edition and version each of the instances are
> running on, what will be the best way to go about it. I would prefer
> not to do it manually, i have a list of instances all saved in a
> table. I would like to loop through this table connect to each
> instance and get the result I want and save it in the same table.
> My challenge is none of the servers are linked and I am not able to
> get openrowset to work with trusted connection, I am able to get what
> I need using SQL user but that requires me to add SQL login to each of
> the servers before I can go about my script. I am sure other DBA's
> have gone through this, can someone please suggest or give ideas.
> Any help in this reagrd will be greatly appreciated.
> Thanks
>
are in SQL 2000 and some are in 2005. I need to be able to find out
what service pack, edition and version each of the instances are
running on, what will be the best way to go about it. I would prefer
not to do it manually, i have a list of instances all saved in a
table. I would like to loop through this table connect to each
instance and get the result I want and save it in the same table.
My challenge is none of the servers are linked and I am not able to
get openrowset to work with trusted connection, I am able to get what
I need using SQL user but that requires me to add SQL login to each of
the servers before I can go about my script. I am sure other DBA's
have gone through this, can someone please suggest or give ideas.
Any help in this reagrd will be greatly appreciated.
Thanks"shub" <shubtech@.gmail.com> wrote in message
news:1194190723.073489.207550@.z9g2000hsf.googlegroups.com...
> We have a bunch of SQL Server instances in our domain. Some of them
> are in SQL 2000 and some are in 2005. I need to be able to find out
> what service pack, edition and version each of the instances are
> running on, what will be the best way to go about it. I would prefer
> not to do it manually, i have a list of instances all saved in a
> table. I would like to loop through this table connect to each
> instance and get the result I want and save it in the same table.
> My challenge is none of the servers are linked and I am not able to
> get openrowset to work with trusted connection, I am able to get what
> I need using SQL user but that requires me to add SQL login to each of
> the servers before I can go about my script. I am sure other DBA's
> have gone through this, can someone please suggest or give ideas.
> Any help in this reagrd will be greatly appreciated.
> Thanks
>
If you can access the file system on the target servers then you can obtain
the version number via VBScript:
Set objFSO = CreateObject("Scripting.FileSystemObject")
Wscript.Echo objFSO.GetFileVersion("C:\Program Files\Microsoft SQL
Server\MSSQL$SS2K\Binn\sqlservr.exe")
--
David Portas|||Hi
http://dimantdatabasesolutions.blogspot.com/2007/04/whats-version-of-sql-server.html
The below is not reliable script. You will have to go throu each server and
run SERVERPROPRTY to get what you want.
CREATE TABLE #servers(sname VARCHAR(255))
INSERT #servers EXEC master..XP_CMDShell 'OSQL -L'
DELETE #servers WHERE sname='Servers:'
SELECT LTRIM(sname) FROM #servers WHERE sname != 'NULL'
DROP TABLE #servers
"shub" <shubtech@.gmail.com> wrote in message
news:1194190723.073489.207550@.z9g2000hsf.googlegroups.com...
> We have a bunch of SQL Server instances in our domain. Some of them
> are in SQL 2000 and some are in 2005. I need to be able to find out
> what service pack, edition and version each of the instances are
> running on, what will be the best way to go about it. I would prefer
> not to do it manually, i have a list of instances all saved in a
> table. I would like to loop through this table connect to each
> instance and get the result I want and save it in the same table.
> My challenge is none of the servers are linked and I am not able to
> get openrowset to work with trusted connection, I am able to get what
> I need using SQL user but that requires me to add SQL login to each of
> the servers before I can go about my script. I am sure other DBA's
> have gone through this, can someone please suggest or give ideas.
> Any help in this reagrd will be greatly appreciated.
> Thanks
>|||I had to monitor several hundred instances before and I did monitoring each
day by creating linked servers through TSQL right before I executed my
monitoring scripts. After the scripts finished I would drop the link server
so I would not have hundreds of linked servers setting around.
Also try using OSQL to make a connection. You can put build the OSQL
connection string from your table for each server and then paste all the
connection strings into a batch job which points to a script file with your
monitoring code.
"shub" <shubtech@.gmail.com> wrote in message
news:1194190723.073489.207550@.z9g2000hsf.googlegroups.com...
> We have a bunch of SQL Server instances in our domain. Some of them
> are in SQL 2000 and some are in 2005. I need to be able to find out
> what service pack, edition and version each of the instances are
> running on, what will be the best way to go about it. I would prefer
> not to do it manually, i have a list of instances all saved in a
> table. I would like to loop through this table connect to each
> instance and get the result I want and save it in the same table.
> My challenge is none of the servers are linked and I am not able to
> get openrowset to work with trusted connection, I am able to get what
> I need using SQL user but that requires me to add SQL login to each of
> the servers before I can go about my script. I am sure other DBA's
> have gone through this, can someone please suggest or give ideas.
> Any help in this reagrd will be greatly appreciated.
> Thanks
>|||The ideal approach is not to do this in T-SQL, but in a little client app
written in a real programming language. Typically, you would want to collect
a lot more info than just versions and build numbers. Having a little client
app gives you ultimate flexibility in whatever inventory information you may
fancy to collect.
Linchi
"shub" wrote:
> We have a bunch of SQL Server instances in our domain. Some of them
> are in SQL 2000 and some are in 2005. I need to be able to find out
> what service pack, edition and version each of the instances are
> running on, what will be the best way to go about it. I would prefer
> not to do it manually, i have a list of instances all saved in a
> table. I would like to loop through this table connect to each
> instance and get the result I want and save it in the same table.
> My challenge is none of the servers are linked and I am not able to
> get openrowset to work with trusted connection, I am able to get what
> I need using SQL user but that requires me to add SQL login to each of
> the servers before I can go about my script. I am sure other DBA's
> have gone through this, can someone please suggest or give ideas.
> Any help in this reagrd will be greatly appreciated.
> Thanks
>
Thursday, February 16, 2012
Auto starting the sqlserver service
Hello all,
I am by no means a SLQ admin/DBA I am the Layer2/3 network admin who somehow
got thrown into figuring out how to fix an issue on one of our production
servers. We have a machine that is regularly rebooted (this is a whole
'nother story) and at times when it comes back on it does not start the
sqlserver service.
I was wondering if anyone knew how to right a batch file that would
regularly check, say every 15min, to see if the service was running, and if
not to star it.
Any help or even a nudge in the right direction would be ever so appreciated
.go to start-->run-->services.msc-->look for MSSQLSERVER and double
click on that-->change the startup type to Automatic...|||It has been verified to be set at automatic. For some reason the
sqlserveragent does not start at bootup on occasion. If we go into the
services panel and right click "start" the service will come up fine. But
unfortunatly we do not know until the end user complains.
We are currently implementing MOM which may help us monitor when the service
is not running.
"Shadow" wrote:
> go to start-->run-->services.msc-->look for MSSQLSERVER and double
> click on that-->change the startup type to Automatic...
>|||Use this to check the sqlagent status:
exec xp_servicecontrol 'querystate', 'sqlserveragent'
If it returns "stopped" then start the service by:
exec master..xp_servicecontrol N'start', N'sqlserveragent'
"FranklinST_Admin" <FranklinSTAdmin@.discussions.microsoft.com> wrote in
message news:F6293761-0C54-4BB4-B6CD-390A8C27C584@.microsoft.com...
> Hello all,
> I am by no means a SLQ admin/DBA I am the Layer2/3 network admin who
> somehow
> got thrown into figuring out how to fix an issue on one of our production
> servers. We have a machine that is regularly rebooted (this is a whole
> 'nother story) and at times when it comes back on it does not start the
> sqlserver service.
> I was wondering if anyone knew how to right a batch file that would
> regularly check, say every 15min, to see if the service was running, and
> if
> not to star it.
> Any help or even a nudge in the right direction would be ever so
> appreciated.
I am by no means a SLQ admin/DBA I am the Layer2/3 network admin who somehow
got thrown into figuring out how to fix an issue on one of our production
servers. We have a machine that is regularly rebooted (this is a whole
'nother story) and at times when it comes back on it does not start the
sqlserver service.
I was wondering if anyone knew how to right a batch file that would
regularly check, say every 15min, to see if the service was running, and if
not to star it.
Any help or even a nudge in the right direction would be ever so appreciated
.go to start-->run-->services.msc-->look for MSSQLSERVER and double
click on that-->change the startup type to Automatic...|||It has been verified to be set at automatic. For some reason the
sqlserveragent does not start at bootup on occasion. If we go into the
services panel and right click "start" the service will come up fine. But
unfortunatly we do not know until the end user complains.
We are currently implementing MOM which may help us monitor when the service
is not running.
"Shadow" wrote:
> go to start-->run-->services.msc-->look for MSSQLSERVER and double
> click on that-->change the startup type to Automatic...
>|||Use this to check the sqlagent status:
exec xp_servicecontrol 'querystate', 'sqlserveragent'
If it returns "stopped" then start the service by:
exec master..xp_servicecontrol N'start', N'sqlserveragent'
"FranklinST_Admin" <FranklinSTAdmin@.discussions.microsoft.com> wrote in
message news:F6293761-0C54-4BB4-B6CD-390A8C27C584@.microsoft.com...
> Hello all,
> I am by no means a SLQ admin/DBA I am the Layer2/3 network admin who
> somehow
> got thrown into figuring out how to fix an issue on one of our production
> servers. We have a machine that is regularly rebooted (this is a whole
> 'nother story) and at times when it comes back on it does not start the
> sqlserver service.
> I was wondering if anyone knew how to right a batch file that would
> regularly check, say every 15min, to see if the service was running, and
> if
> not to star it.
> Any help or even a nudge in the right direction would be ever so
> appreciated.
Auto starting the sqlserver service
Hello all,
I am by no means a SLQ admin/DBA I am the Layer2/3 network admin who somehow
got thrown into figuring out how to fix an issue on one of our production
servers. We have a machine that is regularly rebooted (this is a whole
'nother story) and at times when it comes back on it does not start the
sqlserver service.
I was wondering if anyone knew how to right a batch file that would
regularly check, say every 15min, to see if the service was running, and if
not to star it.
Any help or even a nudge in the right direction would be ever so appreciated.
go to start-->run-->services.msc-->look for MSSQLSERVER and double
click on that-->change the startup type to Automatic...
|||It has been verified to be set at automatic. For some reason the
sqlserveragent does not start at bootup on occasion. If we go into the
services panel and right click "start" the service will come up fine. But
unfortunatly we do not know until the end user complains.
We are currently implementing MOM which may help us monitor when the service
is not running.
"Shadow" wrote:
> go to start-->run-->services.msc-->look for MSSQLSERVER and double
> click on that-->change the startup type to Automatic...
>
|||Use this to check the sqlagent status:
exec xp_servicecontrol 'querystate', 'sqlserveragent'
If it returns "stopped" then start the service by:
exec master..xp_servicecontrol N'start', N'sqlserveragent'
"FranklinST_Admin" <FranklinSTAdmin@.discussions.microsoft.com> wrote in
message news:F6293761-0C54-4BB4-B6CD-390A8C27C584@.microsoft.com...
> Hello all,
> I am by no means a SLQ admin/DBA I am the Layer2/3 network admin who
> somehow
> got thrown into figuring out how to fix an issue on one of our production
> servers. We have a machine that is regularly rebooted (this is a whole
> 'nother story) and at times when it comes back on it does not start the
> sqlserver service.
> I was wondering if anyone knew how to right a batch file that would
> regularly check, say every 15min, to see if the service was running, and
> if
> not to star it.
> Any help or even a nudge in the right direction would be ever so
> appreciated.
I am by no means a SLQ admin/DBA I am the Layer2/3 network admin who somehow
got thrown into figuring out how to fix an issue on one of our production
servers. We have a machine that is regularly rebooted (this is a whole
'nother story) and at times when it comes back on it does not start the
sqlserver service.
I was wondering if anyone knew how to right a batch file that would
regularly check, say every 15min, to see if the service was running, and if
not to star it.
Any help or even a nudge in the right direction would be ever so appreciated.
go to start-->run-->services.msc-->look for MSSQLSERVER and double
click on that-->change the startup type to Automatic...
|||It has been verified to be set at automatic. For some reason the
sqlserveragent does not start at bootup on occasion. If we go into the
services panel and right click "start" the service will come up fine. But
unfortunatly we do not know until the end user complains.
We are currently implementing MOM which may help us monitor when the service
is not running.
"Shadow" wrote:
> go to start-->run-->services.msc-->look for MSSQLSERVER and double
> click on that-->change the startup type to Automatic...
>
|||Use this to check the sqlagent status:
exec xp_servicecontrol 'querystate', 'sqlserveragent'
If it returns "stopped" then start the service by:
exec master..xp_servicecontrol N'start', N'sqlserveragent'
"FranklinST_Admin" <FranklinSTAdmin@.discussions.microsoft.com> wrote in
message news:F6293761-0C54-4BB4-B6CD-390A8C27C584@.microsoft.com...
> Hello all,
> I am by no means a SLQ admin/DBA I am the Layer2/3 network admin who
> somehow
> got thrown into figuring out how to fix an issue on one of our production
> servers. We have a machine that is regularly rebooted (this is a whole
> 'nother story) and at times when it comes back on it does not start the
> sqlserver service.
> I was wondering if anyone knew how to right a batch file that would
> regularly check, say every 15min, to see if the service was running, and
> if
> not to star it.
> Any help or even a nudge in the right direction would be ever so
> appreciated.
Auto starting the sqlserver service
Hello all,
I am by no means a SLQ admin/DBA I am the Layer2/3 network admin who somehow
got thrown into figuring out how to fix an issue on one of our production
servers. We have a machine that is regularly rebooted (this is a whole
'nother story) and at times when it comes back on it does not start the
sqlserver service.
I was wondering if anyone knew how to right a batch file that would
regularly check, say every 15min, to see if the service was running, and if
not to star it.
Any help or even a nudge in the right direction would be ever so appreciated.go to start-->run-->services.msc-->look for MSSQLSERVER and double
click on that-->change the startup type to Automatic...|||It has been verified to be set at automatic. For some reason the
sqlserveragent does not start at bootup on occasion. If we go into the
services panel and right click "start" the service will come up fine. But
unfortunatly we do not know until the end user complains.
We are currently implementing MOM which may help us monitor when the service
is not running.
"Shadow" wrote:
> go to start-->run-->services.msc-->look for MSSQLSERVER and double
> click on that-->change the startup type to Automatic...
>|||Use this to check the sqlagent status:
exec xp_servicecontrol 'querystate', 'sqlserveragent'
If it returns "stopped" then start the service by:
exec master..xp_servicecontrol N'start', N'sqlserveragent'
"FranklinST_Admin" <FranklinSTAdmin@.discussions.microsoft.com> wrote in
message news:F6293761-0C54-4BB4-B6CD-390A8C27C584@.microsoft.com...
> Hello all,
> I am by no means a SLQ admin/DBA I am the Layer2/3 network admin who
> somehow
> got thrown into figuring out how to fix an issue on one of our production
> servers. We have a machine that is regularly rebooted (this is a whole
> 'nother story) and at times when it comes back on it does not start the
> sqlserver service.
> I was wondering if anyone knew how to right a batch file that would
> regularly check, say every 15min, to see if the service was running, and
> if
> not to star it.
> Any help or even a nudge in the right direction would be ever so
> appreciated.
I am by no means a SLQ admin/DBA I am the Layer2/3 network admin who somehow
got thrown into figuring out how to fix an issue on one of our production
servers. We have a machine that is regularly rebooted (this is a whole
'nother story) and at times when it comes back on it does not start the
sqlserver service.
I was wondering if anyone knew how to right a batch file that would
regularly check, say every 15min, to see if the service was running, and if
not to star it.
Any help or even a nudge in the right direction would be ever so appreciated.go to start-->run-->services.msc-->look for MSSQLSERVER and double
click on that-->change the startup type to Automatic...|||It has been verified to be set at automatic. For some reason the
sqlserveragent does not start at bootup on occasion. If we go into the
services panel and right click "start" the service will come up fine. But
unfortunatly we do not know until the end user complains.
We are currently implementing MOM which may help us monitor when the service
is not running.
"Shadow" wrote:
> go to start-->run-->services.msc-->look for MSSQLSERVER and double
> click on that-->change the startup type to Automatic...
>|||Use this to check the sqlagent status:
exec xp_servicecontrol 'querystate', 'sqlserveragent'
If it returns "stopped" then start the service by:
exec master..xp_servicecontrol N'start', N'sqlserveragent'
"FranklinST_Admin" <FranklinSTAdmin@.discussions.microsoft.com> wrote in
message news:F6293761-0C54-4BB4-B6CD-390A8C27C584@.microsoft.com...
> Hello all,
> I am by no means a SLQ admin/DBA I am the Layer2/3 network admin who
> somehow
> got thrown into figuring out how to fix an issue on one of our production
> servers. We have a machine that is regularly rebooted (this is a whole
> 'nother story) and at times when it comes back on it does not start the
> sqlserver service.
> I was wondering if anyone knew how to right a batch file that would
> regularly check, say every 15min, to see if the service was running, and
> if
> not to star it.
> Any help or even a nudge in the right direction would be ever so
> appreciated.
Auto Shutdown Service Manager
Plz Help me.
Auto shutting down of my sql service manager. what i will start it,
but the server is shutdown after 30 seconds. that is why i can't used any
services. Please to help me, what about to start of my sql service manager
and matained of sql server 2000 (enterprice)? Please answer and good thing.
Plz faster and faster moment.
Thank of all and member.
Saw Oo(Myanmar)
Hi,
For Stopping and starting the SQL Server Service please the Control Panel --
Services option.
Thanks
Hari
SQL Server MVP
"SawOo.ygn" <SawOo.ygn@.discussions.microsoft.com> wrote in message
news:AD9546B7-87BB-4386-BB61-F064D200410A@.microsoft.com...
> Plz Help me.
> Auto shutting down of my sql service manager. what i will start it,
> but the server is shutdown after 30 seconds. that is why i can't used any
> services. Please to help me, what about to start of my sql service manager
> and matained of sql server 2000 (enterprice)? Please answer and good
> thing.
> Plz faster and faster moment.
> Thank of all and member.
> Saw Oo(Myanmar)
>
Auto shutting down of my sql service manager. what i will start it,
but the server is shutdown after 30 seconds. that is why i can't used any
services. Please to help me, what about to start of my sql service manager
and matained of sql server 2000 (enterprice)? Please answer and good thing.
Plz faster and faster moment.
Thank of all and member.
Saw Oo(Myanmar)
Hi,
For Stopping and starting the SQL Server Service please the Control Panel --
Services option.
Thanks
Hari
SQL Server MVP
"SawOo.ygn" <SawOo.ygn@.discussions.microsoft.com> wrote in message
news:AD9546B7-87BB-4386-BB61-F064D200410A@.microsoft.com...
> Plz Help me.
> Auto shutting down of my sql service manager. what i will start it,
> but the server is shutdown after 30 seconds. that is why i can't used any
> services. Please to help me, what about to start of my sql service manager
> and matained of sql server 2000 (enterprice)? Please answer and good
> thing.
> Plz faster and faster moment.
> Thank of all and member.
> Saw Oo(Myanmar)
>
Monday, February 13, 2012
Auto reminder emails?
I don't know much about broker service so I have question. We have a content management system the we developed locally, and what we have are catagories and subcatagries. When people choose a topic we send them an email about the topic they are interested in. We are now selling webinars and white papers and articles on demand. We would like to be able to send a reminder email to anyone who signed up for a webinar or special event. Is this possible with broker service?While you could use SSB to build your fan-out and scheduling for the mails, it will not be capable of actually delivering the mails to the users, since it can only send messages between two SQL Server instances. The Database mail functionality in SQL can achieve that though (see http://msdn2.microsoft.com/en-us/library/ms175887.aspx)|||So I can use service broker to send the email to the dbmail engine and than to the customers right?|||Yes, but db mail engine already does that (sp_senddbmail uses Service Broker to communicate with the mailing engine)
Friday, February 10, 2012
Auto Import into sql from a Tab Delmited flat file
i was wondering if there was a way to auto import my file everyday and then process the deminsions and cube i have made in the analysis service program
I suggest looking at the integration services feature in SQL 2005 and DTS in SQL 2000.You could also use BCP or Bulk Insert.
All of these are covered in books online.
Subscribe to:
Posts (Atom)