Tag Info

New answers tagged

4

It seems to ignore any index I put on it Unless you're using SQL Server Enterprise Edition (or equivalently, Trial and Developer), you will need to use WITH (NOEXPAND) on the view reference in order to use it. In fact, even if you are using Enterprise, there are good reasons to use that hint. Without the hint, the query optimizer (in Enterprise ...


2

There are any number of ways to express this in T-SQL. One that works (without using APPLY - which you should learn to love, by the way) is below, with comments inline: WITH SampleData (PERSON,TRANSACTDATE, STARTDATE, END_DATE, IN_PUNCH,OUT_PUNCH,HOURS, PAYCODE) AS ( SELECT 1234,'08/03/2015','08/03/2015','08/03/2015', '06:00','09:00','3', 'REG1' ...


-1

For SQL 2008, we can use OUTER APPLY. Question about the order in which the LEAVE1 and LEAVE2 appear, if they have a particulare order ? Any how , here a sample of it. WITH SampleData (PERSON,TRANSACTDATE, STARTDATE, END_DATE, IN_PUNCH,OUT_PUNCH,HOURS, PAYCODE) AS ( SELECT 1234,'08/03/2015','08/03/2015','08/03/2015', '06:00','09:00','3', 'REG1' UNION ...


5

Assuming you want randomish ordering of your output you can just ORDER BY NEWID(). That would jumble up the ordering of the results reasonably well. If you only need to randomize after a certain order is established then you can still use the NEWID() trick. Below are some quick examples: -- Fully random ordering SELECT * FROM dbo.Foo F INNER JOIN ...


1

Well, there's bad news, good news with a catch, and some really good news. The bad news T-SQL objects execute in the database where they reside. There are two (not very useful) exceptions: stored procedures with names prefixed with sp_ and that exist in the [master] database (not a great option: one DB at a time, adding something to [master], possibly ...


0

Ok. I was able to find the root cause (hopefully) and circumvent it. Most likely the reason was type mismatch 'join' was because ACCT_ID was NULL in the source table. The below worked fine: IF NOT isNULL(DTSSource("ACCT_ID")) varamt = Lookup ("Lookup_tbl1", [Lookup_tbl1].[Amount], Array(DTSSource("DEPT_ID"), DTSSource("ACCT_ID"))) ...


1

As stated in Monitoring Database Mirroring MSDN You can monitor a mirrored database during a mirroring session to verify whether and how well data is flowing. To set up and manage monitoring for one or more of the mirrored databases on a server instance, you can use either Database Mirroring Monitor or the sp_dbmmonitor system stored procedures. A ...


1

Service Broker relies on certificates which is shared between databases. No other info for the user is shared. Service Broker provides 2 distinct types of security - Dialog security — Encrypts messages in an individual dialog conversation and verifies the identities of participants in the dialog. Dialog security also provides remote authorization and ...


6

Getting the SQL from a DDL Trigger for whatever query that is dropping this Stored Procedure will only help so much. If the query is coming from Dynamic SQL from a Stored Procedure, or from a release script, or an integration test, application code, etc, then you will likely only capture the DROP PROCEDURE ... which doesn't give much of a clue as to where ...


1

You can't install UTF-8 as a character set because it's not a character set, it is an encoding. If you want to store Unicode text you use the nvarchar data type. If you want to store text encoded using UTF-8, you store it as binary data (varbinary).


1

First add Category information Get ID number against that Category using function SCOPE_IDENTITY() and save value to a variable Then use product detials and that varible to insert values in Product table . DECLARE @CategoryName VARCHAR(200), @Category_id INT SET @CategoryName='CategoryName1'; INSERT INTO Category(CategoryName) ...


1

I think this is a situation where you just need a cross apply. If you are not familiar with it, look at this link. With your data I created a table and inserted the data with this scrip CREATE TABLE school ( schoolcode VARCHAR(20) ,SchoolYear INT ,Semester INT ,Term INT ,TermEnd DATETIME2 ); INSERT INTO dbo.school ( ...


0

I agree a pivot should get you what you need, but you should be aware that the column names are going to be fixed. Something like the following should work. You could also extend this example to use dynamic column names if you use dynamic SQL. SELECT * FROM ( SELECT [from] AS [Sender] , DATENAME(DW, [date]) + N'' + ...


0

The workaround is: Import the Excel sheet to a staging table Run this SQL query: update main_table set main_table.field1 = staging.field1, -- ... from main_table inner join staging on main.primary_key = staging.primary_key;` Delete staging table.


1

Well...We solved the problem. There was an Update, inside the procedure running that "select * from...". I commented the update. no more problems.


1

Please pay attention. The answer from Julien is correct. BUT if you attach the database to an SQL Server 2008 instance it will be upgraded! If the application can't handle SQL Server 2008 and needs SQL Server 2005 you're in trouble. Take an extra step and make a backup. Furthermore you have to create the needed logins on the new server. You might want to ...


1

You first have to detach it on your old server: USE Master GO -- drop all active connections and roll back open transactions ALTER DATABASE YourDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE GO -- detach it EXEC sp_detach_db 'YourDB' GO On your new server, once the files have been move, you can now attach it: USE master; GO CREATE DATABASE YourDB ON ...


1

You could use dynamic SQL to loop through the years. DECLARE @SQL NVARCHAR(MAX) SET @SQL = 'SELECT '; DECLARE @intYear INT SET @intYear = 2013 WHILE (@intYear < Year(GETDATE())) BEGIN SET @SQL = @SQL + '(SELECT SUM(EmployeePaid)*2 AS CurrYear FROM TblRecords WHERE FYear = ' + CAST(@intYear as CHAR(4)) + ') as Total' + CAST(@intYear as CHAR(4)) + ...


4

Iterative approaches do have their place, but generally speaking it is much better to use set based approaches when working with databases. I've heard that the math says that "set based solutions will be faster than iterative solutions in the vast majority of the cases" several times, but I can't seem to find a good reference at the moment. If you really ...


0

I think I would first outline something like this before testing it while recording time with a stopwatch. D-X get the newProd and newMirror ready (OS + SQL Server + User Account, job, ...) build scripts for every step (back, restore, mirror, ...) D-3 add a new IP to oldProd update DNS with new IP and TTL = 300s (Time To Live) D, H-2 or H-1 (I assume ...


1

This may be an issue with OLEDB calls to remote servers (linked servers and SSIS configurations use OLEDB). This is a design flaw, Microsoft SQL Server bug that wasn't patched until SQL Server 2012 SP1 from what I recall where it doesn't allow remote stats to be used to optimize the query remotely. You will need to run sp_WhoIsActive (download | docs) from ...


2

It's a simple select. Why is this using fetch_cursor? The SELECT is system-generated by the Distributed Query framework, and is associated with the UPDATE you found. The Remote Update query plan operator uses the sp_cursor model to fetch rows from the remote data source. This is the cause of all the cursor API calls. I believe the cursor plan you show ...


4

ASYNC_NETWORK_IO somehow indicates that the client application isn't processing results as fast as SQL Server feeds them. This could be caused by an issue with the client application or with the network connection between the server and the client application. Please refer to a post by Thomas LaRock The ASYNC_NETWORK_IO wait indicates that one of two ...


0

But when I restore another database backup that gets disconnected, i.e. not accessible. You have to add the user to the db_owner role after the restore. You can use ALTER ROLE [db_owner] ADD MEMBER [frank] or EXEC sp_addrolemember 'db_owner', 'frank'; Note: ALTER ANY DATABASE - You dont want to give the user too much rights since this will allow ...


2

To migrate, I will take full backup of 2008 and restore it on 2012. I want to know if the compatibility version internally remains 2008 or changes to 2012? Restore database backup on New SQL Server 2012 Server and then change compatibility level from 2008 to 2012 for each database. Use one of the following methods. i) Use script to change ...


2

I did small tests Database compatibility level remains the same. 1.Created dummy database on 2008r2 2.Restored it on 2012 So while restoring,i could see version being changed,but after restoring compatability level remained the same.So you need to alter database to 110 compatibility level to get benefits of SQL 2012 Database 'backupdb' running the upgrade ...


5

There are a few options that you could use for this. The first would be to just set default values within the procedure declaration to guarantee that there would never be NULL values passed in for these parameters: CREATE PROCEDURE [dbo].[sp_Example] @parameter1 char (8) = 'test', @parameter2 char (20) = 'test2'.... The second alternative to checking ...


0

Try this CTE. You should also read some tutorials about recursive CTE. declare @t table(id int, name varchar(10), books nvarchar(100)) insert into @t(id, name, books) values (1, 'dan', 'history,physics,english') , (2, 'ron', 'chem,social,biology') , (3, 'mon', 'it,ece,eee,www,zzz') ; With split(id, title, books) as ( Select id, left(books, ...


0

Adding important information about OS User name and Machine Name: Select Destination_database_name, restore_date, database_name as Source_database, Physical_device_name as Backup_file_used_to_restore, bs.user_name, bs.machine_name from msdb.dbo.restorehistory rh inner join msdb.dbo.backupset bs on ...


1

It's hard to give a proper answer without the full query. So let's pretend that your query looks something like this: SELECT sum(case when cast(l.settleddate AS date) < min(cast(f.datedue as DATE)) then 1 else 0 end) as BeforeFirstDue FROM tableL l JOIN tableF f ON f.someKey = ...


1

If you want to follow the Snodgrass approach, you have my deepest sympathies. You may want to glance at my approach, if only for comparison. And I would be remiss if I did not at least mention Tom Johnston, who has a new book on the subject. It is at least an improvement on Snodgrass! However, the issue of adding or removing columns to temporal data is ...


1

Are you a member of db_owner? See this: To use Database Diagram Designer, it must first be set up by a member of the db_owner role to control access to diagrams (https://msdn.microsoft.com/en-us/library/ms189279%28v=SQL.100%29.aspx)


0

You are mixing your granularity inside your CASE statement. You cannot compare an aggregate with a non aggregate unless they come from two different datasets


1

Im not sure if this answers all of your questions but this sounds very similar to what I do with our data warehouse. However the way i interpret your post you would want to have 1 table with your data, and another with your table definition meta data. For the data table add a column to flag is_current. (Either an int or bit datatype) Also have 2 date ...


1

DECLARE @myDate AS datetime SELECT @myDate = min(cast(f.datedue as DATE)) FROM tablef f SELECT sum(case when cast(l.settleddate AS date) < @myDate Then 1 else 0 end) as BeforeFirstDue FROM tablel l


3

If you know the exact string, then using a binary collation for your search can help. WHERE [value] LIKE '%{substring}%' COLLATE Latin1_General_BIN , because it won't have to do case conversions and the like. This can make it a few times faster, but not lightning fast. Another option is to consider blowing up your table and indexing that - using an ...


0

Just a note about triggers regarding of performance: If you have an application that may work frequently with different database engines such, let us say an ERP that should accept any known database engines such as MySQl, MS SQl, ORACL, Inerbbase...etc triggers may become a problem in maintaining the code since triggers syntax is different for each database ...


0

Okay, after heeding advice from the comments, I removed the top 10000 * from the select statement. After doing so, forcing the join order actually sped up the query as intended. It took 48 seconds (vs. 60 sec). Here's the original execution plan. And here's the forced join execution plan.


0

If you wrote the query statically with all the passed values in the WHERE, and added OPTION (RECOMPILE), this will cause the optimiser to do parameter embedding. The redundant predicates will be stripped. Only the needed query operators will be in the plan. You are probably suffering the cost of a statement compilation with each invocation anyway. The ...


2

What @Mike_Fal said. This is a huge gaping security hole. Not only does SQL need xp_cmdshell, but it will need Domain Admin rights. I also agree with the other sentiments around not using a trigger. You can still tie it in with your existing process and make it database driven with the security table you are using. I'd probably write a separate, ...


3

I would re-write the whole proc something like this..... ALTER PROCEDURE Member_WS @Program int = NULL, @Card nvarchar(30) = '', @Account nvarchar(128) ='', @Member bigint = NULL, @Email nvarchar(256) ='', @Phone nvarchar(20) ='', @ResponseType ...


1

[Note: I got to the end of writing this and noticed that sometimes you refer to the data field as image and sometimes as attach_data. You'll need to take that into account as you read this.] You might try breaking up the update statement into multiple batches with TOP: UPDATE TOP (5) messages SET attach_data = 0x WHERE ins_date < DATEADD(DAY, -120, ...


0

Consider indexing properly and running the cleanup once every minute. That way each batch is automatically tiny. Each day has 1440 such small batches then.



Top 50 recent answers are included