Microsoft's SQL Server is a relational database management system (RDBMS) that runs as a server providing multi-user access to a number of databases. It originated from the Sybase SQL Server codebase, which is why both products use the extension of SQL called Transact-SQL (T-SQL).

learn more… | top users | synonyms (2)

3
votes
2answers
506 views

Which of the following pieces of SQL is most efficient

I need to add the date part of GetDate() to the time part of a stored DateTime. Naively I thought I could do the following: declare @testTime DateTime = '2013-04-23 13:55:06' select cast(getdate() ...
0
votes
2answers
50 views

Live Partial Table / column replication

I want to know if there is a way to achieve the following with SQL Server 2012 Enterprise Edition? I have Table A: on Server 1 with say 10 columns. Table A: | col1 | col2 | col3 | coln | col10 | ...
5
votes
2answers
70 views

Is the runtime of checking for duplicate primary keys in SQL Server O(n)?

I was curious and I couldn't find too much info on this. Is it also O(n) for checking for duplicates among primary keys? Is it the same for most other SQL databases? Is there a place that lists this ...
5
votes
0answers
72 views

SQL Server stuck in single_user with auto_update_statistics_async on

Books online states Before you set the database to SINGLE_USER, verify the AUTO_UPDATE_STATISTICS_ASYNC option is set to OFF. When set to ON, the background thread used to update statistics ...
0
votes
2answers
39 views

Find field that was changed

I have database with log backup every 15 minutes. I believe some data in one table was changed 10 hours ago. Is there any way I can find that query and located changed/updated data in log backup ...
0
votes
0answers
62 views

Problem with EXEC MyLinkedServer…sp_executesql within transaction

I rather clueless about this so any help is appreciated. We have got a stored procedure that is running within a transaction. The stored procedure is called from a .NET program (via normal ADO.NET). ...
0
votes
1answer
50 views

Working out Duplicated Data in a Customer Data Base

I have a database in SQL2008 of customers, There is an ID number for the customer. What im trying to do is to create a NEWID field. We have duplicated customers that have different ID fields, I am ...
0
votes
0answers
60 views

Window function sorting performance

For background, I am on an instance of SQL Server 2012 Enterprise Edition. I have three tables that join on the same set of three keys - all of them are INT. All tables have their keys clustered in ...
1
vote
1answer
126 views

What's the term that describes filtering query that makes use of indexes

I am looking for a technical term that describes the type of query in SQL that makes use of indexes. I completely forgot the name and can not look it up anymore (there's even a Wikipedia article about ...
0
votes
1answer
35 views

Automatic change of “initial size” of a database?

I just had a problem : days ago, on one of my client's databases, the LDF file suddenly grew up to 100% of the disk capacity. .TRN backups are correctly done every 15min (I don't think reducing this ...
2
votes
1answer
40 views

Restore database with different filename

I have to restore a new back up file daily (as an agent job, after first deleting the existing db) but the file name will vary as it will include the timestamp as well. How can I achieve this so ...
2
votes
3answers
78 views

How do I kill a rollback process when I want to drop the database?

I want to drop a SQL Server 2008 database, but it is currently performing a rollback for a transaction (that transaction took 3 days to run so far). How do I stop the rollback because I want to drop ...
0
votes
1answer
68 views

Best approach to have a Live copy of a table in the same database

I have a table where 15,000 to 20,000 rows are being inserted every hour. Table Schema is something like ColumnName DataType ID BIGINT IDENTITY(1,1) Column1 INT Column2 INT ...
5
votes
2answers
261 views

Terrible Bad Query Plan

I have a weird query plan problem. I have two databases (Let's call them DB1 and DB2) where both are sitting in the same SQL-Server instance and have identical schema. In there, we have a couple ...
0
votes
1answer
62 views

Select children from hierarchy (/a/b/c/d/)

I have to select from database which I did not create and I have this problem: example schema: Unit ---- id HierarchyPath (example: '/42/48/63/84/') UnitName (example1: "John" example2: "Team1") ...
1
vote
1answer
32 views

Minimum SQL Server rights that allow viewing column default values

I have an SQL Server 2008 database, some of the fields (columns) in some of the tables are configured with default values. I have a requirement to limit user security as much as possible, ideally ...
3
votes
1answer
46 views

How to join by most recent preceding date?

Imagine I have a database with historic price data and a second table with dates Item | Price | Date Customer | Item | date ------------------------- --------------------------- ...
1
vote
3answers
63 views

How to run sql agent jobs in a sequence order.with T-sql

My requirement is to run the SQL Agent jobs (EX: job1 to job10) with T-sql in a sequence order. That means job2 starts automatically when job1 completes. This process should continue until job10 ...
1
vote
0answers
42 views

The replication Merge Agent failed to propagate a Publisher schema change to the Subscriber

We are running 2 Microsoft SQL Servers - publisher (2008R2) / subscriber (2008 Express) with merge replication. I tried to make some schema changes via Transact-SQL and partly failed. So tried to ...
0
votes
1answer
36 views

Pulling all data modified in a month [duplicate]

i am required to WRITE A SCRIPT that pulls all the data that has been modified in last 30 days from a database. What approach do we need to follow.
1
vote
1answer
71 views

Stored Procedure slower on one server than the other

I have a stored procedure that I've been testing on two different servers and it runs slower on the server with more memory and I can't figure out why. Setup 1 is my virtual environment, ...
0
votes
0answers
40 views

What is the bare minimum access needed in the public role?

I need to lock down access to a specific database for a user - they should only have access to execute a couple of stored procedures. However, by default the user gets everything in the "public" ...
0
votes
1answer
26 views

Querying multiple servers via CMS: When does [Server Name] become 'real'?

Maybe a silly question. I set up an CMS earlier this year, which has improved my work day immeasurably. Servers are split into appropriate groups, but occasionally I only need to target some servers ...
0
votes
1answer
39 views

Can not re-set [log_reuse_wait] after log back up

I have a SQL Server 2005 database in full recovery mode with the following stats: name XX state 0 state_desc ONLINE size 2620152 max_size -1 growth 128 Log File name XX_Log state 0 ...
2
votes
2answers
48 views

Scrubbing sensitive data

I am looking for an automated solution to scrub sensitive data from my prod environment to my DEV and DEVINT environments so that I don't have to write lots of code to get this done. Does anyone know ...
4
votes
2answers
410 views

Restored Database is different from the original

I have recently performed a restore of our data-warehouse staging database onto a separate instance in our dev environment. After the restore I did a comparison and it looks like the database sizes ...
0
votes
0answers
36 views

How to check if all the extendend stored procedures (xp_) are enabled or disabled?

These are the stored procedures that I want to check if they are enabled or disabled. xp_availablemedia xp_cmdshell xp_deletemail xp_dirtree xp_dropwebtask xp_enumerrorlogs xp_enumgroups ...
0
votes
1answer
39 views

how to select top ID from join operation and staging tables

I have this inner join from staging and operations table jobsCode I want to select the top surrogate key ID and get the Start_Date from this join my problem is my Query rerunning 2 records original ...
0
votes
0answers
47 views

Importing OLE Object from Access to SQL Server

I am trying to import a table from MS Access to MS SQL Server which contains a BLOB field which contains a .zip file. The zip file has two images. This field is null sometimes. I am using the Import ...
0
votes
2answers
45 views

Cannot find .bak in a backup file [closed]

My database backup works well or you can see the backup in the backup file. But the backup has no format of a backup file. I mean you cannot see the .bak associated with it, therefore unusable by ...
1
vote
1answer
52 views

The query processor could not produce a query plan

I'm getting this message: Msg 8624, Level 16, State 17, Line 1 Internal Query Processor Error: The query processor could not produce a query plan. For more information, contact Customer Support ...
1
vote
1answer
150 views

Transaction Log is big! But simple recovery mode is chosen!

I am wondering: We have a Database on our MS SQL Server 2012. The recovery mode is "simple". The DB itself is about 2.7GB big. I have an MAX Size for Trn-Log configured (3GB). Now i made a ...
0
votes
1answer
78 views

Generate All days in moth report using PIVOT of SQL server

My table, tblEmployeeScan(EMPLOYEE_ID varchar(7), ScannedTime) Everyday each employee scans 2 times, 1 time or 0 time Now I want to create store procedure Create proc spMonthScanReport @MONTH ...
0
votes
0answers
52 views

Accidentally deleted records in MS SQL Server 2014

I have a very big problem with SQL Server data lost. Recently, i have performed SQL replication. and i found out that all exising records/rows in the subscriber was totally erased and replaced by the ...
1
vote
0answers
44 views

SQL Server: Buffer page read/writes per second reported high

Our SQL Server is reporting high buffer page read/writes per second. Several documentation sources suggest these stats should be less than 90 per second. Our database server is reporting: counter ...
0
votes
0answers
37 views

Querying system tables from Linked Server

I am trying to query the syspublications table on Linked Server A from Server B. Both servers are running SQL Server 2012. Using this simple query on Server B: SELECT a.dest_table FROM ...
0
votes
1answer
54 views

Indexing a view with xQuery in it

I'm not sure if this can be done at all but if it can it would be a huge help. I have a system where I am not allowed to make changes to the database structures. I have yet to have code changes put ...
1
vote
3answers
92 views

Execute permission denied on object sp_start_job

I need to allow a user to kick of a specific agent job without having any ability to start other ones. To accomplish this, I've created the following procedure (simplified): ALTER PROCEDURE ...
1
vote
3answers
55 views

How to get random samples from a table? I don't want repeated records between samples [closed]

I have this problem, I want to get some random extracts from a table and I dont want repeated records between those extracts, in order that when I join all the different samples together, they look as ...
1
vote
1answer
51 views

TempDB Log Space and ACTIVE_TRANSACTION

Our monitoring solution (SCOM) is currently flagging that the tempdb log is running out of space. However we have auto grow set to 1GB chunks for the log and we have 25GB of space left on the drive. ...
0
votes
1answer
20 views

Database Recovery Stuck on “Restoring”

Scenario: Sql Server Database routinely backed up with Backup Statement below. The "Recovery Model" was set to "Full" (we usually have them set to "Simple"). The ldf file, which had gotten quite ...
0
votes
0answers
32 views

What is the best way to create a database diagram for SQL Azure without having to install SQL Server?

I have a database on SQL Azure and want to create a database diagram for that, but don't want to have to install SQL server and copy the database etc. Is there any way that I can do it that anyone ...
0
votes
1answer
42 views

Help with optimising a sql query on two partitioned tables

I have a SQL query that I am running on two very large tables in SQL Server 2012 Enterprise The two tables are ACKs and Logs. Both are partitioned on their time by day column with the appropriate ...
0
votes
1answer
42 views

Unable to copy a DB from SQL Server 2000 to SQL Server 2012 using copy wizard

I am trying to copy a database from SQL Server 2000 to SQL Server 2012 using copy wizard in my local machine where SQL Server 2012 is installed. But, the wizard is not detecting destination server. ...
8
votes
1answer
107 views

Clustered Columnstore indexes in SQL Serer 2014 for data warehousing

I am performance tuning a DWH using indexes. I Am fairly new to SQL Server 2014. MS describes the following: "We view the clustered columnstore index as the standard for storing large data ...
2
votes
2answers
144 views

Select from stored procedure

Hello I have a table called Workers in my database. Also I have a stored procedure that returns a table with Id, xml Data and xml Schema by Worker.Id. I want to select all Worker.Id values and execute ...
1
vote
1answer
20 views

How to add an extended property to a table type?

According to the MSDN documentation, 'TABLE_TYPE' is a valid value for the @level1type parameter as shown below. EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'An INT ...
-1
votes
1answer
35 views

Is there a way to copy/initialize values in a set

What I want to do (I know this does not work) is: update stats set BandwidthLastMonth = BandwidthThisMonth, BandwidthThisMonth=0 where myPk = 3 Is there a way to do this in a select ...
1
vote
0answers
49 views

Why does SSAS keep restarting every few seconds?

We have a SQL Server 2005 Analysis Server which began restarting this weekend, seemingly without any external changes. The current state of the server is that as long as the SSAS service is enabled, ...
0
votes
2answers
53 views

Identical Servers but different query execution plans [duplicate]

We have two server with identical hardware configuration and SQL server configuration. The only difference in the server's being: Server SQL1 - 256 GB RAM Server SQL2 - 128 GB RAM The same database ...