This tag is specifically for the 2008 version of Microsoft SQL Server.
0
votes
1answer
13 views
checksum(newid()) as primary key - what happens if a collision occurs?
Inspecting a rather critical database that is used by some software on my system, I found that one of the tables had a primary key on the column Id, where Id is calculated using checksum(newid()). ...
2
votes
3answers
56 views
One filegroup, multiple datafiles, how to get list of tables in each file
In one of my production databases, I have a filegroup with 3 data files sitting on 3 different disks. There are over 50 tables on this filegroup.
How do I identify the location of each of the 50 ...
5
votes
1answer
38 views
Dropping Hypothetical Indexes
In the past I thought I'd deleted hypothetical indexes using either a DROP INDEX statement for clustered indexes and DROP STATISTICS statement for non-clustered indexes.
I have a database that is ...
3
votes
1answer
41 views
Convert SQL Server 2012 SSIS package to 2008
I've created a few SSIS packages in SQL Server 2012 that I need to run on SQL Server 2008. I've been told that it is impossible in their current state.
Is there a way to convert the 2012 packages to ...
1
vote
1answer
15 views
Finding oracle physical database files
I'm using oracle 11g and I need to find the database files to copied to another server. The database was created from migrating from SQL Server 2008 to 11g. There doesn't seem to be a counterpart for ...
1
vote
2answers
48 views
SQL Server Memory Clerk Description
Trying to understand different memory clerks reported by sys.dm_os_memory_clerk however, not able to find description of the each individual memory clerk either on web (google and binged) or in SQL ...
4
votes
2answers
66 views
Should I use a composite or single-column index?
I have the following columns in my database table (Medicines).
ID bigint,
MedicineName nvarchar(50),
BrandName nvarchar(50),
MedicineCode nvarchar(20),
and price,quantity.
I am making a stored ...
2
votes
2answers
42 views
Is there a standard way to use common data and procedures across multiple databases?
We have several independent databases that have data and code in common, not in the sense that it is accessed between the databases but in the sense that the data means the same thing in each ...
1
vote
2answers
84 views
Database running out of space
My database has 16MB of space left.
I used to just truncate as I was taught but I found these links that advise against truncating:
...
1
vote
3answers
38 views
How to create new users for restored databases?
I am not too familiar with SQL Server and am facing the following problem: The server was freshly installed and the only existing login is sa.
Then a couple of databases were restored from earlier ...
0
votes
1answer
49 views
using CASE with ISNULL
I have a select statement which is followed by a union. Inside the select, there is a where clause. I am trying to add in the following code to the where clause but I'm having an issue getting it to ...
1
vote
0answers
44 views
Re enable Windows Authentication [closed]
My old employee has disabled Windows Authentication in our server. Now I'm not able to access the SQL Server even though I have Administrator access to the server. I need to reset the sa password.
4
votes
2answers
92 views
Re enable Windows Authetication in SQL Server
My old employee has disabled Windows Authentication in our server. Now I'm not able to access the SQL Server even though I have Administrator access to the server. I need to reset the sa password.
I ...
0
votes
1answer
44 views
convert(varchar, getdate(), 112) or convert(varchar(8), getdate(), 112)
Which sql command is recommended to convert a date to yyyymmdd?
1) convert(varchar(8), getdate(), 112)
or
2) convert(varchar, getdate(), 112)
I notice that if I used I use the 2nd one, it seems ...
1
vote
1answer
45 views
Get the 2nd or 3rd occurrence of a value in a delimited string
I have the following table:
==========================================================
| Name_Level_Class_Section | Phone Num |
...
0
votes
1answer
17 views
Is there a system primary key associated with each row?
Does a system primary key or unique identifier for each row in a table exist? Like @@ROWID, @@IDENTIFER, or some other identifier that uniquely identifies each row regardless of how it was created? ...
10
votes
2answers
235 views
How to avoid using variables in WHERE clause
Given a (simplified) stored procedure such as this:
CREATE PROCEDURE WeeklyProc(@endDate DATE)
AS
BEGIN
DECLARE @startDate DATE = DATEADD(DAY, -6, @endDate)
SELECT
-- Stuff
FROM Sale
...
-1
votes
3answers
80 views
How to restore SQL Server 2008 R2 backup to SQL Server 2008 [duplicate]
I am unable to restore a backup from a 64-bit instance onto a 32-bit instance.
Source Server:
Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64)
Destination server:
Microsoft SQL Server ...
0
votes
1answer
49 views
Can I use the database and query it while full-text indexing?
I need to index my database, but I need to query it while it is indexing, it may take a long time, can I do that?
2
votes
1answer
113 views
Cannot connect to SQL Server after setting sa password to blank
I have installed SQL Server 2008 R2 on a Windows 2008 server. I need to set the sa password to blank for some reasons to make an application works; but it seems like the password changes automatically ...
-1
votes
1answer
61 views
Validate each parent intermediary is also a parent to itself
I have a table for which I want to validate where each parent intermediary is also a parent to itself. How to write query to validate this?
COLUMN DATATYPE DESCRIPTION
...
6
votes
1answer
132 views
How to shred .docx XML?
I am trying to import a xml (actually a docx file) to a sql server 2008 database. I am almost a novice in XML programming. I googled a lot but almost all the examples there are with simple xml file. ...
0
votes
3answers
79 views
How to find all positions of a string within another string
How can I find all the positions with patindex in a table or variable?
declare @name nvarchar(max)
set @name ='ali reza dar yek shabe barani ba yek '
+ 'dokhtare khoshkel be disco raft va ali ...
6
votes
3answers
270 views
Installation of SQL Server RAID 10 vs RAID 5
I am new to setting up SQL Servers and Vms. My predecessor used Raid 5 for C Drive where the SQL installation resided and RAID 10 for both database files and database logs.
I was wondering whether ...
1
vote
1answer
53 views
SQL Server-MySQL master-master replication
We are looking into setting up a master-master replicated MSSQL database and I am interested to hear of any potential pitfalls that we may run into along the way and generally any advice that you ...
-1
votes
2answers
94 views
Validate every client has a valid gender
I have table CLIENT which have columns are
COLUMN_NAME DATA_TYPE DESCRIPTION SOURCE TABLE
ClientPK int Identity column
ClientID int Client ID PARTY ...
2
votes
2answers
30 views
sysjobhistory “sql_severity” codes
Where can I find out what the different "sql_severity" codes mean in the sysjobhistory table?
Eg what does 0 mean?
I've inherited a report that excludes ones with a 0 as "0 is not a real error"?
I ...
3
votes
1answer
49 views
What is the safest way to run SSIS on Multiple Named SQL Server Instances?
I have an Active/Active SQL Server 2008 R2 cluster. There are two named instances on each node and no default instance.
I need to deploy SSIS packages to the different instances. To do this, I ...
4
votes
2answers
112 views
How can I reliably track stored procedure ( and functions ) use over time?
I have a lot of stored procedures and functions in database, many are not used any more and I want to remove them. How can I find them reliably ?
I have seen answer that suggests looking into plan ...
0
votes
1answer
34 views
IIS Application pool running under network service can't connect to SQL Server
I just deployed an ASP.NET MVC 4 application to IIS 7 but when I browse it i get the following error:
Cannot open database "PCSPI" requested by the login. The login failed.
Login failed for user 'NT ...
7
votes
1answer
120 views
Automatic decimal rounding issue
The question is relatively simple. I need to calculate 3 columns where the mid results are huge decimals, and I'm running into a problem early on with SQL Server basically rounding the decimals ...
2
votes
4answers
84 views
Trigger Performance : Is Using Trigger is a good way?
We are using SQL Server 2008 for a j2ee web application. I need to maintains the audits for updates for each column with their previous value and new value on one of my table which is very heavy, ...
3
votes
3answers
99 views
sleeping SPID blocking other transactions
I'm really having trouble tracking down some blocking we are experiencing.
The root blocking SPID's status is 'sleeping', the cmd is 'AWAITING COMMAND', and the sqltext is 'SET TRANSACTION ISOLATION ...
2
votes
2answers
102 views
Is there a difference in performance between @date and getdate()?
Usually I use the getdate() function in my where clauses to go back in time.
Something like:
DOC.DATUM >= DATEADD(DD,-1*SSN_SDO.DANA_ZA_POVRAT,GETDATE())
Will SQL Server 2008R2 perform faster ...
2
votes
2answers
81 views
Cannot rebuild index, but there's no reason why not?
I've created a process whereby I am able to only rebuild indexes that need rebuilding(the process takes an hour and a half if I rebuild them all), and while it works beautifully, it gets stuck on one ...
4
votes
1answer
46 views
Log file growth issues
We're consolidating data from a bunch of databases into four reporting databases each night.
Because the entire dataset is imported each night we do not need to be able to restore the data to a point ...
3
votes
2answers
45 views
Can I get notification when event occur?
SQL Servers has Traces and XEvents. These are used to capture and analyze what is going on with our SQL Server Instances. Events are stored in the stack for later analysis. For example,
If I decide ...
2
votes
1answer
56 views
Is replication from SQL Server 2008 to PostgreSql possible?
Is it possible ? Sql Server as publisher(master) and PostgreSql as slave(subscriber) ? Any type of replication really.
1
vote
1answer
83 views
Total Memory used by SQL Server (64 bit)
My knowledge on the subject suggests that perf counter SQL Server:Memory Manager: Total Server Memory only gives you buffer pool memory. There is a column called physical_memory_in_use in a DMV named ...
0
votes
1answer
82 views
Is it there anyway to restore updated records in Microsoft SQL Server 2008 R2?
We have an test database configured with our web application. Due to internal web application error the entries were get updated in the database. Is it there any way to recover the updated records?
...
5
votes
2answers
142 views
Maximum memory setting in SQL Server
I'm running SQL Server 2008 and a web based application, on a single dedicated server, with only 2Gb of memory available.
As is noted elsewhere, SQL Server regularly takes up to 98% of physical ...
2
votes
2answers
37 views
Modifying replication distributor & publisher
I have a SQL Server 2008 transactional replication setup as follows:
192.168.100.1 <-- Distributor
192.168.100.2 <-- Publisher
192.168.100.3 <-- Subscriber
Our network team want to change ...
2
votes
0answers
45 views
How can I copy a user while preserving all privs?
We're changing domains. I'd like to create a new account for myself, NewDomain\Bob which has the same su-level privileges as OldDomain\Bob, to minimize the chance of disruption when IT flips the ...
1
vote
2answers
84 views
Working Linked Server Query fails in sp_send_dbmail
Take the following example:
EXEC msdb.dbo.sp_send_dbmail
@recipients = 'me@whatever.co.uk' ,
@query = 'SELECT TOP 10 * FROM LINKEDSERVERA.DATABASE.dbo.TABLE' ,
@attach_query_result_as_file = N'True' ...
3
votes
0answers
35 views
SELECT COL_NAME(969262708, ORDINAL_POSITION) returns a NULL value in one of the rows
So I've got a Stored Procedure that's copying data from one table to another. It calls this other SP to get the column names. I didn't write this darn thing, but here's that part:
ALTER PROCEDURE ...
3
votes
0answers
38 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,
...
1
vote
2answers
97 views
Auto-generate scripts to create tables, indexes, 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
72 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 ...
1
vote
1answer
46 views
How can we get table name from inside trigger?
I need to pass the table name from trigger to procedure.
ALTER TRIGGER [dbo].[Trg_ProjectCreation]
ON [dbo].[Projects]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
-- procedure call
...
0
votes
2answers
81 views
Sync Indexes between two tables
I need to automate and Sync only indexes between two tables ( Primary and Stage_Table) within the same database.
I tried using SSIS SQL Server Objects Task, but looks like it works only when we sync ...



