New answers tagged

13

TOP 100 with no ORDER BY means it is undeterministic which 100 rows from the remote table end up participating in the join. This is execution plan dependant and can vary. If it is a one to many relationship it may be the case that one batch of 100 rows has more matches on the other side of the join than another different batch of 100 rows. You should ...


0

You can try forcing the remote query to run remotely: SELECT R.Something, L.ID, L.Something FROM [dbo].[LocalTable] L INNER JOIN ( SELECT TOP 100 Something, L_ID FROM OPENQUERY([RemoteServer], 'SELECT Something, L_ID FROM [RemoteDB].[dbo].[RemoteTable]' ) ) R ON L....


0

Take a recent copy of your database and install in a test environment with similar (or identical if possible) specs. Create your index there. Now when you go to production you will have an idea of how long it should take. When in production it is best to perform this as part of a maintenance window without any other applications hammering away at the ...


4

A computed column would solve the issue and probably be better than having triggers: CREATE TABLE #Moofasa ( gotitdate DATE, dunitdate DATE, delayed AS COALESCE( DATEDIFF(day, gotitdate, dunitdate), 0) ) ; Tested at: rextester.com: INSERT INTO #Moofasa (gotitdate, dunitdate) VALUES ('2016-07-01', '2016-07-05'), ('2016-07-01', '2016-07-04'), ...


0

Yes, it depends on a many factors and there's no way to really know unless you try. I just built an index a single column of type BigInt over 200+ million rows however and it took a hairy 3 minutes. Sure, my hardware is different from yours, my database version is different from yours etc. etc. but now you have a real world example to give you a ballpark ...


5

We found that this error occurred because forced parameterization was on at the database level. For other readers who want to reproduce it, use Kenneth's sample code in a database with this setting on: create database filterme; GO alter database filterme set parameterization forced; GO For parameterized queries, SQL Server doesn't want to cache an ...


0

I think you can get what you need from the DMVs, including sys.dm_fts_index_keywords_by_document and sys.dm_fts_parser, something like this: SELECT p.phrase, kbd.display_term FROM sys.dm_fts_index_keywords_by_document( DB_ID(), OBJECT_ID('dbo.documentText') ) kbd CROSS APPLY dbo.phrases p CROSS APPLY sys.dm_fts_parser ( N'FORMSOF(FREETEXT, "' + ...


1

Try this: go create function dbo.fn_GetCountForPhrase (@phrase varchar(500)) returns int as begin declare @count int declare @localphrase varchar(8000) set @localphrase='"' + @phrase + '"' SELECT @count=COUNT(*) FROM table a WHERE CONTAINS(table.description, @localphrase) return @count end go SELECT phrase, dbo....


1

To add a default is easy: CREATE TABLE dbo.t ( tid INT PRIMARY KEY, ggid UNIQUEIDENTIFIER NOT NULL DEFAULT '00000000-0000-0000-0000-000000000000' ) ; Then the following insert will work: INSERT INTO dbo.t (tid) VALUES (1), (2) ; Test at rextester. If you want NULLs to not raise errors but be converted to the default, I think the easiest ...


1

You could also fire this off in a few concurrent sessions. Only one session will be able to get the exclusive lock at any one time. BEGIN TRANSACTION; EXEC sp_getapplock @Resource = 'FooBar', @LockMode = 'Exclusive'; WAITFOR DELAY '00:00:30'; EXEC sp_releaseapplock @Resource = 'FooBar'; COMMIT TRANSACTION;


2

BEGIN TRANSACTION SELECT * FROM YourTable WITH (TABLOCKX, HOLDLOCK) WAITFOR DELAY '00:05:00' -- 5 minutes ROLLBACK TRANSACTION And then in another Query Window: SELECT * FROM YourTable


3

This is pretty easy to do. Open two query windows in Management Studio. Run something like this in the first one (choose a test table that nobody is using, because this will block them, and make sure it has at least one row in it): BEGIN TRANSACTION SELECT * FROM sometesttable WITH (TABLOCK, XLOCK, HOLDLOCK) Then in the second query window: SELECT * ...


0

Start sql server in single user mode - Net Start MSSQLServer /mSQLCMD /f/t3608 To be in single user mode sqlcmd -S. –E Run the commands Alter database tempdb modify file (name = tempdev, filename = 'E:\SQLDATA\DEFAULT\tempdb.mdf') Alter database tempdb modify file (name = templog, filename = 'E:\SQLDATA\DEFAULT\Templog.ldf') Stop sql server Net stop ...


1

One option that entails minimal change in the calling code is to pass in simplistic XML. This is very similar to Option 2 in @JulienVavasseur's answer, except that it keeps the two input parameters separate. The thought here is that it should be easy enough to convince the developer to change the existing String.Join(',', _SomeIntArray) to instead be ...


0

Exactly how did you run the SHRINK commands? My understanding is that by default it just releases unused space at the end of the file, but you can also have the database compact itself first to make more space available. This is a slow and expensive operation, though; around an hour per 10 GB in my recent experience. That said, I suspect your database is ...


4

There are several ways of doing it. Changing the data model may also be a better option if you can do it. 1. Comma separated parameters If you want to stick with the comma separated string, you can find many string split functions online using CLR, CTE, XML, ... I am not going to put them all here and I won't benchmark them. But you must know that they all ...


2

A quick solution here is to change your where clause from '=' to 'in' (as an example) where col1 = @c1 --> where col1 in (@c1) and change the @c1 to a value list with single quotes, for example @c1 = '1,2,3' --> '1','2','3' So here is the modified procedure script CREATE PROCEDURE [dbo].[FindResult] (@col1 VARCHAR(15) = NULL, @col2 ...


3

This is just an educated guess mind you, but I would assume it is the date the new master database was created at Microsoft for SQL 2005. I checked SQL 2012, 2014 and 2016 and all have the exact same date/time.


3

At some point, you've got to split that string. Either before calling your procedure as suggested by @MguerraTorres, or in the procedure. Here's a good example of a string splitter function: http://www.codeproject.com/Articles/7938/SQL-User-Defined-Function-to-Parse-a-Delimited-Str That function returns a table data type. You can use that table to query for ...


0

It is not possible to make a stored procedure run twice for one single input. I'd suggest a CURSOR or Loop which will Execute the stored procedure once for each desired row in the table. Each loop iteration will: 1) Set two variables to be equal to Col1 and Col2 2) Run the procedure with the new Variables as Input Parameters. --Loop Code Here --For each ...


1

There is a bug in the SQL Server 2008 Upgrade system where even after upgrade, the system still shows the evaluation expiration message. This is resolved in SQL Server 2012 onwards.


0

------------------------------ --erezbensimon@gmail.com - July 2016 use master; go SET NOCOUNT ON print '----------------------------------------------------------------------------------' print '--Script for Moving Multiple database files to a new drive / ' + CONVERT(varchar(256),getdate() ) print '-------------------------------------------------------...


2

Paul Randal (of the SqlSkills and checkdb fame) has a nice set of pre-corrupted databases and instructions how to work with those too.


4

I would like to know if shrinking a data file is a logged operation or not. It is fully logged operation and is severely bad process so ALWAYS try to avoid it. What it does is, when you hit the shrink button, quoting from Paul Randal blog A data file shrink operation works on a single file at a time, and uses the GAM bitmaps (see Inside The Storage ...


2

Check out the work by Steve Stedman. He has corruption examples for days, http://stevestedman.com/?s=corruption&submit=Go


2

How about keeping all the incremental data inside the inactive table until the end of the day, then load it into active and truncate inactive? Your view could reference both tables using a UNION ALL, that way you aren't switching between the two tables. You could use partition swapping with the same method. You'd partition both tables, load inactive until ...


0

For us, this was caused by installing Visual Studio 2015 on our build servers. I followed the steps in one of the answers in this link Thanks for reaching out regarding this. From time to time it's necessary for us to change the format of the model.xml file, and when this happens it's necessary to install the updated version of SqlPackage.exe, so that ...


0

It appears as if ssrs 2016 does not support IE9. The machine I was using to access the server remotely only had IE9 installed. When I used the IE development tools on the server itself to browse to the site using IE9 standards, I had the same problems.


3

Actually there is a simple way. You can add a jobstep in the very beginning and put the following code in the step declare @job_id uniqueidentifier; set @job_id=CONVERT(uniqueidentifier, $(ESCAPE_NONE(JOBID))); if getdate() > '2016-07-15 17:00:00' -- change to your preferred end time begin EXEC msdb.dbo.sp_update_job @job_id=@job_id, @delete_level=...


3

This example code should work, but there are some things to watch out for. Since this is updating the tables while (presumably) another process is using that table to schedule jobs, concurrency could be an issue. For example, I'd assume there is another key field not mentioned that the scheduler process is using to identify the jobs. If it's the xNumber/...


2

Disaster Recovery Plan depends on 1.Acceptable Downtime 2.Acceptable Dataloss Any Disaster recovery plan you devise will revolve around the above two points only Either option meant work for the App guys, because all the applications and associated services would need to re pointed Theoretically, this meant no work for the App team but ...


2

Yes simple way to temporarily stop Logshipping is to disable all the jobs related to Logshipping, since LS is basically backup of transaction logs and restore you have control over it. You need to disable Backup, copy and restore jobs. Please also make sure you disable LS_Alert jobs because they would fire and unnecessarily give you false alerts. Caution: ...


3

Can an SQL Job Deletes itself after its end date? I have created a job which will sets up its start date and end date automatically with a dynamic value. Is there a way to code it while creating or scheduling ? You can do this by using sp_delete_job and dynamically creating applicable syntax and controlled logic to execute this for the ...


0

I observed few orphan jobs under SQLserver agent(which i created in past for testing), in sql server agent logs there were number of error for these jobs. I cleaned up all unnecessary orphan jobs and restarted agent and after that ERROR 'The merge process could not connect 'resolved.


1

The hosts file on a server is typically located at: C:\Windows\System32\drivers\etc. In this location you will see several files, one of which is the Hosts file. Edit with caution.


1

You don’t need to delete the already missing database.The user is configured to use a none existing or deleted database as a default database for the application. First, use trace and check for which application the user is failed to login, then on the application, change the default database to one of the existing databases. Sadequl’s post might help you ...


0

So this is what I came up with as a workaround if the intention is that multiple people in your organization should be notified if a job fails and a different group of multiple people for successes. You will notice the Steps 1-3 are the normal tasks that the schedule job is uses for as you would do for your task. There can as many steps as needed before ...


0

Not sure if it will improve performance but you are killing a number of left join with the where WHERE ipx.ProviderType IN ('IRF') turns that into a join SELECT ip.EpisodeID, SUM(ipx.LengthOfStay) as LengthOfStay FROM dbo.[IPHDR] ip LEFT JOIN dbo.[IPHDR_EXT] ipx ON ip.IPHDRRecordID = ipx.IPHDRRecordID WHERE ipx.ProviderType IN ('IRF') GROUP BY ip.EpisodeID ...


1

Same version of Powershell on the servers/vms hosting SQL Server 2008R2, 2012, and 2016? A parsing error that removes the double quotes around the query would result in this error. Lonny Niederstadt


0

SQL Server Management Studio (2014) needs Visual Studio 2010 shell to run. You can re-install Visual Studio 2010 shell via the free download from Microsoft: https://www.microsoft.com/en-ca/download/details.aspx?id=1366


2

You're missing a CREATE USER to add them in as a database principal. CREATE LOGIN [TRAINW7X64CA04\Test8] from Windows with default_database = Eclipse; Use Eclipse; CREATE USER [TRAINW7X64CA04\Test8]; EXEC sp_addrolemember N'db_owner', N'TRAINW7X64CA04\Test8';


0

So it sounds like you do not have to parse the pdf, just import the entire thing into one column. If so, you could try using SSIS. Create a data flow task and use the Import Column transformation, this should allow you to import the pdf as an image; you'd just need to specify the path to your files. As far as processing several files in the folder, create a ...


2

@wBob got it in one. It's all about those NULLs. The following script creates the Destination table with a NOT NULL modified column and it alters the same column on the TableName_Partition2 to be NOT NULL after creation. This allows the SWITCH to run. CREATE TABLE [dbo].[SourceTable]( [Col1] [bigint] NULL, [Col2] [int] NULL, [Col3] [int] NULL, ...


4

Go to Programs and Features, select SQL Server 2008 R2, and then the Remove option (people tend to panic that this will just remove everything without further input, but this merely launches a wizard): After this, you will be guided through a wizard much like installation. On the Select Instance screen, select the "remove shared features only" option: ...


0

I get this error message during deployment of a SQL Server database tools solution against the database (on SQL Server 2008 R2). After the deployment finishes, the error is gone. It's a little nasty because neither is the DB in Single User mode or readonly (of corse not) or anything else indicating the reason.


2

If you have two objects with the same name, it is almost certain that one is in a different schema. So to differentiate, you need to include the schema name in the call to sp_help as well. EXEC sys.sp_help N'dbo.tablename'; EXEC sys.sp_help N'otherschema.udtname'; You should always be using schema-qualified object names anyway: Bad habits to kick : ...


0

Looks like it was MAXDOP (the SQL Server max degrees of parallelism setting). After changing the value from 4 to 8 (and ultimately 16), the disc backup performance went back to what it was before (and the SQL query performance too). I really have no idea how MAXDOP could have had an impact on disc performance, especially as the value hadn't changed as part ...


0

For "2) How to I take a partition out of service?" This is probably a terrible idea but... Could you: Bring the database offline Delete the physical files in the FileGroup Bring database online Alter partition to exclude the FileGroup Rebuild Indexes



Top 50 recent answers are included