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)

1
vote
2answers
253 views

Should these two indexes suggested by the SSMS missing index feature be combined?

I'm trying to improve the performance of a specific query that is automatically generated by Entity Framework. I have run the query through SSMS, and it has suggested creating two missing indexes. ...
0
votes
1answer
60 views

Looking for a snippet illustrating ambiguous result of a join in a where clause

A while ago (around 2004) I worked as a back-end developer on a team where everyone used the where clause joins. As a proponent of using inner/outer joins instead, I showed them an example of a where ...
2
votes
2answers
125 views

Make sure partitioning is working

I created a partition for a table. There is a column "archived" (tinyint). 0 goes to PRIMARY data file, 1 to ARCHIVED data file. ALTER DATABASE MYDB ADD FILEGROUP [ARCHIVED]; ALTER DATABASE MYDB ADD ...
1
vote
1answer
154 views

Best way to transfer a few tables from reserve server to production

We have two MS SQL Servers (2012 Standard Edition on Windows 2012 Server) which are located in the same subnet. One is production server. Another is reserve server. Currently there is a ...
0
votes
1answer
132 views

moving all databases from SQL 2005 EXPRESS SP2 to SQL2005 Enterprise SP4

Thank you for such a great forums on web. I'm not a DBA specialist and my knowledge about SQL server is low so this simple issue is getting challenging for me. We have an old SQL Server express 2005 ...
0
votes
2answers
212 views

Design a Lab Report tables [closed]

I need to design tables for a laboratory test. I will have Lab test name, and its component to be saved. The component can be changed with time. I need to design a UI for update. Also each ...
1
vote
1answer
468 views

Backup error: Failed to flush the commit table to disk

When I try to backup a SQL Server 2008 database, I receive an error: Backup failed for Server 'SQL2008TEST'. (Microsoft.SqlServer.SmoExtended) Failed to flush the commit table to disk in dbid 32 due ...
1
vote
1answer
265 views

CTE returns an empty set even though anchor is non-empty

I'm got an adjacency list consisting of two tables: CREATE TABLE permission (id SMALLINT AUTO_INCREMENT(-32768, 1) PRIMARY KEY); CREATE TABLE permission_graph (parent_id SMALLINT NOT NULL, child_id ...
0
votes
1answer
5k views

Procedure with Insert Data From One Table to Another Table

I have two tables Person with PersonId, Name,Address,Mobile and PersonLog with Id,Name,Address,Mobile,FK_PersonId. I am trying store the old data in PersonLog and Person to be updated. This is my ...
-4
votes
1answer
270 views

xml values are not inserting into the table [closed]

Here is my code: ALTER PROCEDURE sp_Insertxmldata @xml xml AS BEGIN declare @hdoc as int EXEC sp_xml_preparedocument @hdoc output,@xml Insert into ...
0
votes
2answers
72 views

In SQL Server what is the database cost for setting a default value on table that already has over 1mil records?

NOTE: I am not a dba. :) I have a database table that a client needs to set a column's default value. This table has 1.5mil+ records in it and this column is already populated on all existing records ...
2
votes
3answers
520 views

SQL join with multiple columns as FK to same list table

I have a table that has 5 references to a list table for normalization of the first table. So in the table 1 a row looks line other columns..., reference_1, reference_2, reference_3, reference_4, ...
0
votes
1answer
68 views

Package performs differently on two different instances on the same server

I'm investigating some performance issues for our BI team. They have a package which runs extremely slow and will sometimes run indefinitely on one instance but finishes normally on another instance. ...
0
votes
0answers
566 views

SQL Mirroring synchronize logins and agent jobs

We have just started using SQL Server mirroring for several clients on SQL Server 2008 R2 Standard. We've got the SQL Server mirroring working fine however now we run into the issue of replicating ...
7
votes
2answers
1k views

Alternative way to compress NVARCHAR(MAX)?

I am trying to compress some tables that have NVARCHAR(MAX) fields. Unfortunately, the row and the page compression do not have the desire impact (only ~100/200 MB saved for 20 GB table). Also, I am ...
3
votes
1answer
195 views

sp_estimate_data_compression_savings for column store compressions

Is there a way to get compressions saving for column store and column store column store archival compression? In order to check these compressions saving I have to drop all clustered and non ...
8
votes
3answers
3k views

Keep getting message: Failed allocate pages: FAIL_PAGE_ALLOCATION 540

I'd appreciate some help with the following, I've done some Googling, but haven't managed to resolve this one. I keep receiving a message in the SQL Log 'Failed allocate pages: FAIL_PAGE_ALLOCATION ...
2
votes
2answers
580 views

Compress files and directories into zip file and put it into blob field

What is the best way to do the following using SQL Server? Compress all the files, folders and subfolders from a specific location (specified in a parameter in a stored procedure) - the server side ...
3
votes
1answer
519 views

Cannot attach the AdventureWorks database

I am trying to attach the AdventureWorks database with the following query: CREATE DATABASE AdventureWorks2012 ON (FILENAME = 'C:\AdventureWorks2012_Data.mdf') FOR ATTACH_REBUILD_LOG ; But I get ...
2
votes
1answer
276 views

varchar storage and comparisons in SQL Server 2008

I have a large table with a structure similar to this: Id bigint primary key Sequence int Parent int foreign key Data1 varchar(20) Data2 varchar(20) Data3 varchar(20) Data4 ...
2
votes
1answer
57 views

Why are users allowed to connect to SQL Server when creating sql account, that defaults login to 'master" for login

When a new login is created in SQL Server it automatically defaults database to master. We do not give permissions in master (systemdb) but in our user DB (CustomersDB) with db_datareader and ...
1
vote
2answers
217 views

How do I know if a AWS Server instance has license SQL Server Standard or SQL Server Web?

I'm looking my AWS billing and this doubt has come up. How do I know if a specific instance is running SQL Server 2008-R2 Standard or SQL Server Web? (using the AWS Console or SQL Server Management ...
1
vote
2answers
96 views

Unique column values between tables

I'm keeping track of teams and their members. Each team has one leader, one contact person, and any number of players, and each of them has a unique email address. The problem is that one physical ...
0
votes
1answer
9k views

Conversion failed when converting the varchar value to data type int in SQL Server

While getting some data input, for below query, encountered Convertion failed error:- SELECT a.ProcessInstanceAppianID, a.ProcessInstanceDescription, CONVERT (INT, ...
3
votes
2answers
237 views

Build Restore History table

We restore a database daily that we download from an FTP site. I would like to build a history table that tracks when the database is restored on our SQL Server. This is easy. My issue is sometime ...
0
votes
3answers
145 views

Resume job 'A' at some step till job 'B' is completed

I have two SQL Agent jobs 'A' and 'B' Job 'A' has 10 Steps and it starts at 5:00 Am as per schedule, job 'B' has 5 Steps and it Starts by using trigger in step 3 of job 'A', job 'B' should be ...
7
votes
1answer
453 views

T-SQL - OPTION (FAST x) and trace flag 8722

I have searched a long time and still haven't found the answer to my problem. Our Dynamics AX is generating queries with the query hint OPTION (FAST x), which sometimes forces a bad execution plan to ...
0
votes
1answer
86 views

SQL Server: Clarity on which service user does what (backups, etc) RE accessing network shares

To backup / restore to or from a network share, either the SQLServer or SQLAgent user needs access to that share. Which is it? SQL itself or the agent? And then how? (I wanted to create the ...
0
votes
1answer
185 views

How can I give a group of developers the ability to view the query plan, without the ability to run a trace or to view the server state?

I have a group of 5 developers that need to be able to view the plans of queries they have written for a database, and that database only. I refuse to make them a member of sysadmin. I do not want ...
-3
votes
3answers
365 views

How to query from a linked server via a linked server in sql server?

I have the following set-up: SQL-Server 1 --linked--> SQL-Server 2 --linked-> SQL-Server 3 The question is can I query SQL Server 3 from SQL Server 1 via SQL Server 2? I have ...
0
votes
0answers
228 views

SQL Server 2012 Replication Subsystem Increase Number of Working Threads

I am trying to create Transaction Replication (Push) between two servers, using a distribution database on a3rd server. Server 1 is SQL Server 2008, Server 2 is SQL Server 2012. The Distribution ...
1
vote
2answers
113 views

sql server minus one day

I have a server with SQL Server 2008 R2 installed in it. This server is used as a D -1 Database, in other words, the database it is one day behind from production server. The problem is that my ...
0
votes
0answers
15 views

SQL server - copying tables, constraints and indexes from one database server to another server [duplicate]

An accidental DBA, We have sql server 2012 on both of our payroll production server and development server. We have to copy few tables and indexes and constraints from the production database to the ...
0
votes
2answers
493 views

How to set host_name() value in a Sql Server Stored Procedure?

We have existing triggers which read the workstation id property of the connection string using host_name(). There are existing PowerBuilder client applications connecting to the database directly ...
0
votes
1answer
178 views

READ_COMMITTED_SNAPSHOT locking on Non-Clustered Index

I'm in big trouble with one database instance of SQL Server 2008 R2. I have this structure that I've created it to simulate my real problem: USE [sor] GO ALTER DATABASE [sor] SET ...
1
vote
0answers
79 views

How To Log Select Statements from 1 User On 1 Table Into a Permanent Log Table

We have a centralized authentication library that 100+ applications use to authenticate users. That library has its own DB credentials. It would be extremely useful to log every time that library/user ...
0
votes
1answer
65 views

How Can I restore a database to a point of time without Old backup? [duplicate]

I have a database in full recovery mode with five tables. I accidentally deleted one table. I don't have any old backups. Can I restore the database with five tables?
1
vote
1answer
938 views

Remote linked servers

I have a SQL Server 2008R2, and I want to connect remotely to a SQL Server 2012SP1, for which I am given: IP: 12.34.56.78 Login: john Password: pw1 Database: TESTDB Table: test_table The remote ...
2
votes
3answers
2k views

Non clustered index on multiple columns

I have a table which has around 50 columns. But there are only 6 columns on which we perform regular search. When I am trying to create a non clustered index on it I am getting the warning of 900 ...
1
vote
1answer
55 views

How can I log the way in which a job was started?

Using SQL Server Agent, I've created a Job that runs some SQL. This SQL does some work on one database, then adds a row to a logging table on a separate database. The Job is run on a couple of ...
2
votes
1answer
248 views

SQL Server Error log - strange date

The dates in the SQL Server error log look very strange. A subset for messages between Sep/29/2014 8:00 am and Sep/29/5:40pm show a date of Feb/16/2014. Have you seen this before? Any idea why the ...
6
votes
2answers
7k views

Can SQL Server 2014 Standard Edition Trial be upgraded to a full Core license?

If I install a copy of SQL Server 2014 Standard Trial edition can that version be upgraded (in place without a full re-install) to the full version (core license) afterwards? I'd love to get this ...
13
votes
3answers
2k views

Will increased network latency cause table locks in MS SQL Server?

If I'm making a single call to a SQL Server database over a high-latency network, will table locks occur due to that latency? Say I query table A for some records, and SQL Server has to return that ...
1
vote
1answer
4k views

Database design for an online babyShop ,Toy shop

Hi i'm designing a database for a toy shop this shop offer many products with different categories. boystoys,girlstoys,puzzles,beds,stroller, etc i designed my database as it is consists of 5 tables ...
1
vote
0answers
491 views

“ORA-00936: missing expression” setting up Oracle Publisher in SQL Server

I'm trying to set up an Oracle publisher in SQL Server so I can perform replication. I've gotten connected to Oracle, the drivers work great (I have other linked servers), I can select an article I ...
0
votes
0answers
485 views

Service Broker received an error message on this conversation

I will first have to say, I am still trying to wrap my brain around all the components of the Service Broker and how they work together, so it's very likely I'm missing something obvious... it's just ...
1
vote
2answers
249 views

VLF, Shrink and Simple Recovery

We currently have a transaction log thats been growing incrementally and has too many VLF files. I am going to follow a process of shrinking and then re-growing the log file to a suitable size to ...
0
votes
1answer
73 views

How to create a view joining 2 tables? [closed]

I've got two tables that I'm trying to create a view from. The problem that I', having is that the column that I would join on doesn't have all of the same values in both tables. Here are my table ...
1
vote
2answers
74 views

Specifying per-table recovery mode

I have a table that is used for log data; the data is only INSERTed, never UPDATEd. The table has no business data whatsoever, and thus it is not a problem if I cannot recover it in case of a ...
0
votes
0answers
2k views

why I can't delete old transaction log backup files with an existing scheduled job?

I have a job scheduled in my SQL Server Agent 2008 R2 that runs every 30 minutes, Its part of a maintenance plan that runs: A daily full database backups, differential every hour, and transaction log ...