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)

2
votes
0answers
235 views

Configuring SQL Server Job agent

I am trying to configure the SQL Server Job Agent using the following scripts USE [msdb] GO CREATE USER [mydbuser] FOR LOGIN [mydbuser] GO EXEC sp_addrolemember N'db_datareader', N'mydbuser' GO EXEC ...
2
votes
0answers
889 views

SQL Server Reporting Services Slow TotalTimeDataRetrieval Fast ExecuteReaderTime

I wrote a report that is executed by a few hundred users each day and the underlying stored procedure for the data set only takes milliseconds to execute. According to the ...
2
votes
0answers
195 views

SQL Server : Transactional Replication Filter By Computed Column

There is a database that contains records for multiple groups of people. These records do not contain a column indicating which group the record is "owned" by (very frustrating). I need to setup ...
2
votes
0answers
343 views

Best Practice to update columnstore indexed tables

I have a table with aprox. 22 Bn rows, for each month I have made one partition that and each partition has two data files. I created the columnstore index on this table and the performance ...
2
votes
0answers
45 views

improving this goal query

Goal joining the two tables: tbl_quickSearchParam (fulltext search results) against tbl_product ( active products : prod_status = 1, group by prod_image_name) create table tbl_product ( ...
2
votes
0answers
410 views

SQL Server 2008 R2 - Deadlock Trace, Need Help Understanding It

I really need help on understanding this deadlock, i've already replaced the values, mind if anyone helps me interpreting where my deadlocks are coming from? What does e_waitpipegetrow mean? btw, i'm ...
2
votes
0answers
40 views

Query Regarding Database Instance not proper

Please sort out one issue regarding SQL Server instances. I have one instance of SQL Server named 'Test\Connect' and other one is 'local' by default. So the issue is when I connect to local and ...
2
votes
0answers
140 views

How To Analyse Index Scans

I am using merge replication with SQL Server 2012. During a sync using a merge agent there are a large number of index scans. I can see this by running a profile capturing SCAN:Started and ...
2
votes
0answers
2k views

SQL Server connection with multiSubnetFailover fails, claims mirroring is enabled

I have two SQL Server instances in an availability group. They are not mirrored. I'm trying to connect to the AG using the MS JDBC drivers (4.0) with a connection string including ...
2
votes
0answers
931 views

Finding Microsoft.AnalysisServices.Xmla dll in SQL Server 2008 R2

I'm not able to find Microsoft.AnalysisServices.XMLA.dll that I need to import using Microsoft.AnalysisServices.Xmla; I have found some articles and answered questions for SQL Server 2005 and 2012. ...
2
votes
0answers
84 views

How to display differential backup date in SSMS Object Explorer Details

SQL Server Management Studio exposes the Object Explorer Details (selectable from the View menu option) and its context menu that allows adding fields to the default display. Full and Transaction Log ...
2
votes
0answers
49 views

List of changes required to upgrade scripts after starting to use replication in SQL Server

We have recently setup transactional replication on our test and production databases. As part of that change, we've had to make changes to how we create upgrade scripts as well. The goal is to make ...
2
votes
0answers
766 views

SQL Server Database Engine Tuning Advisor vs DMV queries

What is the best way to tune the indexes on my production database? I normally use the following query : SELECT user_seeks * avg_total_user_cost * (avg_user_impact * 0.01) AS [index_advantage], ...
2
votes
0answers
160 views

How do I back up and restore a SQL Azure database?

How do I back up and restore a SQL Azure database? For that matter, how do I back up and restore an entire Azure account? Let's suppose I am working at a company and I buy a 3 month trial account ...
2
votes
0answers
448 views

Linked server issue

I made a linked server between two sql server instances. The remote one I have very little control over. When I try a query I get this: Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for ...
2
votes
0answers
81 views

Optimizations for new SQL Server 2000 database that will act as email queue AND API database

Due to limitations put on me I've got to put my outgoing Email Queue in the same database as my customer information. What are some optimizations I can make, specifically to the Email portion that ...
2
votes
0answers
212 views

CREATE ASSEMBLY - EXCEPTION_ACCESS_VIOLATION

I am trying to create an assembly on my SQL Server 2005 server. Here is what I am using: CREATE ASSEMBLY [SQLCLRTest] AUTHORIZATION [dbo] FROM 'path\to\my\file.dll' WITH PERMISSION_SET = ...
2
votes
0answers
62 views

How to Remove Articles Based on User

I am using merge replication on SQL 2012. I have a very large database with a large number of tables. The subscribers are using netbooks. I have a filtering scheme that controls which tables end up ...
2
votes
0answers
116 views

Are there any best practices before moving SQL Server to another node (controlled failover)?

Let's say there are multiple jobs running on the SQL Server - we have index maintenance jobs, backups, replication, etc. We would like to perform a switchover using Failover Cluster Manager. Are there ...
2
votes
0answers
366 views

Could not allocate space for object 'dbo.History' in database '[path to mdf]' because the 'PRIMARY' filegroup is full

I have a local project i am working on, on my own workstation. I use a local SQL server as well. The project contains alot of data, and as of now, i just need to be able to store all this data in the ...
2
votes
0answers
466 views

SQL Server SSIS issue retrieve data when i enter into login of server side?

can any one try solve my problem here i am facing a problem Failed to retrieve data when i enter into login of server side here is the error message TITLE: Microsoft SQL Server Management Studio ...
2
votes
0answers
466 views

SQL Server : where with cast(value as varbinary) vs cast(value as varbinary(300))

Are there any theoretical performance differences between these queries in the scenarios below. Scenarios are: MyTable has Value nvarchar(300) MyTable has Value nvarchar(max) Q1: declare @p ...
2
votes
0answers
825 views

How to do automated archiving for a SQL Server database based on interpretation of database metadata?

I am looking for a solution giving me automated archiving of SQL Server databases based on a smart interpretation of table relationships that is able to archive records from related tables minimizing ...
2
votes
0answers
191 views

MDX Block Calculation Optimisation

I have a query I'm trying to optimise. I have read an article here http://web.archive.org/web/20100823233821/http://www.biblogs.com/2008/03/30/optimizing-countfilter-expressions-in-mdx/ about ...
2
votes
0answers
356 views

going from heirarchical XML to heirarchical SQL tables then SQL to c# objects to go to user view, how should I store the data in the databases?

Hi I'm getting my data from a web service using their getData call and it comes in as fairly chunky heirarchical XML, first everything all at once, like <allData> <product1> ...
2
votes
0answers
783 views

Logic to convert a UTC date-time to a specific time zone

I have a table which has 1 row per event. Each event has a datetime stamp column which has values in UTC time. Each row has an associated local timezone in which the event occurred. I have a DIM ...
2
votes
0answers
772 views

Update Statistics Maintenance Plan - Ambiguous column name error

Microsoft SQL Server 2008 R2 Web Edition (RTM) - 10.50.1600.1 (Intel X86) Windows 2003 R2 SP2 I am in the process of setting up a maintenance plan to update statistics on a daily basis for all user ...
2
votes
0answers
289 views

SQL Server retrieval error under heavy load (200 concurrent users)

Currently I'm facing an issue in a SQL Server retrieval procedure. Stored procedure returns unexpected result when executed by many user (No. of users > 40) concurrently for the same parameter. For ...
2
votes
0answers
2k views

Could not open File Control Bank (FCB) for invalid file ID X when restoring from snapshot

We have a scenario in which we, during integration tests, make a backup of an existing database and restore it under a new name. We then create a snapshot of our "new" database. After each test we ...
1
vote
0answers
17 views

SQL Server 2008 R2 services not coming online

I am working for a critical account and yesterday I got a weird issue after applying cumulative update package 14 on top of SQL Server 2008 R2 Standard edition. Before applying CU-14 package on the ...
1
vote
0answers
20 views

SQL AlwaysOn & System Center Configuration Manager TP 4 1601

Recently Microsoft released Technical Preview 4 for Configuration Manager (1601). Per TechNet, "Configuration Manager now supports using a SQL Server AlwaysOn availability groups to host the site ...
1
vote
0answers
30 views

Readable secondary

I set my readable secondary to read intent only and I ran my application without application intent = readonly and start a trace on the secondary node to make sure we are not reading from the ...
1
vote
0answers
35 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 ...
1
vote
0answers
52 views

Query with join to a table on a different database need an index

I'm creating a query that one of its joins is made to a table from a different database. The query analyzer is suggesting the creation of an index, however I don't have permission to add objects to ...
1
vote
0answers
31 views

Unable to use select top N shortcut on Azure SQL Database via SSMS

Yesterday morning I was able to query my Azure databases via SSMS and the Select Top N menu shortcut. That afternoon I received a couple of alerts that my web applications couldn't connect to the ...
1
vote
0answers
58 views

reset identity of a column on a new primary key record

I have the following table CREATE TAbLE aTEST_person ( person_id int default (NEXT VALUE FOR [person_id_TEST]) , version_num int, name varchar(255) CONSTRAINT ...
1
vote
0answers
20 views

Change default value for Job Activity Monitor

Is there a way for me to programmatically (some configuration setting in a table / registry entry etc, I'm hoping) change the default value in the Auto Refresh setting to 5 seconds instead of 60, and ...
1
vote
0answers
31 views

Backup creating fork_point

I have a powershell script that is restoring from production into a test availability group database. It copies the full and t-log backups from production to test, takes the target db out of the ...
1
vote
0answers
60 views

SQL Server 2014 installation taking long time

Normally our SQL Server installation takes 5 - 10 minutes, but on several of our recent server builds on vmWare, the SQL Server 2014 Enterprise Edition setup has been taking a very long time. I have ...
1
vote
0answers
24 views

service broker/resource govenor

Is it possible to configure service broker to work with resource governor? Currently all commands are being sent to the internal group but I want them to go elsewhere.
1
vote
0answers
28 views

Move extended property comments to create table comment

I want to convert / import extended properties of table columns of a lot of tables to simple comments next to the table definition scripts columns in the table definition of a new SSDT database ...
1
vote
0answers
12 views

MSSQL how to open 'Edit' window when databases has been hidden

I hope that someone in here have had this issue before. THE ISSUE: - Because 3rd party consultants are doing work to specific customer databases and should not see customer databases to which they ...
1
vote
0answers
37 views

SQL Server 2012 AG replication issues

We just went away from SQL Serer clusters in SQL Server 2008 to SQL Server 2012 with AG – have five servers with replicas in a secondary datacenter and syncing over a dedicated eline/LAN layer 2 ...
1
vote
0answers
45 views

How to use set-based statement instead of cursor for converting 1 table to two, keeping relations

I'm relatively new to databases and currently working with SQL. I am doing a conversion for a customer. This conversion points to local files on a filesystem. In the old database there simply was a ...
1
vote
0answers
42 views

SQL Server 2008 R2 service pack steps …on cluster

I have a sp3 to apply on SQL Server 2008 R2 on windows 2008 R2 cluster with four nodes and 6 instances. Node 4 do not have any resources but is possible owner for all the instances. Steps I am ...
1
vote
0answers
37 views

Errors while accessing Excel file using openrowset

I'm seeing the below error message while running the following T-SQL statement. select * FROM OPENROWSET( 'Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=123.xls', 'SELECT * FROM [Sheet1$]') ...
1
vote
0answers
37 views

Collecting DB Used Space (MB) from SCOM on an Always-On DB

My client has asked for database sizing for capacity planning. I am trying to use System Center Operations Manager (SCOM), however when I get to the Always-On Cluster, it doesn't seem to display the ...
1
vote
0answers
40 views

Stored procedure not running on the same database when using different login

I am trying to run a stored procedure on the same database using two different login users. While using the Windows authentication , I'm able to run the stored and proc and see all the data available. ...
1
vote
0answers
32 views

SQL Server Trace Flag 834 on VMware

It's recomended to enable trace flag 834 to use large-page on buffer pool for SQL Server running on VMware? The tuning options kb of Microsoft (https://support.microsoft.com/en-us/kb/920093) don't ...
1
vote
0answers
52 views

SQL Server 2012 Installation Error on Windows 2008 Server

I am facing an error while trying to install SQL Server 2012 EXPRESS ADVANCED EDITION(32bit) on Windows Server 2008 Enterprise(64bit). After passing Rule Check, at install updates section, error ...