SQL Server 2008 (major build version 10.00.xxxx). Please also tag with sql-server.

learn more… | top users | synonyms (2)

0
votes
3answers
45 views

How to check if range between two numbers is available in table of ranges

I have following table of number ranges Create table Ranges { Id int identity(1,1) primary key, RangeFrom int not null, RangeTo int not null } Insert into Ranges(RangeFrom, RangeTo) VALUES ...
0
votes
1answer
29 views

Query distinct emails not logged in within a period of time

I need help in writing a query to return distinct emails that have not logged into ABC database in the past 5 months. The exact task I have been asked is as following: Create a table in ABC ...
-1
votes
0answers
9 views

Can we have more passive nodes in a multi-clusterd nodes [on hold]

How many passive Nodes can we have in a five nodes clustering? Any ideas please?
1
vote
2answers
3k views

Calculate time difference between a datetime and the current date & time

I would like to calculate the difference between a past datetime and the current datetime with the result in [hh]:mm format.
1
vote
1answer
6k views

Standard data types for email address, telephone number , first name, rixh text editor, etc

I want to build a database for our system using Sql Server 2008 R2. but i need to know what are the best data types for storing these info:- Email address Telephone number with extension, such as ...
17
votes
7answers
404 views

Group daily schedule into [Start date; End date] intervals with the list of week days

I need to convert data between two systems. First system stores schedules as a plain list of dates. Each date that is included in the schedule is one row. There can be various gaps in the sequence ...
3
votes
3answers
829 views

Can the OUTPUT clause create a table?

I'm doing an update like this: UPDATE dbo.Table1 SET BirthDate = b.BirthDate FROM Table1 a JOIN Table2 b ON a.ID = b.ID And I want to use the OUTPUT clause to back up my changes. UPDATE ...
1
vote
1answer
48 views

Slow-running query returns incomplete resultset

So I'm trying to get some data from an SQL Server 2008 instance, the query is quite simple in structure but it has an extremely poor performance, it takes just under a minute to retrieve a bit over ...
7
votes
1answer
122 views

Should we still be using QUOTENAME to protect from injection attacks?

I was looking at an old stored procedure today and noticed it was using quotename on the input parameters. After doing some digging to figure out what that does exactly I came across this site. I now ...
3
votes
2answers
220 views

Why database snapshot creation is faster than normal database backups?

Could you tell me any reason why creation of database snapshot is faster than database normal database backups in MS SQL server
0
votes
1answer
629 views

Select Query takes more than a minute to retrieve data

I have a table that has 99 columns, all of them are not needed but they were there when we started. There is a Primary Key (Objectuid) which is a GUI and has the Clustered Index on the PK. The Table ...
-4
votes
0answers
15 views

show invalid object name [on hold]

when this code runs on mysql database then fine but when this code run on sql server that showing error com.microsoft sql server exception invalid object name... private void ...
1
vote
1answer
25 views

sql getting sum of calculated columns

How can I get the sum of TotalAmount as single value for example Product Name UnitPrice Quantity Discount% Amount SharingCost% ShippingCost CustomsCost TotalAmount pro1 ...
-1
votes
0answers
15 views

How external/internal fragmentation effect to query performance [duplicate]

could you explain me what is external fragmentation and internal fragmentation? what does that effect to query performance Thanks in advance
0
votes
3answers
62 views

Get all records from inner joined tables if second table does not have data

I follow below syntax by which I achieve the result. but it takes time IF NOT EXISTS(select * from #tblxyz) BEGIN INSERT INTO #tblxyz select NULL END Select a.* FROM tblabc a ...
4
votes
1answer
68 views

When executing a command via xp_cmdshell what user issues the command to the system?

I'm executing the following command to stop a service on a remote server from within a stored procedure in SQL Server. ---------------- -- Stop Service ---------------- SET @Command = 'sc \\' + ...
15
votes
4answers
4k views

Anybody using HierarchyId in production? Is it reliable?

Is anybody using HierarchyId in real production with tables of reasonable size, more than a few thousand rows? Is it reliable/performant? So far I have not found anyone not affiliated with the vendor ...
0
votes
1answer
137 views

Maintaining multiple build versions of asp .net / SQL Server applications

We have new versions of .net applications deployed every week for testing given in form of new build files. Can any one suggest how I can maintain many versions of the builds without having to keep ...
2
votes
1answer
24 views

msdb.dbo.sysjobs and last run date and time

I know that the last_run_date and last_run_time are stored as Int, but when coverted over to datetime (of which I have examples of), is the time GMT, or what? When converted it doesn't match the same ...
0
votes
0answers
13 views

Would snapshot replication avoid the “repl table shuffle” when making schema changes?

I support an application that sits upon a SQL 2008 database and has an admin tool that allows app admins to do some field customization. This issues an ALTER TABLE command when saved, and since the ...
0
votes
0answers
23 views

Any table type that persists across connections

Probably not possible,but I'm going to ask this anyway: I don't have OPENROWSET privileges on server B. I would like to import data while connected to server A (where I do have that privilege) and ...
0
votes
1answer
1k views

syspolicy_purge_history job failed at step 3

In one of my SQL Server 2008 sp1 standalone instances, syspolicy_purge_history job is fails at step 3 Erase Phantom System Health Records. Below is the command - (Get-Item ...
4
votes
1answer
1k views

SQL Server: update trigger fires before insert trigger

I have an application with a SQL Server-hosted database. I do not have access to the application code, but I do have full access to the database. I have added my own custom auditing to a table to ...
4
votes
1answer
715 views

Cannot Delete FileStream Group from Sql Server 2008

We have migrated all our varbinary(max) column data to Azure Blob Storage and so we want to remove the old filestream columns that remain in our Sql 2008 database and the filestream filegroup but when ...
4
votes
1answer
362 views

Updateable Subscriptions: Rows do not match between Publisher and Subscriber

I have transactional replication with updatable subscribers set up in SQL Server 2008. It has 3 subscribers and 1 publisher. I had to setup replication again due to some errors related to the database ...
0
votes
4answers
936 views

SQL Query Declaration problem

I have a view as shown below.The View is good running.When I attempt to run view,I got the following the error.What might the problem? Incorrect syntax near the keyword 'Declare' SQL Query: create ...
4
votes
2answers
92 views

The index columns in sys.index_columns different from what are in the index

When I run the following I get 3 indexed columns: SELECT b.name, c.name FROM sys.index_columns a INNER JOIN sys.indexes b ON a.object_id = b.object_id AND a.index_id = b.index_id INNER JOIN ...
1
vote
1answer
89 views

Most efficient way of finding rows in a table that don't exist in another — SQL Server

What is the best method of finding rows in table A that don't exist in table B based on unique key of the tables, when both tables are huge (A over 300 million rows, B over 500 million rows) and the ...
0
votes
0answers
15 views

Connect MSSQL DB with SSAS

I am trying to build a BI system using SQL Server 2008. I have installed and created the database. I have also installed SSAS but I can't seam to find a way to connect my database as a Data Source ...
1
vote
0answers
27 views

Problem inserting into table from trace file consumes all space in tempdb

I'm working with SQL Server 2008 SP3 Enterprise Edition (64-bit) I'm trying to import a trace file of about 3.5 MB (4667 rows) into a table as following: INSERT INTO DBTRACE with (rowlock) SELECT * ...
4
votes
1answer
111 views

Scenario of huge SQL Server Database backup

I use SQL Server and have a huge database with large compressed backup size (about 250 GB). The backup scenario of this database is: Create full backup at the first day of each month. Create ...
0
votes
2answers
114 views

Mapping a Login to a User

On my production system I login to SQL Server with CPSDOM\mconnors. When I execute SELECT CURRENT_USER; it returns CPSDOM\mconnors. We use Windows authentication on our DBs. On my test system I ...
1
vote
1answer
48 views

OFFSET FETCH in SQL Server 2008

I have this query to paginate the results and it was working fine on SQL Server 2012. However i had to move my database to SQL Server 2008 and now my store procedure is not working. I did some ...
2
votes
3answers
23 views

SQL Login curretly in use or not

Recently we did a change on a client's production server. The change was to rename an sa login. Once we renamed the sa login we found out the client was having a problem because sa login was used in ...
0
votes
1answer
25 views

CMDEXEC The process could not be created for step… (reason: Access is denied). The step failed

I have created a job to test executing a CmdExec step. The job step is simply executing a batch file with dir as the command. The job is executing as the SQL Server service account. This account ...
6
votes
3answers
1k 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 ...
1
vote
1answer
19 views

Managing Security for a Log Shipped db across two (unconnected) domains

SQL Server 2008 (soon to be 2012). We have a vendor that is providing us with log shipped backups of our production database. We use this log shipped database for BI staging, reporting, etc. I've ...
3
votes
1answer
53 views

SQL Server Clustering - Active/Active or Active/Passive?

What is the logic of the Active/Active cluster vs Active/Passive cluster? I have searched a lot and read lots of documents but have not found the answer. In active active cluster can I have 1 ...
2
votes
1answer
424 views

How to include concatenated values in a dynamic pivot column value?

I am extending a dynamic pivot query. The original query works fine. In the second query, instead of displaying the date as a value for the pivot column, I would like to display a concatenated column. ...
0
votes
0answers
31 views

SQL Server accepting Access queries?

I am currently working on migrating a legacy codebase from VB6 and Access to VB.NET and SQL Server. I have run the Microsoft SQL Server Migration Assistant successfully and populated a SQL server ...
0
votes
0answers
8 views

Execution order of triggers for different tables in SQL Server 2008

There is a lot of material that covers the case where there are multiple triggers for one table but what about the case where there are two related tables, each with a trigger, e.g. an update triggers ...
0
votes
1answer
38 views

Is there anyway to tell when the last query was made against a database? [duplicate]

Is there anyway to tell when the last query was made against a SQL 2008 R2 database without downloading a third-party tool ?
0
votes
0answers
19 views

Difference between Database Securable Scope and Database Securable?

I'm reading the Security best practices document for Server 2008 and I noticed that they list both a database securable scope, and a database securable. How should I differentiate between these? ...
12
votes
3answers
15k views

Performance difference between Clustered and Non Clustered Index

I was reading Clustered and Non Clustered Indexes. Clustered Index - It contains Data Pages. That means the complete row information will be present in the Clustered Index Column. Non Clustered ...
0
votes
1answer
133 views

same query different servers,different execution plan and time

I have this query and it runs less than 2 minutes on Test but on Prod it takes 10 minutes. Both are the same version,CPU,RAM,OS the same. I have four tempdb files on Prod but one one Test. I ...
4
votes
1answer
608 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 ...
0
votes
1answer
36 views

Alter multiple Logins through SQL

I am trying to change the following properties for SQL users to disable password complexity and expiration. Using: ALTER LOGIN xx WITH CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF GO Where 'xx' is an ...
18
votes
6answers
63k views

How do you kick users out of a SQL Server 2008 database?

We need to do a restore, and cannot because other users are connected. We thought we had disconnected every process, but apparently not. How can we, from Management Studio, kick off everyone else so ...
1
vote
1answer
107 views

how to get History of queries executed with username in SQL

I can get executed queries by using: SELECT deqs.last_execution_time AS [Time] ,dest.TEXT AS [Query] FROM sys.dm_exec_query_stats AS deqs CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest ...
7
votes
2answers
2k views

nvarchar(max) converting to varchar and table optimization

I am working with a table that has all character types set to nvarchar some of them are nvarchar(max). We are converting all these to varchar and specifying a character width based upon the actual ...