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

learn more… | top users | synonyms (2)

1
vote
1answer
91 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 ...
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 * ...
7
votes
1answer
148 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 ...
1
vote
1answer
58 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 ...
18
votes
7answers
456 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 ...
2
votes
3answers
43 views

SQL Login currently 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
56 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 ...
3
votes
1answer
93 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 ...
1
vote
1answer
20 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 ...
0
votes
3answers
66 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 ...
0
votes
1answer
42 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
12 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? ...
0
votes
1answer
39 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 ...
1
vote
1answer
142 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 ...
0
votes
2answers
113 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 ...
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 ...
1
vote
2answers
52 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 ...
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 ...
1
vote
1answer
32 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
52 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 ...
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] [...
1
vote
1answer
100 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
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 ...
0
votes
0answers
23 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
241 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
91 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 ...
0
votes
0answers
31 views

Case Statement to Identify Multiple Errors

I'm trying to write a statement that identifies any of 200 potential errors / illogical combinations in a data set for each line item. My first thought was to write a case statement for each error ...
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
46 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 ...
1
vote
1answer
79 views

Issues installing patches on SQLserver 2008

There are multiple issues with a server I manage, we cannot start management studio on it and when we try to install patches to SQL Server the setup never shows up. I run the patch as an ...
1
vote
1answer
64 views

Can I bring a warm standby SQL Server database online and then return to standby mode?

What I'm trying to do falls outside of the normal SQL Server work flow so might be impossible. I'm implementing a system that serves out copies of a large production database to multiple test / dev / ...
0
votes
1answer
33 views

Sql Server Security Scenario

I need some advice/help... I need to implement database security in some Databases but I don't know how to do that with this given scenario... Using LDAP Auth We have Table1 In Table1 one we have ...
0
votes
1answer
53 views

Error from Program Compatibility Assistant when installing SQL Server 2008

I am new to this so please do not mind if this is dumb question. I am receiving an error while installing SQL Server 2008 on Windows 8.1 I have .net 4. I have windows installer 4.5
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
1answer
36 views

A login is a member of db_datareader and public roles only, but it is not read-only

I'm an accidental DBA in a small company that doesn't have a dedicated DBA. I need to set up a read-only login/user for SQL Server 2008 database using SQL Server authentication that could be used by ...
0
votes
1answer
26 views

Automatically replicate table data into another table

Is there a way to automatically replicate the table into another table where in if table A has changes, then it will also change the data in the 2nd table.
0
votes
1answer
52 views

SQL Server Backup - move to new server - Media Failure

I've got a server in the UK with SQL Server 2008. It's very locked down firewall-wise. We want a local duplicate of the DB to test and build a new report. Backing up the relevant DB, and then ...
1
vote
1answer
138 views

How to backup SQL Server database once evaluation period has expired

I'm using Microsoft SQL Server Management Studio from last one month that is evaluation period software. I'm not able to login in management studio. when I enter my user name and password it gives the ...
2
votes
1answer
54 views

SQL - Best way to limit access to small number of tables

I have a login I've created on SQL Server 2008, it's a SQL Server login not using AD. I want to give them access to every table in two databases except the tables that I store login information in for ...
1
vote
1answer
39 views

Select entries based on a combination of columns

I have a table with a definition like this: CREATE TABLE [dbo].[Contact] ( ClientNumber [numeric] (20, 0) NULL, ContactDate [Date] NULL, Name [text] NULL, AdditionalInformation [text] ...
1
vote
1answer
75 views

Foreign key not working

This is a very basic foreign Key issue, I presume. Unfortunately I am not a dba so it is a bit baffling to me. I have two tables, Report and Request. The Report table has a primary key of [RP_ID], ...
1
vote
3answers
40 views

Which one to consider for upgrade SQL Server 2008 vs SQL Server 2014 or Compatibility level 100 vs 110

I need to upgrade the SQL Server 2008 to SQL Server 2014 . SQL Server 2008 is currently running on compatibility level 100. I need to upgrade to SQL Server 2014 with compatibility level 100 this year ...
0
votes
1answer
23 views

drawback of including primary key creation along in the table creation script

Sample script: CREATE TABLE [dbo].[Countries]( [Id] [nvarchar](2) NOT NULL, [Name] [nvarchar](100) NULL CONSTRAINT [PK_dbo.Countries] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = ...
1
vote
1answer
26 views

Retire a SQL instance without impacting the primary server?

I have a SQL Server 2008 instance (TST01) that has some databases on it that are being migrated, still a few weeks of work to finish up, before the server is retired, this is a Virtual Server. There ...
0
votes
1answer
12 views

Unable to backup after recovery model change [duplicate]

Previously I had the recovery model as SIMPLE. I have altered it to FULL. While trying to perform BACKUP LOG, I am getting an error saying BACKUP LOG cannot be performed because there is no ...
0
votes
0answers
18 views

How to find difference in usage date based on results

I have a ticket details table that stores the information of transactions. Here is an example of the table data: Ticket_Number Detail_type_ID Description Date_Created TotalAmount Barcode ...
3
votes
1answer
67 views

VLDB - Create SQL DB Snapshot And Run Granular DBCC CHECKDB Commands

I have a 25TB db (SQL2008 Enterprise SP4) that appears to not have had CHECKDB run on it in production. No idea if it was run against a restored backup somewhere at sometime. Right now I don't have ...