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)

3
votes
1answer
579 views

SQL Server WHERE clause on CLR method (spatial) peformance

I have a query that joins to a table containing 2 million rows and need to filter on its geometry column by its geometry type (using geom.STGeometryType()). The query takes less than a second to run ...
3
votes
2answers
1k views

Excessive replication snapshot size causing failure

I am searching for an explanation of a replication snapshot being in excess of 5 times what we're predicting Following a replication failure we are attempting to reinitialise subscriptions with a new ...
2
votes
2answers
6k views

MDX Crossjoin filtering on different hierarchies from the same dimension is very slow

I have an MDX query that was written by an MDX novice that is excessively slow (the query, that is, not the MDX novice). And I am also an MDX novice. Here is the query: SELECT NON EMPTY ( ...
2
votes
1answer
1k views

SQL Server 2012 Express dying randomly

I have a Windows 2008R2 box with SQL Server 2012 Express. The problem is that randomly (sometimes twice a day, sometimes once a month) the SQL Server dies leaving those messages in error log: ...
4
votes
2answers
14k views

Restore of database failed [duplicate]

I wanna restore my database and i use sql server 2012 express edition but when i restore my backup file in sql server management studio, i take this error: System.Data.SqlClient.SqlError: There is ...
0
votes
0answers
387 views

How to transfer logins from sql server 2012 to 2008?

I need to transfer dynamically logins from SQL Server 2012 to SQL Server 2008 and I can't because when I get the hashed password on 2012 does not have a valid format to be used on 2008. I know that ...
13
votes
4answers
6k views

When was Torn Page Detection and Checksum introduced to SQL Server and what are the upgrade behaviors?

There are two different options in modern SQL Server for page verify; being Torn Page Detection and Checksum. None is also of course an option. I believe Checksum was introduced in SQL Server 2005 ...
2
votes
3answers
6k views

Simple select is taking too long

I am facing an issue with the performance. The selection of data from table is taking more than 20 sec to retrieve close to 300k records. Created a clustered index on col1 and col2 but no improvement. ...
6
votes
3answers
5k views

How to know the user doing a delete action on an audit table, when using a shared login?

Background information: I'm creating a collection of audit tables to keep track of updates and deletes to a set of data tables for my app. Audit records are created via triggers. DML in my app's ...
0
votes
1answer
1k views

what server role that needs to be configured for the user to enable sql server agent

I'm on SQL SERVER 2008, I want to enable the SQL Agent for a particular user, who has only read access to the database. I don't want to give sysadmin role? Is there any way, I can do this?
4
votes
1answer
3k views

How does one query a SQL Server Linked Server when SQL Server Authentication is specified?

I have established a linked server and am attempting to query a database to which I have a SQL Server username and password, however I can't find the syntax for specifying the userid/pw in the query ...
2
votes
3answers
15k views

Restoring .bak file SQL Server 2005 in SQL Server 2008

I don't have enough knowledge in SQL Server back and restore. I just want to know is it possible to restore an SQL Server 2005 .bak file in SQL Server 2008? If yes, how can I do it?
1
vote
0answers
232 views

Automatic Version increment on Data-Tier Applications

In the project settings of a database project I can set the version number form my data-tier application, however setting this manually is a bit inefficient. I would like to automatically increment ...
-1
votes
1answer
867 views

SQL Server : openrowset problem [closed]

I'm looking for a solution to use openrowset with variables, without an execute, so that I can use it in a table function. I know that openrowset doesn't accept '+' and that I can't call exec() to ...
7
votes
1answer
984 views

SQL Server 2012 x64 - cannot safely allocate more than 50% RAM

We're running SQL Server 2012 SP1 CU4 on a 64-bit 12-core, 2 socket server using NUMA architecture server. OS is Windows Server 2008 R2 x64. Whenever we allocate more than 50% of the physical RAM to ...
2
votes
1answer
333 views

Select @@identity in Merge Replication

I'm going to implement database replication (merge publication, bi-direction) between two servers running SQL Server 2008 Standard edition. The setup is alright but it comes to a problem when ...
0
votes
3answers
4k views

Duration Field in Profiler

I would like to monitor the script which takes more than 25 Sec. When i use filter i am not able to see the duration taken by the script. How can we get the time taken to execute the query. My ...
3
votes
2answers
3k views

Table data transfer using SSIS and triggers on destination table

I'm a novoice in db operations. SQL Server 2008 R2 is the database I'm using. I need to move selected rows from a staging database table to production database table. I'm using SSIS package to do ...
5
votes
4answers
3k views

Alter Table for Multiple Databases (same table name)

Is there a good way to Alter a table (add a column) for multiple databases (think like 50 or so). Or is it pretty much manual for all of them? the Tables are all the same and the column added will be ...
1
vote
0answers
442 views

The correct way of representing folders

I'm trying to kind of emulate a folder structure in my application. Now I've thought of two ways to achieve this, but what is the best practice for that? Table: Attachments Columns: Id (PK), ...
0
votes
3answers
3k views

Connecting to SQL Server instance through C# [closed]

I made a program, using c# and sql server with sql authentication. It works properly in my device, but after making the program setup file .exe extension using Innosetup, I cannot connect to my ...
2
votes
2answers
147 views

Fixed database paths in stored procedures/views - is there a smoother way of doing this?

Our developers love to create lots of different stored procedures/views, which extracts data from lots of different databases on different servers. Every time we migrate to a new database server, ...
1
vote
1answer
431 views

Why SMO's Transfer tries to login into linked server when scripting?

Consider this code sample: var sourceDatabaseConnectionString = "Data Source=MyServer;Initial Catalog=MyDatabase;User Id=MyLogin;Password=MyPassword"; var serverConnection = ...
4
votes
5answers
2k views

How can I prove that the following T-SQL is bad for performance?

I have recently inherited a codebase with a large amount of Stored Procedures. The system they are supporting is encountering numerous performance problems which I am looking in to. A number of the ...
0
votes
0answers
26 views

How can I prove that the following T-SQL is bad for performance? [duplicate]

I have recently inherited a codebase with a large amount of stored procedures. The system they are supporting is encountering numerous performance problems which I am looking in to. A number of the ...
1
vote
2answers
127 views

In SQL Server, is there way to have a field where an auto-number is appended to the end of whatever value the field is set to?

Sorry if that's not overly clear. In SQL Server, can I have a field which I can place a string into, and then SQL Server appends an auto-number onto the end? So for example, say I have column A. ...
0
votes
0answers
113 views

SQL Server System Type vs Data Type

I have a (SQL Server) column which has a System Type but no Data Type (it is actually more then one) and this causes me problems when using the JDBC drivers on this column - it doesn't show up. I'm ...
0
votes
1answer
2k views

sql server log shipping: how to monitor that the replica is fresh? (e.g. that log shipping is working)

I want a monitor on my replica (destination) server to message me: a) once a day, telling me: "Data as of is ### minutes old" b) any time data is older than N minutes (indicating log shipping has ...
1
vote
2answers
1k views

SQL Server Log Shipping: File copy options?

I usually use redgate sql backup which has a transaction log copy mechanism built in. If I roll my own log shipping, what are established ways to move the log file? I want to emphasize: The way log ...
0
votes
1answer
2k views

How to measure a perfomance of SQL Server database? [closed]

I have the task to improve the performance of a SQL Server 2012 database (one of 4 in an instance) by 60% confirmed by corresponding statistics. So, I need to measure "performance" of the RDBMS ...
10
votes
3answers
24k views

SQL Server not using all CPU cores/threads

After upgrading our SQL Server's hardware, we noticed in the Windows Task Manager that the SQL instance is only using half of the threads available to it: . The server has the following hardware ...
1
vote
1answer
140 views

How to DRY record metadata in SQL Server

There is a database that has been given to me, and all tables contain these columns: CreatorUserId CreatorUsername CreationDate ModifierUserId ModifierUsername ModificationDate However, it seems ...
2
votes
1answer
12k views

Restore failed for server (Microsoft.SqlServer.SmoExtended)

I am using SQL Server 2008. While I am restoring database from backup file I got the error. Restore failed for Server 'WIN-TUT3YRM1MMN\SQLEXPRESS'. (Microsoft.SqlServer.SmoExtended) ...
0
votes
2answers
908 views

Lock Pages in Memory keep SQL database engine stuck at allocating 60MB ram?

I'm using Window Server 2003 Enterprise, Microsoft SQL Server 2005 Standard Edition. The problem is the SQL Server memory gets stuck at allocating 1.7GB RAM max even though I have 11GB of RAM left. ...
1
vote
2answers
2k views

SQL Server: Additional cpus slow down batch

I'm running SQL Server 2012 in Windows 2008R2 in a virtualized environment. I've observed the following under both VMware Workstation 9 and Hyper-V 2012R1 and I don't know how to address it. I've ...
1
vote
1answer
555 views

Retrieving data speed tweaks SQL Server 2005

I have a database in Microsoft SQL Server 2005. I have table with 3 columns, namely HASHKEY (BIGINT), NOTE_ID (INT) and TIME_OFFSET (INT). Columns HASHKEY has 19 digit BIGINT values, which can ...
3
votes
2answers
2k views

Row count from a certain table in multiple databases

I need to know the row count of a certain table that exists (or don't) in multiple databases. Here's what I came with: DECLARE @NomeBase as char(60) declare @base as char(100) declare @select as ...
1
vote
1answer
96 views

Count duplicate versions of databases (SQL Server 2008 R2)

I wish to pull statistics on the number of duplicate database versions we have running in our environment - most databases run with version control in the database name, so I built this query: SELECT ...
0
votes
2answers
475 views

linked server to specific access table

I have built a linked server which links my SQL Server database to an Access DB. Using this object someone is able to see all tables in Access. What I would like to do is to isolate it just to 1 ...
0
votes
1answer
205 views

In partition definition filegroup name appears twice, what does that mean?

short question regarding partitions, consider the following partition definition: CREATE PARTITION SCHEME [MSAppServerPS_ASWcfEventsTable] AS PARTITION [MSAppServerPF_ASWcfEventsTable] TO ...
2
votes
1answer
468 views

Why would a query in code be slower than a query in a SQL tool

When we face performance issues with out application, one of the first things we do is try to isolate if the problem is the code, the queries or the database itself. The most frustrating part of this ...
1
vote
0answers
893 views

Unable to insert into or update table

A have the following table in a SQL Server 2000 database (service pack 2), with about 600,000 rows: Id [int, primary key] Form [int] Element [int] Data [char(8000)] MoreData [varchar(8000)] ...
6
votes
3answers
19k views

Error: “To run a ssis package outside of sql server data tools you must install [send successful email] of Integration Services or higher.”

NOTE that the title of this qusetion is a verbatim error message specific to this user. The error message illustrates a bug in the dtexec application, which substitutes a user variable into the error ...
3
votes
1answer
138 views

How can I monitor a production database to see if there are table scans happening?

How can I monitor a production database to see if there are table scans happening? And what indexes might fix them... I'm told that we have adequate indexes but I can't see every code path, and we ...
0
votes
2answers
432 views

How Can I Determine Object Distribution in a Multiple-File Filegroup?

I am looking to eliminate an extraneous data file in a filegroup in my DB but don't want to use the dreaded DBCC SHRINKFILE method, I am preferring to rebuild indexes into an alternate FG, perform the ...
0
votes
0answers
2k views

SSRS 2008. subreport page breaking issue migrating SSRS 2005 reports over to 2008

I'm moving over several hundred reports that were created in SSRS 2005 over to an SSRS 2008 server, and most run fine when copying the .rpt files over, but the reports that have sub-reports don't page ...
7
votes
2answers
2k views

Clustered index compression vs table compression - are they the same thing?

If I set compression (either page or row) on the clustered index of a table, is that the same as setting compression on the table? SQL Server provides options for doing both, which suggests that they ...
1
vote
1answer
127 views

Constructing datetime from 2 fields: one integer and a datetime

I am looking for the most efficient way to parse 2 values; the first is stored as a datetime field with (hh:mm:ss.fff all being 0) and the second time field stored as an int with the format: ...
2
votes
3answers
518 views

Help me to map Column types betwenn SQL and TopSpeed Clarion Tables

I am tasked to convert an old Clarion App to new one under SQL Server 2012. All I have is 200 tables with TPS extension which I can open only by program called TopScan. (No odbc, no technical ...
-3
votes
1answer
176 views

How big the database should be in order to take 30 minutes to query

We have few tables each containing 2,5 million rows in average. Then there is a select query with joins. It takes more than 30 minutes to execute. The db server runs on a machine with 9GBs of RAM and ...