Friday, September 13, 2013

[MS SQL Server] Looking for powershell script

[MS SQL Server] Looking for powershell script

Looking for powershell script

Posted: 12 Sep 2013 11:44 PM PDT

Hi, i am looking to find powershell expert, so that can help on my query.below script does return sql installed services on server but it will not return anything if service is not installed. my requirement to return wheather service is running then 'running' else 'not installed or not running'. i need to display all status.can anyone please help me.# Configuration data.# Add your machine names to check for to the list:[Array] $servers = "server name";# Defining output format for each column.$fmtName =@{label="Service Name" ;alignment="left" ;width=30 ;Expression={$_.Name};};$fmtMode =@{label="Start Mode" ;alignment="left" ;width=10 ;Expression={$_.StartMode};};$fmtState =@{label="State" ;alignment="left" ;width=10 ;Expression={$_.State};};$fmtStatus =@{label="Status" ;alignment="left" ;width=10 ;Expression={$_.Status};};$fmtMsg =@{label="Message" ;alignment="left" ;width=50 ; `Expression={ if (($_.StartMode -eq "Auto") -and ($_.State -ne "Running") ) {"Alarm: Stopped"} };};foreach($server in $servers){$srvc = Get-WmiObject `-query "SELECT * FROM win32_service WHERE name LIKE '%SQL%' OR name LIKE '%MSOLAP%'OR name LIKE '%ReportServer%'OR name LIKE '%MSDtsServer%'" `-computername $server `| Sort-Object -property name;Write-Output ("Server: {0}" -f $server);Write-Output $srvc | Format-Table $fmtName, $fmtMode, $fmtState, $fmtStatus, $fmtMsg; } Thanks

Error checking in SQL Jobs

Posted: 12 Sep 2013 11:30 PM PDT

Due to some data refresh and application requirements, we need to roll a database forward to our production server on a nightly basis. The issue is that since we now have some many connections getting to the DB, we can't gain exclusive access to the DB to run the restore. Then the DB gets stuck in Single User Mode and our applications get hosed. I wrote error checking code to try and prevent this from happening, but I guess I didn't write the code correctly because it didn't work.There are a total of 4 steps to the job, and I think one of the steps might be an issue, not sure, so looking for insight, and that is outside of the error checking issue. Any insight would be appreciated. Thanks!Step 1: Kill PidsStep 2: Change to single user modeStep 3: Kill Pids again, in case there are any one hanging out there (this might be an issue, as it kills the pid that runs the job itself, so it seems.Step 4: Restore the DB. Here is my code. There is a move and some sync users code in there, but this give you the idea of what I was attempting to do.use mastergoIF @@ERROR <> 3101 --Error 3101 is for not obtaining exclusive access to DB. BEGIN RESTORE DATABASE [XXX] FROM DISK = N'\\mynetworkpath_mydb.bak' WITH FILE = 1, END ELSE BEGIN ALTER DATABASE [HRSADW] SET MULTI_USER WITH ROLLBACK IMMEDIATE END;

SQL Server 2005 Upgrdations and SQL 2012 Review

Posted: 13 Sep 2013 12:09 AM PDT

Right now we are using SQL SEVER 2005 in PRoduction Serversas Price concern,to upgrade SQL Server 2005 which one is better 2005 to 2008 or 2005 to 2012 ?as performance concern, which one is better 2008 or 2012 ?and How the SQL Server 2012 is running in the market and what review of SQL 2012 on Production Servers ?Can any one give licensce details with cost for SQL Server 2012 and 2008.

Cross Domain Authentication for a Reporting Services Server

Posted: 23 May 2009 09:14 AM PDT

I am trying to figure out how to make a Reporting Services server see a database on another domain. The RS Server is in MYDOMAIN (server: MYDEVRPT01) but the database (TargetDB) is in the OTHERDOM domain, specifically on SQL Server TARGETSERV. The RS Server reporting services service currently runs under NETWORK SERVICE, but I have already tried various things among them running the RS Service under OTHERDOM\myaccount which has access to TargetDB on TARGETSERV. This, even with OTHERDOM\myaccount in the Adminstrators group on MYDEVRPT01. Note that I personally can use OTHERDOM\myaccount to access stuff on the OTHERDOM in SSMS from my workstation but only with VPN activated. No matter what I do I get an error saying it cannot connect. Is there some sort of combination I need here to get my MYDOMAIN based report server to see TargetDB on TARGETSERV on OTHERDOM? Do I need the AD Admin to set up some sort of delegation so that MYDOMAIN\RSServiceAcct or OTHERDOM\RSServiceAccount, or even OTHERDOM\myaccount can "see" the other server (without VPN, of course)? My Reporting Server is SQL Server 2008 SP1. TARGETSERV is running SQL Server 2005. Thanks in advance.

SQL Job issue. Job does not run but is running???

Posted: 13 Sep 2013 12:18 AM PDT

Very, Very strange issue here. I have a SQL job with a single step. This step executes a stored procedure. Inside the stored procedure I am exporting some data into a CSV file. The SP turns on xp_cmdshell with this command: EXEC sp_configure 'xp_cmdshell', 1 -- To update the currently configured value for this feature. RECONFIGUREThe stored procedure then exports data to CSV files and runs a batch file using this command: EXEC master..xp_CMDShell 'C:\RapidMarketplaceDataFeeds\FTPCSVBATCH.bat' Then I turn off the command shell with this command: EXEC sp_configure 'xp_cmdshell', 0 RECONFIGURERunning the job manually works. Running the stored procedure works. However, upon job being run by the schedule the job stops to work and cannot be stared again. I am also unable to stop the job. I executed the SP: exec msdb.dbo.sp_help_job @Job_name = '(WEB) RapidMarketplace Data feed daily' to see the status of the job. It appears as if it is not running in the result set. As you can see I did a whole lot before posting here.Another weird thing when I right click the job and go to Stop job it comes back with Success. You can do this 10 times it always comes back with success. That would indicate that this job is indeed running somewhere or at least doing something because all other jobs that are not running come back with an error: cant stop the job because it is not running... Looking at my activity monitor the Status says Executing 1(Exec SP) which is the name of my step. Please help I do not know what to do here.

sql server service status check for 2000 server.

Posted: 12 Sep 2013 10:40 PM PDT

Hi, i am trying to find out service check on 2000 servers but i have got no idea on it. below script runing fine on 2005 and above servers, my requirement is script that will not return any error messages. if anyone fix that will be appreciate. or got any other script that run on any server will be great. Thanks/*---------------------------------------*//* SQL Server Service Check Utility *//*---------------------------------------*/SET NOCOUNT ON/* ------------------------------------------ Inital Setup -----------------------------------------------------*/CREATE TABLE #RegResult(ResultValue NVARCHAR(4))CREATE TABLE #ServicesServiceStatus /*Create temp tables*/( RowID INT IDENTITY(1,1),ServerName NVARCHAR(128) ,ServiceName NVARCHAR(128),ServiceStatus varchar(128),StatusDateTime DATETIME DEFAULT (GETDATE()),PhysicalSrverName NVARCHAR(128))DECLARE @ChkInstanceName nvarchar(128) /*Stores SQL Instance Name*/,@ChkSrvName nvarchar(128) /*Stores Server Name*/,@TrueSrvName nvarchar(128) /*Stores where code name needed */,@SQLSrv NVARCHAR(128) /*Stores server name*/,@PhysicalSrvName NVARCHAR(128) /*Stores physical name*/,@FTS nvarchar(128) /*Stores Full Text Search Service name*/,@RS nvarchar(128) /*Stores Reporting Service name*/,@SQLAgent NVARCHAR(128) /*Stores SQL Agent Service name*/,@OLAP nvarchar(128) /*Stores Analysis Service name*/ ,@REGKEY NVARCHAR(128) /*Stores Registry Key information*/SET @PhysicalSrvName = CAST(SERVERPROPERTY('MachineName') AS VARCHAR(128)) SET @ChkSrvName = CAST(SERVERPROPERTY('INSTANCENAME') AS VARCHAR(128)) SET @ChkInstanceName = @@serverNameIF @ChkSrvName IS NULL /*Detect default or named instance*/BEGIN SET @TrueSrvName = 'MSQLSERVER'SELECT @OLAP = 'MSSQLServerOLAPService' /*Setting up proper service name*/SELECT @FTS = 'MSFTESQL' SELECT @RS = 'ReportServer' SELECT @SQLAgent = 'SQLSERVERAGENT'SELECT @SQLSrv = 'MSSQLSERVER'END ELSEBEGINSET @TrueSrvName = CAST(SERVERPROPERTY('INSTANCENAME') AS VARCHAR(128)) SET @SQLSrv = '$'+@ChkSrvNameSELECT @OLAP = 'MSOLAP' + @SQLSrv /*Setting up proper service name*/SELECT @FTS = 'MSFTESQL' + @SQLSrv SELECT @RS = 'ReportServer' + @SQLSrvSELECT @SQLAgent = 'SQLAgent' + @SQLSrvSELECT @SQLSrv = 'MSSQL' + @SQLSrvEND /* ---------------------------------- SQL Server Service Section ----------------------------------------------*/SET @REGKEY = 'System\CurrentControlSet\Services\'+@SQLSrvINSERT #RegResult ( ResultValue ) EXEC master.sys.xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key=@REGKEYIF (SELECT ResultValue FROM #RegResult) = 1 BEGININSERT #ServicesServiceStatus (ServiceStatus) /*Detecting staus of SQL Sever service*/EXEC xp_servicecontrol N'QUERYSTATE',@SQLSrvUPDATE #ServicesServiceStatus set ServiceName = 'MS SQL Server Service' where RowID = @@identityUPDATE #ServicesServiceStatus set ServerName = @TrueSrvName where RowID = @@identityUPDATE #ServicesServiceStatus set PhysicalSrverName = @PhysicalSrvName where RowID = @@identityTRUNCATE TABLE #RegResultENDELSE BEGININSERT INTO #ServicesServiceStatus (ServiceStatus) VALUES ('NOT INSTALLED')UPDATE #ServicesServiceStatus set ServiceName = 'MS SQL Server Service' where RowID = @@identityUPDATE #ServicesServiceStatus set ServerName = @TrueSrvName where RowID = @@identityUPDATE #ServicesServiceStatus set PhysicalSrverName = @PhysicalSrvName where RowID = @@identityTRUNCATE TABLE #RegResultEND/* ---------------------------------- SQL Server Agent Service Section -----------------------------------------*/SET @REGKEY = 'System\CurrentControlSet\Services\'+@SQLAgentINSERT #RegResult ( ResultValue ) EXEC master.sys.xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key=@REGKEYIF (SELECT ResultValue FROM #RegResult) = 1 BEGININSERT #ServicesServiceStatus (ServiceStatus) /*Detecting staus of SQL Agent service*/EXEC xp_servicecontrol N'QUERYSTATE',@SQLAgentUPDATE #ServicesServiceStatus set ServiceName = 'SQL Server Agent Service' where RowID = @@identityUPDATE #ServicesServiceStatus set ServerName = @TrueSrvName where RowID = @@identityUPDATE #ServicesServiceStatus set PhysicalSrverName = @PhysicalSrvName where RowID = @@identityTRUNCATE TABLE #RegResultENDELSE BEGININSERT INTO #ServicesServiceStatus (ServiceStatus) VALUES ('NOT INSTALLED')UPDATE #ServicesServiceStatus set ServiceName = 'SQL Server Agent Service' where RowID = @@identityUPDATE #ServicesServiceStatus set ServerName = @TrueSrvName where RowID = @@identity UPDATE #ServicesServiceStatus set PhysicalSrverName = @PhysicalSrvName where RowID = @@identityTRUNCATE TABLE #RegResultEND/* ---------------------------------- SQL Browser Service Section ----------------------------------------------*/SET @REGKEY = 'System\CurrentControlSet\Services\SQLBrowser'INSERT #RegResult ( ResultValue ) EXEC master.sys.xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key=@REGKEYIF (SELECT ResultValue FROM #RegResult) = 1 BEGININSERT #ServicesServiceStatus (ServiceStatus) /*Detecting staus of SQL Browser Service*/EXEC master.dbo.xp_servicecontrol N'QUERYSTATE',N'sqlbrowser'UPDATE #ServicesServiceStatus set ServiceName = 'SQL Browser Service - Instance Independent' where RowID = @@identityUPDATE #ServicesServiceStatus set ServerName = @TrueSrvName where RowID = @@identityUPDATE #ServicesServiceStatus set PhysicalSrverName = @PhysicalSrvName where RowID = @@identityTRUNCATE TABLE #RegResultENDELSE BEGININSERT INTO #ServicesServiceStatus (ServiceStatus) VALUES ('NOT INSTALLED')UPDATE #ServicesServiceStatus set ServiceName = 'SQL Browser Service - Instance Independent' where RowID = @@identityUPDATE #ServicesServiceStatus set ServerName = @TrueSrvName where RowID = @@identityUPDATE #ServicesServiceStatus set PhysicalSrverName = @PhysicalSrvName where RowID = @@identityTRUNCATE TABLE #RegResultEND/* ---------------------------------- Integration Service Section ----------------------------------------------*/SET @REGKEY = 'System\CurrentControlSet\Services\MsDtsServer'INSERT #RegResult ( ResultValue ) EXEC master.sys.xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key=@REGKEYIF (SELECT ResultValue FROM #RegResult) = 1 BEGININSERT #ServicesServiceStatus (ServiceStatus) /*Detecting staus of Intergration Service*/EXEC master.dbo.xp_servicecontrol N'QUERYSTATE',N'MsDtsServer'UPDATE #ServicesServiceStatus set ServiceName = 'Intergration Service - Instance Independent' where RowID = @@identityUPDATE #ServicesServiceStatus set ServerName = @TrueSrvName where RowID = @@identityUPDATE #ServicesServiceStatus set PhysicalSrverName = @PhysicalSrvName where RowID = @@identityTRUNCATE TABLE #RegResultENDELSE BEGININSERT INTO #ServicesServiceStatus (ServiceStatus) VALUES ('NOT INSTALLED')UPDATE #ServicesServiceStatus set ServiceName = 'Intergration Service - Instance Independent' where RowID = @@identityUPDATE #ServicesServiceStatus set ServerName = @TrueSrvName where RowID = @@identityUPDATE #ServicesServiceStatus set PhysicalSrverName = @PhysicalSrvName where RowID = @@identityTRUNCATE TABLE #RegResultEND/* ---------------------------------- Reporting Service Section ------------------------------------------------*/SET @REGKEY = 'System\CurrentControlSet\Services\'+@RSINSERT #RegResult ( ResultValue ) EXEC master.sys.xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key=@REGKEYIF (SELECT ResultValue FROM #RegResult) = 1 BEGININSERT #ServicesServiceStatus (ServiceStatus) /*Detecting staus of Reporting service*/EXEC master.dbo.xp_servicecontrol N'QUERYSTATE',@RSUPDATE #ServicesServiceStatus set ServiceName = 'Reporting Service' where RowID = @@identityUPDATE #ServicesServiceStatus set ServerName = @TrueSrvName where RowID = @@identityUPDATE #ServicesServiceStatus set PhysicalSrverName = @PhysicalSrvName where RowID = @@identityTRUNCATE TABLE #RegResultENDELSE BEGININSERT INTO #ServicesServiceStatus (ServiceStatus) VALUES ('NOT INSTALLED')UPDATE #ServicesServiceStatus set ServiceName = 'Reporting Service' where RowID = @@identityUPDATE #ServicesServiceStatus set ServerName = @TrueSrvName where RowID = @@identityUPDATE #ServicesServiceStatus set PhysicalSrverName = @PhysicalSrvName where RowID = @@identityTRUNCATE TABLE #RegResultEND/* ---------------------------------- Analysis Service Section -------------------------------------------------*/IF @ChkSrvName IS NULL /*Detect default or named instance*/BEGIN SET @OLAP = 'MSSQLServerOLAPService'ENDELSE BEGINSET @OLAP = 'MSOLAP'+'$'+@ChkSrvNameSET @REGKEY = 'System\CurrentControlSet\Services\'+@OLAPENDINSERT #RegResult ( ResultValue ) EXEC master.sys.xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key=@REGKEYIF (SELECT ResultValue FROM #RegResult) = 1 BEGININSERT #ServicesServiceStatus (ServiceStatus) /*Detecting staus of Analysis service*/EXEC master.dbo.xp_servicecontrol N'QUERYSTATE',@OLAPUPDATE #ServicesServiceStatus set ServiceName = 'Analysis Services' where RowID = @@identityUPDATE #ServicesServiceStatus set ServerName = @TrueSrvName where RowID = @@identityUPDATE #ServicesServiceStatus set PhysicalSrverName = @PhysicalSrvName where RowID = @@identityTRUNCATE TABLE #RegResultENDELSE BEGININSERT INTO #ServicesServiceStatus (ServiceStatus) VALUES ('NOT INSTALLED')UPDATE #ServicesServiceStatus set ServiceName = 'Analysis Services' where RowID = @@identityUPDATE #ServicesServiceStatus set ServerName = @TrueSrvName where RowID = @@identityUPDATE #ServicesServiceStatus set PhysicalSrverName = @PhysicalSrvName where RowID = @@identityTRUNCATE TABLE #RegResultEND/* ---------------------------------- Full Text Search Service Section -----------------------------------------*/SET @REGKEY = 'System\CurrentControlSet\Services\'+@FTSINSERT #RegResult ( ResultValue ) EXEC master.sys.xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key=@REGKEYIF (SELECT ResultValue FROM #RegResult) = 1 BEGININSERT #ServicesServiceStatus (ServiceStatus) /*Detecting staus of Full Text Search service*/EXEC master.dbo.xp_servicecontrol N'QUERYSTATE',@FTSUPDATE #ServicesServiceStatus set ServiceName = 'Full Text Search Service' where RowID = @@identityUPDATE #ServicesServiceStatus set ServerName = @TrueSrvName where RowID = @@identityUPDATE #ServicesServiceStatus set PhysicalSrverName = @PhysicalSrvName where RowID = @@identityTRUNCATE TABLE #RegResultENDELSE BEGININSERT INTO #ServicesServiceStatus (ServiceStatus) VALUES ('NOT INSTALLED')UPDATE #ServicesServiceStatus set ServiceName = 'Full Text Search Service' where RowID = @@identityUPDATE #ServicesServiceStatus set ServerName = @TrueSrvName where RowID = @@identityUPDATE #ServicesServiceStatus set PhysicalSrverName = @PhysicalSrvName where RowID = @@identityTRUNCATE TABLE #RegResultEND/* -------------------------------------------------------------------------------------------------------------*/SELECT PhysicalSrverName AS 'Physical Server Name' /*Display finding*/,ServerName AS 'SQL Instance Name',ServiceName AS 'SQL Server Services',ServiceStatus AS 'Current Service Service Status',StatusDateTime AS 'Date/Time Service Status Checked'FROM #ServicesServiceStatus/* -------------------------------------------------------------------------------------------------------------*/DROP TABLE #ServicesServiceStatus /*Perform cleanup*/DROP TABLE #RegResultcheers

Tool for SQL Inventory

Posted: 12 Sep 2013 12:57 PM PDT

I am doing a SQL inventory. basicly I need to scan a network range,find all SQL instance and record server name, instance name, version, logins.....What is the best tool for all these?Thanks!

Windows Installer Error - Incorrect version for OS

Posted: 12 Sep 2013 08:15 AM PDT

When I attempt to install SQL Server 2008 R2 I get the following error:Windows Installer Error - Incorrect version for OS.I have a 64 bit Windows Standard 2008 R2 installed.I select Windows6.0-KB942288-v2-x64.msu from the following URL: [url]http://www.microsoft.com/en-us/download/details.aspx?id=8483[/url]Any help would be greatly appreciated.Thanks.
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog