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)

4
votes
2answers
36 views

SSIS OLEDB Command not firing triggers

Got a bit of a strange one that I can't seem to work out. We have a vendor database ( so we have no control over the table structure, foreign keys, etc...) that's on SQL Server 2012 SP2. We have a ...
2
votes
1answer
26 views

How can I have flexible PIVOT column names?

Following the example in https://www.mssqltips.com/sqlservertip/1019/crosstab-queries-using-pivot-in-sql-server/, I have a PIVOT that works but is not very flexible. It is basically SELECT * FROM ...
1
vote
2answers
96 views

Which data type should I use to store a 11 digit number [on hold]

Which data type I should use to store an 11 digit number? It must store '0' as the first digit. For example, if I store 02421402015, it must show 02421402015. I tried using varchar and it shows ...
6
votes
2answers
231 views

Add [Is Primary Key Column] to this query

I made this query, to list tables, its columns, data type and etc: select t.name as 'Table', c.name 'Column', ty.name 'Data_Type', c.max_length as 'Max_Lenght', ...
2
votes
0answers
41 views

What is the correct way to rename/swap a table in production

I have two tables, tblCode and tblCodeAlternative tblCode has about 4100 rows and is used in production. tblCodeAlternative has about 4200 rows and is in the production database but not used ...
0
votes
0answers
31 views

Unable to create availability group

While trying to create an availability group, I am getting the below error. Can someone find the root cause of this? I even granted "create computer objects" permission, but it still did not work. ...
0
votes
1answer
20 views

How do I view operators in a Notify Operator task in a Maintenance Plan?

In the past, we have added each DBA to a Notify Operator task on failure in Maintenance Plans ranging from scheduled backups to indexing and occasionally one-off operations. One of our DBAs recently ...
0
votes
0answers
37 views

SSAS MD dimension joins to same table twice (but processing fails as attribute has 2 names for same ID)

My cube has a product dimension, and each product has an "availability" status attribute. In the database, SellableItemAvailabilityId in DimSellableItemVersion (which contains products) joins to Id in ...
2
votes
1answer
34 views

Do Sequences Have the Same Replication Range Limits As Identities?

With merge replication, if the subscriber runs out of identities for a range when inserts occur into it, the subscriber will throw an error on the insert into the table, because it is the publication ...
0
votes
0answers
34 views

Headers and footers for Crystal Reports [on hold]

I have three tables: Employee [EMPNAME, EMPID] ProductList [ProductName, ProductCode] Orders [EMPNAME, ProductCode, ProductName, DeliveryDate, OrderDate, TOTAMT] I am trying to ...
3
votes
3answers
218 views

Can I run a CLR Stored Procedure on a different server than the database instance?

I want to add a trigger whenever a row is inserted into a certain table. The trigger will fire off a CLR stored procedure which will do things other than database work, such as write a JSON string to ...
6
votes
2answers
97 views

Unable to create restore plan due to break in LSN chain

My backup plan is: Full backup - once a day Differential backup - every four hours Transaction Log backup - every 30 minutes. All database backup are stored on my computer. I add some wrong ...
3
votes
1answer
27 views

Redefine Transaction log size restricion fails

I am trying to set the transaction log's max size restriction to UNRESTRICTED using SSMS using the following dialogue: The actual setting is set to 2.097.152 MB - a nonsense value that exceeds the ...
6
votes
2answers
215 views

Full or bulk-logged recovery model?

My database is about 10 GB and I use full recovery model. Sometimes I have to add a lot of data at once and during that time, I change the recovery model from full to bulk-logged. What do you ...
3
votes
1answer
72 views

Aggregate data from multiple rows into single row

In my table I have rows, and each row has some data and its version. When column is not-null, it overrides an old value otherwise nothing happens. Pseudocode (in C#) var resultTable = new ...
4
votes
2answers
68 views

Optimized date compare in WHERE clause, convert or datediff=0?

In my query I am comparing two dates in the WHERE clause once using CONVERT: CONVERT(day,InsertedOn) = CONVERT(day,GETDATE()) and another using DATEDIFF: DATEDIFF(day,InsertedOn,GETDATE()) = 0 ...
3
votes
1answer
28 views

Set the Priority for two different “after insert” triggers in SQL Server

I'm trying to create triggers by functionality,instead of DML operation. For exp,I have 2 triggers A and B. A is for "AFTER insert, update , DELETE" , and B is for "AFTER INSERT". how can I know ...
-1
votes
0answers
33 views

Possible SQL Server 2012 Bug - failing to show all tables following a full database recovery

Background: I was testing some software that recovers individual tables from a custom backup file. Before I started to run his recovery software I thought that I would take a backup of my database ...
-2
votes
1answer
39 views

SQL Server 2014 can't find local db [closed]

After installing SQL Server 2014 Express can't find local db.The problem is that I can't connect/find my local DB server, and I can't develop DB on my local PC. How can I reach my local server?My ...
0
votes
1answer
26 views

Perfmon Counters writing to SQL tables

I am trying to collect performance data on my sql box, I would like the data to write back to the sql server tables
4
votes
1answer
50 views

How can I select all of the parents of rows that exist in another table?

I have two tables that share a many to many relationship. The relationships are stored in a third table. CREATE TABLE Person ( Id int NOT NULL, Name varchar(255) NOT NULL, PRIMARY KEY ...
7
votes
2answers
391 views

Unused Indexes Best Practices

Based on this query, if I see a low amount of total reads (very close to 0 or 0, like 1 or 2) and a high or moderate amount of user updates (I couldn't find inserts or deletes with this query) with a ...
3
votes
1answer
39 views

Keeping service up while taking drive offline in clustered instance

I have clustered instance with two (2) data drives on it. Drive J has around 50 databases on it and drive K has about 15. One of the system administrators was trying to increase the size on K by 20 GB ...
4
votes
1answer
53 views

How to split a wide table in Production?

Due to some changing business requirements, I will be needing to split a large table in the near future to support new business requirements allowing a 1<=>M relationship from a table that was ...
0
votes
0answers
17 views

SQL Server doesn't send intermediate SSL certificates

I am connecting to SQL Server (2008 R2) over open Internet and properly encrypting the connection is essential. I have COMODO certificate with two intermediate certificates between root CA and server ...
6
votes
1answer
102 views

Is it important to make transaction log backups? [closed]

I don't have huge experience with SQL Server, I'm just studying it, and I have a question: Is it important to make transaction log backups? My database is not big, about 2GB, and I prefer a full ...
4
votes
1answer
79 views

There is insufficient system memory in resource pool to run this query

Over the past year we have been suffering a situation where many different queries at the same time will fail with the following error (or a variation in a different resource Governor group): There is ...
1
vote
1answer
28 views

Do I need different linked server remote users?

I have inherited a project that uses a local Microsoft SQL Server 2012 Express Edition and a central SQL Server 2012 as a backup. There may be multiple local "machines"/databases that all are linked ...
2
votes
1answer
41 views

Get start time and end time of a trip in SQL Server

I have a table that contains zone in and out information about a bus, and its structure is like this: id zoneid status datetime 1 35 IN 2016-02-02 11:36:39 2 35 OUT 2016-02-02 ...
0
votes
2answers
66 views

Concatenate column values [duplicate]

I have a query that uses a couple joins to get a name from another table. It works okay except that I would like to combine "Examiners Name" instead of having multiple duplicates: ID | Name ...
1
vote
2answers
63 views

Join SQL Server table in OPENQUERY on multiple keys

I need to join an Oracle linked server table (with 1 billion rows) with an SQL Server table (with 100 rows) on multiple keys: SELECT * FROM OPENQUERY(EDW, 'SELECT * FROM ORACLE_TABLE') A INNER JOIN ...
1
vote
1answer
32 views

Get Table Name from Transaction Log query

I can't figure out why this join isn't working to return the table name when querying the transaction log file. SELECT [Transaction ID], [Current LSN], PartitionId, sp.object_id, ...
1
vote
1answer
38 views

Compare Timestamp Difference Between Consecutive Records

How do I compare Timestamp difference between 2 consecutive records? This is my example: DECLARE @x TABLE(ProductID INT, SingleRunFailure bit, SampleDate date); INSERT @x VALUES (101, 1, ...
1
vote
2answers
45 views

SQL Server If statement assigning values [closed]

Please see the pseudocode below. Basically what I'm trying to do is write a statement that when an account number contains one of the specified codes it will assign that Level to the entire account ...
-1
votes
1answer
39 views

How can you allow only the database owner and system administrator to use a database? [closed]

I am on SQL Server Management Studio 2014. I need to allow me, the database owner and the system administrator to use a database. Also, can both users use the database at the same time?
3
votes
1answer
34 views

Grant Permission to Execute Set Transaction Isolation Level

I'm a part-time accidental SQL Server 2005 DBA whose full-time job is developing business intelligence solutions, but where the BI intersects with the database, it falls in my 'stack'. We have a ...
4
votes
2answers
54 views

SQL Server backup medianame

If I make full/differential/log backups to disk under full recovery model it is important to specify MEDIANAME? Is it true that MEDIANAME is only for tape backups? If not, when and why should I ...
-1
votes
0answers
20 views

How to recover with Veeam after CHECKDB fails? [closed]

I was wondering if anyone had any experience of recovering a SQL Server database after a CHECKDB failure and Veeam is your company's backup tool. Basically I want to know the process to restore the ...
0
votes
1answer
42 views

Copy table form MS-SQL server to MySQL, is it possible? [closed]

I have a procedure, say "proc1", inside a MS-SQL server. I would like to create a table (copy of proc1) inside a MySQL server. So far what I do is this: 1-Create a connection to MS-SQL Server 2- run ...
1
vote
0answers
44 views

Can I create a referential integrity constraint in SQL Server that prevents time overlaps? [duplicate]

It is trivial to create a Constraint on uniqueness for an integer value, but I am wondering if it is possible to create a constraint on overlap/intersection of a range of values. For example, my ...
0
votes
1answer
14 views

SQL Server backup media set and file separation

SQL Server backup media set and file separation If I am going to do such a backup plan under full recovery model for a particular database Weekly full database backup Daily differential backup ...
1
vote
1answer
38 views

SQL Server Import Export Wizard Error - Server throw Exception

I am trying to move all database tables from one db to another as a nightly job. The first (export) db is running sql server 2008 - SQL Server Authentication - I connect with SQL Server Native Client ...
0
votes
0answers
36 views

Backup schedule [closed]

I'm wondering what is your SQL Server backup schedule, I need it to make my backup plan better. So, please advise, my database is about 10 GB grows about 1GB/month and its run under the full recovery ...
2
votes
1answer
49 views

What can become a reason of database failure? [closed]

I'm the new one with SQL Server, so, please don't beat me. It's known that one of the best ways to protect your database from the failure is to make regularly scheduled backups. But my question is: ...
1
vote
1answer
28 views

Difference between database backup and data-tier application export

I don't know if this is a bad question, but I don't understand what the difference is between normal DB backups and data-tier applications in SQL Server. I know backups can't be imported from SQL ...
1
vote
0answers
40 views

SQL Server Repeatable read locking [closed]

I'm trying to understand the way the repeatable read isolation level works. I've been reading many articles and posts and still can't find my answer. Everywhere that repeatable read is mentioned, ...
0
votes
1answer
18 views

Howto remind and restore recovery mode during SSDT deployment?

Due to deployment of a SSDT database project I would like to be able to switch the target database's recovery mode from FULL to BULK LOGGED and back after the deployment finished in order to do any ...
6
votes
2answers
121 views

Selecting Distinct Rows but Counting All Rows

I have the following table: ---------------------------------------------- | ID | interestingData | timestamp | ---------------------------------------------- | 1 | 400 | ...
2
votes
1answer
76 views

Grant access only to System databases [closed]

Is it possible give an SA like permission minus any access to user databases? I want people to be able to collect information about Sql Server from the system databases but I want to make sure the ...
-1
votes
2answers
39 views

Stored procedure execute another sp with random value of parameters [closed]

For example: exec SpFirst @param1 = 1, @param2 = 'sdad', @randomSP = 'nameOfRandomSP @n1 = 1 @n2 = "2"' In SpFirst i use "exec @randomSP" but don't know how send to SpFirst send ...