Tag Info

New answers tagged

6

I've always been a fan of a dynamic sql approach for this type of problem. I find it provides the optimal balance between complexity versus quality query plan. In the following code, I define a base query which does whatever it would need to do and then only add in the filters if the provided parameter is not null. CREATE PROCEDURE [dbo].[GetData] ( ...


6

SELECT * FROM Table1 WHERE Table1.URL LIKE '%' + @Parameter1 + '%' AND Table1.ID = @Parameter2 AND ( @Parameter3 is null or Table1.ID2 = @Parameter3 ); Take a look at the above example. If you change your AND clause to a nested OR clause specifying your initial expression as well as @Parameter3 is null. That will then demand that the nested ...


0

I managed to get it to work by changing the site in IIS to use the Application Pool Identity, then changing the Application Pool Identity to Network Service. Feel free to comment on any security issues this may raise.


1

I've run into this before and have used 2 different approaches. If the process should only run if files exist, I've written a c# script to set a flag 'DataExists' so I can evaluate and take different paths within the SSIS package. The other is I have a dummy file on the directory and when I build the expression in the foreach container I use variables that ...


0

I had a situation similar to this. Even though the SQL Server Configuration Manager showed that Force Encryption was "No" and the certificate page was clear, I continued to get this error and was unable to start the service at all. In the registry (for my installation): HKLM\SOFTWARE\Microsoft\Microsoft SQL ...


4

The FlushCache message in the error log is caused by checkpoint logging, and in this case by a long checkpoint (which is defined as a checkpoint that is taking longer than the recovery interval). Whether it's logged or not, the behavior is different in pre-2012 and 2012+. Before SQL Server 2012, to get checkpoint logging you'd have to turn on a trace flag ...


1

Have you configured MSDTC on both the source and target machine using dcomcnfg? The distributed transaction coordinator, by default, does not allow distributed transactions. You need to configure it by running dcomcnfg as an administrator, and configure the options applicable to your situation:


3

You can change this behavior with the ALTER SERVER CONFIGURATION statement. The flexibility to modify all of the necessary parameters (path, max files, max file size) should give you enough control to get it to where you want. See the bottom of the referenced link above for samples/examples. Here is one copied from the source: ALTER SERVER CONFIGURATION ...


1

Boiling down the question to the basics: Q: "is there some way to set a policy or something on the Microsoft SQL Server instance itself that will cause any SSMS clients to be colorized?" No. SSMS query color-coding is part of a client-side connection and that data is stored in the registry. You could theoretically export that registry setting and apply it ...


0

Create a new table that is partitioned as you want and copy the data from the old table into it rather than trying to apply the new partitioning scheme to an existing table. Once the data is copied, remove the relations to the old table, drop it, and rename the new to take the place of the old and recreate all of the relations. Perform this within a ...


0

It is most likely not the DOMAIN\computer$ at all. This message is known to be a misleading. I think all it wants is that "NT AUTHORITY\NETWORK SERVICE" be granted the server access. Open SQL Server login properties and add this account. Also, check what user is used in the IIS to impersonate anonymous user, it should be something like "IUSR_ServerName". ...


1

I managed to resolve the issue. I wasn't able to uninstall SQL Server but I downloaded an installer, converted it to a .zip and extracted it and then pointed the setup to that as the install media. Doing this I was able to get Management Studio up and running. Thanks for all the suggestions!


2

There are a few good DMVs (system views) where you can find this info: sys.partition_functions and sys.partition_schemes can be joined to sys.partition_range_values to get all existing ranges. The number of ranges should be the number of partitions. The data_space_id of the partition scheme can be joined to that of the index or heap in sys.indexes on the ...


1

The solution was removing the empty files E:\MSAS11.DWH_AS\OLAP\Data\Cube Fraude.0.db\MY_DATABASE OLAP.56.cub.xml which allowed the aggregation to continue


0

You could follow below suggestion to check whether windows installer is in consistent state. Go to run and type services.msc scroll to Windows Installer services Double click to bring up the service. Check to see if the startup type is set to Manual.Service status is usually stopped. Try and start the Windows Installer server.Also open CMD as ...


3

Variable colors share settings with table, object and other names. So you can change the text color using Identifier: Just note that it will also affect table names, column names etc. For the highlighted color of identifiers and variables, use Selected Text. The highlights will use a slightly muted version of that color. And no, these are not ...


0

There came no other solution up than manually make Usermappings to all databases existing before the server role does. For upcoming databases, the role will automatically applied.


1

In AGs writes can only occur on the primary. Shrink operations are writes. Therefore you must do the shrink on the primary. Note that the shrink may not shrink as much as you expect, your test on the restored DB had probably leveraged simple recovery model. Read How to shrink the SQL Server log for more info. Do not shrink to 160MB. Determine why did the ...


0

I found a solution. Here is example where filetable name is 'physical_files', target path is 'folder1', target file have stream_id = 'FF9CF452-B522-E411-A464-00259060BBB9' declare @path varchar(MAX); set @path = '\folder'; declare @parent hierarchyid; set @parent = GetPathLocator(CONCAT(FileTableRootPath('dbo.physical_files'), @path)); select @parent ...


0

Below is the script that I wrote for myself to make copy of database. Its flexible and can be converted into a stored procedure. The comments will explain what it does. Test it on a Test server before running it in PROD !! /* Author : KIN SHAH Purpose : Written for dba.stackexchange.com - This script will take the ...


1

Ask your boss to get you a pluralsight subscription. Additionally, read answers from people like Paul White, Aaron Bertrand, Remus, etc and try to answer questions on this site. Read blogs from sqlskills.com, brentozar.com, mssqltips.com, sqlperformance.com. Also folks from sqlskills and brentozar are giving inperson training in LD. Sqlbits event in UK ...


0

I suspect that you are not actually creating a SERVER ROLE but in fact are creating a DATABASE ROLE in the model database. I looked under server roles for a bit and could find nothing that would grant read access to a database (old or new). I can see ALTER ANY DATABASE, CREATE ANY DATABASE and VIEW ANY DATABASE but nothing about read/write of the tables. ...


1

You can split the debit/credit columns using a CASE syntax: (CASE WHEN [Debit or credit]='cr' THEN Amount END) AS cr, (CASE WHEN [Debit or credit]='dr' THEN Amount END) AS dr As for the "sno" column, I presume that the answer you're looking for is something like DENSE_RANK() OVER (ORDER BY TransactionType) AS sno


0

Below are some good links from Microsoft which tell you how to move databases specially content database of Sharepoint server. You can select appropriate sharepoint version. Move all databases in SharePoint 2013 Move content databases in SharePoint 2013 NOTE: I have installed SQL Server 2012 Std on same server but the db size limit still persist, is ...


0

I believe the mystery is cleared up. The documentation only says that the SQL Server account needs full access to the filestream folder, but when I checked who had access to the main DATA folder, I saw that the SYSTEM account also had access. I gave full rights to the FS folder to the SYSTEM user, and now I don't get this error anymore.


1

When you failover a cluster resource group from a node to different node SQL Server instance and other resources which are part of this resource group is stopped on current node and then ownership is changed to node which you are failing over to. After ownership is changed SQL Server is restarted with owner as new node. During failover SQL Server might go in ...


0

Nothing should happen other that the cluster resources should fail over. If other resources are failing than something isn't setup correctly.


2

I want to log all logons using a logon trigger from server A in server B. Ideally, if you are using Enterprise edition, then as Shawn mentioned, you can use SQL Server Audit. You can even set up a light weight server side trace and then define a WMI event notification - TRACE_FILE_CLOSE to automatically load the trace data into a table when it ...


0

SQL Server Audit is your best option if you are using Enterprise Edition, anything less (Standard) does not allow database level auditing. However you could just as well setup Extended Event session to do the auditing for you at the database level. You can set the target to a file or ring buffer. Ring buffer would require period polling to persist the data ...


2

Start out with getting all the procedures from sys.procedures and then use EXEC sp_helptext on each to load the text of the procedures. Search for text to add, add if needed, load the updated text to a variable and execute dynamically.


1

Since you are in 2012 version, here's another way to solve this gaps-and-islands problem that uses the new LAG() function: ; WITH seq AS ( SELECT SiteIDNumber, SequenceNumber, LagSequenceNumber = LAG(SequenceNumber) OVER (PARTITION BY SiteIDNumber ORDER BY SequenceNumber), ...


5

The merge join works like a zipper - if you don't care about order, SQL Server knows that it can sort the input in any way it wants, and not have to worry about re-ordering anything. When you add the order by, in this case a merge join is no longer the best choice, because materializing and sorting the first CTE twice in the order defined by the ROW_NUMBER() ...


2

On Checking for #tbl It can't hurt to check for the table's existence (and drop it if it exists) at the beginning of the procedure, but it depends on how you want to handle that scenario, and in most cases it's not possible for it to exist already anyway (at least if we're talking about the same #temp table as defined within that stored procedure). You ...


0

Do you just want to configure this? Or are you not averse to writing some code? In the latter case, it should be relatively easy to write a small program that connects to both the LDAP server and the database and synchronizes the data. I've written such a thing once in PL/SQL in Oracle -- if it's possible there it should be possible in most languages and ...


1

I think it would depend on how you are using temp tables. If SELECT INTO is being used I would test before and drop it at the end. Testing before hand and dropping will prevent unwanted issues down the road that may or may not creep up, it is just good code practice to me. If you are using CREATE TABLE to first create the temp table I think I would do the ...


6

I assumed it would follow my hint, and maybe error out at execution time if I wound up with some bad data and the index was missing some needed values. The query optimizer will only use a filtered index in a query plan if it can guarantee (within its reasoning framework) that all possible matches can be served from the index. This is by design, to avoid ...


2

To use a filtered index it wants to see the predicate there, or one that matches very closely. So explicitly saying AND ins.ParentID IS NULL is going to be useful. Now, you should generally include the columns you're filtering on in the index itself because of a QO quirk. If the predicate in the query matches exactly the one in the index, and you're not ...


1

You could use a variation of your INSERT INTO ... query even if the columns are not named the same, and have a differing quantity of columns. You simply need to name the columns explicity. For instance: CREATE TABLE [dbo].[WordList]( [Word] [varchar](255) NOT NULL, [WordRow] [int] IDENTITY(1,1) NOT NULL, ); CREATE TABLE [dbo].[ShortWords]( ...


2

.mdf and .ldf files do not show the last modification date in the way you think it does. The file-system date reflects the last time the instance started the database. Modifications to data continue while the instance has the database open, without the instance actually updating the file-system dates for the files in use by the database. Can you add the ...


2

The client connection role for secondary replicas determines whether your database is always available, or only available to read-only connections, or not available at all. If you set the database to read-only connections, then your connection string has to include the parameter ApplicationIntent=ReadOnly as defined in that Books Online page. When you try ...


4

There is no "BEST" way to add a column. There is only one way - using the T-SQL command. What do you think happens when you use SSMS table design? it's just a way to simplify tasks for those who don't know the syntax. When you click OK, SSMS generates the ALTER TABLE statement and executes it for you.


3

I would not recommend SQL Server Management Studio's Table Designer, since in my experience, it will create a new table, move all the data, then drop the old table and rename the new table. The syntax for renaming a column is not to ALTER TABLE, but to use sp_rename: EXEC sp_rename 'table_name.old', 'new_name', 'COLUMN'; ...


1

There is another way to rebuild an entire MS SQL database. Step 1: Use script to copy data of all tables from Old to New database. Declare @OldDBName Varchar(200) Declare @NewDBName Varchar(200) Select @OldDBName='OldDB',@NewDBName='NewDB' Select 'Select * into '+@NewDBName+'.'+sc.name+'.'+tbl.Name+ ' From '+@OldDBName+'.'+sc.name+'.'+tbl.Name ...


3

To make the scripting task alot easier, you can also use 3rd party tools. MSVisualStudio has both a Schema and Data comparison toolset. I personnally prefer RedGate's SQLCompare tool. I also use theses kind of tools all the time to ease DEV->QA->PROD deployments. Once the schema is in place, you do a simple "Data import Wizard" to copy the data over, as ...


1

Management Studio remembers each server that you connect to and the logins used to connect to them. The logins are remembered in from the last new login used. For example if you connect to a server for the first time as 'sa' and then connect as 'bob', 'bob' will appear at the top of the login drop down. Then if you connect as 'tony' then 'tony' will ...


3

A simple approach if all you care about is column names and merely identifying any tables that have the same naming scheme but have columns that don't exist in the "model" or "ideal" table (also, I kept this simple, assuming all tables are in dbo): USE tempdb; GO CREATE TABLE dbo.repo_2012_08_04(a INT, b INT); -- model CREATE TABLE dbo.repo_2012_08_05(a ...


2

You could use the CHECKSUM() and CHECKSUM_AGG() functions. Though not perfect (checksums aren't, by definition), you'll get a pretty nice resultset to look at, where you can quickly spot the differences. SELECT tbl.[name], CHECKSUM_AGG(CHECKSUM(col.[name], typ.[name], col.max_length, col.[precision], col.[scale])) AS ...


0

Something like this would give you all the different tables, that have the same column name: SELECT t.[name], c.[name], t2.[name] FROM sys.columns c INNER JOIN sys.tables t ON c.[object_ID] = t.[object_ID] INNER JOIN sys.tables t2 ON c.[name] = t2.[name] WHERE c.[object_ID] <> t2.[object_id] But it would be better to provide a list of the column ...


1

How do I transfer a database from a physical server running SQL Server 2012 to another server running SQLEXPRESS 2008 Direct transfer of database using backup restore or using attach dettach is not possible because you cannot move database from higher version(SS 2012) to lower version(SS2008). Its not allowed by Microsoft. What you can do is script out ...


5

Use SSMS to generate scripts for the entire database DDL. Use the script to create a new, empty database. Use BCP to export the data from all tables. Use BCP to import that data into the new database. MAKE SURE YOU BACKUP THE ORIGINAL DATABASE before DROPing it (if you in fact intend to drop it at all). MAKE SURE YOU TEST RESTORE THE BACKUP.



Top 50 recent answers are included