Friday, September 27, 2013

[SQL Server 2008 issues] data copy

[SQL Server 2008 issues] data copy

data copy

Posted: 26 Sep 2013 07:17 PM PDT

I have a DEV database i.e DEVDBI also have a TEST database i.e TESTDBboth are SQL SERVER 2008I want to import data from TESTDB to DEVDB.Is it possible through SQL Server Management Studio Wizard ? Is it faster than other approach ?I see a Import/Export on GUI of SQL Server Management Studio Wizard. Which datasource to use to copy data ?

Is it possible to install adventureworks 2012 database inside SQL server 2008 R2?

Posted: 26 Sep 2013 06:07 PM PDT

I tried doing that and got an incompatibility error. I was wondering if there is a workaround or fix for that or if there is a 2012 DB for SQL 2008.The error message is -TITLE: Microsoft SQL Server Management StudioAttach database failed for Server 'YourComputer'. (Microsoft.SqlServer.Smo)For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1617.0+((KJ_RTM_GDR).110422-1901+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Attach+database+Server&LinkId=20476ADDITIONAL INFORMATION:An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)The database 'AdventureWorks2012' cannot be opened because it is version 706. This server supports version 661 and earlier. A downgrade path is not supported. Could not open new database 'AdventureWorks2012'. CREATE DATABASE is aborted. (Microsoft SQL Server, Error: 948)For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=948&LinkId=20476

replacing numbers with strings using PATINDEX

Posted: 26 Sep 2013 06:54 PM PDT

Hi I am migrating data from a legacy system to a new system. I am trying to replace legacy client id with the new client ids.[code="sql"] create table sampleclient (clientid int, clientcode varchar(10)) INSERT sampleclient VALUES (1, 'ABCD'), (2, 'EFGH'), (3, '3RETS'), (4, 'T6UY');[/code]I am trying to create a function where i can pass in a string with the old client id and return the new clientcode.[code="sql"]select dbo.ReplaceClientIDwithClientcode('ActiveMaster.Fields("ClientID").Value = 1')[/code]I am expecting the output to be [quote]ActiveMaster.Fields("ClientID").Value = 'ABCD'[/quote]Some of the input strings are bit complex.[quote]ActiveMaster.Fields("ClientID").Value <> 356 and ActiveMaster.Fields("ClientID").Value <> 481 and ActiveMaster.Fields("ClientID").Value <> 17 and ActiveMaster.Fields("ClientID").Value <> 1775[/quote]Here is the code i have so far.[code="sql"] DECLARE @clientid varchar(100), @clientcode varchar(100), @string varchar(3000), @start int, @end int, @len int SET @string = 'ActiveMaster.Fields("ClientID").Value <> 356 and ActiveMaster.Fields("ClientID").Value <> 481 and ActiveMaster.Fields("ClientID").Value <> 17 and ActiveMaster.Fields("ClientID").Value <> 1775'while PATINDEX('%[0-9]%',@string) > 0begin set @len = len(@string) set @start = PATINDEX('%[0-9]%',@string) --print 'start of first number ' + cast (@start as varchar(1000)) --print substring(@string, @start, @len) set @end = PATINDEX('%[^0-9]%',substring(@string, @start, @len))-1 --print @end if @end = -1 begin set @clientid = substring(@string, @start, @len) end else begin set @clientid = substring(@string, @start, @end) end print @clientid set @clientcode = (select clientcode from clients where ClientID = @clientid) print @clientcode set @string = REPLACE(@string, @clientid ,''''+ @clientcode + '''') --set @loopbreaker = @loopbreaker +1 --if @loopbreaker = 10 -- breakend print @string [/code]The problem i am facing is that the client code which replaces the client id also contains numbers. My loop finds the number again and chops up the client code.Any help is greatly appreciated.

Find Locked objects with high waitstats

Posted: 26 Sep 2013 06:40 PM PDT

Hi,I have a SQL JOB that takes almost 8 hours to complete. I could find that when this runs there are a lot of waits. Screenshot is attached.I would like to know on which objects these locks and latches are occurring and if possible the SQL statement involved. These locks and latches are not being kept for a long duration, so it is hard to track from sysprocesses.Thanks in Advance !

Logshipping status report Throgh Mail

Posted: 28 Feb 2012 06:04 PM PST

i'm writing below this code..exec msdb..sp_send_dbmail@profile_name = 'local',@recipients='chintu@www.com', @subject ='Log Shipping Job succeeded', @QUERY = 'exec sp_executesql @stmt=N'exec sp_help_log_shipping_monitor',@params=N'I want Log shipping Status Through Mail.please Suggest Me.....because last back up transaction in the day and last restoration trn file in the day...so may be above script right..please help me.

Need Urgent Help with Capacity Report Script

Posted: 16 Apr 2011 07:52 AM PDT

I really need help with SQL 2008 Script on generating the Capacity Report with following columns;- Execution date as getdate()- ServerName-Database Name-DB-Size in MB-Log-Size in MB-DB-Used space in Mb-Log-Used space in Mb-DB- Available free space-Log- Available free Space- Quartery growth in MB-1 year Forcast growth in GBI need the store procedure that creates the table if not exists and load the above information from systems table so that I can call that store procedure via sql agent job on daily basis. Please some help me with the script-Thanks in advance!

Need to combine multiple sql queries into one to produce a single outcome

Posted: 26 Sep 2013 05:27 PM PDT

Greetings, I have several queries that all produce a "yes" or "no" outcome. I need to consolidate these queries in a way that will produce a count for each "yes" and produce a result like 2 out of 3. So if 4/4 is produced it would say challenge, 3/4 benchmark, 2/4 strategic, 1/4 & 0/4 intensive. What's the best way to do this? Is it even possible? Greatly appreciate any help on this.Here are the queries:1.)[code="plain"]SELECT CASE WHEN HISTORY >= 6 AND ELA >= 6 AND MATH >= 6 AND SCIENCE >= 6 AND FL >= 6 AND VA >= 6 AND Prep >= 6 THEN 'Yes' ELSE 'No' END AS Yes_No FROM ( SELECT STU.ID, COUNT(CASE WHEN CRS.U1 = 'A' THEN HIS.CN END) AS HISTORY, COUNT(CASE WHEN CRS.U1 = 'B' THEN HIS.CN END) AS ELA, COUNT(CASE WHEN CRS.U1 = 'C' THEN HIS.CN END) AS MATH, COUNT(CASE WHEN CRS.U1 = 'D' THEN HIS.CN END) AS SCIENCE, COUNT(CASE WHEN CRS.U1 = 'E' THEN HIS.CN END) AS FL, COUNT(CASE WHEN CRS.U1 = 'F' THEN HIS.CN END) AS VA, COUNT(CASE WHEN CRS.U1 = 'G' THEN HIS.CN END) AS Prep FROM dbo.CRS INNER JOIN dbo.HIS ON CRS.CN = HIS.CN INNER JOIN dbo.STU ON HIS.PID = STU.ID WHERE STU.ID = @ID) AS derived[/code]2.)[code="plain"]WITH cteSource(CN, U1)AS ( SELECT r.CN, r.U1 FROM dbo.SSS AS s INNER JOIN dbo.STU AS t ON t.SN = s.SN INNER JOIN dbo.CRS AS r ON r.CN = s.CN WHERE t.ID = @ID UNION ALL SELECT r.CN, r.U1 FROM dbo.HIS AS i INNER JOIN dbo.CRS AS r ON r.CN = i.CN WHERE i.PID = @ID)SELECT CASE WHEN p.A >= 6 AND p.B >= 6 AND p.C >= 6 AND p.D >= 6 AND p.E >= 6 AND p.F >= 6 AND p.G >= 6 THEN 'Yes' ELSE 'No' END AS [On Target?]FROM cteSource AS sPIVOT ( COUNT(s.CN) FOR s.U1 IN ([A], [B], [C], [D], [E], [F], [G]) ) AS p;[/code]3.)[code="plain"]SELECT MIN(CASE WHEN TST.SS > 349 AND CTL.NM = 'Math- total' AND CTL.ID = 'CAHSEE' THEN 'Yes' WHEN TST.SS > 349 AND CTL.NM = 'ELA- total' AND CTL.ID = 'CAHSEE' THEN 'Yes' ELSE 'No' END) AS PassFROM TST INNER JOIN CTL ON TST.PT = CTL.PTWHERE (CTL.NM LIKE '%- total') AND (TST.PID = @ID) AND (TST.ID = 'CAHSEE')[/code]4.)[code="plain"]SELECT CASE WHEN [Total Absences] < @Absences THEN 'Yes' ELSE 'No' END AS [On Target?]FROM (SELECT SUM(CASE WHEN Val = 'A' THEN 1 ELSE 0 END) AS [Total Absences] FROM (SELECT ATT.* FROM ATT INNER JOIN STU ON ATT.SC = STU.SC AND ATT.SN = STU.SN WHERE STU.ID = @ID) t UNPIVOT (Val FOR Cat IN ([A1], [A2], [A3], [A4], [A5], [A6])) u) AS derived[/code]

SSIS source file with time in the name

Posted: 26 Sep 2013 06:18 AM PDT

Hi everyone.I have been tasked with building some SSIS packages to load data from a csv into the database each day. There are four file sources. Three of them are very simple because the file name is source_date.csv. The file name is predictable so creating an expression that get the current day's file is easy. One of the files is source_date_time. Because the time stamp is always a few seconds different, I don't know how to create an expression to get the file by name and load it.What do people do when they need to have their package go out and grab the day's file if there is a time stamp in the name leading to an unpredictable file name?

SSIS - ForEachLoop Container performing task twice for the last file

Posted: 26 Sep 2013 07:27 AM PDT

Okay, so I can't figure this out and it's driving me nuts. It's minor thing but I don't have any answer. Maybe some SSIS expert can shed some light.[b]Problem[/b]I have a Foreach Loop container with file system task. File system task renames all the files in the folder; it basically adds date and time in front of the file name. For the last file in the folder, it adds date and time twice. See attached Image. [b]Here is how I have it setup[/b]1. ForEach Loop Container: Under collection: Enumerator is "Foreach File Enumerator". Then my folder is populated. "Retrieve file name" is setup to be "Name Only". Then I assigned it to a variable. See attached image.2. Setup a variable for the file location/folder. 3. Setup a variable to create date and time. Here is the code: [code="vb"](DT_STR, 4 , 1252)DATEPART( "year" , GETDATE() ) + "" + RIGHT( "00" + (DT_STR, 2 , 1252)DATEPART( "month" , GETDATE() ) , 2 ) + "" + RIGHT( "00" + (DT_STR, 2 , 1252)DATEPART( "day" , GETDATE() ) , 2 )+(DT_STR,2,1252)DATEPART("hh",GETDATE())+(DT_STR,2,1252)DATEPART("mi" ,GETDATE())+(DT_STR,2,1252)DATEPART("ss",GETDATE())[/code]4. Set up a variable to create original file name with full path by using the path stated in step #2+#1.5. Set up a variable to create new file name Using steps #2+#3+#1. Here is the code: [code="vb"]@[User::FROIOriginalLocation] + @[User::FileNameDate]+ @[User::FileNamesForNameChange]+".txt"[/code]What do you guys think is going on?

Data loading performance

Posted: 26 Sep 2013 12:41 PM PDT

HelloI have to transfer 300gb's of data from a table in one database to a table in another database on the same server. I've done some research into data loading and testing and found the quickest and most efficient way to do this was set the recovery to BULK_LOGGED, turn on trace flag 610 and lock the target table. I have also ordered the data set on the primary key of the target table as well.The current transfer rate that I'm getting is around 1gb per hour.My question is would I get a quicker transfer rate if I was to unload the data to a file using BCP or bulk insert and then load it back into the target database and table?

Question about select count(*)

Posted: 26 Sep 2013 02:57 PM PDT

Hi,I got a question about select (*),when someone is executing a transaction that inserts huge number of records, and before it has been committed, will select count(*) get the keep changed record numbers even if (nolock) hint is on?Thanks,V

can you delete the contents of the updatecache folder

Posted: 26 Sep 2013 03:39 PM PDT

this directory is over 2gb\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Update Cachecan you delete the contents safely?

Newbie needs help with a query!!

Posted: 26 Sep 2013 06:01 AM PDT

CREATE TABLE [dbo].[Schedule]( [id] [int] IDENTITY(1,1) NOT NULL, [weekdayid] [int] NULL, [starttime] [varchar](10) NULL, [endtime] [varchar](10) NULL) GOINSERT INTO [dbo].[Schedule]VALUES(1,'9:00am','10:00am')INSERT INTO [dbo].[Schedule]VALUES(1,'11:00am','12:00pm')INSERT INTO [dbo].[Schedule]VALUES(1,'2:00pm','4:00pm')INSERT INTO [dbo].[Schedule]VALUES(1,'6:00pm','8:00pm')id weekdayid starttime endtime----------- ----------- ---------- ----------1 1 9:00am 10:00am2 1 11:00am 12:00pm3 1 2:00pm 4:00pm4 1 6:00pm 8:00pmHow to validate parameters passed as @startime.@endtime from SP should not fall outside the timeframe for the same day.

Need advice for best option

Posted: 26 Sep 2013 03:48 AM PDT

I have a 600GB database that I need to copy up to date information to. I must have it in a ready only state while we migrate information from it. I cannot replicate because all the tables do not replicate and I need all of them. Log shipping was a bust, thinking of mirroring but I need it in a read only state. I read that I can put it in standby mode for read only but will I be able to access it for reading purposes? I get conflicting answers out on the web and want you guys to chime in.Thanks in advance!

How to find where sp_spaceused is being executed from?

Posted: 15 Mar 2013 03:30 AM PDT

I inherited an environment where a couple of times a day, we start getting a single spid blocking other spids which causes errors in the application. In researching it using sp_who2, I see the command as DBCC, the program as OSQL-32 and the execution as sp_spaceused @updateusage = 'TRUE'. This runs for 15 minutes or so and then the errors go away because the spid is no longer blocking.I don't know where this is being executed from? I have researched the jobs that are scheduled in Windows during that time and don't see it. I have looked at SQL schedules and don't see it. Is there an easy way to track down where this is coming from?

WestCoast - EastCoast Speed

Posted: 26 Sep 2013 05:32 AM PDT

Our database is physically located on the East Coast, we have a COTS product were our users uses it to read, open modify records in the database as well as run reports. Our West Coast users complains how slow the application takes to respond. What can be done to help easy their pain when using the COTS application. I guess because they are on the west coast it's going to be slow and I'm talking 20-40 seconds slow.

Jobs that run during specific duration

Posted: 26 Sep 2013 02:40 AM PDT

Hi,Do you have any handy script where I can get the list of jobs that ran during specified time period on a particular day.For example, I need the job details that were running on 25th Sep 2013 between 2AM to 3AM. It is not necessary that job has to run from 2AM to 3 AM. The job may get completed/failed at 2.05 or the job might have started at 2.55. All the jobs that ran any time during this time span has to be listed.Please help.

How to install an oracle linked server on SQL Server 2008 r2 failover cluster manager

Posted: 14 Dec 2012 02:27 AM PST

How to install an oracle linked server on SQL Server 2008 r2 failover cluster managerI'm reading some articles and applying them but with no result.First of all where can I get the ODBC driver free and the install, as I got the driver but does include the odbc_install.exeThanks for your help, this is driving me crazy.Thanks againAPA

Save float valur with a stored procedure adds some decimal numbers

Posted: 26 Sep 2013 02:18 AM PDT

Hi!I'm having a strange behaviour with a stored procedure that has some parameters in float format.I used:float x = (float)1.5;float y= (float)4.3;...row["x"] = x;row["y"] = y;I passed both parameters as this:cmd.Parameters.Add("@y", SqlDbType.Float).Value = (float)row["x"];cmd.Parameters.Add("@x", SqlDbType.Float).Value = (float)row["y"];And when I check in the data saved in database, it appears like this:x = 1,5y = 4,30000019073486Anyone knows why this happens?Regards

listing of months for a particular year

Posted: 26 Sep 2013 12:52 AM PDT

Dear All,Hope you are doing fine. What am I trying to do is get a stock count for each item per month for a particular year. I am trying to do a report on that.Table1: list all Purchase OrdersPoNum Date P001 2013-01-01 P002 2013-02-01 P003 2013-02-10P004 2013-03-01Table2: list items for each PoNumPoNum ItemRef QtyP001 I0001 10P001 I0002 5P002 I0003 15P003 I0003 20P004 I0003 5is it possible to have something like that?Year 2013Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec I000110 0 0 0 0 0 0 0 0 0 0 0I0025 0 0 0 0 0 0 0 0 0 0 0I0030 35 5 0 0 0 0 0 0 0 0 0Basically, I will need each item's qty per month for a year.I came up with something. But I have the item's qty for the month the items are in only. How do I get it 0 for the other month?I was reading on creating a CTE to list the months of the year. But I am stuck.Please advise.Thanks,Ashley

TDE Restore Problem - Intermittent - anyone else experienced this?

Posted: 26 Sep 2013 01:46 AM PDT

Hi All,We have a very strange problem restoring backups which has only started since TDE was implemented on 19th September 2013. This is on SQL Server 2008 (SP3) EE, running on Windows Server 2008 Enterprise (SP2) on a paired cluster. When TDE was initially implemented, test backups and restores were successfully completed without issue. Yesterday though, a backup was taken and proved to not restore (Msg 5243, Level 16, State 6, Line 1An inconsistency was detected during an internal operation. Please contact technical support.). We then attempted to restore scheduled backups from the previous 3 nights - same problem.Early this morning the instance was failed over to the other node. Test backups were taken and successfully restored. 5 backups between 05:30 and 11:45 proved to restore successfully. A further backup was taken at 13:30..... and the problems with restore re-started ;-) This leads us to believe that restarting SQL resolved the problem.... maybe memory related?There's nothing helpful in the event logs. There are stack dumps for each attempted restore.We've contacted MS, but in the interim I was wondering if anyone out there had experienced anything similar? We are suspecting TDE (as that is the only change made recently, and backups taken prior to TDE implementation still restore perfectly). It could of course be totally co-incidental and not related to TDE at all!Any help, ideas, thoughts etc would be gratefully received.CheersCatherine

Fullscan statistic refresh produces badly performing histogram

Posted: 26 Sep 2013 01:07 AM PDT

Hello there,I have found an issue in our environment that I cannot explain.There is a statistic based off of two columns in our central table that are used in many stored procedures to JOIN back and forth to this table. There is an auto generated column statistic based off of these two columns. This table as ~47,000,000 rows.When the statistic is refreshed WITH FULLSCAN, it produces a histogram with 96 steps. Numerous stored procedures that rely on this statistic then take several seconds to complete.When the statistic is refreshed with a really low row count sample, or dropped and recreated from scratch without giving any sample rate allowing SQL to determine the sample to use, the procedures that were previously taking several seconds to run, complete in < 1 second.My question is, how can a full scan statistic refresh produce a histogram that causes performance issues over a very small sampled refresh.

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

Getting error message when trying to load XLSM file using SSIS package in SQL Server 2008 environment

Posted: 25 Sep 2013 07:42 PM PDT

Hi All,I use an SSIS package to dynamically load data from specific tabs of xlsm files into a database.The files are all structured identically, occasionally I find inconsistencies in cell formats which may cause issues when loading and usually this is resolved by modifying the cell format.The SSIS package loads over 250 files successfully but I am now experiencing an issue with one file which will not load in my server environment, however it does load successfully in my test environment.The error messages I get in the BIDs Development environment on the server environment are[i]Error: There were errors during task validation.[SSIS.Pipeline] Error: One or more component failed validation.[SSIS.Pipeline] Error: component "Excel Source" (1) failed validation and returned error code 0xC020801C.[Excel Source [1]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.[u][/u][/i] The test environment is 64 bit Windows 7 Professional with SP1, SQL Server 2008 R2The server environment is 64 bit Windows Server 2008 R2 Standard, SQL Server 2008 R2Any enlightenment appreciated,Thanks
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog