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)

0
votes
1answer
398 views

Using a string with single quotes in a table

I'm trying to insert the following string, as written, into a table: 'affinity mask', 0 However I can't figure out how exactly I need to escape the quotes. It always wants to treat the , 0 as a ...
4
votes
4answers
871 views

Keep track of time intensive stored procedure with prints

I am no database admin nor is my SQL-foo any good (yet), but have a few tasks to get done in MSSQL, and one is a rather time intensive procedure where I fetch from a CURSOR and WHILE @@FETCH_STATUS = ...
3
votes
2answers
2k views

Dynamic Creation of Table using Stored Procedure Without a Use of OpenRowSet in SQL Server

Q.--> An alternative of OpenRowSet in SQL Server Description: I have created a procedure which uses Dynamic PIVOT to get a result. I have a table which contains column DateRange with values Jan-2014 ...
0
votes
0answers
81 views

How can I lock an INSERT operation

OK, I know the title is confusing, let me rephrase with a description of the problem. The environment is SQL Server 2012. I have a table with Customers Id 1 And a table with their transactions ...
3
votes
1answer
236 views

Why does SQL authentication sometimes fail and how can I make it work reliably?

I have SQL Server Express installed locally on a machine and it works most of the time but occasionally it fails to make a connection with this error: Could not obtain information about Windows ...
1
vote
1answer
80 views

SQL Server Database Table Partitioning Consideration

I have a SQL Server table with 42 million + records. This table acts as a staging area for transaction records loaded from a different system. Data from this staging table have to be transformed and ...
0
votes
1answer
649 views

how to select first 9 numbers from other table from database using sql query?

I want to fetch first 9 numbers from matching with other column please help me to find out thank you....? SELECT MAINFILE.facno,GLMAST.fglhead FROM MAINFILE INNER JOIN GLMAST ON ...
2
votes
3answers
87 views

Determining which SQL Server tables are accessed over a week?

I inherited a large, pretty-much undocumented SQL Server 2008 R2 general purpose database, used by dozens of different applications. We know many of the tables are no longer in use. Any ...
2
votes
2answers
1k views

Performance Order By large table with Join

Hello I have a table with 206,902 records and I am struggling to improve my query: SELECT TOP (50) [ID] ,[Title] ,[Modified] ,[Created] FROM [Data] Inner Join [Permission] On ...
0
votes
1answer
70 views

Unable to call sp_start_job from website only

I have an agent job that I'm trying to use to kick off an SSIS package via a web page. I have configured my web site running in an application pool as user X. When I invoke sp_start_job as use X ...
3
votes
1answer
131 views

Get a specific row from a TSQL common table expression

I have this tsql code to recursively get the whole workgroup hierarchy for a specific workgroup (WgID 283 here): WITH GroupList(WgID,WgParentWgID) AS ( SELECT WgID,WgParentWgID FROM ...
2
votes
1answer
144 views

How to get all logged in users to SQL Server database for this week?

I'm using SQL Server 2008R2 and I setup a testing database that I need to know who tried logging in so far. Is there a stored procedure or a View or anything that I can gather these type of ...
2
votes
2answers
465 views

SQL Backup tuning large databases

I am tuning SQL backups using compression, writing to multiple files, and changing the BufferCount and MaxTransferSize options. I have SQL instance with a 4 TB database and several smaller databases ...
0
votes
2answers
3k views

Does SQL Server require SSLv3?

We have a Windows 2008 R2 server running SQL 2012 Express RTM (11.0.2100.60). All three protocols (shared memory, named pipes, TCP/IP) are enabled for the server and both 32-bit and 64-bit clients. ...
2
votes
1answer
35k views

Case with multiple conditions

I need to change returned value, from select statement, based on several conditions. I tried something like that: ,CASE i.DocValue WHEN 'F2' AND c.CondCode IN ('ZPR0','ZT10','Z305') THEN c.CondVal ...
2
votes
1answer
503 views

Does view take advantage of table indexes

I have a table with a clustered index and 2 non clustered index. Now, in my view I am selecting everything from my table create view dbo.MyView as Select * from MyTable Now, when I am using this ...
0
votes
1answer
2k views

Type conversion in expression try_cast may affect cardinality estimate in query plan choice

I have a query that is running very slow so I started looking into the Execution Plan. TRY_CAST is a safe way of finding out if the data can be cast to a type. And there is a warning in the plan ...
1
vote
1answer
4k views

Store the resulting list of a SELECT in a variable to count AND use the result

I have stored procedure that uses a cursor and for every fetch a duplicate SELECT, once to determine the number of IDs found (SELECT COUNT(ID) FROM TableA WHERE something.. @cur_var) and if the ...
0
votes
0answers
512 views

SQL Server : error 15208 Level 16, State 1, Line 1

Trying to Backup a certificate from SQL Server causes this error: Msg 15208, Level 16, State 1, Line 1 The certificate, asymmetric key, or private key file does not exist or has invalid format. ...
2
votes
1answer
244 views

Adding a distributor for availability groups

According to this MSDN article, I need to add a distributor: Configure Replication for AlwaysOn Availability Groups (SQL Server) I'm confused by this particular statement: The distributor should ...
0
votes
4answers
185 views

DB performance vs replication

I am working on a db optimization (planing for future project growth) and need some help. Currently, every table is using uniqueidentifier as PK (clustered index) and we have high index fragmentation ...
0
votes
2answers
230 views

SQL Server 2012 Very Slow to Consume Memory

I have a bit of a conundrum. I recently upgraded a ~20gb database from Window Server 2008 R2 || SQL Server 2008 R2 to Window Server 2012 || SQL Server 2012. The previous server had 32gb of memory, ...
1
vote
2answers
3k views

Stored proc getting ANSI_NULLS and ANSI_WARNINGS error when executed by application

I have a stored procedure that is getting the following error when executed via an application : Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the ...
3
votes
1answer
7k views

Migrate SQL Server from 2012 to 2014 (11 to 12)

I have just installed SQL Server Management studio 2014 and I also need to update my (local) server to accept a new database via a bak file from a version 12 server. When I look in Server Properties I ...
1
vote
1answer
272 views

non clustered index on same table but the order is different?

I have a table with 5 columns col1...col5 . I need to create a non clustered index on col2,col3,col4. I did, then my boss is asking me to create one more non clustered index on col4,col3,col2. It ...
1
vote
0answers
48 views

Can CDC read through a log file when the agent job hasn't been running?

We currently had CDC running on our SQL server, however, at some point last week the CDC capture job stopped running and started throwing an error. At this point when it stopped somebody had restored ...
0
votes
1answer
1k views

how best to add unique index on nvarchar(max) field on existing table

The situation I find myself in is that I am getting bad data into the database for some reason. I want to preserve data integrity by adding to the User table a UNIQUE INDEX on the UFID field. ...
1
vote
0answers
146 views

Can you speed up this query to aggregate millions of rows?

We store our website traffic data in SQL Server 2005, like so: | Date | IPAddress | GalleryUserID | ThumbView | LargeView | ZoomView | | 2014-11-11 | 192.168.0.1 | 22 | true ...
0
votes
2answers
164 views

Error after removing sql server 2008 R2 Express edition and instaling Entreprise edition: Unable to connect to MyHost\MSSQLSERVER

So I'm having this issue after installing MS SQL Server 2008 R2 Enterprise edition, I'm getting this message error: Unable to connect to WIN7 \ MSSQLSERVER A network-related error or ...
0
votes
1answer
1k views

What does it mean when I see a lock in sys.dm_tran_locks and there's no matching row in sys.dm_exec_requests?

I'm running this query select sessions.session_id from sys.dm_tran_locks locks inner join sys.dm_exec_sessions sessions on locks.request_session_id=sessions.session_id and it consistently ...
1
vote
1answer
82 views

Network connection from old application to SQL Server 2008 R2 lost once in a while

I have put this question on serverfault.com as well, but since the problem concerns connecting to SQL Server, I will also put it here. We are trying to migrate an Axapta 2.5 database from SQL Server ...
5
votes
2answers
518 views

How to store immutable, unique, ordered, lists?

I have an entity with millions of instances. Each instance has to reference an ordered list of items. The lists have to be unique so no list will be stored more than once. But once created, both lists ...
1
vote
2answers
372 views

Are there any special characters that are never allowed in SQL?

I am a Java programmer. We always make database-related things configurable so that they can be easily modified by the DB team as per their requirements. I want to make some queries as configurable, ...
0
votes
0answers
103 views

DBCC error reported

In the SQL Server, I have these errors reported in the error log "Could not export certificate, trying again with random name. If this is a mkmastr build then this is an error." "Creating ...
2
votes
0answers
167 views

Impact of Service Broker on Server Performance [closed]

For DDL auditing we are thinking to use service broker, but I want to check few parameters before deploying it to a production server, where there are about 200 DBs and Standard Edition of SQL Server. ...
4
votes
1answer
755 views

How SQL Server agent calculates next run date for a job

I am developing a system for scheduling task & I am following Microsoft SQL agent approach to schedule task. I have replicated sysschedules table for capturing users scheduling occurrences and ...
3
votes
0answers
707 views

How to insert values into Bridge Table in star schema?

I am modelling my database with respect to star schema with bridge tables. My master Table looks like below. I need to convert this format to star schema. MasterTable StudentNumber | Subject1 | ...
1
vote
1answer
77 views

How to properly restore database from file groups backups?

I have very (from my point of view) strange error while trying to restore SQL server DB from a series of filegroup backups. Here is my corrent config: I have DB with 2 files 2 filegroups and 1 log ...
0
votes
1answer
145 views

DateTime returning with AM/PM

I have a SQL Server database (2008R2) running on a Windows 2003 box. I'm connecting to it using another Win2003 box. When I select a datetime value from the database, I'm getting it back in this ...
1
vote
2answers
3k views

SQL Delete duplicate rows based on column [closed]

I need to remove the duplication of rows based on column to have unique data in this column, additionally I need another condition when the value equal specific information. In my example I want to ...
1
vote
2answers
800 views

SQL Server Grant execute on all stored procedures without ddl permissions

I have some users in my database. They are member of db_datareader. They now also need to have execute permissions on stored procedures. I was just wondering what would happen if one of the stored ...
2
votes
4answers
519 views

Saving a variable globally in SQL server it self and use it when required

We are using ENCRYPTBYPASSPHRASE to encrypt some columns of some database. To decrypt the values every time we need to pass passphrase with DECRYPTBYPASSPHRASE. So I planned to create a stored ...
-1
votes
0answers
30 views

SQL Server Dba backup related [closed]

How can I use a SQL Server database backup with password? Restore that backup by using password all versions SQL Server 2005 and SQL Server 2008 and R2 and SQL Server 2012. Please let me know step ...
1
vote
2answers
288 views

Deciding on a replication strategy

We have several dozen small-medium (<1Gb) databases on our SQL Server 2008R2 (Standard Edition) server, with one or two more being created every month. The server itself is a fairly well spec'd box ...
0
votes
2answers
6k views

Update datatype of primary key without deleting the table-SQL Server [closed]

I have created a table State which my other table, Addresses references. CREATE TABLE State ( StateId varchar(20) PRIMARY KEY NOT NULL, Text varchar(40) NOT NULL ) I want to change its ...
2
votes
2answers
7k views

Can't create table in database with db_owner role (SQL Server 2012)

I have database in SQL Server 2012 SP2 Enterprise. I created new SQL Login and mapped to X database with db_owner role. Now I can't create table on this database. When I run simple create table script ...
0
votes
2answers
329 views

Creating tables with inheritance

I'm fairly new to SQL. I just wanted to create a database from scratch to learn. So I have a table called Addresses and I want to create two tables dependent on it that are Country and State, Im not ...
0
votes
1answer
605 views

Breaking changes between 2008R2 RTM going to 2008R2 SP3?

I have a few A/P FCIs running 2008R2 RTM, and I'd like to upgrade the passive node to SP3, fail over, and then upgrade the formerly passive node. Has anyone else attempted this? Are there breaking ...
1
vote
1answer
3k views

View permission to SQL Server logs on SSMS GUI

I would like to give a user see SQL Server logs from GUI, not through xp_readerrorlog. I was just wondering if this was possible? Thank you.
-1
votes
3answers
670 views

How to load multiple values in cell from Excel to SQL Server many to many table [closed]

I have values in excel like this. StudentNumber| Subjects 1| english, science, maths 2| maths, biology 3| history, zoology I have Student table in database ...