Saturday, June 22, 2013

[SQL Server 2008 issues] Big Performance Problem with Merge Statement

[SQL Server 2008 issues] Big Performance Problem with Merge Statement

Big Performance Problem with Merge Statement

Posted: 20 Jun 2013 07:12 AM PDT

Problem Summary: Merge Statement takes several times longer to execute than equivalent Update, Insert and Delete as separate statements. Why?Details: I have a relatively large table (about 35,000,000 records, approximately 13 GB uncompressed and 4 GB with page compression - including indexes). A MERGE statement pretty consistently takes two or three minutes to perform an update, insert and delete. At one extreme, updating 82 (yes 82) records took 1 minute, 45 seconds. At the other extreme, updating 100,000 records took about five minutes.When I changed the MERGE to the equivalent separate UPDATE, INSERT & DELETE statements (embedded in an explicit transaction) the entire update took only 17 seconds. The query plans for the separate UPDATE, INSERT & DELETE statements look [i]very[/i] similar to the query plan for the combined MERGE. However, all the row count estimates for the MERGE statement are way off.Obviously, I am going to use the separate UPDATE, INSERT & DELETE statements. :-) However, I would like to understand what is happening here. I've read some forum posts that talk about various bugs in the MERGE operation. Has anyone run into this particular problem before? Does anyone know why this might happen?The actual query plans for the four statements ( combined MERGE and the separate UPDATE, INSERT & DELETE ) are attached. SQL Code to create the source and target tables and the actual queries themselves are below. I've also included the statistics created by my test run. Nothing else was running on the server when I ran the test.Please let me know if you need any further information, and [i][b]thank you[/b][/i] very much for your help! :-)Server Configuration:SQL Server 2008 R2 SP1, Enterprise Edition3 x Quad-Core Xeon ProcessorMax Degree of Parallelism = 8148 GB RAMSQL Code:Target Table:[code="sql"]USE TPS;IF OBJECT_ID('dbo.ParticipantResponse') IS NOT NULL DROP TABLE dbo.ParticipantResponse;CREATE TABLE dbo.ParticipantResponse( RegistrationID INT NOT NULL ,ParticipantID INT NULL ,ResponseDate SMALLDATETIME NULL ,CallNumber INT NULL ,ElementCategory NVARCHAR(80) NULL ,ElementNameID INT NULL ,ElementName NVARCHAR(80) NULL ,Response VARCHAR(3000) NULL ,SourceTable VARCHAR(30) NOT NULL ,SourceTableID INT NOT NULL ,SpecialistName VARCHAR(80) NULL ,ID BIGINT NOT NULL ,TransferKey INT NOT NULL ,CONSTRAINT [PK_ParticipantResponse] PRIMARY KEY ( SourceTableID ,ID ) )WITH ( DATA_COMPRESSION = PAGE ); /* Index IX_PersonElement*/CREATE NONCLUSTERED INDEX IX_PersonElement ON dbo.ParticipantResponse ( RegistrationID ASC, ParticipantID ASC, ElementName ASC ) WITH ( MAXDOP = 1, SORT_IN_TEMPDB = ON, ONLINE = OFF, DATA_COMPRESSION = PAGE );/*Index IX_ParticipantResponse*/CREATE NONCLUSTERED INDEX IX_ParticipantResponse ON dbo.ParticipantResponse ( RegistrationID ASC, ParticipantID ASC, ResponseDate ASC, ElementName ASC ) WITH ( MAXDOP = 1, SORT_IN_TEMPDB = ON, ONLINE = OFF, DATA_COMPRESSION = PAGE ); /*Index IX_ParticipantID*/CREATE NONCLUSTERED INDEX IX_ParticipantID ON dbo.ParticipantResponse ( ParticipantID ASC ) WITH ( MAXDOP = 1, SORT_IN_TEMPDB = ON, ONLINE = OFF, DATA_COMPRESSION = PAGE );/*Index IX_ParticipantCall*/CREATE NONCLUSTERED INDEX IX_ParticipantCall ON dbo.ParticipantResponse ( ParticipantID ASC, CallNumber ASC ) WITH ( MAXDOP = 1, SORT_IN_TEMPDB = ON, ONLINE = OFF, DATA_COMPRESSION = PAGE );/*Index IX_ElementParticipant*/CREATE NONCLUSTERED INDEX IX_ElementParticipant ON dbo.ParticipantResponse ( ElementName ASC, ParticipantID ASC ) WITH ( MAXDOP = 1, SORT_IN_TEMPDB = ON, ONLINE = OFF, DATA_COMPRESSION = PAGE );[/code]Source Table:[code="sql"] IF OBJECT_ID('ETL.ParticipantResponseBuild') IS NOT NULL DROP TABLE ETL.ParticipantResponseBuild; CREATE TABLE ETL.ParticipantResponseBuild ( RegistrationID INT NOT NULL ,ParticipantID INT NULL ,ResponseDate SMALLDATETIME NULL ,CallNumber INT NULL ,ElementCategory NVARCHAR(80) NULL ,ElementNameID INT NULL ,ElementName NVARCHAR(80) NULL ,Response VARCHAR(3000) NULL ,SourceTable VARCHAR(30) NOT NULL ,SourceTableID INT NOT NULL ,SpecialistName VARCHAR(80) NULL ,ID BIGINT NOT NULL ,TransferKey INT NOT NULL ); ALTER TABLE ETL.ParticipantResponseBuild ADD PRIMARY KEY ( SourceTableID, ID ); CREATE INDEX [IX_TransferKey] ON ETL.ParticipantResponseBuild ( TransferKey ) WITH ( MAXDOP = 1, SORT_IN_TEMPDB = ON ); CREATE INDEX [IX_ParticipantID] ON ETL.ParticipantResponseBuild ( ParticipantID ) WITH ( MAXDOP = 1, SORT_IN_TEMPDB = ON );[/code]Combined MERGE Statement:[code="sql"]USE TPS;DECLARE @LastKeyCompleted INT = 476161;SET STATISTICS IO,TIME ON; MERGE INTO TPS.dbo.ParticipantResponse WITH (HOLDLOCK) AS Production USING TPS.ETL.ParticipantResponseBuild AS Build ON Production.SourceTableID = Build.SourceTableID AND Production.ID = Build.ID WHEN MATCHED AND Build.TransferKey > @LastKeyCompleted THEN UPDATE SET RegistrationID = Build.RegistrationID ,ParticipantID = Build.ParticipantID ,ResponseDate = Build.ResponseDate ,CallNumber = Build.CallNumber ,ElementCategory = Build.ElementCategory ,ElementNameID = Build.ElementNameID ,ElementName = Build.ElementName ,Response = Build.Response ,SourceTable = Build.SourceTable ,SpecialistName = Build.SpecialistName ,TransferKey = Build.TransferKey WHEN NOT MATCHED BY TARGET THEN INSERT ( RegistrationID ,ParticipantID ,ResponseDate ,CallNumber ,ElementCategory ,ElementNameID ,ElementName ,Response ,SourceTable ,SourceTableID ,SpecialistName ,ID ,TransferKey ) VALUES ( Build.RegistrationID ,Build.ParticipantID ,Build.ResponseDate ,Build.CallNumber ,Build.ElementCategory ,Build.ElementNameID ,Build.ElementName ,Build.Response ,Build.SourceTable ,Build.SourceTableID ,Build.SpecialistName ,Build.ID ,Build.TransferKey ) WHEN NOT MATCHED BY SOURCE AND Production.ParticipantID IN ( SELECT ParticipantID FROM ETL.ParticipantResponseBuild ) THEN DELETE;[/code]Statistics for MERGE Statement:[code="plain"]SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.SQL Server parse and compile time: CPU time = 16 ms, elapsed time = 83 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.Table 'ParticipantResponse'. Scan count 1, logical reads 162202, physical reads 0, read-ahead reads 137853, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table 'Worktable'. Scan count 5, logical reads 41074, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table 'ParticipantResponseBuild'. Scan count 35964835, logical reads 72281824, physical reads 0, read-ahead reads 1, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.(9731 row(s) affected)(1 row(s) affected) SQL Server Execution Times: CPU time = 138981 ms, elapsed time = 143911 ms.SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.[/code]Separate UPDATE, INSERT & DELETE Statements:[code="sql"]USE TPS;DECLARE @LastKeyCompleted INT = 476161;SET STATISTICS IO,TIME ON; BEGIN TRANSACTION UPDATE dbo.ParticipantResponse SET RegistrationID = Build.RegistrationID ,ParticipantID = Build.ParticipantID ,ResponseDate = Build.ResponseDate ,CallNumber = Build.CallNumber ,ElementCategory = Build.ElementCategory ,ElementNameID = Build.ElementNameID ,ElementName = Build.ElementName ,Response = Build.Response ,SourceTable = Build.SourceTable ,SpecialistName = Build.SpecialistName ,TransferKey = Build.TransferKey FROM dbo.ParticipantResponse AS Production JOIN ETL.ParticipantResponseBuild AS Build ON Production.SourceTableID = Build.SourceTableID AND Production.ID = Build.ID WHERE Build.TransferKey > @LastKeyCompleted; INSERT dbo.ParticipantResponse ( RegistrationID ,ParticipantID ,ResponseDate ,CallNumber ,ElementCategory ,ElementNameID ,ElementName ,Response ,SourceTable ,SourceTableID ,SpecialistName ,ID ,TransferKey ) SELECT Build.RegistrationID ,Build.ParticipantID ,Build.ResponseDate ,Build.CallNumber ,Build.ElementCategory ,Build.ElementNameID ,Build.ElementName ,Build.Response ,Build.SourceTable ,Build.SourceTableID ,Build.SpecialistName ,Build.ID ,Build.TransferKey FROM dbo.ParticipantResponse AS Production RIGHT JOIN ETL.ParticipantResponseBuild AS Build ON Production.SourceTableID = Build.SourceTableID AND Production.ID = Build.ID WHERE Production.SourceTableID IS NULL; DELETE dbo.ParticipantResponse FROM dbo.ParticipantResponse AS Production LEFT JOIN ETL.ParticipantResponseBuild AS Build ON Production.SourceTableID = Build.SourceTableID AND Production.ID = Build.ID WHERE Build.SourceTableID IS NULL AND Production.ParticipantID IN ( SELECT ParticipantID FROM ETL.ParticipantResponseBuild ); COMMIT TRANSACTION[/code]Statistics for Separate UPDATE, INSERT & DELETE Statements:[code="plain"]SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.SQL Server parse and compile time: CPU time = 77 ms, elapsed time = 77 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.Table 'ParticipantResponse'. Scan count 0, logical reads 39541, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table 'Worktable'. Scan count 5, logical reads 41074, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table 'ParticipantResponseBuild'. Scan count 1, logical reads 794, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.(9731 row(s) affected)(1 row(s) affected) SQL Server Execution Times: CPU time = 483 ms, elapsed time = 483 ms.Table 'ParticipantResponseBuild'. Scan count 9, logical reads 873, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table 'ParticipantResponse'. Scan count 9, logical reads 53986, physical reads 0, read-ahead reads 15, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table 'Worktable'. Scan count 13, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.(0 row(s) affected)(1 row(s) affected) SQL Server Execution Times: CPU time = 15290 ms, elapsed time = 1955 ms.Table 'ParticipantResponse'. Scan count 2313, logical reads 7497, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table 'Worktable'. Scan count 5, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table 'ParticipantResponseBuild'. Scan count 2, logical reads 332, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.(0 row(s) affected)(1 row(s) affected) SQL Server Execution Times: CPU time = 93 ms, elapsed time = 102 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 87 ms.SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.[/code]

xml queries with exist and contains giving unexpected results

Posted: 21 Jun 2013 05:59 PM PDT

I have a query as shown below.[code="sql"]DECLARE @tbl TABLE ( id INT, col XML ) INSERT INTO @tbl VALUES (1,'<Root> <Row> <User>xyz</User> <Rowid>1</Rowid> </Row> <Maxrowid>1</Maxrowid></Root>'),(2,'<Root> <Row> <User>xyz</User> <Rowid>1</Rowid> </Row> <Row> <User>mnj</User> <Rowid>2</Rowid> </Row> <Maxrowid>2</Maxrowid></Root>'),(3,'<Root> <Row> <User>abs</User> <Rowid>1</Rowid> </Row> <Row> <User>xra</User> <Rowid>2</Rowid> </Row> <Maxrowid>2</Maxrowid></Root>') --table before---SELECT *FROM @tbl t1------------------DECLARE @id varchar SELECT @id = 'xyz' SELECT * FROM @tbl t1 WHERE col.exist('//*/text()[contains(.,sql:variable("@id"))]') = 1 [/code]I am expecting this query to output rows which has 'xyz' in xml column 'col' anywhere. But it returns all rows.(when i checked it returns all rows having 'x' in xml column. So please tell me why does it happen?where i am going wrong?Also please correct it.Also i have another problem where below query for the same above xml does not return any output.I expect it to return xml column having 'xyz' anywhere in it.So please have a look at it also and correct my errors.[code="sql"]DECLARE @id varchar SELECT @id = 'xyz' SELECT * FROM @tbl t1 WHERE t1.col.exist('//*[text()=sql:variable("@id")]')=1 [/code]

Delete data and Transaction Logs

Posted: 20 Jun 2013 09:01 AM PDT

I have already Bulk inserted 9 million records from one table into another using SSIS; my problem now is how to delete these 8M records from the original table without blowing up my transaction logs? This table is heavily used 24-7, and honestly, I don't know what 'pitfalls' might occur if I change the recovery from Full to Simple, and back again after the delete. Do the transaction logs pickup where they ended? Don't they realize somethings missing :w00t:?I found this query, where the writer said it was more efficient, but I'm just not experienced enough to know. From everything I've read, I'm assuming there is no way to bypass the transaction logs all together?DELETE FROM myProductionTable WHERE EXISTS(SELECT * FROM myNewArchiveTable as arcWHERE arc.id = myProductionTable.id)Any advice would be great!! Thanks!!

Change the system date to future date in desired database

Posted: 21 Jun 2013 10:01 AM PDT

How to change the system date in only one database?When an application accesses this database, it should show the future date. But the rest of the database should show system date and when I'm done I can set it backIs this possible in sql server. I know it's possible in Oracle.

Need Help putting a Join to this Query

Posted: 21 Jun 2013 08:26 AM PDT

Hi,I am badly stuck in making the query which I guess needs a couple of joins.I have a list of visitors whose IPs I have recorded. Now for a specific report, I need to see which country each visitor has come from.I divided this task into two part, first getting all the IPs of the user who have uniquely logged in and the second part is to search two tables with country/IP information (from ip2nation) and to get the country with the provided IP.[b]First Part - getting all the IPs of the user who have uniquely logged in------------------------------------------------------------------------[/b][quote] SELECT a.uid, a.hostname, a.timestamp, COUNT(*) AS times FROM login_activity a GROUP BY a.hostname ORDER BY times desc[/quote]This gave me IPs (hostname) of all the past-logged in users. Works fine.[b]Second Part - Get country from the two tables (both have thousands of records) by inputting IP-------------------------------------------------------------[/b][quote] SELECT c.country FROM ip2nationCountries c, ip2nation i WHERE i.ip < INET_ATON( "157.191.122.36" ) AND c.code = i.country ORDER BY i.ip DESC LIMIT 0 , 1[/quote]This works great too.Now, for the real problem. Joining these two queries, to get the country (instead of an IP) from all the logged in users. This is what I wrote:- [quote] SELECT a.uid, a.hostname, a.timestamp, c.country, COUNT(*) AS times FROM login_activity a, ip2nationCountries c, ip2nation i WHERE i.ip < INET_ATON(a.hostname) AND c.code = i.country GROUP BY a.hostname ORDER BY times desc;[/quote]This has two problems:-- it takes a lot of time to load.- it gives wrong data (showing the visits in thousands for each row).- Basically, it shows all the data wrong.Could you help me in making this SQL?Just in case, I am putting in Structure and Sample Data of all three Tables.[b]Structure/Data of the Tables is :------------------------------[/b][b]ip2nation[/b] (has a lot of data)---(Structure) [quote] CREATE TABLE ip2nation ( ip int(11) unsigned NOT NULL default '0', country char(2) NOT NULL default '', KEY ip (ip) );[/quote](Data) [quote] INSERT INTO ip2nation (ip, country) VALUES(0, 'us'); INSERT INTO ip2nation (ip, country) VALUES(687865856, 'za'); INSERT INTO ip2nation (ip, country) VALUES(689963008, 'eg'); INSERT INTO ip2nation (ip, country) VALUES(691011584, 'za'); INSERT INTO ip2nation (ip, country) VALUES(691617792, 'zw'); INSERT INTO ip2nation (ip, country) VALUES(691621888, 'lr'); INSERT INTO ip2nation (ip, country) VALUES(691625984, 'ke'); INSERT INTO ip2nation (ip, country) VALUES(691630080, 'za'); INSERT INTO ip2nation (ip, country) VALUES(691631104, 'gh'); INSERT INTO ip2nation (ip, country) VALUES(691632128, 'ng'); INSERT INTO ip2nation (ip, country) VALUES(691633152, 'zw'); INSERT INTO ip2nation (ip, country) VALUES(691634176, 'za'); INSERT INTO ip2nation (ip, country) VALUES(691650560, 'gh'); INSERT INTO ip2nation (ip, country) VALUES(691666944, 'ng'); INSERT INTO ip2nation (ip, country) VALUES(691732480, 'tz'); INSERT INTO ip2nation (ip, country) VALUES(691798016, 'zm'); INSERT INTO ip2nation (ip, country) VALUES(691863552, 'za'); INSERT INTO ip2nation (ip, country) VALUES(691994624, 'zm'); INSERT INTO ip2nation (ip, country) VALUES(692011008, 'za'); INSERT INTO ip2nation (ip, country) VALUES(692027392, 'mg'); INSERT INTO ip2nation (ip, country) VALUES(692035584, 'ao'); INSERT INTO ip2nation (ip, country) VALUES(692043776, 'na'); INSERT INTO ip2nation (ip, country) VALUES(692060160, 'eg'); INSERT INTO ip2nation (ip, country) VALUES(692191232, 'ci'); INSERT INTO ip2nation (ip, country) VALUES(692207616, 'za'); INSERT INTO ip2nation (ip, country) VALUES(692240384, 'gh'); INSERT INTO ip2nation (ip, country) VALUES(692256768, 'sd');[/quote][b]ip2nationCountries (has a lot of data)[/b]---(Structure) [quote] CREATE TABLE ip2nationCountries ( code varchar(4) NOT NULL default '', iso_code_2 varchar(2) NOT NULL default '', iso_code_3 varchar(3) default '', iso_country varchar(255) NOT NULL default '', country varchar(255) NOT NULL default '', lat float NOT NULL default '0', lon float NOT NULL default '0', PRIMARY KEY (code), KEY code (code) );[/quote](Data) [quote]INSERT INTO ip2nationCountries (code, iso_code_2, iso_code_3, iso_country, country, lat, lon) VALUES('ad', 'AN', 'AND', 'Andorra', 'Andorra', 42.3, 1.3); INSERT INTO ip2nationCountries (code, iso_code_2, iso_code_3, iso_country, country, lat, lon) VALUES('ae', 'AR', 'ARE', 'United Arab Emirates', 'United Arab Emirates', 24, 54); INSERT INTO ip2nationCountries (code, iso_code_2, iso_code_3, iso_country, country, lat, lon) VALUES('af', 'AF', 'AFG', 'Afghanistan', 'Afghanistan', 33, 65); INSERT INTO ip2nationCountries (code, iso_code_2, iso_code_3, iso_country, country, lat, lon) VALUES('ag', 'AT', 'ATG', 'Antigua and Barbuda', 'Antigua and Barbuda', 17.03, -61.48); INSERT INTO ip2nationCountries (code, iso_code_2, iso_code_3, iso_country, country, lat, lon) VALUES('ai', 'AI', 'AIA', 'Anguilla', 'Anguilla', 18.15, -63.1); INSERT INTO ip2nationCountries (code, iso_code_2, iso_code_3, iso_country, country, lat, lon) VALUES('al', 'AL', 'ALB', 'Albania', 'Albania', 41, 20); INSERT INTO ip2nationCountries (code, iso_code_2, iso_code_3, iso_country, country, lat, lon) VALUES('am', 'AR', 'ARM', 'Armenia', 'Armenia', 40, 45); INSERT INTO ip2nationCountries (code, iso_code_2, iso_code_3, iso_country, country, lat, lon) VALUES('an', 'AN', 'ANT', 'Netherlands Antilles', 'Netherlands Antilles', 12.15, -68.45); INSERT INTO ip2nationCountries (code, iso_code_2, iso_code_3, iso_country, country, lat, lon) VALUES('ao', 'AG', 'AGO', 'Angola', 'Angola', -12.3, 18.3); INSERT INTO ip2nationCountries (code, iso_code_2, iso_code_3, iso_country, country, lat, lon) VALUES('aq', 'AT', 'ATA', 'Antarctica', 'Antarctica', -90, 0); INSERT INTO ip2nationCountries (code, iso_code_2, iso_code_3, iso_country, country, lat, lon) VALUES('ar', 'AR', 'ARG', 'Argentina', 'Argentina', -34, -64); INSERT INTO ip2nationCountries (code, iso_code_2, iso_code_3, iso_country, country, lat, lon) VALUES('as', 'AS', 'ASM', 'American Samoa', 'American Samoa', -14.2, -170); INSERT INTO ip2nationCountries (code, iso_code_2, iso_code_3, iso_country, country, lat, lon) VALUES('at', 'AU', 'AUT', 'Austria', 'Austria', 47.2, 13.2); INSERT INTO ip2nationCountries (code, iso_code_2, iso_code_3, iso_country, country, lat, lon) VALUES('au', 'AU', 'AUS', 'Australia', 'Australia', -27, 133); INSERT INTO ip2nationCountries (code, iso_code_2, iso_code_3, iso_country, country, lat, lon) VALUES('aw', 'AB', 'ABW', 'Aruba', 'Aruba', 12.3, -69.58);[/quote][b]login_activity[/b]---(Structure) [quote]CREATE TABLE IF NOT EXISTS `mslop_login_activity` ( `aid` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'The primary identifier for an activity (session).', `uid` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'The mslop_users.uid corresponding to a session, or 0 for anonymous user.', `host_user_agent` varchar(256) NOT NULL DEFAULT '' COMMENT '$_SERVER["HOST_USER_AGENT"] string. This can be used with get_browser() in PHP.', `hostname` varchar(128) NOT NULL DEFAULT '' COMMENT 'The IP address that was used for this session.', `timestamp` int(11) NOT NULL DEFAULT '0' COMMENT 'The UNIX timestamp when the session was started.', PRIMARY KEY (`aid`), KEY `aid` (`aid`), KEY `uid` (`uid`), KEY `timestamp` (`timestamp`) );[/quote](Data) [quote]INSERT INTO `mslop_login_activity` (`aid`, `uid`, `host_user_agent`, `hostname`, `timestamp`) VALUES (1, 3, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10.8; rv:19.0) Gecko/20100101 Firefox/19.0', '172.24.1.143', 1363038356), (2, 3, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10.8; rv:19.0) Gecko/20100101 Firefox/19.0', '172.24.1.143', 1363038374), (3, 3, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_8_2) AppleWebKit/537.17 (KHTML, like Gecko) Chrome/24.0.1312.57 Safari/537.17', '172.24.1.143', 1363193841), (4, 3, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_8_2) AppleWebKit/537.17 (KHTML, like Gecko) Chrome/24.0.1312.57 Safari/537.17', '172.24.1.143', 1363194789), (5, 3, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_8_2) AppleWebKit/537.17 (KHTML, like Gecko) Chrome/24.0.1312.57 Safari/537.17', '172.24.1.143', 1363197889), (6, 3, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_8_2) AppleWebKit/536.26.17 (KHTML, like Gecko) Version/6.0.2 Safari/536.26.17', '172.24.1.143', 1363207361), (7, 35, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10.8; rv:19.0) Gecko/20100101 Firefox/19.0', '172.24.1.143', 1363301612), (8, 35, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10.8; rv:19.0) Gecko/20100101 Firefox/19.0', '172.24.1.143', 1363301751), (9, 1, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10.8; rv:19.0) Gecko/20100101 Firefox/19.0', '172.24.1.143', 1363364574), (10, 1, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_8_2) AppleWebKit/537.17 (KHTML, like Gecko) Chrome/24.0.1312.57 Safari/537.17', '172.24.1.143', 1363374517), (11, 1, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_8_2) AppleWebKit/537.17 (KHTML, like Gecko) Chrome/24.0.1312.57 Safari/537.17', '172.24.1.143', 1363377701), (12, 3, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_8_2) AppleWebKit/537.17 (KHTML, like Gecko) Chrome/24.0.1312.57 Safari/537.17', '172.24.1.143', 1363714792), (13, 3, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_8_2) AppleWebKit/537.17 (KHTML, like Gecko) Chrome/24.0.1312.57 Safari/537.17', '172.24.1.143', 1363714911), (14, 3, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_8_2) AppleWebKit/537.17 (KHTML, like Gecko) Chrome/24.0.1312.57 Safari/537.17', '172.24.1.143', 1363714929), (15, 3, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10.8; rv:19.0) Gecko/20100101 Firefox/19.0', '172.24.1.143', 1363715946), (16, 3, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_8_3) AppleWebKit/536.28.10 (KHTML, like Gecko) Version/6.0.3 Safari/536.28.10', '172.24.1.161', 1363791080), (17, 4, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_8_3) AppleWebKit/536.28.10 (KHTML, like Gecko) Version/6.0.3 Safari/536.28.10', '172.24.1.161', 1363791124), (18, 1, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_8_3) AppleWebKit/536.28.10 (KHTML, like Gecko) Version/6.0.3 Safari/536.28.10', '172.24.1.161', 1363791144), (19, 3, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_8_1) AppleWebKit/537.22 (KHTML, like Gecko) Chrome/25.0.1364.152 Safari/537.22', '172.24.1.143', 1363791365), (20, 64, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_8_1) AppleWebKit/537.22 (KHTML, like Gecko) Chrome/25.0.1364.152 Safari/537.22', '172.24.1.143', 1363791650);[/quote]Thanks.

SQL Server T-SQL Boot Camp 3-5 days anywhere in the United States

Posted: 21 Jun 2013 08:54 AM PDT

I have a team of coders that I need to train how to code better in SQL 2005 and 2008. Is there a Microsoft approved partner that anyone knows about that I can send 2-4 people to train them on coding? I don't want to send them to DBA courses, because they are simply coders. I just want to beef up their coding knowledge so we can improve performance, technique, and efficiency.

dead lock

Posted: 21 Jun 2013 12:14 AM PDT

how dead lock terminates in sql ?

record stored in log

Posted: 21 Jun 2013 12:13 AM PDT

when we delete the record it will store in log file , how the transaction is stored in log file ?

OS + ldf or mdf + ldf sharing on RAID?

Posted: 21 Jun 2013 05:10 AM PDT

Hi,At work today I noticed that most of our SQL Server environments have the same setup:2 disks in RAID 14 disks in RAID 5The database config also follows this format: OS s/w & backups on the RAID 1db s/w, mdf & ldf all on the RAID 5Wouldn't it be better for performance if the OS & logs were on the RAID 1 instead of the logs being on the same disks as the datafiles? I guess the split was decided to ensure the backup were on a separate RAID collection but was just wondering~ it's not as if these servers are ever pushed much

An issue with my Execution plan

Posted: 03 Mar 2013 09:27 PM PST

I have a view. I made some changes (included few CASEs in SELECT list for a business requirement)i did a relative cost check between these old and new script with estimated and actual execution plans.it was 50-50%. so, i conclude that i didnt disturb my query performance.But, the perfo. went bad.so, where am i wrong.Help me pls.Appreciating your helps always.

a delema that migrating a SQL Server 2005 database with huge tables.

Posted: 21 Jun 2013 06:13 AM PDT

I run into problems that migrating a SQL Server 2005 database. The database size is 1 TB and there are two huge tables.Those tables have over 2 billions records. The steps that I prefer to take are:1. Archive those big table*************************************************************The SQL Server 2005 are running in two node clusters (SAN) and there are BI packagesThose packages are configured with connection string, etc pointed to old Cluster.I installed Microsoft SQL Server 2008 R2 with Service Pack 2 in the new three nodes cluster.However, the BI packages configured, etc pointed to old SQL Cluster nodes.2. I plan to create SQL Alias.http://www.mssqltips.com/sqlservertip/1620/how-to-setup-and-use-a-sql-server-alias/ Would somone help on the best practice to implement?Tank you.Edwin

How to exclude rows that have a specific column blank

Posted: 21 Jun 2013 03:34 AM PDT

I am looking for a case statement that will only show me rows that Have the Column First name filled in, if the first name column is blank then I do not want to see the entire row...I hope I expained this correctcase when First.Name is null then ???

How to prevent SQL Injection Attack from SQL server side

Posted: 20 Jun 2013 05:05 AM PDT

Hi Everyone,Is it possible to stop SQL Injection Attack at SQL server level?I have gone through some posts and articles that suggest all the checks at application level so that only authentic data can be entered into database.My client has a travel portal and facing SQL injection attack. My knowledge is limited in this topic. Please can anyone help and let me know in case we can do something at SQL server level so that it can be stopped.An early response would be highly appreciated.thanks in advance.

confused With View , Function and Store Procedure

Posted: 03 Mar 2013 06:08 PM PST

i get confused where to use function , store procedure and view.plz help me;-)

Nested if"S

Posted: 20 Jun 2013 09:39 PM PDT

HiI figured I'd put this out there before I leave for work...looking to see if I can do a nested if like this:if value1 = '1' and value2 = '2' then 'A' if value1 = '1' and value9 = '2' then 'B' else if value99 = 'l' and value9 = 'x' then 'C' end as? What I'm trying to do is find values between 2-3 fields then assign a code.also one supersede the other , so if first and second if conditions are met i want the first if valueThanks In AdvanceJoe

Performance Dashboard

Posted: 04 Apr 2011 12:59 AM PDT

Good morning,I have encountered an error when clicking on the "System CPU Utilization" graph. The error says:A data source instance has not been supplied for the data source 'CHECK_DEPENDENCIES'I have altered the stored proc, usp_Main_GetSessionInfo. To resolve an earlier date overflow error, I changed the idle_connection_time from:sum(convert(bigint, datediff(ms, login_time, getdate()))) - sum(convert(bigint, s.total_elapsed_time)) as idle_connection_timetosum(convert(bigint, CAST ( DATEDIFF ( minute, login_time, getdate()) AS BIGINT)*60000 + DATEDIFF ( millisecond, DATEADD ( minute,DATEDIFF ( minute, login_time, getdate() ), login_time ),getdate() ))) - sum(convert(bigint, s.total_elapsed_time)) as idle_connection_timeAnd now I have the CHECK_DEPENDENCIES error. Does anyone know how to resolve this?

Could you help me to extract any relevant information from this errorr dump?

Posted: 21 Jun 2013 02:17 AM PDT

One of our sql servers crashed 2 times in last 2 weeks and I noticed similar error logs. The problem is that I'm not able to extract anything useful from it. I wonder if you can guide me. Please don't hesitate in requesting any extra information you may need.ThanksCurrent time is 07:25:02 06/21/13. ===================================================================== BugCheck Dump ===================================================================== This file is generated by Microsoft SQL Server version 10.50.1600.1 upon detection of fatal unexpected error. Please return this file, the query or program that produced the bugcheck, the database and the error log, and any other pertinent information with a Service Request. Computer type is Intel(R) Xeon(R) CPU E5620 @ 2.40GHz. Bios Version is INTEL - 6040000 PhoenixBIOS 4.0 Release 6.0 8 X64 level 8664, 14 Mhz processor (s). Windows NT 6.1 Build 7601 CSD Service Pack 1. Memory MemoryLoad = 52% Total Physical = 16383 MB Available Physical = 7858 MB Total Page File = 32765 MB Available Page File = 23720 MB Total Virtual = 8388607 MB Available Virtual = 8371713 MB **Dump thread - spid = 0, EC = 0x0000000000000000 ***Stack Dump being sent to C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\LOG\SQLDump0001.txt * ******************************************************************************* * * BEGIN STACK DUMP: * 06/21/13 07:25:02 spid 1564 * * ex_terminator - Last chance exception handling * * * MODULE BASE END SIZE * sqlservr 0000000000CF0000 000000000489DFFF 03bae000 * ntdll 0000000077850000 00000000779F8FFF 001a9000 * kernel32 0000000077730000 000000007784EFFF 0011f000 * KERNELBASE 000007FEFD940000 000007FEFD9AAFFF 0006b000 * ADVAPI32 000007FEFE620000 000007FEFE6FAFFF 000db000 * msvcrt 000007FEFDB70000 000007FEFDC0EFFF 0009f000 * sechost 000007FEFDE70000 000007FEFDE8EFFF 0001f000 * RPCRT4 000007FEFDC60000 000007FEFDD8CFFF 0012d000 * MSVCR80 0000000075350000 0000000075418FFF 000c9000 * MSVCP80 0000000074FD0000 00000000750D8FFF 00109000 * sqlos 0000000074F10000 0000000074F16FFF 00007000 * Secur32 000007FEFD200000 000007FEFD20AFFF 0000b000 * SSPICLI 000007FEFD490000 000007FEFD4B4FFF 00025000 * pdh 000007FEF6E70000 000007FEF6EBDFFF 0004e000 * SHLWAPI 000007FEFE980000 000007FEFE9F0FFF 00071000 * GDI32 000007FEFEC10000 000007FEFEC76FFF 00067000 * USER32 0000000077630000 0000000077729FFF 000fa000 * LPK 000007FEFE320000 000007FEFE32DFFF 0000e000 * USP10 000007FEFEC80000 000007FEFED48FFF 000c9000 * USERENV 000007FEFC960000 000007FEFC97DFFF 0001e000 * profapi 000007FEFD630000 000007FEFD63EFFF 0000f000 * WINMM 000007FEF6E30000 000007FEF6E6AFFF 0003b000 * IPHLPAPI 000007FEFB0E0000 000007FEFB106FFF 00027000 * NSI 000007FEFE310000 000007FEFE317FFF 00008000 * WINNSI 000007FEFB110000 000007FEFB11AFFF 0000b000 * opends60 0000000074F00000 0000000074F07FFF 00008000 * NETAPI32 000007FEFAE40000 000007FEFAE55FFF 00016000 * netutils 000007FEFCAF0000 000007FEFCAFBFFF 0000c000 * srvcli 000007FEFD0D0000 000007FEFD0F2FFF 00023000 * wkscli 000007FEFAE20000 000007FEFAE34FFF 00015000 * LOGONCLI 000007FEFCCB0000 000007FEFCCDFFFF 00030000 * SAMCLI 000007FEFA110000 000007FEFA123FFF 00014000 * BatchParser 0000000074ED0000 0000000074EFCFFF 0002d000 * IMM32 000007FEFDC10000 000007FEFDC3DFFF 0002e000 * MSCTF 000007FEFDA60000 000007FEFDB68FFF 00109000 * psapi 0000000077A10000 0000000077A16FFF 00007000 * instapi10 0000000074F40000 0000000074F4CFFF 0000d000 * cscapi 000007FEF6E20000 000007FEF6E2EFFF 0000f000 * sqlevn70 0000000074CC0000 0000000074EC0FFF 00201000 * CRYPTSP 000007FEFCEC0000 000007FEFCED6FFF 00017000 * rsaenh 000007FEFCBC0000 000007FEFCC06FFF 00047000 * CRYPTBASE 000007FEFD4C0000 000007FEFD4CEFFF 0000f000 * BROWCLI 000007FEF63B0000 000007FEF63C1FFF 00012000 * AUTHZ 000007FEFD090000 000007FEFD0BEFFF 0002f000 * MSCOREE 000007FEFA060000 000007FEFA0CEFFF 0006f000 * mscoreei 000007FEF9F00000 000007FEF9F8FFFF 00090000 * ole32 000007FEFEA00000 000007FEFEC02FFF 00203000 * credssp 000007FEFCAB0000 000007FEFCAB9FFF 0000a000 * msv1_0 000007FEFCDF0000 000007FEFCE40FFF 00051000 * cryptdll 000007FEFD1E0000 000007FEFD1F3FFF 00014000 * kerberos 000007FEFCEE0000 000007FEFCF93FFF 000b4000 * MSASN1 000007FEFD6D0000 000007FEFD6DEFFF 0000f000 * schannel 000007FEFCC50000 000007FEFCCA5FFF 00056000 * CRYPT32 000007FEFD6E0000 000007FEFD849FFF 0016a000 * security 0000000074CB0000 0000000074CB2FFF 00003000 * WS2_32 000007FEFE700000 000007FEFE74CFFF 0004d000 * SHELL32 000007FEFEDD0000 000007FEFFB57FFF 00d88000 * OLEAUT32 000007FEFDD90000 000007FEFDE66FFF 000d7000 * ftimport 0000000060000000 0000000060024FFF 00025000 * MSFTE 0000000049980000 0000000049D2DFFF 003ae000 * VERSION 000007FEFC770000 000007FEFC77BFFF 0000c000 * dbghelp 0000000073DA0000 0000000073EFDFFF 0015e000 * WINTRUST 000007FEFDA00000 000007FEFDA38FFF 00039000 * ncrypt 000007FEFD040000 000007FEFD08CFFF 0004d000 * bcrypt 000007FEFD010000 000007FEFD031FFF 00022000 * mswsock 000007FEFCE60000 000007FEFCEB4FFF 00055000 * wship6 000007FEFCE50000 000007FEFCE56FFF 00007000 * wshtcpip 000007FEFC840000 000007FEFC846FFF 00007000 * ntdsapi 000007FEFA880000 000007FEFA8A6FFF 00027000 * DNSAPI 000007FEFCCE0000 000007FEFCD3AFFF 0005b000 * rasadhlp 000007FEFA920000 000007FEFA927FFF 00008000 * fwpuclnt 000007FEFAFA0000 000007FEFAFF2FFF 00053000 * bcryptprimitives 000007FEFCB00000 000007FEFCB4BFFF 0004c000 * ntmarta 000007FEFC740000 000007FEFC76CFFF 0002d000 * WLDAP32 000007FEFE5C0000 000007FEFE611FFF 00052000 * SAMLIB 000007FEFA040000 000007FEFA05CFFF 0001d000 * CLBCatQ 000007FEFDE90000 000007FEFDF28FFF 00099000 * sqlncli10 0000000073750000 0000000073A07FFF 002b8000 * COMCTL32 000007FEF83B0000 000007FEF844FFFF 000a0000 * COMDLG32 000007FEFE110000 000007FEFE1A6FFF 00097000 * SQLNCLIR10 00000000722A0000 00000000722D6FFF 00037000 * netbios 000007FEEDE00000 000007FEEDE09FFF 0000a000 * xpsqlbot 0000000072290000 0000000072297FFF 00008000 * xpstar 0000000072200000 0000000072287FFF 00088000 * SQLSCM 0000000073740000 000000007374DFFF 0000e000 * ODBC32 000007FEF0C70000 000007FEF0D20FFF 000b1000 * ATL80 0000000075300000 000000007531FFFF 00020000 * odbcint 00000000736C0000 00000000736F7FFF 00038000 * clusapi 000007FEF0C20000 000007FEF0C6FFFF 00050000 * resutils 000007FEF1DC0000 000007FEF1DD8FFF 00019000 * xpstar 00000000721D0000 00000000721F4FFF 00025000 * xplog70 00000000721C0000 00000000721CFFFF 00010000 * xplog70 00000000721B0000 00000000721B1FFF 00002000 * dbghelp 000000006D090000 000000006D1EDFFF 0015e000 * * P1Home: 0044004C00510053: * P2Home: 0000000000518284: 0122002500210023 0024002900230027 0026002D0025002B 002800310027002F 002A003500290033 002C0039002B0037 * P3Home: 0000000000519CF0: 0000000009994C00 0000000019473730 0000000000000000 0000000000000000 0000000000000000 00000000195BC360 * P4Home: 0000000000000000: * P5Home: 0052004500AE00AC: * P6Home: 00000000194B4E98: 0050005C003A0043 00720067006F0072 00460020006D0061 00730065006C0069 00630069004D005C 006F0073006F0072 * ContextFlags: 000000000010000F: * MxCsr: 0000000000001F80: * SegCs: 0000000000000033: * SegDs: 000000000000002B: * SegEs: 000000000000002B: * SegFs: 0000000000000053: * SegGs: 000000000000002B: * SegSs: 000000000000002B: * EFlags: 0000000000000202: * Rax: 000000007EC2CD2C: * Rcx: 00000000095ABDA0: 0044004C00510053 0000000000518284 0000000000519CF0 0000000000000000 0052004500AE00AC 00000000194B4E98 * Rdx: 0000000000000000: * Rbx: 0000000000000000: * Rsp: 00000000095AC3B0: 0000000000000000 0000000000000000 00000000095AC5E0 000007FFFFF92000 00000000000042AC 0000000000000000 * Rbp: 0000000003A7887C: 0000000719930522 0000000202D888A4 0000000F02D88954 0000016002D888DC 0000000000000000 00000000FFFFFFFF * Rsi: 0000000000000000: * Rdi: 00000000095AC5E0: 00000000194B4E90 0000000000000000 0000000000000000 0000000000000000 0000000000000000 0000000000000000 * R8: 0000000000000000: * R9: 0000000000000000: * R10: 0000000000000000: * R11: 00000000095ACBC0: 0044004C00510053 0000000000518284 0000000000519CF0 0000000000000000 0052004500AE00AC 00000000194B4E98 * R12: 0000000000000001: * R13: 0000000000000000: * R14: 00000000031DDF00: 0074005F00780065 0069006D00720065 006F00740061006E 0020002D00200072 007400730061004C 0061006800630020 * R15: 000000000000002F: * Rip: 000007FEFD949E5D: C3000000C8C48148 9090909090909090 08245C8948909090 D98B4820EC834857 00002AE8FA8B098B 2301840FC0854800 * ******************************************************************************* * ------------------------------------------------------------------------------- * Short Stack Dump 000007FEFD949E5D Module(KERNELBASE+0000000000009E5D) 0000000002D9981D Module(sqlservr+00000000020A981D) 0000000002D9DD3A Module(sqlservr+00000000020ADD3A) 0000000002D9D89D Module(sqlservr+00000000020AD89D) 00000000031DDDC2 Module(sqlservr+00000000024EDDC2) 00000000031DD5AB Module(sqlservr+00000000024ED5AB) 000000007538ACF0 Module(MSVCR80+000000000003ACF0) 0000000075389E0B Module(MSVCR80+0000000000039E0B) 000000007538A62B Module(MSVCR80+000000000003A62B) 000000007538A86B Module(MSVCR80+000000000003A86B) 000000007538ABE7 Module(MSVCR80+000000000003ABE7) 0000000077879D0D Module(ntdll+0000000000029D0D) 00000000778691AF Module(ntdll+00000000000191AF) 00000000778A1278 Module(ntdll+0000000000051278) 0000000000CF6F5A Module(sqlservr+0000000000006F5A) 0000000000CF6FB9 Module(sqlservr+0000000000006FB9) 0000000000CF72D7 Module(sqlservr+00000000000072D7) 000000000112A5C8 Module(sqlservr+000000000043A5C8) 000000000148B3CE Module(sqlservr+000000000079B3CE) 000000000115D83D Module(sqlservr+000000000046D83D) 000000000115DC99 Module(sqlservr+000000000046DC99) 000000000112AD10 Module(sqlservr+000000000043AD10) 000000000112ABB1 Module(sqlservr+000000000043ABB1) 0000000001486BDA Module(sqlservr+0000000000796BDA) 0000000000D3DC11 Module(sqlservr+000000000004DC11) 0000000000CFBBD8 Module(sqlservr+000000000000BBD8) 0000000000CFB8BA Module(sqlservr+000000000000B8BA) 0000000000CFB6FF Module(sqlservr+000000000000B6FF) 0000000001218FB6 Module(sqlservr+0000000000528FB6) 0000000001219175 Module(sqlservr+0000000000529175) 0000000001219839 Module(sqlservr+0000000000529839) 0000000001219502 Module(sqlservr+0000000000529502) 00000000753537D7 Module(MSVCR80+00000000000037D7) 0000000075353894 Module(MSVCR80+0000000000003894) 000000007774652D Module(kernel32+000000000001652D) 000000007787C521 Module(ntdll+000000000002C521)

How to convert binary file to text file in ssis or in SQL server.

Posted: 21 Jun 2013 12:49 AM PDT

How to convert binary file to text file in ssis package and i want to load it into the table.

Generate a table with all possibilities.

Posted: 21 Jun 2013 12:23 AM PDT

Hello All,I need to generate a table.6 Fields.(Possible values in all fields are -1, 0, a,b,c,d,e,f)A field can contain a minus 1,A field can contain a zero,A field can contain the next value not yet used. (a,b,c,d,e,f)A field can contain a value allready used. (a,b,c,d,e)So the first field can contain -1, 0 or an a.The second field can contain -1, 0, a or an b (b only if first field is a).So for a row:0 a -1 b a x (The x can be a -1, 0, a, b or c)Not possiblea b a c f x (f is used before d)With these rules I want to generate all possible rows.I spend some time on this, but have not managed yet.Started of with building the table with all combinations and deleting combinations which were not allowed.At the moment I am thinking of doing something with strings.(First a should be before the first b etc.).Haven's solved the problem yet,Any handy and elegant methods to do this ?Thanks for your attention,Ben Brugman

SSIS Scheduled Job Completes in 2 Seconds

Posted: 20 Jun 2013 09:44 PM PDT

I have a long running SSIS package that I have run manually so far via "Execute Package Utility". Now I created a job to schedule and run it. If I start the job, instead of the package, it completes in 1 second as successful. I thought maybe I commented out the command, but it looks fine:[code="sql"]exec xp_cmdshell 'dtexec /DTS "\MSDB\Website_FTP_Import" /SERVER MyServer /CHECKPOINTING OFF /REPORTING V '[/code]I run other SSIS packages this way ..... strange. Maybe it's a permission thing somehow, but no errors.Any ideas ?

DMV's

Posted: 21 Jun 2013 01:40 AM PDT

What is the useful dmv's as dba needs in daily life ?DMVto check the blocking other than sp_who2

Need help with a SQL/OLAP Query (average of a particular weekday over the year)

Posted: 21 Jun 2013 12:33 AM PDT

Hi all,I'm struggling with the following query:I'm trying to get the average sale form [u]each[/u] Saturday in year 2005 and in the end the average from [u]all[/u] Saturdays in 2005.This is how it's supposed to look likeOrderDate AverageSale01.01.2005 1857,1208.01.2005 1754,2510.01.2005 1539,86... ...... ...17.12.2005 1754,5724.12.2005 2076,1630.12.2005 1829,79ALL 1947,64I already got this, which shows me each Saturday with it's average sale ...except the last line.SELECT OrderDate AVG(CAST((ProductPrice * OrderQuantity) AS MONEY)) AS AverageSaleFROM TEST3WHERE DATENAME(WEEKDAY, OrderDate) = 'Saturday' ANDYear(orderdate) = 2005GROUP BY (OrderDate)Does some know how to get the last line with the average sale form all Saturdays in 2005?? Perhaps with a OLAP function?Thanks in advance for any advice!!cheers anna

How to remove an unknown special character?

Posted: 19 Jun 2013 10:39 PM PDT

Hi,I am extracting data from a table to a .csv file in SSIS. The data in one of the fields has been copied from what looks like Microsoft Word and contains bullet points - some (not all) of these are causing the package to fail. I am unable to identify what sort of bullet point it is exactly as it loses any recognizable properties when copied, to replace it in the SQL query.Any ideas how I can select JUST the alpha-numeric text from the field excluding ALL special characters? (Instead of multiple REPLACE statements - even this won't work though as I don't know what the character is to map to its ASCII value).Thanks.

Unable to Replay Default Profiler Trace

Posted: 19 Jun 2013 09:20 PM PDT

Hello Everyone,I am facing an issue wherein the default trace file(.trc) when opened with profiler on the same server doesn't allow to replay and I get the error as below.I tried to find oout on google if this is a problem and what would be a resolution but to no avail.Cans omeone please share if there is something that I am doing wrong or if this is a default behaviour ? My server is Sql Server 2008 R2

Dynamic query validation before executing

Posted: 20 Jun 2013 06:14 PM PDT

How do we validate a string query before executing the query.e.g I have a table dbo.Test with columns A int, B int and C int.I have a three VARCHAR variables@VChrTotalString, @VChrString and @VChrFilterCondition (Filter condition can have multiple conditions)@VChrString = 'SELECT * FROM dbo.Test WHERE '@VChrFilterCondition = ' C = 5'@VChrTotalString = @VChrString + @VChrFilterCondition We do EXEC sp_executesql @VChrTotalString .This runs fine and is perfectly legitimate.Suppose @VChrFilterCondition = 'C=5 AND B IN (8,7,5,6) OR A1 NOT IN (4,2)'Here A1 is not a valid column in the table.If we execute this query we will get and error saying Invalid column A1.I want to show a custom message for the same saying "Invalid Filter condition"but for that i want value from the EXEC sp_executesql @VChrTotalString.on which I can display a message.If we use SET PARSEONLY ON; Then this query is parse correctly SELECT * FROM dbo.Test WHERE A1 = 2Is there any way of capturing the error before executing the query.Can you please help me resolving this issue.

Bulk Insert format file but input file not always fixed

Posted: 20 Jun 2013 05:18 AM PDT

I am trying to do a bulk insert using a non-xml file format. When my input file exactly matches my format file the bulk insert works fine. I have data that is older and was not written in the exact same manner as the newer input data. In the case of this older data, the last field may either not be there at all or be shorter than the allotted characters. This causes an unexpected end of file error. Here is an example of what I mean:Format file:8.031 sqlchar 0 5 "" 1 recid ""2 sqlchar 0 5 "" 2 testfield ""3 sqlchar 0 0 "/r/n" 0 skipfield ""The data that works is1 test12 test2The data that does not work is1 test2 tesanother one that does not work (the first field is 5 characters with nothing for the second field)12Without creating a format file for each case, is there a way to have the bulk insert work with the variable length in the last field?

SQL QUERY REQUIRED

Posted: 19 Jun 2013 07:50 PM PDT

Dear all,I have following tablesTABLE : AENTERPRISE_MAIN_CODE ENTERPRISE_SUB_CODE ENTERPRISE_DESCRIPTION1 0 A2 0 B3 0 C4 0 D5 0 E6 0 F7 0 G8 0 H9 0 ITABLE : BID_NO ENTERPRISE_MAIN_CODE ENTERPRISE_SUB_CODE1 1 12 1 23 7 14 4 15 4 1 6 6 17 6 5TABLE : CID_NO ENTERPRISE_ID_NO ENTERPRISE_DATE ENTERPRISE_AMT1 1 20130109 10001 2 20130315 2000 2 3 20120120 30002 4 20120305 40003 5 20141111 50004 6 20130115 60005 7 20130320 7000user inputs only month and year: for example 201303.the date format is yyyymmddo/p details: it has 4 parts which are explained below1. User should see the count and the amount of the selected year and month (201303 i.e 2013 march data)2. the previous year of the selected year and month ( count and the amount ) (201203 i.e 2012 march data)3. the first month of selected year till the selected year's selected month (count and the amount) ( 20130101 - 20130330 )4. the first month of previous year till the previosu year's month (count and the amount) ( i.e 20120101 - 20120330 )The expected op is as follows ENTERPRISE_DESCRIPTION count amt count amt count amt count amt A 1 2000 1 4000 2 3000 2 7000B 0 0 0 0 0 0 0 0C 0 0 0 0 0 0 0 0D 1 7000 0 0 2 13000 0 0E 0 0 0 0 0 0 0 0F 0 0 0 0 0 0 0 0G 0 0 0 0 0 0 0 0H 0 0 0 0 0 0 0 0I 0 0 0 0 0 0 0 0ThanksNick

Need Help:Error log

Posted: 19 Jun 2013 11:10 PM PDT

Hi all,when i checked my error log it has taken 100 gb of space and my disk is out of space. when i checked log folder location i found so many sqldump mdmp files. i am not sure whats going on... i deleted all those files and started sql server.. but i can see they are still generating mdmp files and falling in to the log folder.

data migration from ms access database to ms sql server 2012

Posted: 20 Jun 2013 10:07 PM PDT

I have a client who is currently using a Microsoft Access Database system to store data of which the system has the maximum capacity storage of up to 2 GB. The system is currently 1.4G and is estimated to reach 1.6 GB in about 6 month's time. **I would like to know if whether does ms sql server 2012 have a UI (user Interface ) part that can be used to develop the Front-end/User Interface of the tool** **Does it have the ability to convert the current UI, developed in CLARION?** PLEASE ASSIST...

index statistics updation

Posted: 20 Jun 2013 06:25 PM PDT

Hi,We are using Sql server 2008 R2 Express. We have clustered index with incremental key for our almost all tables. We noticed our queries is executing slow. So we needed to optimize query working in server.We do de-fragment indexes with high fragmentation monthly.Fill factor for indexes are set to 100.I monitored [b]Access Methods\Page Splits/sec[/b] counter and average value is around 1.Is there any need to modify Fill factor setting? Auto create statistics and Auto update statistics set to true.Is there any need to update statistics manually? If so how often i need to update statistics? Is there any reasons for query slowing down?Thank you

Move currentdate+1 to field when null?

Posted: 20 Jun 2013 05:25 AM PDT

HiI have a where clausetable.AdminDate <= @startdate AND table.DischargeDate >= @enddateBut I want to move currentdate+1 to Dischargedate if Dischargedate isnull ?ThanksJoe

Search for string in xml column

Posted: 20 Jun 2013 04:26 PM PDT

I have a table with column name 'xmlmycolumn' which is of datatype xml. I want to search for string in that whole xml. So to do this i am casting column as varchar and then i use charindex as shown below.SELECT * FROM mytableWHERE ( Charindex('abc',CAST([xmlmycolumn] AS VARCHAR(MAX)))>0 ) Is there any other method other than casting the column as varchar so that performance of the query is increased?

Connect with different AD credentials - Alias set up

Posted: 20 Jun 2013 06:32 PM PDT

Hello , everyoneCould some one tell me how to setup an alias on my windows 7 desktop that will allow me to connect to a SQL server in a different domain using a different AD login.Can't for the life of me remember where to set up in Windows 7..thanks all

COLLATION issue

Posted: 20 Jun 2013 04:53 PM PDT

Hi all,I have created a database on server1 with default collation, now I want to restore that database on another server server2 which has different collation due to this I am getting collation error in my scripts. Is there any solution for this?
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog