Tuesday, September 17, 2013

[SQL Server 2008 issues] Merge with more then one insert statement

[SQL Server 2008 issues] Merge with more then one insert statement

Merge with more then one insert statement

Posted: 16 Sep 2013 01:43 AM PDT

Table1 :id, NameTable2:id, name1,name2,name3,name4,name5MERGE Table1 t1USING table2 t2ON t1.id = t2.idWHEN NOT MATCHED THEN INSERT(name)values(t2.name1)values(t2.name2)values(t2.name3)values(t2.name4)values(t2.name5)as like this i have 16 names in my table(t2.name1 ..... t2.name16)is it possible to do? can we write multiple insert statement in merge clause? if not would like to know the best way to to that.Note : id in table1 is identity column

Lookup tables - when too ude them

Posted: 16 Sep 2013 01:36 AM PDT

Hi All,I need to create an attribute table for say a product. Each of the attributes for a product can have have different fixed 'look-up' types.My question is do I create a look-up table for each type or enter the look-up value straight into the table. The types are fixed and will rarely ever change.I will probably have about 2000 - 3000 rows in the the tablee.gProduct = T-shirtattribute 1 - colour : red/blue/pink/green/black/whiteattribute 2 - size : XS/S/M/L/XLattribute 3 - cut : slim/standard/taylorSo do have this all stored in the product_attributes table:Product ID - 1Colour - redsize- Mcut - standardOr use look up tables with idsProduct ID - 1Colour - 1 - foreign key to product_colour tablesize- 3 - foreign key to product_size tablecut - 2 - foreign key to product_cut tableI want to do look up tables as i feel that it is best practice, but for querying the data it would be simpler to have the data straight in the table.Also there wont be million rows (max 10,000) in the Table so searching the data should still remain relatively quick.Many thanksD

Temporary table created in parent SP persists in child SP

Posted: 16 Sep 2013 05:42 PM PDT

Many of you may know this but I found below observation today and though of posting it:I have creted One procedure test1 and created temp table in it. Executed another procedure test2 and fetch temp table created inparent procedure test 1. it ran fine.**********************************************************************************************create procedure dbo.test2asbegin select * from #temptabletestendcreate procedure dbo.test1asbegin create table #temptabletest ( col1 int ) insert into #temptabletest values(1) insert into #temptabletest values(2) insert into #temptabletest values(3) exec dbo.test2 endExec dbo.test1

Second Highest

Posted: 15 Sep 2013 10:27 PM PDT

Could anyone know how to write the query fro fetching the second highest salary without using top, rowcount and rank keywords?

Executing Scribe packages with stored procedure in SQL

Posted: 15 Aug 2012 05:45 AM PDT

Hi,Is there anybody that has any experience launching a scribe package (dts) with in a stored procedure on a SQL 2008 R2). I have recently done an upgrade of our existing setup from a SQL 2005. The issue I'm facing is that I cannot launch my Scribe packages. It has been done with the help of xp_cmdshell and there after with sp_excutesql. The challenge is also that there are also linked servers involved. My SQL server and Scribe is linked.I will be very grateful for A N Y help or hint out there!Cheers :-)

error in mirroring

Posted: 16 Sep 2013 04:23 PM PDT

am getting the following error, when i start executing mirroring after the endpoints configuredTITLE: Database PropertiesAn error occurred while starting mirroring.ADDITIONAL INFORMATION:Alter failed for Database 'AdventureWorks2008R2'. (Microsoft.SqlServer.Smo)[left][/left]"An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)The server network address "TCP://panendarrao-PC:5023" can not be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational. (Microsoft SQL Server, Error: 1418)"BUTTONS:OK

IO stalls --very high in PROD

Posted: 16 Sep 2013 03:54 PM PDT

Hi, I'm using the below query to find out the IO stalls on my SharePoint databases and the values are very very high tempdb and all other content databases also.[quote]SELECT DB_NAME(database_id) AS [Database Name] , file_id , io_stall_read_ms , num_of_reads , CAST(io_stall_read_ms / ( 1.0 + num_of_reads ) AS NUMERIC(10, 1)) AS [avg_read_stall_ms] , io_stall_write_ms , num_of_writes ,CAST(io_stall_write_ms / ( 1.0 + num_of_writes ) AS NUMERIC(10, 1)) AS [avg_write_stall_ms] , io_stall_read_ms + io_stall_write_ms AS [io_stalls] , num_of_reads + num_of_writes AS [total_io] , CAST(( io_stall_read_ms + io_stall_write_ms ) / ( 1.0 + num_of_reads + num_of_writes) AS NUMERIC(10,1)) AS [avg_io_stall_ms]FROM sys.dm_io_virtual_file_stats(NULL, NULL)ORDER BY avg_io_stall_ms DESC ;[/quote][u]Results:[/u]Database Name file_id io_stall_read_ms num_of_reads avg_read_stall_ms io_stall_write_ms num_of_writes avg_write_stall_ms io_stalls total_io avg_io_stall_mstempdb 1 7379637 5193579 1.4 13426056418 7808809 1719.3 13433436055 13002388 [b]1033.2[/b]tempdb 3 7704708 5191584 1.5 13428092553 7818850 1717.4 13435797261 13010434 [b]1032.7[/b]WSS_Logging 1 3688926053 7913329 466.2 11414374 898077 12.7 3700340427 8811406 [b]419.9[/b][u]Questions:[/u]1. Is this DMV giving cumulative values for io_stall_read_ms and io_stall_write_ms from last sql service restart?2. We are taking content database full backups daily, which will take 8 hours to complete. Is full backup creates any IO on tempDB and content databases?3. Running DBCC CHECKDB on weekly, taking 8 hours to complete. Is DBCC CHECKDB is causing this high IO stalls?4. Tempdb is data files and log files are on separate drives. We have 2 mdf files on same drive with equal size since we have 2 cpus and ldf file is on separate drive. Tempdb is pre sized to 50 GB. Indexes are healthy. But still we are seeing high IO wait times?[i]Can you please give step by step details on how to run SQLIO tool to see the SAN IO stats?[b][/b][/i]

Lots of awaiting command with status sleeping in SQL Server 2008R2

Posted: 16 Sep 2013 03:43 PM PDT

Hi All,In my SQL Server 2008R2; if i run SP_WHO2 i see lots of SPID(Awaiting Command) with sleeping status. I checked and found these are consuming lots of CPU time and in program name column details are - OSQL-32, Micorsoft SQL Server Mangement Studio, Microstoft SQL Server Management Studio-Query Analyzer and some users applicaiton name. And CPU usage is shooting between 40-50% for SQLServer.exe all time since there is no active transaction too. How to troubleshoot this?Thanks in advance.

how to find staled statistics?

Posted: 16 Sep 2013 03:42 PM PDT

I'm using the below query to find when was my statistics last updated. [b]But I wanted to know how stale my statistics are [/b]? Is there any script to get that information?'USE [?];IF DB_NAME() NOT IN(''master'',''msdb'',''tempdb'',''model'')begin SELECT ''CHECKING STATS FOR '' + DB_NAME() AS ''DATABASE NAME''SELECT OBJECT_NAME(A.OBJECT_ID) AS ''TABLE NAME'', A.NAME AS ''INDEX NAME'', STATS_DATE(A.OBJECT_ID,A.INDEX_ID) AS ''STATS LAST UPDATED''FROM SYS.INDEXES AJOIN SYS.OBJECTS BON B.OBJECT_ID = A.OBJECT_IDWHERE B.IS_MS_SHIPPED = 0ORDER BY OBJECT_NAME(A.OBJECT_ID),A.INDEX_IDend'

can i apply SP3 on Microsoft SQL Server 2008 (RTM)?

Posted: 16 Sep 2013 03:25 PM PDT

SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')10.0.1600.22 RTM Standard Edition (64-bit)select @@versionMicrosoft SQL Server 2008 (RTM) - 10.0.1600.22 (X64) Jul 9 2008 14:17:44 Copyright (c) 1988-2008 Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (VM)

Query with One Column Changing by Month

Posted: 16 Sep 2013 04:27 AM PDT

Hello all, I "inherited" the following code as part of a query for a report:[code="sql"]-- Januaryif @MonthUnits = 1 begininsert into #tmpJDEnbrunits select '000' + ltrim(MCMCU), GBAN01/100, mcdl02 from [JDEPSQL1\JDEProd].JDE_PRODUCTION.PRODDTA.F0902, [JDEPSQL1\JDEProd].JDE_PRODUCTION.PRODDTA.F0006 where GBMCU = MCMCU and GBFY = @fy and GBLT = 'AU' andGBOBJ = '9900' and GBSUB = '006' andMCSTYL in ('R') AND MCMCU<' 999' AND MCRP22<>'X'end-- Februaryif @MonthUnits = 2 begininsert into #tmpJDEnbrunits select '000' + ltrim(MCMCU), GBAN02/100, mcdl02 from [JDEPSQL1\JDEProd].JDE_PRODUCTION.PRODDTA.F0902, [JDEPSQL1\JDEProd].JDE_PRODUCTION.PRODDTA.F0006 where GBMCU = MCMCU and GBFY = @fy and GBLT = 'AU' andGBOBJ = '9900' and GBSUB = '006' andMCSTYL in ('R') AND MCMCU<' 999' AND MCRP22<>'X'end[/code]And so on through December.As you can see, the only thing changing for each month is the two digits at the end of the "GBAN" field (GBAN01 for January, GBAN02 for February, etc.) based on the value of @MonthUnits (again, 1 for Jan, 2 for Feb and so on). I think there has to be a more efficient way of coding this, can anyone help?Thanks in advance.

Need help in building a SQL query?

Posted: 16 Sep 2013 06:42 AM PDT

Hi Experts,I have table called "Entries" having general information of Employees as...TableName: Entries[code="sql"]Select * from Entries where mskey = 1234[/code]MSKEY, ATTRNAME, AVALUE1234, NUMBER, 31234, FNAME, DAVID1234, MNAME, M1234, LNAME, HUDSON[code="sql"]Select * from Entries where mskey = 2345[/code]MSKEY, ATTRNAME, AVALUE2345, NUMBER, 52345, FNAME, MARK2345, LNAME, WOOD2345, EMAIL, mwood@email.comNOTE: MSKEY is the Primary Key for the table Entries. NUMBER is also primary key.I need to write a SQL query and the output should look like...NUMBER, FNAME, MNAME, LNAME, EMAIL1234, DAVID, M, HUDSON, NULL (Blank)2345, MARK, NULL (Blank), WOOD, mwood@email.comI did a SQL query as...[code="sql"]select a.avalue as [NUMBER], b.avalue as [FNAME], c.avalue as [MNAME], d.avalue as [LNAME], e.avalue as [EMAIL]from Entries.a, Entries.b, Entries.c, Entries.d, Entries.ewhere a.mskey = b.mskey and b.mskey = c.mskey and c.mskey = d.mskey and d.mskey = e.mskey and a.attrname = 'NUMBER' and b.attrname = 'FNAME' and c.attrname = 'MNAME' and d.attrname = 'LNAME' and e.attrname = 'EMAIL' and a.mskey in(select mskey from Entries where avalue in ('1234','2345'))[/code]Please help me to write a SQL query using the table "Entries". Thanks in advance.Regards,Arun

How connect Excel 2007 to Analysis Services 2008

Posted: 14 Jun 2009 01:57 AM PDT

Hi AllI am trying to use Excel 2007 to connect to my 2008 Analysis Server (64 bit SQL 2008 on Small Business Server 2008).From Excel, I go:Data --> From Other Sources --> From Analysis ServicesThen I get an error message: Unable to connect to data source. Reason: Unable to locate database server.Should this functionality work out of the box? Or do I need to install an add-in or something?Any help would be most appreciated.thanksKevin

Why is my logon trigger blocking logons?

Posted: 11 Sep 2013 08:14 AM PDT

I've created the following trigger to track logons:[code="other"]Use masterGoCreate database AuditDBGoUSE [AuditDb]GO CREATE TABLE [dbo].[ServerLogonHistory]( [SystemUser] [varchar](512) NULL, [DBUser] [varchar](512) NULL, [APP_NAME] [varchar](512) NULL, [SPID] [int] NULL, [LogonTime] [datetime] NULL, [HOST_NAME] [varchar](512) NULL) ON [PRIMARY] GO Grant insert, update, select on ServerLogonHistory to publicGo CREATE TRIGGER [Tr_ServerLogon]ON ALL SERVER FOR LOGONASBEGINif( (ORIGINAL_LOGIN() <> 'sa')and APP_NAME() not like 'SQLAgent%'and APP_NAME() not like '%IntelliSense')INSERT INTO AuditDb.dbo.ServerLogonHistorySELECT SYSTEM_USER,USER,APP_NAME(),@@SPID,GETDATE(),HOST_NAME()END GO[/code]When I run this all is good, table is in place, etc. but I tested it by logging in as another user and I'm unable to logon. I then granted permission to the table explicitly to that user and am still unable to logon. When I check the user it is a member of public. When I check the table under permissions-effective tab I get the following for public: "[i]cannot execute as the database principal because the principal "public" does not exist, this type of principal cannot be impersonated, or you do not have permission[/i]"For the other user it says: "[i]the server principal "domain\username" is not able to access the database "AuditDb" under the current security context[/i]"I'm missing something here - but am stumped as to what it is. Can anyone point out the obvious to me? :-)Oracle is my usual forte, and implementing a logon trigger is a little simpler process.

SQL Query (Powerpivot) – for Excel Headers

Posted: 16 Sep 2013 08:30 AM PDT

Hi,Today I'm using a connection to an excel file using PowerPivot and I'm using a sql query to return just the number of rows with info of that excel file , it is working perfectly however I need to do the same for multiple files and opening a connection is not really straight forward because it will add an extra tab in the data model , so my only option is to use a sql query to join the results of all the files .I've used union before, to consolidate excel files with the same number of columns and same headers so I know that what I'm trying to do is possible , here is a couple of things that I've tried .With an open connection to the excel file , this query is working[code="sql"]SELECT 'AMID_MAPG' AS WORKBOOK , COUNT(*) AS TOTAL_ROWSFROM [AMIDMapping$][/code]I'm getting the error "The SQL statement is not valid. There are no columns detected in the statement." with the next query :[code="sql"]SELECT 'AMID_MAPG' AS WORKBOOK , COUNT(*) AS TOTAL_ROWSFROM [AMIDMapping$]UNION ALLSELECT 'CTO SKU Template' AS WORKBOOK , COUNT(*) AS TOTAL_ROWSFROM 'C:\Users\josh\Desktop\Projects\IT\CTO SKU Table.xlsx'.[CTO SKU Template$][/code]Any help would be much appreciated

Any point in EVER shrinking trans log?

Posted: 15 Sep 2013 10:47 PM PDT

I have databases that generally do not accumulate much in the transaction log. There is an automated script that runs a backup stored procedure every 15 minutes, doing backup of the trans log one the quarter, half and three quarter hour and differential on the full hour during the workday, and a full every night at 11pm, all geared to execute ony if the database has undergone a change during the preceding appropriate interval.The activity is fairly light, so not much ever accumulates in the trans log. However, I occasionally do things like mass imports and such that make the trans log swell quite a lot. This does not happen much - a few times per year, but it's generally a pretty (relatively) massive undertaking. The most active database is a bit over 100 MB, its trans log is currently 560 MB, as a result of several large actions and some missed backup cycles.Is there EVER any point in shrinking the trans log, even when the growth is a result of such random and infrequent activity? Does an unnecessarily large log slow anything down, or are there any other benefits from releasing the unused space? Disk space is absolutely NOT an issue - I'm at barely 3% usage and no real expectation of that radically increasing anytime soon.

Problem with Cursor in trigger

Posted: 16 Sep 2013 03:29 AM PDT

Hi all,I have the following trigger after AvailPhysical field is updated.Although I have written that table field ITEMID must start from 'HD-' or 'MM-' or 'FT-' and ITEMGROUPID must be 'parts' it works for every ITEMID and every ITEMGROUPID and not only for them that have the above criteria.I have tried to add one more condition for ITEMID before cursor starts but again with no results.AFTER INSERT,UPDATE ASIF UPDATE(AVAILPHYSICAL)BEGIN DECLARE @ITEMID NVARCHAR(21) DECLARE @AVAILPHYSICAL NUMERIC(15,2)DECLARE @INVENTLOCATIONID NVARCHAR(15)DECLARE @ITEMGROUPID NVARCHAR(15)DECLARE @INVENTDIMID NVARCHAR(15)DECLARE outerCursor1 CURSOR FOR SELECT S.itemid, SUM(AVAILPHYSICAL),INVENTLOCATIONIDFROM INSERTED Sjoin inventtable IT ON IT.ITEMID=S.ITEMID AND IT.DATAAREAID='SRVC' and it.ITEMGROUPID='parts' JOIN INVENTDIM d on S.INVENTDIMID=d.INVENTDIMID and d.DATAAREAID=S.DATAAREAID WHERE d.DATAAREAID='001' AND d.INVENTLOCATIONID IN ('SPW100','SPN260') AND LEFT(S.ITEMID,3) IN ('HD-','MM-','FT-') GROUP BY S.ITEMID,d.INVENTLOCATIONID--,IT.ITEMGROUPID OPEN outerCursor1 FETCH NEXT FROM outerCursor1 INTO @ITEMID, @AVAILPHYSICAL,@INVENTLOCATIONID WHILE @@FETCH_STATUS = 0 BEGIN IF NOT EXISTS (SELECT ITEMID,INVENTLOCATIONID FROM ESHOPINVENTSUM WHERE ITEMID = @ITEMID AND INVENTLOCATIONID=@INVENTLOCATIONID ) BEGIN Insert into dbo.TEMPINVENTSUM (ITEMID,AVAILPHYSICAL,INVENTLOCATIONID ) Select @ITEMID,@AVAILPHYSICAL,@INVENTLOCATIONID END ELSE BEGIN UPDATE TEMPINVENTSUM SET ESHOPINVENTSUM.ITEMID =@ITEMID ,ESHOPINVENTSUM.INVENTLOCATIONID= @INVENTLOCATIONID ,ESHOPINVENTSUM.AVAILPHYSICAL = @AVAILPHYSICAL WHERE ESHOPINVENTSUM.ITEMID =@ITEMID AND ESHOPINVENTSUM.INVENTLOCATIONID= @INVENTLOCATIONID END FETCH NEXT FROM outerCursor1 INTO @ITEMID, @AVAILPHYSICAL,@INVENTLOCATIONID END CLOSE outerCursor1 DEALLOCATE outerCursor1ENDPlease help.Thanks

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-Server 2008/R2 and 2012 parallel

Posted: 15 Sep 2013 10:31 PM PDT

Hello,I have installed SQL Server 2008/R2-64Bit (Developer Edition) on Win7.Can I install SQL Server 2012 64Bit (Developer Edition) parallel on the same system?RegardsNicole ;-)

Run Dos Command in SSIS

Posted: 16 Sep 2013 01:31 AM PDT

Hi,I need to run DOS commands in SSIS 2008 to copy from one server to another server.Currently using batch files to process it.RegardsShaun

DELETE Duplicates

Posted: 16 Sep 2013 01:40 AM PDT

Hello,I did a check in my table and I found these duplicates:Key Month Usage45 Sept 46445 Sept 47128 Sept 76228 Sept 76641 Sept 56141 Sept 56847 Sept 86247 Sept 872How to delete the duplicate rows with less usage?For example for 45 Sept 46445 Sept 471I need to delete the first 45 Sept 464Thanks.

SQL releasing memory while there is free memory to use.

Posted: 16 Sep 2013 12:31 AM PDT

All,We had a situation on one of our production servers today. The max memory is set to 110 GB. At a certain point the SQL Process started to release memory and got all the way down to 50 GB. At this point it started to have performance problems for the application hooked up to it. IT also threw an error like this in the log: AppDomain 6 (mssqlsystemresource.dbo[runtime].5) is marked for unload due to memory pressure.During this period of time, about an hour the CPU level went very high. IT stayed high as the memory consumption gradually grew again. I eventually cleared the procedure cache and the CPU came down.During this period of time there was not memory pressure from any other applicaiton -- the free memory was increasing as the amount of memory sql was using decreased. I have checked windows event logs and sql errorlogs and find nothing.Has anyone seen this before? Any ideas on why this happened?ThanksJohn Tracy

Need SQL query to check Lockings as per below columns

Posted: 15 Sep 2013 10:33 PM PDT

Query to check Lock occurance--Need a SQl query which can show the details colums as belowColumns Names - dd:hh:mm:ss:msssession_idsql_textlogin_namewait_infocpuetcPlease let us know how to provide the complete query text which causes locks & which takes long time to execute??

Copy database from 2012 to 2008r2

Posted: 15 Sep 2013 09:30 PM PDT

Hi All,I have "Quiz database" in sql server 2012, Now i want copy same database in another 2008 server(SQL server 2008)I used copy database option but while execution it is throwing error. Note: I have not implemented any 2012 features in "QUIZ" database. earlier it take backup from 2008 and restored in 2012 server. Now it is not working back-word restoration.Is there any third-party tool to do this operation.Regards,RR
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog