All versions of Microsoft SQL Server (not MySQL). Please also add a version-specific tag like sql-server-2016 if that is relevant to the question.

learn more… | top users | synonyms (2)

2
votes
1answer
117 views

How can I combine two queries

I would like to combine these queries: UPDATE T1 SET Opening_date = T2.Opening_date FROM FactTransaction T1 INNER JOIN DimStore T2 ON T1.cod_Store = T2.Cod_StoreKey and UPDATE FactTransaction SET ...
3
votes
1answer
267 views

calculate row wise total sum

SELECT RegionManager, [Face-to-Face], [Phone], [Face-to-Face]+[Phone] as[Grand Total] FROM (select ed.FRST_NM + ' ' + ed.LAST_NM AS RegionManager ,ACTVTY_TYP ,CALL_ID --,Month(CALL_DT) ...
7
votes
0answers
168 views

SQL Server 2012 Compatibility with New TLS 1.2 Standards [duplicate]

I'm trying to switch off TLS 1.0 on my SQL Server 2012 server in order to comply with PCI standards. Initially I had some trouble with the SQL Server service not starting. I found help for this ...
1
vote
1answer
183 views

How to remove triggers from the restored database in SQL Server

I am using SQL Server 2008 Management Studio. I've got a .bak backup file from a client to resolve a specific issue. Once I restore the database from the .bak file, I am unable to do any alter query ...
1
vote
2answers
65 views

SQL Server 2008 having issues with HAVING clause

I have a question regarding a report I am currently writing. I need this query to only show me line items that have < -5 as mins_elapse Here is the code. I have left out the FROM clause to ...
2
votes
3answers
53 views

SQL Server: How to add a full join to two tables but have matches line up?

I am working with a system that has two databases. One for the ERP system and one for the website. Both DB's have tables for alternate codes. I am trying to write a query that will show me differences ...
1
vote
1answer
20 views

Grant Permissions Within Report SP

Leaving aside why one would want to do such a thing: In MS SQL Server 2008, Is it possible to have a report SP grant permissions to a column used in that report to a table in a database other than ...
3
votes
2answers
579 views

SQL Server 2014 Developer on Windows 10?

Has anyone tried to use SQL Server 2014 Developer Edition (64 bit) on Windows 10 yet? I have a Lenovo G50-45 laptop that came with Windows 8.1 and I have the option to upgrade, but I don't want to ...
2
votes
2answers
241 views

Use random string variable in a insert

This is one of my first tries to code something. I'm trying to develop a code, to add first name, last name, and date, randomly. I have this for now: declare @date date, @val1 int, ...
5
votes
4answers
236 views

Compare each column in a row and return an error if any of them is different

I'm attempting to find any identifiers with any values that are different in the same row. +------------+--------+--------+--------+--------+ | Identifier | Value1 | Value2 | Value3 | Value4 | | f001 ...
19
votes
1answer
740 views

Is my SQL Server patched?

How can I tell whether my SQL Server instances are patched? Is there native functionality that will identify whether there are patches available for my server? Heck, do I even have version data ...
12
votes
2answers
302 views

Why do SQL Server row estimates change when I add a join hint?

I have a query that joins a few tables and performs pretty badly - row estimates are way (a 1000 times) off and Nested Loops join is chosen, resulting in multiple table scans. The shape of the query ...
0
votes
0answers
34 views

Blocking SPID has Zero Disk IO and Zero CPU Time

Running sp_who2 and following the BlkBy trail up to the root cause of blocking returns a SPID which has CPUTime and DiskIO values of 0; yet it's blocking 4 other SPIDs. I'm confused by how this is ...
1
vote
1answer
51 views

Temporarily removing replication from Publisher

Is it possible to temporarily remove Transactional Replication from a publisher database in order to allow a release to be executed against the database? If I stopped the log reader agent would I be ...
2
votes
1answer
197 views

Categorize age ranges and display in one column

I want to see the data as follows Member Age Category Test1 25 Under 30 Test2 55 51-60 Test3 67 Above 60 I have used the below query (from an answer to my previous question) to ...
6
votes
2answers
143 views

Scripting an application role with a hashed password

I need to script an application role with a hashed password, so I can copy it from one database to another. Consider the following example code, which uses an Application Role to provide elevated ...
0
votes
0answers
14 views

Downgrading SQL Server 2008 R2 from Enterprise edition to Standard editions [duplicate]

Can anyone tell me when we downgrade from SQL Server 2008 R2 Enterprise to Standard edition in a production environments. What part of SQL Server is needed to install and run a SSIS package? I need ...
7
votes
2answers
247 views

When running a script with several commands, how to find out where we're at?

I have some processes that require different steps to be taken before they start. For example: Disable check constraint on foreign key: select 'alter table '+fk.table_schema+'.'+fk.table_name +' ...
0
votes
1answer
22 views

Replay trace file and filter on error>0?

I have captured a trace file using the replay template. It includes all columns available, of course including ERROR. When I replay this trace file, the "trace configuration" window pops up, I declare ...
0
votes
0answers
215 views

Speeding up an openrowset between SQL Server and MySQL

Good morning I'm trying to import data from an MySQL table using openrowset so I can do calculations and transformations on SQL Server. The problem is that the table in question is quite big. The ...
1
vote
0answers
23 views

How to identify a process that has been cancelled?

How can you identify a process that has been cancelled? for example: I was running this dynamic sql - via a linked server: declare @sql nvarchar(max) declare @i int declare @z int DECLARE @vCrlf ...
3
votes
0answers
49 views

Isolate database performance on an instance

I have SQL Server 2014 Standard Edition installed on a machine with two different users and two catalogs, each of them accessed by a different user (each user is strictly tied to his catalog). Is it ...
2
votes
0answers
64 views

session id null in sys.dm_os_tasks

We are seeing parallelism causing worker thread shortage in SQL,while investigating we found session id is null for some sessions,Below is the query used.What does this session id null means ? select ...
7
votes
2answers
474 views

Use database to track locking/unlocking of objects

I have a requirement to track lock/unlock actions of objects. Before any action made on an object (contract, partner, etc), an lock event is issued. After the action is finished, it issues the unlock ...
0
votes
0answers
30 views

To Get Possible Conditions for provided Symptom and set of symptoms

Please look into my Schenario for symptom checker I am providing you sample table structure with sample data CREATE TABLE Symptoms ( ID INT, Title VARCHAR(100) ); INSERT INTO Symptoms VALUES ...
1
vote
1answer
83 views

Identify sequence of events based on certain rules

I need help on identifying a sequence of events in SQL Server 2008 R2 rows please: This is the sample data with SQLFiddle: ID EventTime TypeID EventNr 1 06:34:51 ...
0
votes
0answers
34 views

Only some databases on primary are in a “principal/synchronized” status - not all

There are over 30 databases hosted in a clustered environment. The primary server lists 9 databases out of the 30 as "mirror, synchronized/restoring" when they should list as "principal/synchronized." ...
0
votes
0answers
29 views

Stored procedure deadlock on another process

Is it possible to reproduce a deadlock situation on a stored procedure (which executes about 10 other stored procedures) when you don't know the exact process it's being deadlocked on? When this is ...
1
vote
1answer
94 views

dynamically creating query in a loop [closed]

I"m looping through a table trying to create a dynamic sql query. It appears to work fine except when I'm concatenating results to make the actual statement. Psudo code follows WHILE (Data is in the ...
2
votes
1answer
162 views

Can I create a SQL Job to run on specified dates?

I want to create a new SQL job that runs the beginning of every quarter: 01/01 04/01 07/01 10/01 Is this possible? I know i can set it to run every 90 days but that's not exact. I am using SQL Server ...
2
votes
1answer
82 views

MDF file size and performance

We have peaks where latency (as measured by [Physical Disk\Avg Disk Sec read] on the drive where our data file is) goes up to 0.2 sec. I immediately started suspecting the SAN while the DBA is ...
1
vote
1answer
34 views

Can a SQL Server 2005 msdb database be restored as a User database?

Can a SQL Server 2005 msdb database be restored as a User database on another instance (same Build)? "Why?" you ask: I need a list of the mail items that existed prior to installation of two service ...
6
votes
4answers
474 views

How can I Debug a Buffer Issue?

I have a production "Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)" that is showing weird buffer and page life expectancy (PLE) symptoms. I am running this every minute on my server (to track ...
3
votes
1answer
130 views

Can I convert a VARCHAR field body to unquoted JSON result in SQL Server 2016 CTP 2.X?

I have downloaded and installed SQL Server 2016 Community Technology Preview 2.3, which if I understand correctly, should contain a built-in function called JSON_VALUE. (Update: CTP 2.4 has shipped ...
0
votes
1answer
92 views

Convert ASCII Bytes from XML to VARCHAR

I have XML data which includes strings as byte array, which I'd like to convert to VARCHAR in SQL Server 2012. Example snippet with text="2Hallo" and the code I tried: DECLARE @testxml XML = N' ...
0
votes
0answers
123 views

Backup storage file corruption: file not found. Possible cause of this error is corruption of backup file

I backup my SSAS cubes called "BI LIVE" and restore it to a specific location on a different server. I am using sql server 2014. This is where you can see the routine I use to run the backups: How ...
0
votes
1answer
44 views

SQL Server LockMatchID Command

I am seeing calls like those shown below in my SQL Server Audit logs but have no idea what it means. A google search turned up a possible relationship with compiles/recompiles. The line (or similar ...
15
votes
2answers
734 views

How to Drop Table with Non-Printable Character on its name

I have a legacy table with a name containing non-printable characters (CHAR(31), to be specific). The non-printable character is beside a underscore, and I've discovered that the shortcut ...
0
votes
1answer
371 views

The ALTER TABLE statement conflicted with the CHECK constraint

I need to check datecom is less than datelivr create table Commande ( Numcom int identity primary key, Datecom date, Datelivr date, Codecli int foreign key references clients (Codecli) ) alter ...
1
vote
2answers
207 views

what is the quickest way to delete duplicate rows?

I need to delete duplicate rows from a large table. what is the best way to achieve that? currently I use this algorithm: declare @t table ([key] int ) insert into @t select 1 insert into @t ...
2
votes
2answers
70 views

Moving database from one server to another

I have a virtual machine running SQL Server 2012 in Azure and have also generated a script of my database to be run on the SQLSERVER2012 VM on Azure, however I am getting an error "C:\Program ...
8
votes
3answers
1k views

Filtered index only used when the filtered part is in the JOIN not the WHERE

I've created the filtered index below however when I run the 2 queries further down this index only gets used for a seek in the first example that has the END_DTTM in the JOIN rather than the where ...
0
votes
1answer
930 views

Cannot remotely connect to SQL Server 2012 on azure

I have a SQL Server instance running on a virtual machine in azure, I have tried to connect to it using the public virtual IP address however I get an error saying The server was not found or not ...
1
vote
1answer
21 views

restoring SQL Server from backup into a database with different database name?

I have been provided SQL Server backups to restore but they have to be restored into new databases with different names on same server. To explain it, say I have database "MyDB" and its backup ...
8
votes
1answer
369 views

Replace value of strongly typed xml element in SQL Server with XQuery

Given a element, defined within an XML Schema Collection as such: <xsd:element name="xid"> <xsd:simpleType> <xsd:restriction base="xsd:string"> ...
-2
votes
1answer
44 views

Last date SQL Server query required

Scenario: I am writing a cash management system and want to put some date columns for getting date info / history. Actually there are two columns for date in same table at this moment. Voucher Date ...
-1
votes
1answer
190 views

Is UNION faster than UNION ALL with DISTINCT?

UNION can eliminate "Distinct" use to optimize the query? Like below, NO distinct is needed if the UNION is used to return distinct set. Sample data declare @T1 Table ( ObjId1 int , Name ...
2
votes
1answer
181 views

SQL Server AG databases went into 'Synchronizing/In recovery' after upgrading to 2014

We have 3 replica AG setup: 2 replicas are in sync/automatic failover the other(DR Server, different subnet) in asynchronous/manual mode All these replicas were on SQL Server 2012. Recently we ...
3
votes
1answer
397 views

Load balancing with Windows Clustering and AlwaysOn Availability Groups

I have some questions regarding Clustering and AlwaysON. If I need to build a 3 node cluster - 2 active and one passive. Each node will be 16 CPU and 128 GB RAM. Does this mean that I will have ...
3
votes
1answer
2k views

How can I resolve SSIS Excel Connection Manager Error 0xC0209303?

I have created an SSIS package that imports an excel file into a SQL Server table. The SSIS package runs without any problem when I run it locally on my machine but when I run it on the server where ...