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

learn more… | top users | synonyms (2)

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
28 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 * ...
1
vote
1answer
65 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 ...
0
votes
1answer
67 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
2k 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
21 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
130 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 ...
0
votes
0answers
14 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
40 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
23 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? ...
13
votes
2answers
16k 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
136 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
652 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
42 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
67k 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
195 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
3k 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 ...
0
votes
1answer
255 views

LDF files vs. TRN files vs. Tempdb.MDF+LDF files.:

We have a dilemma… We have all of our MDF and LDF files in the same…\192.168.6.22\d$\Program Files\Microsoft SQL Server\DATA\Production...folder. We have all of our TRN and BAK files on the…\192.168.6....
5
votes
2answers
5k views

'CONCAT' is not a recognized built-in function name

A client reported that they were running on SQL Server 2012, and we delivered some test queries for testing prior to a final delivery, however: 'CONCAT' is not a recognized built-in function name. ...
0
votes
2answers
115 views

How to optimize this SQL Server query?

Anyone have any suggestions for how to optimize this query? Running SQL Server 2008.. The object is to look at accounts that were created yesterday, compare their email addresses and phone numbers ...
8
votes
5answers
3k views

How can I detect broken stored procedures after a schema change?

I've modified a central table in my database, and sp_depends literally returns hundreds of results, and I'm concerned some of those stored-procedures might not compile anymore after my change. ...
6
votes
2answers
12k views

Unable to delete SQL Server 2008 R2 login

I had created a SQL Server login some days back. Now I need to delete the same. So I went into SQL Server Management Studio => Security => Logins and selected the login that I want to delete. It ...
2
votes
1answer
39 views

Simple logging of table access

In SQL Server 2008, is there some simple logging of how often a particular table is selected (in an hour, a day, a week, etc.)? I have a large table that is accessed by dozens of applications and ...
4
votes
1answer
60 views

How to get all connected values as a single column

I'm trying to use CTE recursion. This is my scenario SQL Fiddle. It is a simple tree structure: CREATE TABLE [dbo].[AL]( [IdAL] [int] IDENTITY(1,1) NOT NULL, [ID1] [int] NOT NULL, [ID2] [...
0
votes
1answer
338 views

Milliseconds to datetime

I have this statement EXEC [spProject_Update] @ProjectID = 496 ,@Name = 'Hangman Bollywood iPhone' ,@Description = '' ,@EstimateTime = 2674800000 I will get milliseconds in @EstimateTime. How can I ...
9
votes
3answers
12k views

Can I export a maintenance plan without using Integration Services?

I'm trying to export a simple maintenance plan from an SQL Server instance. I want to check the export into source control, and apply the exported maintenance plan to functionally identical instances....
1
vote
2answers
53 views

Is there a way to have multiple error messages displayed with RAISERROR conditionally?

I am thinking of something like the CASE statement for RAISERROR, but I want the remaining code to be checked too, and not skip anything after encountering the first RAISERROR. Right now I have ...
2
votes
2answers
7k views

Using OPENQUERY to execute a script

I have a SQL Server 2008 instance which I want to use to import data from an Oracle server. I have set up a linked server that works correctly when running simple queries like SELECT * FROM table. ...
6
votes
3answers
2k views

Remove field from composite key and collate duplicate data

I'm looking for the best way to go about changing the primary key on a table in my DB without having to completely reinsert data from files again. The modification I'm going to make is going to ...
0
votes
1answer
38 views

Average of last 4 runs of a product on SQL Server 2008

I'm looking to do the exact same moving average from this post: Average of last 4 runs of a product but now on SQL Server 2008, which doesn't support the extended window aggregate functions. Is ...
6
votes
2answers
4k views

Which datatype to store XML data in: VARCHAR(MAX) or XML

I am defining a schema for a new set of resources using SQL Server 2008... In this case, each record (e.g. row) will need to store XML fragments. From time-to-time; although not frequently; I'll need ...
1
vote
1answer
2k views

Convert spatial points data to line data for use in Line Layer in SQL Server Reporting Services (SSRS)

I have a query that returns the fields Order, Lat, Long, SpatialData, <OtherStuff>. SpatialData is a point. I want to create a line layer that draws lines between my points based on the order ...
7
votes
2answers
1k views

Window functions cause awful execution plan when called from a view with external parametrized 'where' clause

I had this issue long time ago, I found a workaround which suited me and forgot about it. But now there's that question on SO so I'm willing to bring this problem up. There's a view that joins few ...
1
vote
1answer
120 views

Can we create multiple users in one login in SQL Server 2008?

I have one database where I want 4 users in that particular database, so do I have to create 4 logins, one for each user ? I want multiple users from one login. I tried to create a second user by ...
1
vote
1answer
33 views

How do i insert a value into table2 from a trigger on table1

i have 2 tables with a column name WHO, and im using these 2 tables to create a view. where FSwho.who = FStasks.who if i enter a new record into FStasks where FStasks.who = LIZ, but theres no record ...
0
votes
2answers
58 views

Can I see Table I/O per month?

I'm using SQL Server 2008, and will migrate DynamoDB some tables. I want to get I/O statistics of read bytes, and write bytes from table. I don't know how to do this. What command should I use?
0
votes
0answers
12 views

RadPdf Server Installation

I am trying to setup RadPdf on remote SQL Server. I have one static user name and password on that server like usrABC123. RadPdf Service is trying to connect to database with local system account and ...
3
votes
0answers
507 views

Auditing - parameter values not being written to log

I am using auditing to track usage of some procs in the database. In the log I can see a record for the Procedure being run and, immediately afterwards,the SQL statement that is being run. The ...
2
votes
0answers
88 views

How Much Overhead Does Web Sync Add?

I am running merge replication in SQL2012 using web sync. Has anybody done any experiments to compare the performance of web sync to connecting to replication using a straight TCP/IP connection? ...
2
votes
0answers
129 views

Tools for maintaining many dev/test environments on the cloud [closed]

The company I'm currently working with is "moving to the cloud" (isn't everyone these days?) In this case, they are moving to AWS, and they want a simple and effective way of managing multiple (SQL) ...
-1
votes
0answers
2k views

Installing over a SQL Server 2008 Express default instance named “SQLEXPRESS” [closed]

We have a system in which there a SQL Server Express 2008 default instance called "SQLExpress". Normally the SQL Server Express default instance is called "MSSQLSERVER". Now we have an application ...
3
votes
1answer
2k views

Event 4104, MSDTC Client 2

I am using Idera SQL Diagnostic Manager to monitor a Windows Server Failover Cluster with two SQL Server 2008 R2 clusters configured to run with one SQL Server cluster on each node. It just started ...
1
vote
1answer
48 views

SQL Server IO per session

Activity Monitor can show real time the overall IO performance in MBytes/s on a server, but cannot show the same per session level. I know about DMVs, and sp_who2 to retrieve session level io ...
6
votes
3answers
12k views

How to map another server through SQL Server Management Studio

I am trying to map another server by giving the command EXEC xp_cmdshell 'NET USE H:\\568.256.8.358\backup_147 1234abc /USER:cranew /PERSISTENT:yes' I got an error with this: network path ...
0
votes
0answers
13 views

Connecting SQL Server using Server IP Address

This works when I try it internally (Within the LAN network) sConnString = "PROVIDER=SQLOLEDB;" & _ "Data Source=192.168.1.200,1433;" & _ "Initial Catalog=MyDB;" &...
0
votes
0answers
24 views

Advice on query optmization - Multiple Unions

I'm trying to understand how this might be better optimized. I currently have these three different queries to union the results of each. The first case finds all records with the where condition of ...
2
votes
2answers
293 views

Convert hexadecimal to varchar

I have a column with values saved as hexadecimal. When I run this query: select column from table where column like '%22639935-KCN%'; This is the output I get: ...
4
votes
1answer
93 views

Finding changed column values

Table 1 ID |COL2|COL3|COL4|COL5| ROW1| 1 | 1 | 1 | 1 | ROW2| 1 | 3 | 2 | 6 | ROW3| 1 | 1 | 1 | 1 | ROW4| 1 | 1 | 1 | 1 | ROW5| 1 | 1 | 1 | 1 | ROW6| 1 | 1 | 1 | 1 | Problem ...
2
votes
1answer
56 views

Query Each Database in A Table [closed]

We have it set so that any database that shows an error code during the server scan is set to write the database name into a table. What I want to do is then have an email generated that will send the ...
-1
votes
1answer
48 views

SQL command or trigger needed to capture changes in tables [duplicate]

I want a query or trigger that will identify what changes done in the database tables from the Third Party Software such CMS etc. I want to check that which data is modifying with time. There is no ...