New answers tagged

1

After digging into the backup logs between LiteSpeed and SQL Server I noticed that the first LSN number between the daily full backup and the full backup LSN number in differential backups didn't match up. After doing some reading on how the LSN values are used in the backups of the database I did some digging on what would cause the full backup's LSN ...


5

As long as they are not nested, you should be fine. @@FETCH_STATUS is set at the time the FETCH NEXT is performed. @@FETCH_STATUS is global to the session, not the entire server. There is no equivalent to SCOPE_IDENTITY() for @@FETCH_STATUS. If you need to nest looped FETCH NEXT operations, make sure that your outer FETCH NEXT is performed after the inner ...


0

In my case I was using SQL 2005 and got "transaction context in use by another session" when running Insert....exec over a linked server. The fix for me was to patch from SP2 build 3161 to SP3. SP2 cumulative 5 is supposed to fix though. https://support.microsoft.com/en-us/kb/947486


3

You can just remove the WITH XMLNAMESPACES(DEFAULT 'https://XYZ.xsd') part: DECLARE @XML XML SET @XML = '<NodeA> <NodeB verzePis=""> <NodeC1 attrA="Hello" /> <NodeC2 attrA="World" /> </NodeB> </NodeA> ' ;--WITH XMLNAMESPACES(DEFAULT 'https://XYZ.xsd') SELECT r.value('fn:local-name(.)', ...


7

To say MAXDOP=1 is always beneficial is totally incorrect statement. MAXDOP value should be set according to SQL Server CPU core configuration followed by a good round of testing. Few applications I know that woks good with MAXDOP 1 is DynamicsAX and Sharepoint. Yes ghost cleanup is single threaded as mentioned by Paul in this article but just because ...


0

The SQL Server Agent job definitions are stored in the msdb database, in the sysjobs and sysjobsteps tables. You could do a plaintext search on those tables with the following query: SELECT * FROM msdb.dbo.sysjobsteps WHERE subsystem='TSQL' AND command LIKE '%nameofmyprocedure%' Note that there are still cases when your stored procedure still won't ...


0

Create a new table such as this: create table CaptureUnwantedJob ( SQL_Command nvarchar(max), SPID int, Status nvarchar(125), Command nvarchar(255), ProgramName nvarchar(500) ) go Create a new Agent job that will start at 6:59 AM. Place this query in it: truncate table CaptureUnwantedJob declare @i int = 1 while @i <= 10 begin insert into ...


0

I have got suggestion from one of the DBA to Generate script of the database and RUN DBCC CHECK DB . 1: I have Created the database with DB-->ScriptDB as -->create To --->New Query Editor Window and created the Secondary db 2:I have created script of all Table with generate Script Wizard. 3:Export table from Primary to Secondary with Export Wizard But ...


0

This problem might be the DBCC CHECKDB bugs reported by Microsoft a year ago,The bugs appear to be on sql server 2012 and 2014 version,They promise to fix it but I don't know how far with that. The only option I will advice is to backup the (issueDB) and perform DBCC CHECKDB on the restored database on another server,that is using the ...


2

Just in case you are still interested, there is a potential workaround you can employ. Here is the updated code, which introduces the #deferResolution temporary table to each query in the procedure. Because the temporary table will only exist at runtime, the procedure is able to compile even though the proper columns don't yet exist on myTable. You will ...


1

I second the suggestion of a unique index on NetworkId+NetworkUserId. Incorrect results may be due to the way you're writing to that table unsafely. You have no transaction there and no locks being held on the table while performing reads and writes. I suggest that you rewrite your procedure with a MERGE statement: CREATE PROCEDURE [dbo].[Procedure] ...


2

First, make sure your SQL Server is fully patched and up to date. For 2008 R2, at this time I'd say the hard minimum is build 10.50.6220 (SP3 + MS15-058), and you really should be at 10.50.6537 (SP3 + MS15-058 + TLS 1.2 capability). Second, make sure you have a unique index on the combination of NetworkUserID, NetworkID (with the highest cardinality one of ...


0

One approach if you can't use a partitioned view is INSTEAD OF triggers like the examples below. Note that the UPDATE trigger assumes that one never changes a primary key value after the initial insert. CREATE TRIGGER [TRI ALL COMPANIES$Employee] ON [ALL COMPANIES$Employee] INSTEAD OF INSERT AS INSERT INTO [Company 1$Employee] (Col1, Col2, Col3) ...


1

The Import/Export wizard builds an SSIS package under the covers but the package the wizard builds only performs the E and L of ETL (Extract, Transform, Load). What you're looking for is T because you needed to transform this weird data into something more manageable. For the weird header row, you can always specify that there is no header row and the ...


0

The below query uses this sample data: Declare @xml1 xml = CAST('<root> <A><bb>1</bb><cc>6</cc><dd>3</dd></A> <A><bb>2</bb><cc>5</cc><dd>6</dd></A> <A><bb>3</bb><cc>4</cc><dd>9</dd></A> ...


1

One way is to calculate the aggregate inside a CTE (I blogged about this last month): ;WITH CTE AS ( SELECT time1, time2, t2 = SUM(time1) OVER() FROM #abcd ) UPDATE CTE SET time2 = t2/60/60; But I don't understand the value of putting the same SUM(), aggregated over the whole table, into that column for every single row. Seems easier to just store ...


4

Guessing at column names: INSERT #temp(dateColumn) SELECT dateColumn FROM dbo.WorkCalendar WHERE IsWorkDay = 1 AND dateColumn >= @begindate AND dateColumn < DATEADD(DAY, 1, @enddate); Also please try to avoid regional formats like m/d/y. These will bite you in the end, because someone will run your code on a server with different language ...


2

Issue was resolved.. Actually improper build of sql server caused for issue. Check the registry key level for mssql server for what the default data, backup path existing in the host.. Go for same path in the explorer and check whether the database files existing in the same folder.. if not then msagent certificate will be not created in that path. Eg. ...


1

The way I understand your question is that you would like to send 1 mail to each person with each a list of tickets they have assigned. For convenience I have created a temporary table with temporary data based upon your information. The email column was missing in yours. I create first a cursor that select distinct the owners. When I run over the cursor ...


1

Below is how you do it ... Create table and add processed column create table dbo.Tickets (Ticket bigint, [Owner] varchar(50)) go insert into dbo.Tickets select 14675, 'John' union all select 38759, 'John' union all select 45879,'Catheline' union all select 23980, 'Mark' union all select 16754, 'John' --- add a processed column so that next time ...


1

When calling sp_send_dbmail, you can set @body_format='HTML' and place the HTML source code in the @body parameter. Important: I'm not sure if there will be a plain-text version of your message for recipients that can't or won't read HTML formatted e-mail. Like Mark says, you should check the documentation for sp_send_dbmail for more information. Also, if ...


8

While I agree that you're raising an error even if you have a PK violation (as the other answer suggests, you need BEGIN/END, because your IF conditional currently assumes that everything indented after it will be part of the IF logic), that doesn't really answer why you're getting -4 specifically. You don't have an explicit return value in the event that ...


4

You don't have a BEGIN/END for the IF statement so it is executing the RAISEERROR regardless of the ERROR_NUMBER. I've added the block around the SELECT and the RAISEERROR and added a RETURN (0); in an ELSE block. ALTER PROCEDURE [dbo].[pSetUserItemLike] @UserID int ,@ItemID int AS BEGIN SET NOCOUNT ON; -- for exception handling DECLARE ...


0

@Brandon Williams - it's easy to pass this error. Just recreate procedures on the publication database. Execute this on the publication database: EXEC sp_scriptpublicationcustomprocs 'PublicationName'; Take the script and run it on the subscription databases. The errors will be gone.


1

There is an excellent blog post http://blogs.msdn.com/b/support_sql_france/archive/2012/02/07/sql-server-compilation-gateways-and-resource-semaphore-query-compile.aspx that explains what's happening. SQL Server allows for a set number of compilations based on their complexity. It groups them into small, medium, and large. For large compilations, there ...


3

If you look at Maximum Capacity Specifications for SQL Server, you will see that the maximum size for transaction log is 2 TB: SQL Server Database | Maximum sizes/numbers | Maximum sizes/numbers Engine object | SQL Server (32-bit) | SQL Server (64-bit) ... | ... | ... File size (log) | 2 terabytes ...


1

This error occurred because the TCP port was wrongly configured for the instance. Initially, I checked named pipes and shared memory; everything was perfect. Then I discovered that the TCP/IP protocol port was mismatched, by checking the port configuration in the IP Address tab. I enabled port 1433 for the server's IP address and pressed OK. Now it works.


3

Itzik Ben-Gan's blog post (link provided by Daniel Hutmacher) has some nice solutions to the problem for SQL Server 2012 and later: The Last non NULL Puzzle. Here is one more that will work in older versions (even 2005) and can be easily adapted for more columns. I haven't tested for efficiency or compared to the other solutions but I'd expect it to work ...


0

I would like to Add to 'Kin's answer and add that if you are running a 32 Bit office products and there for a 32 bit version of AccessDatabaseEngine which is what is used for communications to office products (Excel) Then you may have this issue, however if you change to 64 bit this should disappear. (and Ace will appear on your linked servers.)


0

I had this same error when trying to modify the file growth properties of the log file of a production database. I tried using SSMS and had the same error: Property size is not available for XXX database. I used T-SQL to modify the database log file instead. USE [master] GO ALTER DATABASE [MyDatabase] MODIFY FILE ( NAME = N'MyDatabase_log', ...


2

I've never tried this before but thinking this out logically, these are the things I would do (in no specific order): To answer your question yes, exclude drive K as a dependency as a cluster resource (this should prevent it from causing the cluster to go down when you need to remove it) Manually take those databases offline that are stored on drive K (I ...


-1

CREATE TABLE #ErrorLogAnalysis ( LogDate DATETIME, ProcessInfo NVARCHAR(255), [Text] NVARCHAR(MAX) ); INSERT INTO #ErrorLogAnalysis EXEC sp_ReadErrorLog 0,1 SELECT * FROM #ErrorLogAnalysis WHERE [Text] NOT LIKE 'Login Succeeded%' ORDER BY LogDate; DROP TABLE #ErrorLogAnalysis


2

Is there any way to prevent this time consuming process? There sure is! Stop caring about column order. If it is that important, be willing to pay the price, or spend more time up front on the design so that you're not re-deploying the same table multiple times before you get the column order "right." SSMS does the exact same thing if you use the ...


3

You are correct, the only way to "rearrange" the order of the columns is to create a table with the new structure and push the old data into it, drop the old table and then rename the new table (or some variation of that). It requires copying all the data in the table and some drops and renames.


1

I doubt seriously that the entire deployment happens in one batch and therefore variables wouldn't persist throughout the deploy (but you could test it). What I think makes more sense is to just store any "pre" values in a temp table (maybe a global temp table) and then retrieve them during the post deploy.


0

As @Aaron Bertrand SQL Blog Aaron Bertrand SQL Blog to Troubleshooting Error 18456. The Error 18456 generally means a failed login attempt. Like other error messages, it also has a State number and there are different State numbers associated with it, each having a different meaning for the failure. Error: 18456 Severity: 14 State: 38 Reason: ...


1

The database could have the auto close option enabled, which means it's closed when the last user logs off, in order to save resources. On a SQL Server Express instance with lots of other things happening maybe this is a solution, but even then I would recommend against it. When the database is closed, an antivirus product might begin scanning it, and thus ...


0

I had this same issue when google geocoding and parsing the XML result. Seems that when run via an SQL job the XML result was being truncated. Found the solution here: http://www.progtown.com/topic376715-a-problem-with-spoagetproperty-p2.html Had to put: SET TEXTSIZE 2147483647; at the start of my stored proc.


3

How about: WHERE D.NDISCOMPLFAB LIKE DS.codigoDesenhoSubstitutivo + '_' ...which will allow an index on (NDISCOMPLFAB, Dendis) to be used effectively. Please check that the column types are the same kind between D.NDISCOMPLFAB and DS.codigoDesenhoSubstitutivo, such as both nvarchar or both varchar. If they are different, use: WHERE D.NDISCOMPLFAB LIKE ...


2

"Should I add more memory to the server" is a tough question to answer. Your box might be coping "just fine" with what it has, or you may find that tuning the system in other ways (expensive queries, for example) would be more effective. There are a number of resources that you should consider, including Jon Kehayias' How much memory does my SQL Server ...


2

Since you're doing a TOP(1), I recommend making the ORDER BY deterministic for a start. At the very least this will ensure results are functionally predictable (always useful for regression testing). It looks like you need to add DC.D_ID and CJ.CORRESPONDENCE_ID for that. When looking at query plans, I sometimes find it instructive to simplify the query: ...


5

I've read the results from this post and understand the concept of a Row Goal etc. What I'm curious about is how I can go about changing the query so that it uses the better plan Adding OPTION (QUERYTRACEON 4138) turns off the effect of row goals for that query only, without being overly prescriptive about the final plan, and will probably be the ...


2

Amending Paul's answer: You seem to be confused why an index that essentially copies the whole table can be beneficial. Indexes can be used to accelerate WHERE predicates and ORDER BY directives. The fact that your query plans have key lookups shows that the queries have at least a WHERE or an ORDER BY in them. It is not at all an egregious thing to do to ...


7

The missing index suggestions are opportunistic entries added whenever the optimizer happens to notice that an exact-match index for the current set of predicates it is considering do not exist on the base object. The information recorded in the DMVs is intended to be a helpful input to the normal activities of a skilled database tuner; it is not intended ...


2

As of January 29th, Microsoft SQL Server supports TLS 1.2 for SQL Server 2008, SQL Server 2008 R2, SQL Server 2012 and SQL Server 2014 and major client drivers like Server Native Client, Microsoft ODBC Driver for SQL Server, Microsoft JDBC Driver for SQL Server and ADO.NET (SqlClient). Blog post about the release: ...


1

As of January 29th, Microsoft SQL Server supports TLS 1.2 for SQL Server 2008, SQL Server 2008 R2, SQL Server 2012 and SQL Server 2014 and major client drivers like Server Native Client, Microsoft ODBC Driver for SQL Server, Microsoft JDBC Driver for SQL Server and ADO.NET (SqlClient). Blog post about the release: ...


1

IMO if you are going to the effort to migrate then you should go all the way to 2014 even if you run it in 10.0 compatibility mode. You are going to pay for the license anyway. Also the regression testing effort and developer/DBA learning curve will be significant in either case. If you stop at 2008R2 now you will just have to repeat the exercise again in ...


2

I'm not sure how to fix this In the comments we investigated but never found out conclusively whether the page is allocated or not. If it's allocated this is definitely a corruption that CHECKDB missed. A bug in CHECKDB. Please report that to Microsoft. If it's not allocated here's how I would fix it: Fill the file with dummy data e.g. ...


10

So my question here is not how, rather will like to know the risk involved or any pre-checks that can be done before planning this upgrade? You should run upgrade advisor and address the issues reported by it before migrating. Refer to my answer for an extensive list of pre and post upgrade steps. SQL server which needs to be upgraded from version ...


10

That's a really big question so let's break it up a bit. What can I do in advance? Start with some required reading. 2008 R2 Backwards Compatibility 2008 Backwards Compatibility These links have links to further information such as Deprecated SQL Server Features Discontinued SQL Server Features Breaking Changes Behavior Changes to SQL Server ...



Top 50 recent answers are included