Wednesday, May 15, 2013

[SQL Server 2008 issues] Running DTS packages on 2008 R2

[SQL Server 2008 issues] Running DTS packages on 2008 R2

Running DTS packages on 2008 R2

Posted: 12 May 2013 07:54 PM PDT

Hope someone can help me on this oneWe have a 2008 R2 cluster which failed over recently, a number of jobs that run DTS packages failed due to the DTS components not installed. Went through the install of the DTS components and the 2005 Backwards Compatability. At this point I still couldn't open a package on the server. Read up a few articles that showed that on occasion, binary files needed to be manually copied into the program files directory where the SQL server was installed (http://msdn.microsoft.com/en-us/library/ms143755%28v=sql.105%29.aspx)After this was done I could open the packages but the jobs still failed.The code in the job step is set to run as a CmdExec with the direct call to the DTS package on the servere.g dtsrun /S "Server" /E /N "Package"I tried running this code via xp_cmdshell and got the error that the dtsrun component could not be found. Unfortunately I couldn't test adding the full path of the location of the DTSRun into an xp_cmdshell command but I'm guessing that this is what the error is down to.A bit more research found that there can be issues with the order of path's in a machines Environmental Variables:http://blogs.msdn.com/b/ramoji/archive/2008/10/16/how-to-open-dts-packages-in-sql-server-2008.aspxI made the necessary changes but the job still fails.One last thing, I compared the location of the dll's and rlls (that have to be manually moved as directed in the first link) from the node where the DTS packages run to where the DTS packages don't run and there were some discrepancies (strangely enough on the node that the packages are working, they're not in the same folder as directed in the first link). Made the necessary changes and now both nodes are identical in terms of file locationsThe jobs still failed. Can anyone else suggest something??Thanks

SQL Server 2008 Audit

Posted: 13 May 2013 06:12 PM PDT

Hi Guys,I wish to audit all the activities performed by users having sysadmin role.Is there any straight foward way to do it?Can't find such options under Server Audit or Database Audit specifications.thanks

SSIS export to Tab Delimited Text file

Posted: 14 May 2013 07:00 PM PDT

Hello,I was asked to make an SSIS package that extracts Supplier data from the source db and export the data to a Tab Delimited Text File.When I open the Text File the values of the different columns don't neatly organize under the column names. Is it possible to make a Tab Delimited Flat File export in which the values of the export organize neatly under the column headers of the text file?Thx in advance!

Failed to initialize the Common Language Runtime (CLR) v2.0.50727 with HRESULT 0x80004005

Posted: 23 Jan 2013 06:14 AM PST

This lovely error happens frequently when I need to add articles to my transactional replication setup. After making the changes needed to the publication, I start the Snapshot Agent (needed to replicate the new articles/changes over) an error RANDOMLY occurs with the CLR – and as far as I know, the only way this will be fixed is to restart the MSSQL service on Production[quote]Message: Failed to initialize the Common Language Runtime (CLR) v2.0.50727 with HRESULT 0x80004005. You need to restart SQL Server to use CLR integration features.Failed to initialize the Common Language Runtime (CLR) v2.0.50727 with HRESULT 0x80004005. You need to restart SQL Server to use CLR integration features. Command Text: if object_id('sys.sp_replcleanupccsprocs') < 0 exec sp_replcleanupccsprocs @publicationParameters: @publication = MyDB[/quote]Found an [url=http://support.microsoft.com/kb/2003681]article[/url] from MS that pertains specifically to the error (my situation is #3) and it basically says to reinstall the .NET Framework 2.0 - Just lovely...as this happens to frequently occur on our busiest, most critical production server :crazy:My question is: Does anyone know any way around this or am I SOL?[b]EDIT[/b]: Also wanted to ask if anyone knows of potential pitfalls/areas of concern with reinstalling this older .Net platform

Chinese Language and Decoding.

Posted: 13 May 2013 08:12 PM PDT

Dear Gurus,Your suggestion is required. Our team wants to override "windows regional language Settings" for Chinese Language by applying encoding technique.Is there any way to show Chinese characters properly without configuring the language settings from "Control Panel". please suggest the possible solution if any.ThanksMalik Adeel Imtiaz

Updating a column in batches

Posted: 13 May 2013 02:23 PM PDT

[b]Background: [/b]I've added a new column to a table with some 800 million rows. I need the column populated with data from another table in the db. I do this in batches using a where clause between two boundary values. I do this in a loop incrementing the boundary values by a batch size. I do this to keep the batch size within managable levels (preventing paging etc). I set the batch size to 100,000 and each loop was taking 4 minutes. At that rate, it would take almost 4 days to complete. So I started to investigate, first up, take the query and look at the execution plan. So I took the guts of the query, replace the variables with scalar constants and ran it, it takes under a second to run - wierd - ok maybe it was cached, so I run it over a record batch I know I haven't updated yet, same result. I run the loop again, and look at the execution plan and surprise, they're different. Update statistics, no change. Can someone please tell me why this is occuring?[code="plain"]UPDATE TranItem SET [TransactionDate] = [Transaction].[TransTime]FROM TranItem INNER JOIN [Transaction] on [TranItem].TransactionId = [Transaction].TransactionIdWHERE TranItemID between 671726 and 771726[/code]Yeilds the first execution plan - 2013-05-14 13_09_15.png[code="other"]DECLARE @BatchSize intDECLARE @IDStart intDECLARE @IDEnd intSET @IDStart = 1SET @IDEnd = 900000000SET @BatchSize = 100000While @IDStart < @IDEnd BEGIN UPDATE TranItem SET [TransactionDate] = [Transaction].[TransTime] FROM TranItem INNER JOIN [Transaction] on [TranItem].TransactionId = [Transaction].TransactionId WHERE TranItemID between @IDStart and (@IDStart + @BatchSize) set @IDStart = @IDStart + @BatchSizeENDGO[/code]Yeilds the second execution plan : 2013-05-14 13_21_12.pngCould someone shed some light on this seemingly weird behaviour? Cheers.

Get who eliminated a default trace.

Posted: 14 May 2013 09:53 AM PDT

Hello, Is there any way that i can find who have eliminated a default trace from a machine?The point is that SQL Server has 5 default traces and the current five i've got them in my machine. Actually in the machine i've got [b]log_34.trc, log_35.trc, log_36.trc, log_37.trc, log_38.trc[/b], as i need to get information that has many days i restore from backup device another traces, , [b]log_31.trc, log_30.trc, log_29.trc, log_28.trc, log_27.trc [/b]but i cannot get [b]log_32.trc[/b] and [b]log_33.trc[/b] as they are not in the backup devices, so i try to understand who eliminate the from the operating system before the filesystem backup runs :crazy:Is there any way to get the information of those trace files or even who eliminate them? I really need to get information from those two trace files.Thanks and regards, hope u can help me :)

Enabling page compression, and shrinking a DB file...

Posted: 14 May 2013 03:31 AM PDT

Looking to free up some disk space on a server, and have a question.The DB itself, we're going to enable page level compression on the data and non-clustered indexes (testing shows that enabling compression on the table will also enable it on the clustered index) We're also going to shrink the data file down a few 10s of GB.Now, I know that the shrink of the file will horrifically fragment the indexes, so we're going to also rebuild the indexes when the file shrink completes (and I'll be leaving room in the file for the data to grow without growing the file)Looking at the T-SQL for enabling compression on an index, it looks like I can do the rebuild and compression of the non-clustered indexes in one step:[code="sql"]ALTER INDEX IX_INDEX_1 ON T1REBUILD WITH ( DATA_COMPRESSION = PAGE ) ;GO[/code](Yes, this is straight from the MSDN page on compression)Of course, I also just realized I'll probably also need to rebuild the Clustered Index as well...But, my question stands, will the Alter Index both compress and rebuild (removing / reducing fragmentation) the indexes?Thanks,Jason A.

SSIS Data flow failing

Posted: 08 May 2013 01:00 AM PDT

I have a data flow that has been working for months. Starting this week it will transfer some of the rows then just fail. I am copying data from a DB2 database to SQL Server. Here is the error that I get from the agent:[i]The step did not generate any output. The return value was unknown. The process exit code was -529697949. The step failed.[/i]That's all it shows which seems odd, ever other error I've looked at there is usually a lot more information.I see no errors in the sysssislog for the process failing.This process moves 3 different tables from DB2 to SQL Server. This morning the first 2 data flows worked, al data moved. The third data flow moved around 376,000 rows of the 393,000 rows it should have. When I tried to rerun the data flow it only moved about 99,000 rows.Any thoughts? Any thing else you need to know?Thanks in advance for your help.

Alter Stored Procedure to only update changed values

Posted: 14 May 2013 06:37 AM PDT

Hi,My Problem is that Data-Updates are done via an .asp-Application which uses a Stored Procedure to update Records in our SS 2008 DB when a User hits the Save-Button on an Edit-Page, and this Stored Procedure passes all Values as Updated, but I need a particular Field to be checked for Value Changes and only be updated when the value actually changes, as there is an Update-Trigger on it, that fires all the times this Stored Procedure is executed ... the trigger works fine from within the DB, it's just the sproc that kinda messes it up and I don't know how to change it.Here's the code of the Stored Procedure (I took some fields out for readability):[code]CREATE PROCEDURE [dbo].[pALPHACustomerUpdate] @pk_CU_ID int, @p_Resort int, @p_RoomNo int, @p_DepartDate datetime, @p_Paid_Accomm bit, @p_prevConValue nvarchar(4000), @p_force_update char(1)ASDECLARE @l_newValue nvarchar(4000), @return_status int, @l_rowcount intBEGIN IF NOT EXISTS (SELECT * FROM [dbo].[Customer] WHERE [CU_ID] = @pk_CU_ID) RAISERROR ('Concurrency Error: The record has been deleted by another user. Table [dbo].[Customer]', 16, 1) IF (@p_force_update = 'Y') BEGIN UPDATE [dbo].[Customer] SET [Resort] = @p_Resort, [RoomNo] = @p_RoomNo, [DepartDate] = @p_DepartDate, [Paid_Accomm] = @p_Paid_Accomm WHERE [CU_ID] = @pk_CU_ID SET @l_rowcount = @@ROWCOUNT IF @l_rowcount = 0 RAISERROR ('The record cannot be updated.', 16, 1) IF @l_rowcount > 1 RAISERROR ('duplicate object instances.', 16, 1) END ELSE BEGIN Select @l_newValue = CAST(BINARY_CHECKSUM([CU_ID],[Resort],[RoomNo],[DepartDate],[Paid_Accomm]) AS nvarchar(4000)) FROM [dbo].[Customer] with (rowlock, holdlock) WHERE [CU_ID] = @pk_CU_ID IF (@p_prevConValue = @l_newValue) SET @return_status = 0 -- pass ElSE SET @return_status = 1 -- fail IF (@return_status = 0) BEGIN UPDATE [dbo].[Customer] SET [Resort] = @p_Resort, [RoomNo] = @p_RoomNo, [DepartDate] = @p_DepartDate, [Paid_Accomm] = @p_Paid_Accomm WHERE [CU_ID] = @pk_CU_ID SET @l_rowcount = @@ROWCOUNT IF @l_rowcount = 0 RAISERROR ('The record cannot be updated.', 16, 1) IF @l_rowcount > 1 RAISERROR ('duplicate object instances.', 16, 1) END ELSE RAISERROR ('Concurrency Error: The record has been updated by another user. Table [dbo].[Customer]', 16, 1) ENDENDGO [/code]This updates all Field Values, even if the data hasn't changed, which then causes my Trigger to fire ...I tried to change the behavior of that SProc by adding either ISNULL or COALESCE in the SET Statement, but the Outcome is the same ...[code] [RoomNo] = COALESCE(@p_RoomNo,RoomNo),[/code]or:[code] [RoomNo] = ISNULL(@p_RoomNo,RoomNo),[/code]any help is appreciated to be able to update only values that are changed within this Stored Procedure ...

row_number with recursive cte and update

Posted: 13 May 2013 10:09 PM PDT

I am sorry if this has been answered before - I have seen and read many articles about row_number and cte but cannot get my head around this particular issue.I have a series of items which may have predecessors or may not. predecessors may themselves have predecessors - hence a recursive cte would seem ideal. I want to have an ordered list where any items with predecessors get run in the correct order but not with the same sequence number as other items.here is my example input data (tabs don't seem to work very well): -RowID TaskName Predecessor runorder1 Task1 NULL 12 Task2 NULL 13 Task3 NULL 14 Task4 Task2 15 Task5 Task3 16 Task6 Task9 17 Task7 Task5 18 Task8 Task5 19 Task9 Task10 110 Task10 NULL 1The table is called testschedule. The runorder is the column I want to fill in.so here is my cte and the resulting select.with Successors (runorder, predecessor, taskname, Level)as( select ts.runorder, ts.predecessor, ts.taskname, 0 as Level from testschedule ts where predecessor is null union all select ts.runorder, ts.predecessor, ts.taskname, Level+1 from testschedule ts inner join Successors as s on ts.predecessor = s.taskname)select * , row_number() over (order by taskname) desiredorder from successorsOUTPUTrunorder predecessor taskname Level desiredorder1 NULL Task1 0 11 NULL Task10 0 21 NULL Task2 0 31 NULL Task3 0 41 Task2 Task4 1 51 Task3 Task5 1 61 Task9 Task6 2 71 Task5 Task7 2 81 Task5 Task8 2 91 Task10 Task9 1 10NOW maybe I am being dense or "can't see the wood for the trees" but what I want is to convert the "select * , row_number ..." to an update to put the correct "desiredorder" value into the runorder column on taskschedule.Can someone help me please?TIAKevin

What TOOLS do you include in your production SQL builds?

Posted: 14 May 2013 07:43 AM PDT

Where I work, once a server is "in production" I can't make *any* changes like installing software, not even stored procedures, or a DBA database. Nothing. (not without going through a terrible approval process, and then the answer is almost always NO.)But what I can do is include such software/scripts when I write my build documents. No one actually ever reads them, so that's how I can sneak in basically whatever I want. The document is approved, I build the production server, and presto! My tools are included. (I can still run ad-hoc SQL queries, by many of the more powerful tools are SPs or Jobs, etc)So here are some of the "tools" I do/will include in all future builds. Do y'all do something similar? [u]What do you include in your builds?[/u][b]Who is Active?[/b]Who is Active? is a comprehensive server activity stored procedure based on the SQL Server 2005 and 2008 dynamic management views (DMVs). Think of it as sp_who2 on a hefty dose of anabolic steroids...... [url=http://sqlblog.com/files/10/default.aspx]http://sqlblog.com/files/10/default.aspx[/url][b]SQL Server 2008 Diagnostic Information Queries[/b][url=https://sqlserverperformance.wordpress.com/]https://sqlserverperformance.wordpress.com/[/url][b]Detect Worst Performing SQL Queries[/b]...how to detect worst performing sql queries which is slowing down Microsoft SQL Server...[url=http://www.sqlfeatures.com/2012/01/29/detect-worst-performing-sql-queries/]http://www.sqlfeatures.com/2012/01/29/detect-worst-performing-sql-queries/...[/url][b]A Better sp_who2 using DMVs (sp_who3)[/b]The following code generates the same information found in sp_who2, along with some additional troubleshooting information. [url=http://sqlserverplanet.com/dba/a-better-sp_who2-using-dmvs-sp_who3]http://sqlserverplanet.com/dba/a-better-sp_who2-using-dmvs-sp_who3[/url][b]Ola Hallengren - SQL Server Backup, Integrity Check, and Index and Statistics Maintenance[/b]The SQL Server Maintenance Solution comprises scripts for running backups, integrity checks, and index and statistics maintenance on all editions of Microsoft SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, and SQL Server 2012.[url=http://ola.hallengren.com/]http://ola.hallengren.com/[/url] [b]Microsoft® SQL Server® 2012 Performance Dashboard Reports [/b] (Google the 2005 version if you need it, which I think works with 2008)The SQL Server 2012 Performance Dashboard Reports are Reporting Services report files designed to be used with the Custom Reports feature of SQL Server Management Studio. [url=https://www.microsoft.com/en-us/download/details.aspx?id=29063]https://www.microsoft.com/en-us/download/details.aspx?id=29063[/url]

Identity insert without reseed

Posted: 14 May 2013 02:29 AM PDT

Hi!Can I insert a table without identity reseed?Exapmle:[code="sql"]CREATE TABLE #table (id int IDENTITY (1,1), col1 int)INSERT INTO #table DEFAULT VALUES-- Id = 1-- If I insert identity values, then identity is reseedSET IDENTITY_INSERT #table ON INSERT INTO #table ( id, col1 ) VALUES ( 1000, 0)SET IDENTITY_INSERT #table OFF-- Id = 1000INSERT INTO #table DEFAULT VALUES-- Id = 1001 and this is my problem.SELECT * FROM #table[/code]Thanks

Linked Servers - Link drops out intermittently..Grrrr

Posted: 09 May 2013 08:06 PM PDT

Hi All, I am experiencing an issue where two servers with matching linked server configurations are connected. They are configured to connect under the current security context, which is a windows account and with RPC out and from set to true along with data access. This all works fine the majority of the time and there is a service account calling a process that relies on these connections.However, from time to time the link drops and refuses conections with the "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. Reason: Token-based server access validation failed with an infrastructure error". If I log onto the server and try a cross db query this seems to resolve the issue for a while, even though this is not the service account that is running the process. I know that access tokens are created and may exist for a period of time after I log in to the server :hehe: but I'm sure that should only be relevant if I was logging in with the relevant service account. Is there a permament fix to this issue? Has anyone else experienced the problem and resolved it?BTW - Both servers are run via a service account that has been configured for delegation and both servers have SPN's registered against the account that the SQL service is running against for both the server and the port it is using.Any help or guidance much appreciatedCheersElliot

Generate Scripts for Insert Statements

Posted: 14 May 2013 06:33 AM PDT

Hello EveryoneI am using the SSMS Tool Pack to generate the Insert Into statements for a table.Is there a way to NOT have [N'] to begin all the character string columns?ThanksAndrew

Master database 'missing'

Posted: 14 May 2013 01:51 AM PDT

Hi folks,SQL Server 2008R2 - newbie userWierd issue of a missing master database - wierd because I would have thought this was a newbie topic but I've found nothing for it. I googled and had a 'decent' look through this forum and only found a bunch of topics on 'how to restore master database'.I wouldn't have thought I need to restore the master database because my SSMS works fine and I can query the master database. I can also see it in the drop down list of available databases in the Query Designer toolbar. The problem is just that I can't see it in the list of databases. I can see all the other databases I've created, and I can see the master database in the DATA folder. But not in the SSMS. I'm pretty sure I was able to see it a few weeks ago when I first loaded SQL Server 2008R2. But its not there now.Any useful comments/suggestions please?Cheers,Chris

execute ssis package in sql server 2008 r2 error

Posted: 14 May 2013 06:45 AM PDT

Hi,When I executed my SSIS package in BIDS successfully but when I try to schedule to execute my SSIS package in SQL Server Agent and I got this error:Microsoft (R) SQL Server Execute Package Utility Version 10.50.2500.0 for 32-bit. Code: 0XC0209302 Description: SSIS Error Code DTS_E_OLEDB_NOPROVIDER_ERROR. The requested OLE DB provider Microsoft.ACE.OLEDB.12.0 is not registered. Error Code: 0X00000000. An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0X80040154 Description: "Class not registered".I already installed the data connectivity components from http://www.microsoft.com/en-us/download/details.aspx?id=23734, set run64bitruntime to false, and checked the "Use 32 bit runtime" in SQL Server 2008. I use SSIS 2008.Please help me, thanks.

Using the Generate Script

Posted: 14 May 2013 05:52 AM PDT

Hello EveryoneI am working to generate the scripts in SSMS. I would like to be able to see the actual date and time that is stored in the table, not the binary equivalent.The Insert Into script that is generated gives me this: CAST(0x0000A12D0182C74D AS DateTime)Is there an option that I am missing that will allow me to see the date and time in the INSERT INTO Statement, as a simple date and time value?Thanks in advanceAndrew SQLDBA

Suppress Warning during Update

Posted: 14 May 2013 05:21 AM PDT

Hello EveryoneI am updating one column with the data from another column, and I want to suppress the warning message about "data could be truncated"The column that will be updated is varchar(50), that column used as the source is varchar(100). When I preform an update, I am getting the warming message. How may I suppress the warning message and let the update complete?ThanksAndrew SQLDBA

call sproc in Oracle

Posted: 14 May 2013 03:02 AM PDT

We have a .net application, when a new entry is inserted or updated in SQL server table, we would also need to do the same thing on remote oracle server on our domain.A stored procedure is ready in the oracle side. We just need to call that stored procedure.What is the best way to do it? In code or in SQL server?Thanks,

Separating different types of invoice numbers

Posted: 14 May 2013 02:49 AM PDT

I have developed this query to separate distinct invoice IDs under a certain sales id.. The one problem I am having is that our company currently has two different types of sales IDs. As of now, we currently have one Sales ID per order and then different Invoice IDs based on when the different parts of the order ship. This query has worked fairly well for this new system to let me know which orders have multiple invoices. However, before we transitioned to this system, our sales order numbers changed based on when the item shipped. for example, the first salesID that was shipped would look like "3224-0" and the second set "3224-1" even if it was originally ordered together. Any help on how I can group all of those together? Sorry if it's confusing, let me know if I need to be more clear. Also, to clarify, I have excluded any results that begin with QR because that is a different segment of our company, as well as any blank invoices that may have found a home in the database.select it.SALESID 'Sales Order Number',ij.invoiceaccount 'Account Number',count (distinct ij.INVOICEID) as 'Lines Per Order'from CUSTINVOICETRANS itfull join custinvoicejour ijon it.INVOICEID=ij.INVOICEIDwhere it.INVOICEDATE between '1/1/2012' and '12/31/2012'and ij.INVOICEACCOUNT = '3845331'and left (it.INVOICEID,2) != 'QR'and it.INVOICEID != ''group by it.SALESID,ij.INVOICEACCOUNT

Messages

Posted: 14 May 2013 03:04 AM PDT

Hello all, I have a huge query that produces 800.000 lines of messages how can i save the output to a table? Is that possible since i have tried it on a file to export it and it didnt work. Much appreciate your helpZ

UpsizingWizard

Posted: 14 May 2013 12:21 AM PDT

Trying to use the Upsizing Wizard and link our Access 2007 tables into our SQL Server 2012 but we get a message saying The upsizing wizard does not work with the version of SQL Server to which your Access project is connected.Anyone got any suggestions as to how else i can link these tables?

SQLBufferpool AWE Consuming Available Memory

Posted: 14 May 2013 02:19 AM PDT

Good Day AllI have a server with 128GB RAM, Server2008R2 (64Bit) and SQLServer2008R2 also 64bit.We have lock pages in memory set on the SQL service account and also set sql max server memory to 110GB.The problem is that RAM Map states the 80GB+ is allocated to AWE.SQL is not configured to use AWE (sp_configure value = 0).But when querying the sys.dm_os_memory_clerks sys view it states the MEMORYCLERK_SQLBUFFERPOOL is consuming 87,912,480KB in the awe_allocated_kb field.My question is if SQL is 64bit, and the OS is 64bit, why on earth would the commited memory be a few hundered MB but SQL AWE is over 80GB?I read somewhere that some API's that are enabled to use AWE might be causing this but i have no idea how to prove/trace that.Regards

Add a value IF ISNULL

Posted: 14 May 2013 02:28 AM PDT

Hi,I need again your help. Something very easy for you I guess:I need to add a value in the column of a table when the value is null.So let's say I have table_ads with the columns:id country1 GB2 GB3 4 5 GBI need an SQL query to add the value 'GB' when country IS NULLSomeone can help me?Regards

SQL counter&gt; need help

Posted: 14 May 2013 01:35 AM PDT

Hi,My boss ask me some information: How many properties has each estate agent in our database. I need your help please.I have a database with estate agents and properties.The properties are stored in table_ads:id category userid ad_text45 flat 236 flat for rent46 house 237 house for rentThe estate agents are stored in table_ea:id name username email password236 Robert Royalestate royalea@gmail.com x564d5s6s237 David Boboestate boboea@gmail.com x5s4yhhs6sI would like to get a list of estate agents with the quantity of properties they haveusername qtyRoyalestate 18Boboestate 26etc...So I need to creqate an SQL query which will count the quantity of each estate agent, display this quantity with the username.Can you help me please?Regards

Word Doc in SQL Server!

Posted: 14 May 2013 01:55 AM PDT

Hi AllI wish to store a word document in SQL Server. I should be able to search on the content. e.g. Select Content from DocTable where content like '%this doc is good%'.I want to retain basic formatting like old,underline , italics.Is there a way to achieve this without saving the document as HTML?Thanks

TDE Plus DB Snapshot

Posted: 14 May 2013 01:22 AM PDT

Given a SQL 2008 R2 DB with TDE enabled, is there a way to enable ENCRYPTION for the SNAPSHOT as well? (Is this implied at the file level?) Are the contents of my .SS file encrypted if the source DB was encrypted?

To Split SP or Not to Split SP

Posted: 13 May 2013 11:50 PM PDT

HiI have a question regarding the performance gains (if any) of splitting an SP into smaller SP'sThe procedure in question has some logic resembling the following:[code="sql"]DECLARE @Input INT,@Param NVARCHAR(64)IF @Input = 1SELECT A,B,C,D,EFROMSomeTableWHEREA = @ParamELSE IF @Input BETWEEN 2 AND 4SELECTA,B,C,D,EFROMSomeTableWHERECASE @Input WHEN 2 THEN B WHEN 3 THEN C WHEN 4 THEN D= @Param[/code]So basically depending on what the value on @Input is the SP will be filtering on different columns.My question is this in terms of performance and indexing ect will I get any performance gain from splitting thisSP up essentially creating a separate SP for 2 - 4:[code="sql"]DECLARE @Input INT,@Param NVARCHAR(64)IF @Input = 1SELECT A,B,C,D,EFROMSomeTableWHEREA = @ParamELSE IF @Input = 2SELECTA,B,C,D,EFROMSomeTableWHEREB = @ParamELSE IF @Input = 3SELECTA,B,C,D,EFROMSomeTableWHEREC = @ParamELSE IF @Input = 4SELECTA,B,C,D,EFROMSomeTableWHERED = @Param[/code]And then using a separate SP to call the one that needs to be used:[code="sql"]IF @Input = 1EXEC SP_1ELSE IF @Input = 2EXEC SP_2ELSE IF @Input = 3EXEC SP_3Ect....[/code] I would be interested in peoples views and experience?Disclaimer the code above is obviously very much oversimplified however I'm after advice on the concept rather than the actual query tuning. :-DCheersAndy

Parse Expression Error

Posted: 13 May 2013 07:26 AM PDT

Hi All,I have the following expression in a Precedence Constraint Editor [code]@Table_Number == "3290056" || @Table_Number== "3290057" || @Table_Number == "3290058" || @Table_Number == "3290059" || @Table_Number == "3290060" || @Table_Number == "3290061" || @Table_Number == "3290062" || @Table_Number == "3290063" || @Table_Number == "3290064" || @Table_Number == "3290065" || @Table_Number == "3290066" || @Table_Number == "3290067" || @Table_Number == "3290068"[/code]but for some reason I get the following error:[quote]Error at Constraint 46:Attempt to parse the expression "@Table_Number == "3290056" || @Table_Number== "3290057" || @Table_Number == "3290058" || @Table_Number == "3290059" || @Table_Number == "3290060" || @Table_Number == "3290061" || @Table_Number == "3290062" || @Table_Number == "3290063" || @Table_Number == "3290064" || @Table_Number == "3290065" || @Table_Number == "3290066" || @Table_Number == "3290067" || @Table_Number == "3290068"" failed. The token "■ " at line number "1", character number "79" was not recognized. The expression cannot be parsed because it contains invalid elements at the location specified.[/quote]Can anyone tell me why? I don't understand the character reference.

DR - Quickest way to recover system databases.

Posted: 14 May 2013 12:42 AM PDT

I was tasked to do a DR rehersal with one of our test SQL 2008 R2 server last week, while investigating I came across the following post - http://blogs.technet.com/b/fort_sql/archive/2011/02/01/the-easiest-way-to-rebuild-the-sql-server-master-database.aspx As it seemed straight forward and quick fix, I decided to follow it and found that I could recover all the system databases in this manner. After recovering the server I ran dbcc checkdb on the system databases and everything looks in order. As this was a test server I did not have any user databases on it hence unable to test that side of things. I wonder if anyone has tried this in the live environment and have found any issues with it? I was quite impressed with the speed at which a SQL Server could be recovered by following this method. Kind RegardsKailash.

Data flow task error in SSIS

Posted: 12 May 2013 08:05 PM PDT

Hi All,I am getting the below error in data flow task in SSISSource: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "Insert bulk failed due to a schema change of the target table."Error message seems to be weird since there are no schema changes in source and target tables.The package has been running for months and we usually insert around 350 million records.Please help

Copying database objects

Posted: 12 May 2013 10:07 PM PDT

How to copy the database objects (tables, views, Stored procedures, functions, schemas) from one database to another database of different server. Both source and target are SQL server 2008 R2. I just want to copy the database objects and not the actual data. Could somebody please help.

query with linq generate different execution plan...

Posted: 13 May 2013 06:38 PM PDT

HelloI have a simple linq query :from c in listwhere DATE >= DA && DATE <= Aselect c.ID).Distinct().Count()When i run this query, with profiler i see that the execution time is about 10 seconds. But if i get the generated sql query and launch it in management studio, execution time is 1 second!!!I've seen (with profiler) the execution plan generated by the first query and it's different by the execution plan of the same query launched in ssmsAny idea???ThanksAndrea

1TB TempDB too big

Posted: 13 May 2013 08:41 PM PDT

Hi All,There is a set of stored procedures that is executed as a separate module. At the time this module executes nothing else is working on the database. The last three days the client complained for a huge TempDB growth (1TB) which was not a case so far. Additionally it's been 25 days since we moved the Sql Server 2008 r2 database from Windows server 2008 r2 to Windows server 2012.In the above mentioned set, temp tables are used. After a check in some SPs the temp tables were not dropped. We plan to do this. In one SP there is a global temp table which is dropped under a condition defined in the application running the sp set. We will check this too and ensure the global temp table is dropped for sure.Can anyone suggest any other tips or hints. Can it be something with the Windows server version? Please you're welcome to share...Best regards,IgorMi

How to display Financial Year wise Summary

Posted: 13 May 2013 06:16 PM PDT

Hi, I had requirement,I want a report in sql server 2008 as follows,Total PayDate Amount 2000 20110401 1000 20110501 1000 5000 20120401 2000 20120501 1000 20120501 1000 20120601 1000 4000 20130401 2000 20130501 1000 20130601 1000I had Paydate and Amount data with me,But my Total should be displayed Finacial year wise.means Sum of Amount in that FY Year onceand for rest of the month as blank.Kindly help me in this requirement.Thanks in Advance!!!!
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog