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:

[Articles] Risk and Assumptions

[Articles] Risk and Assumptions

Risk and Assumptions

Posted: 12 Sep 2013 11:00 PM PDT

Building software always involves risk, but in these tough times, Steve Jones thinks we should be working to lower the risk of IT projects.
Posted by at No comments:

[SQL 2012] CDC; AlwaysOn and CDC Jobs question.

[SQL 2012] CDC; AlwaysOn and CDC Jobs question.

CDC; AlwaysOn and CDC Jobs question.

Posted: 13 Sep 2013 02:16 AM PDT

Hi all,I have a Primary Replica A and Secondary B part of the AG Group. CDC is enabled at the Primary and the CDC jobs are created by default when you enable(CDC) on the Primary.According to the Microsoft article [url=http://technet.microsoft.com/en-us/library/hh403414.aspx][/url] The best practice would be: [QUOTE]You should create the jobs at all of the possible failover targets before failover, and mark them as disabled until the availability replica at a host becomes the new primary replica. The CDC jobs running at the old primary database should be also disabled when the local database becomes a secondary database. To disable and enable jobs, use the @enabled option of sp_update_job (Transact-SQL). For more information about creating CDC jobs, see sys.sp_cdc_add_job (Transact-SQL).[/QUOTE]However, When I try to specifically run the sp_cdc_add_job for the Secondary it says the "Could not update the metadata for database 'DBName' to indicate that a Change Data Capture job has been added" Two questions:1) Should I script the Job out from ServerA and run it on B?2) Proposed Solution : (After I sucessfully recreate Jobs on both the replica's) At every Job execution step add this :If sys.fn_hadr_backup_is_preferred_replica( @dbname ) = 1 (Checks whether server is primary) BEGIN--RUN CDC JOB ENDELSE EXITDoes this make sense? Feedback/suggestions would be appreciated.

version_ghost_record_count

Posted: 12 Sep 2013 09:25 PM PDT

Hi,I understand that [version_ghost_record_count] as retrieved from sys.dm_db_index_physical_stats shows the ghost records count sustained by a snapshot isolation transaction in an allocation unit.My question is this:I am seeing version ghost records in one of my databases but the database has Snapshot Isolation switched off. Is this possible?

monitorLoop

Posted: 12 Sep 2013 09:23 PM PDT

Hello.In SQL SERVER 2012, the xml of the event class 137 has now a new attribute, monitorLoop="xxxxxx" in the tag blocked-process-report. "xxxxxx" is a sequential number that it can to be repeated.What is this? I don't find anything about that.Thanks.

Error 18059: Impersonation Contexts

Posted: 12 Sep 2013 05:25 AM PDT

We have an app (from Active Networks) which, since being upgraded and moving to SQL Server 2012, repeatedly throws this error multiple times through the day: Error: 18059, Severity: 20, State: 1.From what I've read, documentation points to this being a problem with application pooling and security impersonation. As far as I can tell (from a profiler trace), it seems like the app initiates a connection, assumes an application role, then a new connection is made with the same credentials which throws the error. But, I may be incorrect in my observations.Has anyone seen anything like this before? The vendor seems certain that we're an isolated case and they haven't seen the same problem anywhere else, but we're no further towards resolving the error.

SS 2012 Data Tools to Manage SS 2005?

Posted: 12 Sep 2013 04:50 AM PDT

I just installed the Sql Server 2012 Client and Data tools on a term server in the hopes that we could use them to manage our Sql Server 2005 servers. Mgt Studio seems to work well with those. However, using Sql Server 2012 Data Tools, will we able to1) modify existing 2005 SSIS packages and resave them to our 2005 Sql Servers (Integration Services)?2) create new SSIS packages and save them to our 2005 SQL Servers w/ Integration Services?Hopefully the answer is 'yes' to the above and, if so, if you have links on the right way to do this, let me know. For example, I am wondering if you have to upgrade all the packages, etc.
Posted by at No comments:

[T-SQL] Error formatting query?

[T-SQL] Error formatting query?

Error formatting query?

Posted: 12 Sep 2013 09:39 PM PDT

HiError Messages, Could you help me. how to resolve this error? purpose of script email sending when rows selected..Error formatting query, probably invalid parameters [SQLSTATE 42000] (Error 22050). The step failed.[code="sql"]select MACHINE, COUNT(*) as Instance into #tableAfrom LIMSPROD.dbo.CM_INSTANCE group by MACHINESelect COUNT(*)/4 as InstanceA into #tableBfrom LIMSPROD.dbo.CM_INSTANCESelect A.MACHINE, A.Instance, B.InstanceA, (B.InstanceA-A.Instance) as Diff into #resultsfrom #tableA A, #tableB Bwhere (B.InstanceA-A.Instance) < -3 or (B.InstanceA-A.Instance) > 3--number of records to not be zeroIf ((select count(*) from #results) <> 0)BeginEXEC msdb.dbo.sp_send_dbmail @profile_name='db_mail',@recipients='ananda.murugesan@xyz.com',@subject='Alert!-Verify Instance',@query='select * from #results'Enddrop table #tableAdrop table #tableBdrop table #results[/code]

Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation. [SQLSTATE 42000] (Error 468). The step failed.

Posted: 12 Sep 2013 09:15 PM PDT

Hi Guys,Got the following sp that I created a job to run once a day in Hosted server and the collate is Latin1_General_CI_AS and I have DR server that has collate SQL_Latin1_General_CP1_CI_AS and I'm creating the same job on my DR server but I come the following error: [b][b]Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation. [SQLSTATE 42000] (Error 468). The step failed.[/b]here is the Sp..please assist it's urgent the job has been failing over a week now.ALTER PROCEDURE [dbo].[RPT_MTX_AllUnitsReportDaily] AS --REMOVE OLD DATA DELETE FROM dbo.AllUnitsReportDaily; --==================================Get Box_no, MSISDN from Navision================================================== --Pulls MSISDN info from DynamicsNavdatabase SELECT DISTINCT CAST(REPLACE(si2.[Serial No_],'AC1','BX1') AS VARCHAR(15)) AS Box_no, si.[Serial No_] AS SIM, si.[MSISDN Serial No_] AS MSISDN INTO #NavisionUnitSerials -- drop table #NavisionUnitSerials FROM DynamicsNavdatabase.dbo.[MIX TELEMATICS$Serial No_ Information] si LEFT JOIN DynamicsNavdatabase.dbo.[MIX TELEMATICS$Serial No_ Information] si2 ON si2.[SIM Serial No_ (Embeded)] = si.[Serial No_] WHERE si.[Item No_] = '1000851' AND si.[MSISDN Serial No_] IS NOT NULL AND si.[MSISDN Serial No_] <> '' AND si2.[Serial No_] <> '' AND si2.[Serial No_] IS NOT NULL; CREATE NONCLUSTERED INDEX IDX_NAV_BOX ON #NavisionUnitSerials ( Box_no ); --=============================Get last passed test date of RSLead & MiXControl========================================= SELECT unitid AS [Box_No], createdate AS [CreateDate], 1 as tab INTO #LastPassedTestDate_Init -- drop table #LastPassedTestDate_Init FROM RSLead.dbo.unittesthead H WITH (NOLOCK) WHERE (testrefcode IS NOT NULL OR testrefcode <> '') --IF HAS REF THEN TEST PASSED AND H.unittestheadno IN (SELECT MAX(td2.unittestheadno) FROM RSLead.dbo.unittesthead td2 WITH (NOLOCK) WHERE h.unitid = td2.unitid COLLATE SQL_Latin1_General_CP1_CI_AS GROUP BY td2.unitid) UNION ALL SELECT [Box_No], [CreateDate], 2 FROM [MixControl].[dbo].[UnitTestResultHead] H WITH (NOLOCK) WHERE TestStatus = 3 AND TestReference IS NOT NULL AND H.[UnitTestResultHeadID] IN (SELECT MAX(td2.[UnitTestResultHeadID]) FROM [MixControl].[dbo].[UnitTestResultHead] td2 WITH (NOLOCK) WHERE h.[Box_No] = td2.[Box_No] COLLATE SQL_Latin1_General_CP1_CI_AS GROUP BY td2.[Box_No]); CREATE CLUSTERED INDEX IDX_LastPassedTestDate_Init ON #LastPassedTestDate_Init ( [Box_No] ); SELECT [Box_No], MAX([CreateDate]) AS [CreateDate] INTO #LastPassedTestDate -- drop table #LastPassedTestDate FROM #LastPassedTestDate_Init GROUP BY Box_No; CREATE CLUSTERED INDEX IDX_LastPassedTestDate ON #LastPassedTestDate ( [Box_No] ); --=============================Get Company RegNo on MiXControl========================================= Select ClientID,CompanyRegistrationNumber into #tempCompanyReg from dbo.ClientCompany where CompanyRegistrationNumber <> '' COLLATE SQL_Latin1_General_CP1_CI_AS --==========================================Get Unit Info============================================================= DECLARE @debperiodno INT; SET @debperiodno = ( SELECT DISTINCT(periodno)FROM rsdebtors.dbo.period WHERE curflag = 1 ); INSERT INTO [AllUnitsReportDaily] SELECT Distinct UT.UnitTypeDesc AS [TYPE], C.ContractID, U.Box_No, A.RegistrationNumber, CL.AccountName, rscon.chargecode, --CASE -- WHEN (cch.escalchargeflag = 1) THEN rscon.chargeamt -- WHEN (rscon.incrperiod > @debperiodno + 1) THEN rscon.chargeamt -- ELSE cch.chargeamt -- END AS 'ChargeAmt', rscon.chargeamt AS 'ChargeAmt', cch.chargeamt as NormalChargeAmt, rscon.startperiod, C.CreateDate AS [ContractDate], rscon.accountno AS [AccountNo], C.salespersonid AS [SalesPersonID], b2.brokername as SalesPersonDesc, C.FitmentCentreID AS [FitmentCentreID], case when left(u.Box_No,2) = '04' then i.name when LEFT(u.box_no,2) = '35' then i.name else b1.brokername end as [FitmentCentreName], CASE WHEN (cch.escalchargeflag = 1) THEN 'RENTAL CONTRACT' ELSE 'CASH CONTRACT' END AS 'ContractType', ISNULL(CCT.contracttypedesc,CCT1.contracttypedesc) AS ContractTypeDesc, CAST(ISNULL(aic.PolicyNumber,'') AS CHAR(30)) AS 'PolicyNumber', ISNULL(C.UniqueNumber,'') AS 'UniqueNumber', ISNULL(l.leadno,ML.LEADID) AS LeadNo, ISNULL(l.brokerno,LP.BrokerID) AS BrokerNo, ISNULL(ISNULL(B.brokercode,LP.ExternalPartnerCode),'') AS ExternalPartnerCode, ISNULL(L.idno,LP.IDNumber) AS IDNumber, zl.MSISDN, LPT.CreateDate AS LastPassedTestDate, a.ChassisNumber, tcr.CompanyRegistrationNumber, ISNULL(l.createopr,ML.CreateOpr) AS LeadCreatedBy, c.editOpr as [ContractModifiedBy], ama.AssetMakeDesc, am.AssetModelDesc FROM MixControl.dbo.unit U WITH (NOLOCK) JOIN MixControl.dbo.unittype UT WITH (NOLOCK) ON U.UnitTypeID = UT.UnitTypeID JOIN MixControl.dbo.ContractAsset CA WITH (NOLOCK) ON CA.AssetID = U.AssetID JOIN MixControl.dbo.CONTRACT C WITH (NOLOCK) ON C.ContractID = CA.ContractID JOIN MixControl.dbo.Asset A WITH (NOLOCK) ON CA.AssetID = A.AssetID JOIN MixControl.dbo.assetmodel AM WITH (NOLOCK) ON a.assetmodelid = am.assetmodelid JOIN MixControl.dbo.assetmake AMA WITH (NOLOCK) ON am.assetmakeid = ama.assetmakeid JOIN MixControl.dbo.Client CL WITH (NOLOCK) ON CL.ClientID = C.ClientID JOIN RSDebtors.dbo.contractcharge rscon WITH (NOLOCK) ON rscon.contractno = C.ContractID JOIN RSDebtors.dbo.charge cch WITH (NOLOCK) ON rscon.chargecode = cch.chargecode LEFT JOIN RSlead.dbo.lead L WITH (NOLOCK) ON l.contractid = c.ContractID AND l.eventtypecode = 'PUSHTOFX' LEFT JOIN RSlead.dbo.broker B WITH (NOLOCK) ON L.externalpartnerno = B.brokerno LEFT JOIN MixControl.dbo.lead ML WITH (NOLOCK) ON ML.contractid = c.ContractID LEFT JOIN MixControl.dbo.LeadPerson LP WITH (NOLOCK) ON LP.LeadPersonID = ML.LeadPersonID LEFT JOIN MixControl.dbo.AssetInsuranceCompany AIC WITH (NOLOCK) ON CA.AssetID = AIC.AssetID LEFT JOIN RSLead.dbo.contracttype CCT WITH (NOLOCK) ON CCT.contracttypeno = L.contracttypeno LEFT JOIN RSLead.dbo.contracttype CCT1 WITH (NOLOCK) ON CCT1.contracttypeno = ML.contracttypeID LEFT JOIN #navisionUnitSerials zl ON U.Box_No = zl.box_no COLLATE SQL_Latin1_General_CP1_CI_AS LEFT JOIN #LastPassedTestDate LPT WITH (NOLOCK) ON U.BOX_NO = LPT.Box_No COLLATE SQL_Latin1_General_CP1_CI_AS left join #tempCompanyReg tcr with(nolock) on cl.ClientID = tcr.ClientID left join RSlead.dbo.broker b1 on c.FitmentCentreID = cast(b1.brokerno as varchar(10)) left join RSlead.dbo.broker b2 on c.SalespersonID = b2.brokercode left join Installer i on c.FitmentCentreID = cast(i.installerID as varchar(15)) WHERE rscon.endperiod > @debperiodno --and rscon.startperiod < = @debperiodno AND U.IsActive = 1 AND CA.IsActive = 1 AND C.IsActive = 1 order by U.Box_No desc ; --=========================================CA with service charges========================================================= INSERT INTO [AllUnitsReportDaily] SELECT Distinct UT.UnitTypeDesc AS [TYPE], C.ContractID, U.Box_No, A.RegistrationNumber, CL.AccountName, 'MixServices', SUM(conserv.serviceamt) AS [chargeamt], '0.00' as NormalChargeAmt, conserv.startperiod, C.CreateDate AS [ContractDate], conserv.accountno AS [AccountNo], C.salespersonid AS [SalesPersonID], b2.brokername as SalesPersonDesc, C.FitmentCentreID AS [FitmentCentreID], b1.brokername as [FitmentCentreName], CASE WHEN l.contracttypeno = 2 THEN 'RENTAL CONTRACT' ELSE 'CASH CONTRACT' END AS 'Contract Type', ISNULL(CCT.contracttypedesc,CCT1.contracttypedesc) AS ContractTypeDesc, CAST(ISNULL(aic.PolicyNumber,'') AS CHAR(30)) AS 'PolicyNumber', ISNULL(C.UniqueNumber,'') AS 'UniqueNumber', ISNULL(l.leadno,ML.LEADID) AS LeadNo, ISNULL(l.brokerno,LP.BrokerID) AS BrokerNo, ISNULL(ISNULL(B.brokercode,LP.ExternalPartnerCode),'') AS ExternalPartnerCode, ISNULL(L.idno,LP.IDNumber) AS IDNumber, zl.MSISDN, LPT.CreateDate AS LastPassedTestDate, a.ChassisNumber, tcr.CompanyRegistrationNumber, ISNULL(l.createopr,ML.CreateOpr) AS LeadCreatedBy, c.editOpr as [ContractModifiedBy], ama.AssetMakeDesc, am.AssetModelDesc FROM unit U WITH (NOLOCK) JOIN unittype UT WITH (NOLOCK) ON U.UnitTypeID = UT.UnitTypeID JOIN ContractAsset CA WITH (NOLOCK) ON CA.AssetID = U.AssetID JOIN CONTRACT C WITH (NOLOCK) ON C.ContractID = CA.ContractID JOIN Asset A WITH (NOLOCK) ON CA.AssetID = A.AssetID JOIN dbo.assetmodel AM WITH (NOLOCK) ON a.assetmodelid = am.assetmodelid JOIN dbo.assetmake AMA WITH (NOLOCK) ON am.assetmakeid = ama.assetmakeid JOIN Client CL WITH (NOLOCK) ON CL.ClientID = C.ClientID JOIN rsdebtors.dbo.contractservice conserv WITH (NOLOCK) ON conserv.contractno = ca.ContractID LEFT JOIN AssetInsuranceCompany AIC WITH (NOLOCK) ON CA.AssetID = AIC.AssetID LEFT JOIN rslead.dbo.lead l WITH (NOLOCK) ON CA.ContractID = l.contractid AND l.eventtypecode = 'PUSHTOFX' LEFT JOIN RSlead.dbo.broker B WITH (NOLOCK) ON L.externalpartnerno = B.brokerno LEFT JOIN MixControl.dbo.lead ML WITH (NOLOCK) ON ML.contractid = c.ContractID LEFT JOIN MixControl.dbo.LeadPerson LP WITH (NOLOCK) ON LP.LeadPersonID = ML.LeadPersonID LEFT JOIN RSLead.dbo.contracttype CCT WITH (NOLOCK) ON CCT.contracttypeno = L.contracttypeno LEFT JOIN RSLead.dbo.contracttype CCT1 WITH (NOLOCK) ON CCT1.contracttypeno = ML.contracttypeID LEFT JOIN #navisionUnitSerials zl ON U.Box_No = zl.box_no LEFT JOIN #LastPassedTestDate LPT WITH (NOLOCK) ON U.BOX_NO = LPT.Box_No left join #tempCompanyReg tcr on cl.ClientID = tcr.ClientID COLLATE SQL_Latin1_General_CP1_CI_AS left join RSlead.dbo.broker b1 on c.FitmentCentreID = cast(b1.brokerno as varchar(10)) left join RSlead.dbo.broker b2 on c.SalespersonID = b2.brokercode WHERE @debperiodno BETWEEN conserv.startperiod AND conserv.endperiod AND U.UnitTypeID = 7 --ca AND U.IsActive = 1 AND CA.IsActive = 1 AND c.IsActive = 1 GROUP BY C.ContractID, U.Box_No, A.RegistrationNumber, CL.AccountName, conserv.startperiod, C.CreateDate, conserv.accountno, C.salespersonid, b2.brokername, C.FitmentCentreID, b1.brokername, CASE WHEN l.contracttypeno = 2 THEN 'RENTAL CONTRACT' ELSE 'CASH CONTRACT' END, CCT.contracttypedesc, CCT1.contracttypedesc, aic.PolicyNumber, C.UniqueNumber, UT.UnitTypeDesc, ISNULL(l.leadno,ML.LEADID), ISNULL(l.brokerno,LP.BrokerID), ISNULL(B.brokercode,LP.ExternalPartnerCode), ISNULL(L.idno,LP.IDNumber), zl.MSISDN, LPT.CreateDate, a.ChassisNumber, tcr.CompanyRegistrationNumber, ISNULL(l.createopr,ML.CreateOpr), c.editOpr, ama.AssetMakeDesc, am.AssetModelDesc COLLATE SQL_Latin1_General_CP1_CI_AS

Help in Dynamic query....

Posted: 12 Sep 2013 04:59 PM PDT

Hi all,I am new to this dynamic sql concept and I have written a script uaing dynamic sql but I am getting while executing it...[code="sql"]Declare @ServerName nvarchar(100)='[OTINSRCPELODD01\SFA]', @databasename nvarchar(100)='[ESSOTISNL_73DEV]' , @sql nvarchar(4000) SET @sql = 'SELECT DISTINCT Sol.EquipmentOfficeId, Ofc.CompanyID, DRD.ProjectId, DRD.ProposalId, DRD.SolutionId, DRD.UnitId, DRD.DeviationNo, I.IsRevisedRequest, DRD.DeviationStatus, DRD.RequestDate, DRD.RequestedBy, DRD.RepliedBy, DRD.DeleteFlag, DRD.DateAdded, DRD.AddedBy, DRD.DateChanged, DRD.ChangedBy, DRD.ReplyDate FROM'+ @ServerName +'.'+ @Databasename +'.dbo.'+ 'DeviationRequestDetails DRD WITH(NOLOCK) INNER JOIN'+ @ServerName +'.'+ @Databasename +'.dbo.'+ 'vw_MaxDeviation C ON C.ProjectId = DRD.ProjectId AND C.ProposalId = DRD.ProposalId AND C.SolutionId = DRD.SolutionId AND C.UnitId = DRD.UnitId AND C.RID = DRD.RequestId INNER JOIN'+ @servername+'.'+@Databasename+'.dbo.'+ 'DeviationRequestDetailsInstallation I WITH(NOLOCK) ON DRD.OfficeId = I.OfficeID AND DRD.ProjectID = I.ProjectId AND DRD.ProposalId = I.ProposalID AND DRD.SolutionID = I.SolutionId AND DRD.UnitID = I.UnitId AND DRD.RequestId = I.RequestId INNER JOIN'+ @servername+'.'+@Databasename+'.dbo.'+'Solution Sol WITH(NOLOCK) ON Sol.OfficeId = DRD.OfficeId AND Sol.ProjectId = DRD.ProjectId AND Sol.ProposalId = DRD.ProposalId AND Sol.SolutionId = DRD.SolutionId INNER JOIN'+ @servername+'.'+@Databasename+'.dbo.'+ 'vw_Office Ofc ON Ofc.OfficeID = DRD.OfficeId'--SELECT @sql EXEC sp_executesql @sql,@databasename,@servername --select * from [OTINSRCPELODD01\SFA].ESSOTISCLC_73DEV.dbo.ssislastrun[/code]When I select the query from SELECT @sql and run it it runs succesfully but when I run using sp_execute sql it gives me error:[b]Incorrect syntax near 'ESSOTISNL_73DEV'.[/b]

sum in tsql

Posted: 12 Sep 2013 05:05 AM PDT

Hi,I have a question and see if people can help me out in this, I was trying to sum the column number up, but somehow if I do select " + @GrandTotalCol + " from ##temp1, it doesnot sum the column, however if I do select ISNULL([column1],0) + ISNULL ([column2],0) then it works. my @GrandTotalCol variable does print ISNULL([column1],0) + ISNULL ([column2],0). I am not sure what did I wrong. any help and clue would be appreciate.thanks/* GRAND TOTAL COLUMN */DECLARE @GrandTotalCol NVARCHAR (MAX)SELECT @GrandTotalCol = COALESCE (@GrandTotalCol + 'ISNULL ([' + CAST (ORGANIZATIONID_NAME AS nvarchar(max)) +'],0) + ', 'ISNULL([' + CAST(ORGANIZATIONID_NAME AS nvarchar(max))+ '],0) + ')FROMREQUESTSSET @GrandTotalCol = LEFT (@GrandTotalCol, LEN (@GrandTotalCol)-1)select reporttypename," + @GrandTotalCol + "from ##temp1
Posted by at No comments:

[SQL Server 2008 issues] Finding 2nd and 4th Saturdays of Current Year

[SQL Server 2008 issues] Finding 2nd and 4th Saturdays of Current Year

Finding 2nd and 4th Saturdays of Current Year

Posted: 12 Sep 2013 06:25 PM PDT

Hi all,Please help me to find out 2nd and 4th saturdays of current year.Thanks and RegardsShirish Phadnis

Compare View and Table...

Posted: 12 Sep 2013 03:52 AM PDT

A Table and View having same structure and amount of data 40 Columns and 1.5 Million Data...This data has to be fetched by a BI Tool Which will give better performance...

Conflict between Commvault and SQL Server Backup

Posted: 12 Sep 2013 04:19 PM PDT

I have a strange sittuation here, On one of the live server(2008 R2+SP2) we have Commvault as well as SQL Native backups enabled, below are the schedule details:A]Commvault Backup Schedule a)7:30PM-Full backup(daily) b)9:00 PM – 11:00PM log backup(every 2 hours), c)11:00PM-7:00AM-no backup at all d)7:00 AM to 7:00PM-log backup(every 2 hours) and the cycle continues.B]SQL Native Backup Schedule a)11:50 PM-Full Backup(Daily)Please hold your laugh and spare the questions as why? how? etc., It has been recently overtaken by our team.My question is:1)Will the SQL Native backup at 11:50 PM, break the commvault log backup chain?2)If yes, will i be able to use Commvault log backups taken after SQL Native(Full) backup in case of point in time recovery?(ie keeping SQL Native Full backup as base and applying commvault log backups sequentially)

Need to run few sql scripts and store the results in a CSV file

Posted: 12 Sep 2013 02:37 PM PDT

Hi All,I have a requirement where in, I need to automate to run all the scripts from a specific location like 'c:\xyz' on a sqlserver and then need to store the results in a CSV. Each script results has to be stored in individual sheet of CSV. Appreicate your help.Thanks,Vamsi

Error DTExec: The package execution returned DTSER_FAILURE (1)

Posted: 09 Sep 2013 09:45 PM PDT

Hi,A Maintenance Plan Job for UpdateStatistics is scheduled from SQL Server 2008 and it is a weekly job (Sunday). Last Sunday it failed with the below error. Can some body help me to fix the issue.Error:Executed as user: ABC\XXX. Microsoft (R) SQL Server Execute Package Utility Version 10.50.1600.1 for 64-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Started: 6:00:02 AM Progress: 2013-09-08 06:00:11.52 Source: {9B20A854-9645-4762-B4C7-8B5DA1A19210} Executing query "DECLARE @Guid UNIQUEIDENTIFIER EXECUTE msdb..sp...".: 100% complete End Progress Error: 2013-09-08 06:22:09.05 Code: 0xC0024104 Source: Update Statistics Description: The Execute method on the task returned error code 0x80131501 (An exception occurred while executing a Transact-SQL statement or batch.). The Execute method must succeed, and indicate the result using an "out" parameter. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 6:00:02 AM Finished: 6:22:09 AM Elapsed: 1327.41 seconds. The package execution failed. The step failed.Thanks in advanceTapas

How to import a sql server database ?

Posted: 12 Sep 2013 02:03 PM PDT

I have installed SQL Server 2008 - R2 version in my local machine.I want to import database from another remote SQL Server 2008 database inside a LAN.How to do this ? Can you please tell me the steps ?Also , do I have to create user and give permission in my database after import ? or users and permission also be copied from remote database to my database ?

temp table in ssis

Posted: 12 Sep 2013 10:40 AM PDT

I have a store procedure that uses temp tables and CTE . I am calling the stored procedure from Oledb source in data flow task in ssis. It gives me some error like " The metadata could not be determined because statment ' WITH CTE1 AS (...... unable to retrieve column information from the data source. Make sure the target table in the database is available. There seem to be dislike/disconnect between temp table and ssis oldb source using stored proc- anybody has any idea for resolving this?Also i am trying to push data to a database that is in a completely different network. I can ping it but it is not visible through sql server management or ssis. how can dump data into a table in a different network-ed database?

Select record from group

Posted: 12 Sep 2013 01:19 AM PDT

I was assigned a difficult project. It is beyond by skill. I need expert to help me.From sample data, 1) Group by ID and COLOR2) From this group, select ID, min(DATEFROM),max(DATETO) and Color and then insert into a new table,3) For example, goup 111 and blue, select min(DATEFROM)=01/10/2012 and max(DATETO)=03/16/20124) Keep on selecting until end file.Here is sample data:ID DATEFROM DATETO COLOR111 01/10/2012 01/11/2012 BLUE111 02/02/2012 02/02/2012 BLUE111 03/15/2012 03/16/2012 BLUE111 04/05/2012 04/05/2012 GREEN111 05/25/2012 05/26/2012 GREEN111 06/06/2012 06/16/2012 GREEN111 07/17/2012 07/17/2012 BLUE111 09/08/2012 10/10/2012 BLUE222 01/10/2013 01/11/2013 RED222 02/02/2013 02/02/2013 RED222 03/15/2013 03/16/2013 RED222 04/05/2013 04/05/2013 GREEN222 05/25/2013 05/26/2013 GREEN222 06/06/2013 06/16/2013 GREEN222 07/17/2013 07/17/2013 BLUE222 09/08/2013 10/10/2013 BLUEFinally, I need select records and insert a new table like below:111 01/10/2012 03/16/2012 BLUE111 04/05/2012 06/16/2012 GREEN111 07/17/2012 10/10/2012 BLUE222 01/10/2013 03/16/2013 RED222 04/05/2013 06/16/2013 GREEN222 07/17/2013 10/10/2013 BLUE

get only the alphabets from string

Posted: 12 Sep 2013 11:08 AM PDT

i have productname column in my table and the query i need to get only the alphabets.column contains the special characters (!@#$%^&*():'"{}[]\|?-+=,) and numbers(0-9), spacesample dataFoodhold USA,mlc.Beverage Partners Worldwide (North canada)......Bread World 8my expected output will be FoodholdUSAmlcBeveragePartnersWorldwide(Northcanada)BreadWorld[code="sql"]SELECT productname, SUBSTRING(Name,1,ISNULL(NULLIF(PATINDEX('%[^A-Za-z.''0-9]%',LTRIM(RTRIM(productname))),0)-1,LEN(productname))) AS noSpecials FROM Manufacturer [/code]but it is not working. can anyone please show me sample query..

Best way to move Several Million Record query results to a table?

Posted: 12 Sep 2013 04:29 AM PDT

I have a query that will utlimatley return several million records. The query itself is too much for the memory on the particular server it's on; however, we used the Import Export wizard to push it to a table and it zips right along. Can anyone explain why this is more efficient? The wizard is using the same query as it's source. Indexes are on the join fields.ThanksCrusty

Set DTS Globalvariable & file path from Java

Posted: 12 Sep 2013 09:09 AM PDT

Hi All,Greetings!!!I am new to DTS and i need some help regarding the followingOur environment is SQL 2008 but we have 2005 backward compatability pack on our server so we have dtsrun.exe.We have the DTS placed on a shred drive and by using xp_cmdshell we run the dts packages. Our application was based on MS Access before and now we are trying to migrate to Java. We have a stored procedure that we call from java to run the DTS.The Java application will run on a unix machine.Currently we are able to execute the DTS from the java application but we are facing difficulties updating the global variables. I tried thisexec databasename..xp_cmdshell 'DTSRun.exe /a VariableName:String="21713-1" /f "D:/DTS/Load table.dts"' but when i execute this the query is running for long time and nothing is happening.I also have one more DTS, which accepts the input file and updates the tables. The user can browse the file from any location and upload. In the DTS, if i right click for the connection properties we have a kept the datasource as text source and hardcoded a file name now. How can we update the file name location from java.This is what we have in Access applicationmyPkg.Connections("Load File").DataSource = stFilePlease help

Set DTS Globalvariable & file path from Java

Posted: 12 Sep 2013 08:59 AM PDT

Hi All,Greetings!!!I am new to DTS and i need some help regarding the followingOur environment is SQL 2008 but we have 2005 backward compatability pack on our server so we have dtsrun.exe.We have the DTS placed on a shred drive and by using xp_cmdshell we run the dts packages. Our application was based on MS Access before and now we are trying to migrate to Java. We have a stored procedure that we call from java to run the DTS.The Java application will run on a unix machine.Currently we are able to execute the DTS from the java application but we are facing difficulties updating the global variables. I tried thisexec databasename..xp_cmdshell 'DTSRun.exe /a VariableName:String="21713-1" /f "D:/DTS/Load table.dts"' but when i execute this the query is running for long time and nothing is happening.I also have one more DTS, which accepts the input file and updates the tables. The user can browse the file from any location and upload. In the DTS, if i right click for the connection properties we have a kept the datasource as text source and hardcoded a file name now. How can we update the file name location from java.This is what we have in Access applicationmyPkg.Connections("Load File").DataSource = stFilePlease help

Early Month end processing, How do you handle the dates?

Posted: 12 Sep 2013 03:35 AM PDT

We have always waited until the first of each month to start our month end processing. We would use code like the following to determine the prior months last day.[code="sql"]declare @runDate datetime;declare @evalDt datetime;set @runDate = convert(char(10),getdate(),101);set @evalDt = convert(char(10),(dateadd(dd, - datepart(dd,@runDate) , @runDate)),101);[/code]We have been asked by management for awhile now that they want us to start processing sooner. Like when the first falls on a Monday or Sunday to be able to start on Saturday. So we have thought about how to make this work with all of the processes that have the above calculation, something similar would be done to find the first day of the prior month. So we looked at the dates through the years and determined that the earliest date we could potentially need to start would be the 26th, we always have Thanksgiving and the day after off. So we started adding the following code to our processes.[code="sql"]set @rundate = (case when datepart(dd, GETDATE()) >= 26 then dateadd(dd, 10, GETDATE()) else GETDATE() end);[/code]Now management is throwing us a new curve. For this coming November they want to cut off our month at 11/22/2013. And they want anything processed after that date to fall into December results. As you can see the logic we were adding isn't going to work for this situation.So my thoughts went to setting up a table that we could update that would contain the start date for that month and an end date. So in the this situation I would have a start date for the month of November as 11/01/2013, the end date would be 11/22/2013, these all would go under the month end date of 11/30/2013. Then for December the start date for the month would be 11/23/2013 and the end date would be 12/31/2013, month end date for these would be 12/31/2013.I'm curious to know if or how others handle their dates? Do you think I'm on the right track with using a table? Any issues you've run into?Thanks,

Sql 2008 - Query Response Tiem v/s High CPU and IO Related Query

Posted: 12 Sep 2013 06:17 AM PDT

Hi,I have a question regarding Query tuning that if it shows high CPU and IO intesive query but resposne time is ok so still nedds to be tuned or optimized?Example:While Runing tining script, if it shows Total_CPU_Time = 2094320785, MAx_CPU_Time = 684039, Execution counts = 94903 and total Logical Reads = 602159535 but query response time is ok then what will be the effect if i don't create the Index on filtered criteria or suggested index from the Execution plan or Tuning Advisor?I have Index also ReBuilded and updated the stats.I would liek to know any performance impact within a IO and COU intensive query but resposne time is ok.Thanks.

Delay in Update Large Table Wtih Trigger

Posted: 12 Sep 2013 01:35 AM PDT

Hi all,I have the following statement in a trigger, in order to update a link table that has 5000000 records.UPDATE LINK.DATABASE.dbo.PPP1 SET KODAN= @ITEMID ,ONANT=@ITEMNAME --,REMOTE_PPP1.KODKA=SUBSTRING(I.ITEMGROUPID,1,17) ,KODKA=SUBSTRING(@AMDEVICEBRANDID,1,17) ,MM=SUBSTRING(@UNITID , 1 , 5) ,TVAL=@AMOUNT ,THON=@WHOLESALEAMOUNT WHERE KODAN2= cast(@REFRECID as char(25))When I run it from managemet studio it runs quickly. When it runs from the trigger it takes about 30 minutes to do the update.I would appreciate if someone could help.Thanks,John

Import Text File to SQL Database

Posted: 12 Sep 2013 03:08 AM PDT

I have a text file with no delimiters and fixed width fields that I need to import into a SQL Server database. An example of the text file is:012345678901012013TYPEADESC FIELD 8.00123456789001022013TYPEBDESC FIELD 14.00234567890101032013TYPECDESC FIELD .75Text File is set up as EmpID: 10Date: 8 (mmddyyyy)Type: 5Desc: 12Hours: (6,2) DecimalSQL Server table is set up as:Date (datetime)Type: varchar(5)Desc: varchar(12)Hours: decimal(6,2)EmpID: varchar(10)The problem I'm having is with the Date field. How do I convert the date from the text file (mmddyyyy) to the sql server date field (yyyy-mm-dd hh:mm:sss)?

Cluster Upgrade -- some suggestions please

Posted: 12 Sep 2013 05:04 AM PDT

We currently have a three node cluster running on Windows Server 2008 SP1 (not R2) and two of the Nodes are Active with four MS SQL 2008 SP1 Instances on each and the third node is passive for all 8 instances. Below I'll call the Active Nodes A1 and A2 and the Passive node P1 just to keep it simple :)I need to install several updates plus MS SQL 2012 as a new Instance on the cluster, but given this requires many updates to accomplish I wanted to bounce our suggested plan to the group in-case others have experienced problems going this route or with any of these updates.Here are the updates and applications we plan on rolling out:- KB948465 – Windows Server 2008 Service Pack 2- KB956250 – Microsoft DotNet 3.5 Service Pack 1 Update- KB2546951 – Microsoft SQL Server 2008 SP3 on all Instances- KB968930 – Windows Management Framework Core Package (WinRM 2.0 and PowerShell 2.0)- Microsoft .NET Framework 4.0 – Required for future software updates- Microsoft OLEDB Provider for DB2 v4.0My plan is to install Windows Service Pack 2 on the Passive node (P1), then after reboot move all Instances from A1 to P1 then back to A1, then move all instances from A2 to P1 and back just to verify everything moves across okay. If no hiccups then we'll move all instance from A1 to P1, install Windows SP2 to A1, then move the instances back and do the same to A2.Next we'll install KB956250 then KB2546951 on the Passive node (P1) and move the four instances on A1 and A2 over to P1, one node at a time, and if everything moves without issues move the Instances on each node one at a time to P1 and update each node as before.. After all this I'll install KB968930, DotNet 4.0, and OLEDB Provider for DB2 to passive node then once again move everything over and back one node at a time to test, and if all works install on Active nodes as before.Then after all this is done I should be ready to install SQL 2012.. Sheesh! It's a lot, but I'm not sure how else to get everything caught-up and tested in between updates so if something fails I can pinpoint the cause. Any other suggestions or ideas for this? Or has anyone ran into caveats with any of these updates on a Failover Cluster?Thanks,Sam

Datediff in means

Posted: 11 Sep 2013 11:52 PM PDT

Hi all,Please what is the syntax for datediff in minutes.I have tried the expression datediff("m", fields!startddate.value, fields!enddate.value) but it is not working. I have equally use the full minutes as in "minutes" but still not working. "mm" does not work either.

Correct permissions after restore from another domain?

Posted: 12 Sep 2013 03:27 AM PDT

Hi,I've been restoring databases from another domain. As a result the users that are restored with the db have the user accounts from the other domain, and are not linked to a login.So I've been going in to each db I restore and looking at the permissions, and then adding a login with the matching permissions to that db with the correct domain account. The names for the most part are the same just with a different domain prefix.This is taking forever, because I'm also removing the old domain db users, and I have to change schema owners in some cases to delete the user.Has anyone found a faster way to do this, even just a little bit faster? If you can help thanks....

How to add this condition into where clause? Thanks.

Posted: 12 Sep 2013 12:44 AM PDT

I need to have a query like:declare @dt bitselect * from document where case @dt when 1 then doctype in (1, 2) when 0 then doctype = 1 end How do I write the query? Thank you in advance

replication

Posted: 11 Sep 2013 08:09 PM PDT

i did not implement replication in my current environment , i have 4 publishers iwant to know how many distribution and log agents for this ?

SSAS Instal in a Cluster

Posted: 11 Sep 2013 07:23 PM PDT

I need to install SSAS as an extra feature on a 2 node SQL Cluster. This will have to run as a separate instance, what is the process to get it installed. Are there any gotcha'sI imagine I need to pause the node and install SSAS. The failover and do the same?

Relation ship in SQL Server

Posted: 11 Sep 2013 11:38 PM PDT

i want to create Primary key for 3 columns in parent table and foreign key in for 2 coloumns in child table.can i refer these 2 foreign key columns to those 3 primary key columns ?

MS Security patch MS11-049 (KB2494086) fails on SQL R2 build version 10.50.1753.

Posted: 12 Sep 2013 12:24 AM PDT

Trying to install MS Security patch MS11-049 (KB2494086) on SQL Server 2008 R2 build version 10.50.1753.The patch installation fails in midway with the log file error message as :Overall summary: Final result: The patch installer has failed to update the following instance: MSSQLSERVER. To determine the reason for failure, review the log files. Exit code (Decimal): -2061893602 Exit facility code: 1306 Exit error code: 30 Exit message: The patch installer has failed to update the following instance: MSSQLSERVER. To determine the reason for failure, review the log files. Start time: 2013-08-31 16:48:44 End time: 2013-08-31 16:50:03 Requested action: PatchInstance MSSQLSERVER overall summary: Final result: The patch installer has failed to update the shared features. To determine the reason for failure, review the log files. Exit code (Decimal): -2061893602 Exit facility code: 1306 Exit error code: 30 Exit message: The patch installer has failed to update the shared features. To determine the reason for failure, review the log files. Start time: 2013-08-31 16:49:20 End time: 2013-08-31 16:50:02 Requested action: Patch Log with failure: C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\20130831_164844\MSSQLSERVER\Detail.txt Exception help link: http%3a%2f%2fgo.microsoft.com%2ffwlink%3fLinkId%3d20476%26ProdName%3dMicrosoft%2bSQL%2bServer%26EvtSrc%3dsetup.rll%26EvtID%3d50000%26ProdVer%3d10.50.1600.1%26EvtType%3d0x4E0712AD%400x2F2F19BE%401306%4030Only the Database Engine Services is not getting patched. The remaining components seem to have got patched as below :[size="2"] Product Instance Instance ID Feature Language Edition Version Clustered Sql Server 2008 R2 MSSQLSERVER MSSQL10_50.MSSQLSERVER Database Engine Services 1033 Enterprise Edition 10.50.1753.0 No Sql Server 2008 R2 Management Tools - Basic 1033 Enterprise Edition 10.50.1790.0 No Sql Server 2008 R2 Management Tools - Complete 1033 Enterprise Edition 10.50.1790.0 No Sql Server 2008 R2 Client Tools Connectivity 1033 Enterprise Edition 10.50.1790.0 No Sql Server 2008 R2 Client Tools Backwards Compatibility 1033 Enterprise Edition 10.50.1790.0 No Sql Server 2008 R2 Client Tools SDK 1033 Enterprise Edition 10.50.1790.0 No Sql Server 2008 R2 Integration Services 1033 Enterprise Edition 10.50.1790.0 No [/size] Tried repair and install multiple times, but the patch install fails. Let me know if you require any more details.Can someone please help. Urgent.

SQL job owner

Posted: 11 Sep 2013 10:15 PM PDT

What is the purpose and significance of the SQL job owner setting on a job?I've never considered this before, but it has come up today as we have found jobs where the owner was set to a particular user account.How is this property supposed to be used? What is the significance of having it set to a particular account?Thanks.

Extracting Data From a MySQL Linked Server

Posted: 17 Dec 2010 12:53 AM PST

Hi all,I am using MS SQL 2008, and I am trying to extract data from a MySQL database. I am having trouble extracting the data I need, and I was unable to find a post where someone had a similar issue.In SQL Server management studio, I can see the linked server...I can browse the different databases on the server. I can see user and system tables in all of the databases.When I try and query a linked table (select * from server.db.table) I get Invalid object name 'servername.databasename.tablename'.When I try script the table.. right_click on the linked table, Script table as, Select to, New Query Editor window, I get an error '[servername].[databasename]..[tablename] contains no columns that can be selected or the current user does not have permissions on that object.'This leads me to believe that it is a permissions problem, but if I have access to the MySQL database using MySQL and the same login/password) and can retrieve the data there, then I think my login credentials should be enough using MS SQL. I guess I think its odd that I could have enough credentials to get in and see table names, but not do a select against it.I have read articles on this forum about people who couldn't see the tables in the explorer, but could access them in a query. I've read articles where people could see some system tables, but not user tables. I find it weird that I can see them ALL in the explorer, but can't access any of them.Any help would be greatly appreciated.Thanks,Bill

Run Query and email results as CSV.

Posted: 11 Sep 2013 07:05 PM PDT

Morning everyone.I have a query that works ok , and uses msdb.dbo.sp_send_dbmail to send the results.The results in .csv look terrible.Can anyone recommend anything to format the results ?Query results to grid look fine , results to text look terrible. Does anyone have any recommendations on how to out put in a better format ?many thanks
Posted by at No comments:
Subscribe to: Posts (Atom)

Search This Blog