This tag is specifically for the 2008 version of Microsoft SQL Server.
1
vote
1answer
83 views
Setting DATEFIRST to 6
I would like to set FIRSTDATE to 6 (ie: saturday), i have read that to change it parmanently i could set the default language of the login to one of an existing language from the sys.syslanguages and ...
6
votes
1answer
180 views
Landed as BI, but databases are a big WTF, what to do?
Maybe a duplicate, but I believe my case is a bit different. From one of the answers I got to this post on SQL Server Central that also comes handy too but is not quite the same scenario: 9 Things to ...
1
vote
2answers
96 views
How to retrieve the definition behind statistics added to tables
Is there a way to programmatically retrieve the definition of each STATISTICS added to table columns and indexes. For both user added and system created indexes. There are many STATISTICS like ...
5
votes
3answers
916 views
cannot connect to sql server in single user mode
I have started my SQL Server 2008 sqlcst02\inst01 in Single User Mode by appending the ;-m to the end of the startup parameters for this instance in SQL Server Configuration Manager. I have stopped ...
2
votes
1answer
71 views
Windows native backups makes SQL Server think that databases backup has been done
community.
I've Windows Server 2008 R2 which hosts few virtual machines.
One of machines is Windows 2008 R2 server with SQL server 2008 R2 Express installed.
Here's the thing:
I wrote a script that ...
2
votes
2answers
223 views
Error trying to run SSIS Packages in SQL Server 2008 SQL Server Agent
I am trying to run a number of SQL Server 2008 stored procedures that are wrapped in SSIS packages consisting mainly of Execute SQL Tasks. They run fine in Visual Studio 2008 after deploying to a ...
3
votes
0answers
37 views
Error statement is not logged, only *password-— with many dashes
I use extended events to store database errors, like this:
CREATE EVENT SESSION [ErrorCapture]
ON SERVER
ADD EVENT sqlserver.error_reported
(
ACTION
(
sqlserver.client_hostname,
...
3
votes
2answers
125 views
SQl server indexes file damaged, was on ramdisk!
I have put some of my indexes into a file-group that contains one file, that file is on the ramdisk, the performance goes fire!! but the problem is that the file has deleted by wrong (The file ...
1
vote
2answers
90 views
Auto-generate scripts to create tables, indexess, etc
In SSMS, we can right click a database/table/index/... and select the "Script As" option to generate a drop and create script.
Is there a way to automate this script generation and drop the scripts ...
0
votes
2answers
69 views
Database Design Confusion
I am making a Phonegap based ERP solution for school in which every school can configure their own school individually and can manage the whole school from that single application, in which a main ...
2
votes
1answer
121 views
Bitmask Flags with Lookup Tables Clarification
I've received a dataset from an outside source which contains several bitmask fields as varchars. They come in length as low as 3 and as long as 21 values long. I need to be able to run SELECT queries ...
0
votes
1answer
73 views
SQL Server: Sync Indexes between two tables within Same database
I need to automate and Sync only indexes between two tables ( Primary and Stage_Table) within same database.
Tried using SSIS SQL Server Objects Task, but looks like it works only when we sync between ...
1
vote
1answer
40 views
How can we get tablename from inside of Trigger Code?
I have requirement that, need to pass tablename from trigger to procedure.
ALTER TRIGGER [dbo].[Trg_ProjectCreation]
ON [dbo].[Projects]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
-- ...
1
vote
1answer
59 views
Restoring SQL Server 2012 database onto 2008
I am trying to restore a SQL Server 2012 database after I set the compatibility level to SQL Server 2008 by following the instructions as listed on: ...
0
votes
0answers
29 views
Deleted a file accidentally, I need to remove it's entry, I don't need it's data because it contains only indexes [duplicate]
I have a C# program to deal with a big database, I need the databas not to be the buttle-nick of my program, in the database, to increase performance, I have created a filegroup with a single data ...
2
votes
1answer
60 views
Visualiser for SQL Server Spatial Query Results
This question Whats the easiest way to get sql 08 spatial data onto a map? (and many other articles) recommends Craig Dunn's Geoquery, but all the download links appear to be kaput.
SQL Server ...
0
votes
0answers
14 views
Restore SQL Server 2008 R2 Express from Description
When backing up SQL Server we have the opportunity to input a description of the backup. Yet non of the Microsoft tools will show that description for restoring. Is there a way for me to review the ...
1
vote
1answer
35 views
Publishing stored proc execution in transactional replication
In SQL 2008 R2, I am going to update a table which will affect 25 million rows, table is currently replicated (transactional replication). To minimize the impact on replication can I create a stored ...
0
votes
1answer
67 views
How can I set Timeout by View, User or Role?
For SQL 2008 R2, data views.
Looking for a timeout control using Microsoft SQL Server Management Studio (SSMS) that is NOT at the Server Level, and/or is NOT dependent on query timeout as set by ...
25
votes
5answers
15k views
What are the main causes of deadlocks and can they be prevented?
Recently one of our ASP.NET applications displayed a database deadlock error and I was requested to check and fix the error. I managed to find the cause of the deadlock was a stored procedure that ...
4
votes
1answer
1k views
Illegal XML character out of the extended event
I'm capturing extended events in SQL server. Trying to analyse the trace - converting the event data to xml an error comes up:
Msg 9420, Level 16, State 1, Line 28
XML parsing: line 1, character 731, ...
0
votes
1answer
67 views
How to find which tables and views a user has access to?
We have a SQL Server 2008 database and have restricted all tables and views away from a specific user ID. Over the years we have granted back tables and views one at a time based on user need.
We ...
4
votes
2answers
89 views
How does “Be made using the login's current security context” pass the users password to remote server
Say you have a SQL Server with a "Linked Server" to another server - said "Linked Server" is set to use "Be made using the login's current security context" as its authentication model.
How does the ...
0
votes
3answers
130 views
Easier way to handle so many isnull() situation
Is there any good way to avoid writing so many times isnull() function inside sproc ?
I have stored procedure that use almost 30 times isnull() function, I think that I am miss a concept , but until I ...
0
votes
1answer
65 views
Deadlock : Behaviour of NOLOCK
We are running into deadlocks when we run concurrent instances of following query :
BEGIN TRAN
IF NOT EXISTS (SELECT TOP 1 * FROM Table1 WITH (NOLOCK) WHERE Col1 = Val1 AND Col2 = Val2)
BEGIN
...
5
votes
3answers
270 views
What could cause the wrong ID to be inserted?
I have a SQL Server 2008 server (build 10.0.5500). Earlier this week I ran this on a table that already had data in it:
delete from dbo.table
go
dbcc checkident('dbo.table',reseed,0)
When the ...
0
votes
3answers
126 views
Transfer 1 million records from one table to another
I want to replicate 1 million rows from table1 to table2. I have written a job with a query like this:
delete from table1 OUTPUT *.delete into table2
I am executing this job every one hour, but it ...
1
vote
1answer
774 views
Invalid use of side-effecting operator 'INSERT EXEC' within a function [closed]
I'm trying to create a scalar function that will return a delimited list from some sql that would be passed in. Unfortuantly where I work the previous people responsible for the database thought it ...
0
votes
1answer
94 views
sql server-percentage calculation
Sample data :
LINE_NO E_FIELD F_FIELD G_FIELD HSA_STATUS FAMILY CACHE_FAMILY Count
23053B 00000 00000 00000 S SUMMIT WER 43
23053B 00000 00000 00000 T ...
2
votes
2answers
172 views
How to refresh AD security group on Sql Server permissions
I am using Sql Server 2008 on Small Business Server 2003; client is using WinXP.
I added a user to my Active Directory Security Group; Why can't this user immediately access the database? It seems ...
3
votes
2answers
50 views
maintaining full text index on large table
I'm on SQL Server 2008 and have a table, for reporting purposes, with 500,000 records that will easily reach the millions. The table will employ a full text index for rapid searching
on a handful of ...
0
votes
1answer
175 views
Import batches of rows from Excel in parallel
We receive an Excel file weekly currently containing about 250k rows. The file grows by about 300 rows each week.
In SSIS I've hooked up an Excel source to an OLE DB destination to import the file ...
4
votes
2answers
278 views
SQL Server 2008 R2 SP1 - Tempdb “Available free space” showing negative value
Please help as this issue is driving me nuts...
Tempdb has four data files & one log file. They are all located on the same dedicated drive.
When I right click on tempdb in SQL Server Management ...
7
votes
8answers
16k views
Moving tables to another SQL2008 database (including indexes, triggers, etc.)
I need to move a whole bunch (100+) of large (millions of rows) tables from one SQL2008 database to another.
I originally just used the Import/Export Wizard, but all the destination tables were ...
0
votes
1answer
62 views
Does a single, simple select query creates locks?
I am very new to SQL Server, and would like to understand whether the following, very simple select statement would take any locks.
Select * from Student;
Please consider the case where the ...
0
votes
0answers
22 views
Fast afterUpdate cross-database trigger blocks insert
I'm trying to fix a timeout issue in an existing application.
There is a trigger on after_update that cascades a value from the update from master.Client to internal.Client and external.Client
The ...
4
votes
1answer
70 views
SQL Server 2012 indirect checkpoint
I am trying to understand where to draw a fine line for Indirect Checkpoint that was introduced in SQL Server 2012.
Based on my understanding
SQL Server does checkpoint to flush dirty pages out to ...
1
vote
2answers
70 views
Will SSIS 2012 work with SQL Server 2008R2 (not integrated)
Our company has SQL Server 2008R2 and plans to upgrade to 2012 in late 2013 or early 2014. I would like to use SSIS 2012 now as we do have the software.
We have a company policy where we cannot run ...
3
votes
1answer
207 views
query processor ran out of internal resources and could not produce a query plan
This is showing up in the logs several times a night. How do I find the query causing the issue? SQL Server 2008 R2 Sp1.
Thank you
0
votes
2answers
31 views
Enforce indirect relationship
Given the following structure
There's a way to enforce that Table1.id3 only can seted to values in Table4.id3 where Table4.id2 = Table1.id2? Initially I thought in a CHECK constraint, but subqueries ...
2
votes
1answer
47 views
How to enforce a nullable foreign key?
I have a relation between two tables. The foreign key table can have a row related in the primary table. There's a way to enforce the value of the FK column to be NULL or one of the values of the PK ...
2
votes
2answers
63 views
Using TSQL to dynamically create Extended Properties Name
I would like to add "Description" and "Status" in the Extended Properties of a large database to all my Tables and Columns before someone fills in the values.
It would be very laborious to add these ...
0
votes
1answer
23 views
How to get default locking hint of a table
Is there is any way to get default locking hint using a query. I want to know whether the locking use by my transaction is tablock | rowlock | nolock etc.
6
votes
1answer
199 views
problem running xp_cmdshell when Application Name set in connection string
I have an app on 10 or so servers that runs a few xp_cmdshell statements against MSSQL 2008. It works fine on all the servers except for one. To make things worse, I can run all the commands in SQL ...
0
votes
1answer
41 views
Generic SQL Job Scheduler for multiple RDBMS's?
I have been searching for an answer to this, but can't seem to find anything. So my problem is this - we have an environment with MS SQL Server 2008, MySQL, and RedShift, and have some complex ...
1
vote
1answer
33 views
SQL Server Key lookup Partition Pruning
I have a query inner joining multiple tables all of which are partitioned and have 10 partitions each
SELECT
A.COL1
B.COL2
C.COL3
FROM
A
INNER JOIN B ON A.ID = B.ID
INNER JOIN C ON A.ID = C.ID
...
3
votes
2answers
622 views
Users cannot view tables in non-default schema in SSMS
I'm having an issue setting the VIEW DEFINITION permission appropriately at the schema level for one of my users. I've created the schema TestSchema and added some tables. The user currently has ...
0
votes
0answers
48 views
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON' [closed]
I have 4 SQL Server 2008 instances from which I need to pull data using OPENROWSET. I am adding this data to a table on server 1. Server 1 and 2 are in a cluster, same as 3 and 4. I am remoted into ...
2
votes
3answers
80 views
Getting sql statement to refer to more than 2 tables?
I have the following tables:
StaffName
============================================================================
| Name | Income_Group | Educational_Level | Country |
...
0
votes
1answer
42 views
SQL Server database files on different disk as is OS installed
My very short overview
SQL Server databases are installed as virtual machines on VMWare, there are plugged a few disk areas with the same (or very similair) parameters
versions are SQL Server ...
