Hello Experts,
I am a New Bee in SQL.I would like to know,As a System Admin I would like to automate the task of keeping the SQL Server up to date with latest Service Packs and Patches.
Is there any procedure / Software where I can automatically download the patch / sp's from the site and install on the servers or maybe schedule the task for every month.
I would appreciate a reply from the experts
Best Regards
SunilVKulkarni
I'm a newbie to SQL Server, trying to make the leap from Access because I'm starting out grow it for my needs. I have a lot of automation stuff in Access where it does a query/code driven process for us via a macro. Sometimes the output is a report, or a form with assembled data that we use. I'm wondering if SQL Server 2005 is capable of doing automation of processes like these as well. Even if it's not necessarily a macro, something I can program, compile and have use off of my SQL Server database to do the tasks that the Access macro did.
View 1 Replies View Related
Hi all,
I have a task which needs to be automated.
1) Excel spreadsheet load into sql server database as a table.
2) Then , there will be some quering of data joining this table ( imported from excel) with other tables.
3) Then, output should be back into excel spreadsheet.
All these steps 1,2,3 need to be automated.
Could anybody give me the different steps(if not,, any links which guide me doing so), for doing this and any pre setup required for this.
Thanks for all your help..
Overview of Automation with SQL server.
View 2 Replies View RelatedH...
I have a problem to run a package with a scrip task containing the code in this page. I try to create a new excel workbook, change some cells, and save it to the harddrive. When designing the package in BI dev studio, I can execute it without a problem. I can even run it with DTexec.exe. The problem is that when I run this package from the SQL server agent, then it breaks. It breaks on a machine running Windows server 2008 with SQL server 2008 on it and it also breaks on a machine running Vista with sql server 2005, but it runs fine on a windows server 2003 machine with sql server 2005 on it.
I can also mention that if I dim out the one line after the comment in the code, then It runs fine on any one of my machines. The problem seems to be with saving or opening an excel file. I am also making use of a proxy with a credential using my login name. I am part of the administrators on these machines.
Imports eXcel = Microsoft.Office.Interop.Excel
Public Sub Main()
Dim objExcel As New eXcel.Application
Dim objSheet As eXcel.Worksheet
Dim strFname As String = "c: empBook3.xls"
objExcel.DisplayAlerts = False
objExcel.Application.Interactive = False
objExcel.Workbooks.Add()
objExcel.Cells(1, 1) = "0"
'I dim out the next line and it works from sql server agent on any machine
objExcel.ActiveWorkbook.SaveAs(strFname)
objExcel.DisplayAlerts = True
objExcel.Application.Quit()
objSheet = Nothing
objExcel = Nothing
Dts.TaskResult = ScriptResults.Success
End Sub
The error that I receive is this one:
Executed as user: BS-PROCESS1dlangec. ...0.0.1300.13 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 10:25:16 PM Error: 2008-03-18 22:25:22.40 Code: 0x00000001 Source: Script Task Description: System.Runtime.InteropServices.COMException (0x800A03EC): Exception from HRESULT: 0x800A03EC Server stack trace: at Microsoft.VisualStudio.Tools.Applications.RemoteTypeAdapter.System.AddIn.Contract.Automation.IRemoteTypeContract.InvokeMember(String name, BindingFlags bindingFlags, IRemoteObjectContract target, IRemoteArgumentArrayContract remoteArgs, Boolean[] remoteArgModifiers, Int32 lcid) at System.Runtime.Remoting.Messaging.StackBuilderSink._PrivateProcessMessage(IntPtr md, Object[] args, Object server, Int32 methodPtr, Boolean fExecuteInContext, Object[]& outArgs) at System.Runtime.Remoting.Messaging.StackBuilderSink.PrivateProcessMessage(RuntimeMethodHandle md, Object[] args, Object server, Int32 metho... Process Exit Code 1. The step failed.
I have a 2 node cluster with 2 standalone 2k14 instances having alwayson setup. As per client requirement we have created a client access point with a cname alias in dns to connect to secondary replica. Now, everytime whenerver the roles switch over one has to manually move this resource from the previous secondary node to the new secondary node. This is tedious, and should not be done manually either, so I am looking for a way to automate it so that as soon as the role switches over, the resource group after some time should also switch over to the current secondary.
Env details
xa (current primary)
y2a (secondary)
client access point (resource group): aglreadtest
I have three problems related to Sql Server
a.I am developing a database application using VB5/VB6, SQL server 7 and Crystal reports.When I try to generate reports I get the message 'Automation error'. Please help-urgent.
b. I am using temperorary tables to generate reports.ie Table is created by user.Tables are generated runtime ie created as well as deleted. Sometimes
Sometimes i get the message 'SQLServer error' What should I do.What are the rights to be assigned to LOGINS?.
c.Is there precaution while installing SQLserver in WIN98.
Is there a way to automation SQL on an SQL Server to perform
impports and exports at a certain of the night. The server is
always running. Thank Dee
I'm trying to bring down a file from the AS/400 to update a SQL database table. Either thru the configuration limitations on the SQL server or ??? I cannot reach the as/400 from my SQL box so I'm running the data down to a Citrix server to a text file via client access.
I need to automate the DTS package run on the SQL box to pull the data from the Citrix server over to the SQL box.
How do I set up the DTS package to run on a schedule?
One of my developers needs on enable Ole Automation on production? What security loop holes we are creating, by enabling this feature in SQL2K5?
------------------------
I think, therefore I am - Rene Descartes
How can I import an xml file to SQL 2005 at the same time every night? I
will need to create a new database first via the import after that I will be
appending to the dataabase. Then I will need to xport the data into a
different xml file and upload to my hosting system which is yahoo. All so the
original xml file is on the yahoo. store server.
I have SQL 2005 standard install on my personal work stations. When I iron
the bugs out I will be running this from our SQl 2005 Enterprise server.
Thank you
Dee
We've an a VB application connecting to SQL Server 7. Recently the log files show 'Automation error' in one of the routine. I've scan thru the code & pass in some test case & it is ok.
Any idea what could have cause the 'Automation error'?
Thanks in advance.
Ben
Hi,
I have just taken over the DBA role for an application which at best you can describe their DR plans as woeful! So basically i am starting from scratch and trying to automate the database re-creation as much as possible. I have managed to write some bespoke scripts for backup devices, and user ids etc., but the main bulk of the work is in the re-creation of the 300 databases they have (boy i wish i had been here when they did this database design!!).
Anyway, onto my question. I know that i can go to each database in SQL Enterprise manager (SQL 2000 by the way), and right click and select generate scripts and it will do it for me, but i was wondering (and hoping) that there was a API interface that i could utilise which would allow me to pass in the database name and for it to generate the scripts automatically. Height of laziness i know, but it would allow me to generate the scripts for the databases on a regular basis in order to keep the DR recovery scripts up to date.
Thank you in advance for your help, even though i am guessing the answer will be "Tough, you have to do it by hand!"
Regards
Mark
Hi,I tried to add column headers and delete all rows to excel databasewhich is a linked server to my sql server db.I got an error message, "delete data through linked server is notsupported by Isam".I tried to use FSO to write to the excel file, but seems like FSOsupport write to only text file.the last time I tried to create an excel applicaiton object and temperwith its cell, I hang my Excel application and had to reinstall it.Have any ideas,Thank you very much
View 1 Replies View RelatedHello,
Last night was the first night we used Sql Server Agent to run our Warehouse ETL packages. We use the built in logging of SSIS as well as our own event logging mechanism as it goes through all of the packages. Unfortunately, when we checked this morning the agent showed failure but in both logs from the packages no errors were reported. I checked the Windows event log and the Sql Server logs but found nothing that would appear to report an error.
Does anyone have any ideas what I might be missing, any recommendations on things to check, we have manually been running these packages for weeks with no problems. Any help would be greatly appreciated! Thank you in advance!
In MSS 2000, it was possible to have a stored procedure that used OLE Automation to execute a DTS package. E.g.
declare @oPKG int
exec sp_OACreate 'DTS.Package', @oPKG OUTPUT
declare @pkgName varchar(128)
declare @server varchar(20)
declare @cmd varchar(1100)
declare @userid varchar(20)
declare @ServerPWD varchar(20)
set @pkgName = 'PKTestOuter'
set @Server='myServer'
set @userid='myuser'
set @ServerPWD='myPW'
SET @Cmd = 'LoadFromSQLServer("' + @Server +'", "' + @userid + '", "' + @ServerPWD + '", 0, , , , "' + @PkgName + '")'
EXEC sp_OAMethod @oPKG, @Cmd,NULL
exec sp_OAMethod @oPKG, 'Execute'
When I move - but not migrate - packages to 2005, this still works. I don't have a problem with permissions or any of the other problems that I have seen posted in this forum. But if I migrate a package to SSIS, the package is now seen in sysdtspackages90 instead of table sysdtspackages (assuming I remove the old version). But "LoadFromSQLServer(...)" can't find it anymore. So it seems like one of the following is true:
a) There is a different item that I should use in sp_oacreate besides DTS.Package. I tried SSIS.Package or DTS.Package90 which seemed like reasonable possibilities but they don't work. Is there something that will work? Or is "LoadFromSQLServer(...)" not supported for SSIS packages?
b) Or OLE Automation doesn't work with SSIS packages. OLE Automation is enabled and it works on non-migrated packages. Is it true that it was decided by Microsoft that this capability was not needed in SSIS?
I see numerous postings saying not to use the sp_OA routines. I understand there are other ways to execute an SSIS package from a SP and that there may be more secure ways. But I need to know if it is an absolute requirement to use these other methods.
Paul
hi
i have created a rss script for report deployment which i working pretty good but icheckout my all reports on report server and from another machine i mapped that drive from command line and run rss script then i got following error
================================================================
:Automation>rs -i PromotionAutomation.rss -s http://fsaleem/reportserver -v en
v=Dev -v DBServer=fsaleem
Unhandled Exception: System.TypeInitializationException: The type initializer fo
r "Microsoft.ReportingServices.ScriptHost.ScriptHost" threw an exception. ---> S
ystem.Security.SecurityException: Request for the permission of type System.Secu
rity.Permissions.FileIOPermission, mscorlib, Version=1.0.5000.0, Culture=neutral
, PublicKeyToken=b77a5c561934e089 failed.
at System.Security.CodeAccessSecurityEngine.CheckHelper(PermissionSet granted
Set, PermissionSet deniedSet, CodeAccessPermission demand, PermissionToken permT
oken)
at System.Security.CodeAccessSecurityEngine.Check(PermissionToken permToken,
CodeAccessPermission demand, StackCrawlMark& stackMark, Int32 checkFrames, Int32
unrestrictedOverride)
at System.Security.CodeAccessSecurityEngine.Check(CodeAccessPermission cap, S
tackCrawlMark& stackMark)
at System.Security.CodeAccessPermission.Demand()
at System.Reflection.Assembly.VerifyCodeBaseDiscovery(String codeBase)
at System.Reflection.Assembly.GetName(Boolean copiedName)
at System.Reflection.Assembly.GetName()
at Microsoft.ReportingServices.ScriptHost.ScriptHost..cctor()
The state of the failed permission was:
<IPermission class="System.Security.Permissions.FileIOPermission, mscorlib, Vers
ion=1.0.5000.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"
version="1"
PathDiscovery="Z:AutomationRS.exe"/>
--- End of inner exception stack trace ---
at Microsoft.ReportingServices.ScriptHost.ScriptHost.Main(String[] args)
================================================================
hello
is it possible to generate PDF files from reports programmatically ?
Hey,How can I schedule a stored function to run atleast once a day, other than by having it as a scheduled task on the server?Sql Server 2000Thanks
View 3 Replies View RelatedI have spent the last couple of weeks going through about 15-20 different examples/tutorials on running DTS packages from inside a C# Windows app (and even asp.net). I must have a mental block, because I can't seem to make it click in my head. I have hundreds of DTS packages saved as .dtsx files. I can double click on them and run them perfectly. What I need to do is be able to run them conditionally from inside a windows app. Could anyone point me to a tutorial or example that could help me with this? I've even looked at some VB source to see if I could work it out, but I keep running up against a wall. I'd appreciate any help anyone can provide.
View 1 Replies View RelatedI have been trying to execute the following code:
DECLARE @object int
DECLARE @hr int
DECLARE @property varchar(255)
DECLARE @return varchar(255)
DECLARE @output varchar(255)
DECLARE @source varchar(255)
DECLARE @description varchar(255)
--Instantiate a Network Object
EXEC @hr = sp_OACreate 'WScript.Network', @object OUT
IF @hr <> 0
BEGIN
EXEC sp_displayoaerrorinfo @object, @hr
RETURN
END
-- Get a property using an output parameter.
--EXEC @hr = sp_OAMethod @object, 'userDomain' --,@property OUT
EXEC @hr = sp_OAGetProperty @object, 'userDomain'--, @property OUT
IF @hr <> 0
BEGIN
EXEC sp_displayoaerrorinfo @object, @hr
RETURN
END
--PRINT @property
-- Destroy the object.
EXEC @hr = sp_OADestroy @object
IF @hr <> 0
BEGIN
EXEC sp_displayoaerrorinfo @object, @hr
RETURN
END
I get the same behavior stated in microsoft's support site related to BUG
55840 for Sql Server 7.0.
I get an error message when trying to retrieve with @property OUT parameter.
And I get an Empty record set when NO Output param is supplied.
http://support.microsoft.com/support/kb/articles/Q236/4/40.ASP
I am running Sql Server 7.0 SP2 on Windows 2000 AS.
SP2 has this BUG listed as being Fixed. Am I missing something?
Thanks in Advance for your Help,
Chris
Hi friends,
I have a immediate requirement for our company to load text files into different tables in sql 2000.
Requirement is this:: people submit txt files to our webserver via asp pages. Acording to the schedule event(immediate,end of day) DTS should pick up the file and the table they selectd and load the file into the coresponding table.
(main feature we are looking for is how we do a generic dts pakage, which will satisfy different tables loads acording to the corresponding text files.)
I need to have only one dts package doing the upload, regardless the filename and the tablename they selected. I think it should be done by active x and global variable. I am new to dts programming and I am kinda stuck here.
If anyone out there could help me on how to do it, I really really appreciated.
Thanks in ad...
Jay
Hello everybody ,
I need to create a automation job for 2005 analysis services database to backup a database every night . i dont have sql server agent to create a job. I was told about a method to create a batch file and than use ssis or something similar to execute the job.
can anyone please help me out in guiding me with proper documentation to perform this procedure so that i can create a automation job without the help of sql server agent to create a backup job every night for anaylysis services 2005 database ......analysis services 2005 only
Thanks in advance
Yukon DBA
Hopeing this is something I can accomplish. I use MS-SQL for close to100 CMS web servers. Everytime I create a new website I have to createa new database and then use the export from SQL to SQL option and copyall data / SP's / etc... Just no security information. My question iscan I automate this? Can I create a script that will run all this?Thanks for any help, and thanks for reading.Chris AuerJoin Bytes!
View 2 Replies View RelatedI have written quite a complex query in Query Analyser, now need toknow how to automate the running of the query to dump the results intoa flat file at scheduled times. Am an experienced Unix user, where itwould be quite simple but new to sql server, is there a relativelysimple way to do this?
View 1 Replies View Relatedcan any one send me a useful link which is easy to undersatnd Step by Step for Aumaotion of Reports Deployment?
Hello,
I have recently become a release manager for SRSS in our company.
Since then I've been swamped with requests to migrate reports, permissions and subscription lists from development environment to production.
Each time I have to do it manually with a lot of clicks. It is a real pain...
So, may be... may be there is an automation tool out there to help me? Does anybody know?
This tool or s/w package should move a report file along with its permissions and subscription lists from one server to another.
Please advise!
thanks,
I need to perform a backup from prod and restore the backup onto Staging server.
This entire task needs to be automated using sqlcmd and staging server is configured with always-on.
What is the best possible way to automate the entire tasks?
Hello!
I am looking to automate the rollout of the SQL Native client to our workstations. What I would like to do is use our login script to initiate an unattended install on the workstation. I will create the unattended file using the one that comes with SQL Server 2005 as a guide. This seems pretty straightforward.
More importantly, the other task I want to tackle is adding server aliases to the workstations. I am thinking of creating an alias for each of our applications and pushing the changes out by merging a registry key(maybe WMI? I dont know...). This way, should we need to move application databases to a different instance, we can just push an updated list of aliases to the workstations. The actual storage location remains transparent to the applications and users, (ie no configuration changes).
Has anyone done this or have any ideas? Are there any pros/cons that I have not thought of?
Thanks for any input!
Chris G
Hi all,
I am looking for any information about using SSIS as a workflow automation (job engine) tool. My company is looking into buying a 3rd party app to do our job scheduling and I think that SSIS could do all that we need. The only issue they found with SSIS is the lack of a GUI/dashboard to view all jobs at once. We need more flexibility than the job scheduler in SQL Server Agent will allow. I have heard that it is possible to build a C# GUI app that can serve as a job engine front page.
What we need is a way to view all jobs in the system and be able to start, stop, pause all jobs manually in a graphical interface. I know of a few companies that are doing this but I am unable to find anything on line about it. My bosses are ready to give SSIS a shot if I can prove that we can build such an interface. Does anyone have any first hand knowledge of such an application or have any tips on where I should look?
Thanks in advance!
-- Craig ***
Read only transacted replication to about 60 clients. 100 or so transactions a day (8 hours), transaction (record) is probably about 8K/transaction. Reliable connection over 100base-T. Latency - 15 minutes would be great.
This doable? -- I couldn't find a timing/ sizing model any place.
Is it possible to write a SP (Automate) to generate STATISTICS on any database and then use the output to create the stats on that database.
I ran the tuning adviser and it suggested indexes with lot of STATISTICS on the dev environment. This dev environment is replicated in several other environment with data size in these environment varying. I would like to know if I can create a SP which generates STATISTICS information pertaining to specific database environment for the query in question for tuning.
My server is a dual AMD x64 2.19 GHz with 8 GB RAM running under Windows Server 2003 Enterprise Edition with service pack 1 installed. We have SQL 2000 32-bit Enterprise installed in the default instance. AWE is enabled using Dynamically configured SQL Server memory with 6215 MB minimum memory and 6656 maximum memory settings.
I have now installed, side-by-side, SQL Server 2005 Enterprise Edition in a separate named instance. Everything is running fine but I believe SQL Server2005 could run faster and need to ensure I am giving it plenty of resources. I realize AWE is not needed with SQL Server 2005 and I have seen suggestions to grant the SQL Server account the 'lock pages in memory' rights. This box only runs the SQL 2000 and SQL 2005 server databases and I would like to ensure, if possible, that each is splitting the available memory equally, at least until we can retire SQL Server 2000 next year. Any suggestions?