This tag is for SQL Server 2008, major build version 10.00.xxxx.

learn more… | top users | synonyms (2)

1
vote
2answers
285 views

SQL 2008 Server - performance loss possibly connected with a very large table

We have SQL Server 2008 with 3 active databases running on it. DB1 - cca 400 MB size DB2 - cca 8 GB size DB3 - cca 42 GB size - but the majority of records are not used at all In DB2 we have a ...
1
vote
2answers
55 views

SQL Server : Tracking a slow running query on completion

Am trying to work on script for slow-running queries. I understand that we can get the list of slow running queries using sys.dm_exec_requests. Say, I have a query Q1 which is running very slow, say, ...
2
votes
4answers
5k 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 ...
3
votes
2answers
196 views

Only show results greater than a certain number when summing two values together

I have a query that sums up two values in the same table and then calculates one as a percentage of the other select account_no, 100*((sum(COMMISSION/100)) / (SUM(AMOUNT/100))) from accounts etc ...
-3
votes
0answers
14 views

sql query for interchanging rows and columns [on hold]

My data looks like: orderA file1 file2 file3 file4 file5 orderb file7 file8 file9 file10 file11 I want output as like OrderA file1 file2 file3 ...
1
vote
3answers
185 views

How to get the first or last row in ordered result set, depending by column value?

I have the follwoing result set from the table PUNCHREPORT EMPLID Name PUNCHDATE PUNCHTIME DEVICETYPE .................................................................. 70 ...
0
votes
0answers
11 views

Combine multiple same rows into one row by using Id

This is my table: ID Contact Id Ad-type Ad-Title Images_Path 1 2 Houses For Sale lands/im1.jpg 2 2 Houses For Sale lands/im2.jpg I need output like this: ...
6
votes
1answer
7k views

Add DEFAULT to existing column

In Microsoft SQL server, how can I add/change the default on an existing table column using T-SQL?
2
votes
3answers
1k views

Twice job results in sql server, last one is left in progress

I have the job MP - Create CSV which execute a package SSIS in sql server 2008, when I check for the job history I see the result at step 0, I expand and I have 2 results for the step 1. Fist result ...
2
votes
1answer
20 views

Hosting SQL Server and SSIS/SSRS in same VM

I have SSIS / SSRS and large number of databases currently being hosted on the same prod server (SQL1, SQL Server 2008R2). Since SQL1 already has SSRS/SSIS installed, would it be a good option to ...
1
vote
2answers
175 views

Why does FETCH NEXT return second row after opening cursor?

I have a cursor declaration: DECLARE idCursor CURSOR FOR SELECT DISTINCT [id], [data] FROM #tempRemaining OPEN idCursor And the statement: SELECT DISTINCT [id], [data] ...
9
votes
3answers
42k views

How to handle TimeZone properly in SQL Server?

My local development server is in the Middle East, but my production server is in the UK. I need to show the date to the user in their time zone. For example, if a user is in Saudi Arabia then I ...
10
votes
5answers
17k views

How to detect any changes to a database (DDL and DML)

There are a lot of databases on my client's sql server. In common, these databases is under development, so developers can design, refactor, do data modifications and so on. There are some databases ...
0
votes
0answers
21 views

Linq Query Optimization [on hold]

Urgent,I am really in trouble. can anybody help me? my query is slow: I have created a view in sql server containing nested loops like this: Tis is my view: SELECT dbo.VE.V, dbo.VE.Operation, ...
6
votes
4answers
2k 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. ...
0
votes
1answer
177 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 ...
12
votes
1answer
426 views

What is the cause of this problem with CONVERT()?

Consider the following two statements: PRINT CONVERT(NUMERIC(38, 0), 0x0100000001, 0); PRINT CONVERT(NUMERIC(38, 0), 0x0100010001, 0); Both statements return -1; isn't that incorrect since the ...
21
votes
4answers
84k views

Restore SQL Server 2012 backup to a SQL Server 2008 database?

Is there a way to restore a SQL Server 2012 database backup to a SQL Server 2008? I tried to attach file, it does not work.
4
votes
1answer
13k views

How can I get my linked server working using Windows authentication?

I'm trying to get a linked server to ServerA created on another server, ServerB using "Be made using the login's current security context" in a domain environment. I read that I'd need to have SPNs ...
0
votes
1answer
12 views

Do we have logging feature for SQL AUDIT like we have it for transaction and error logs in SQL server 2008?

Do we have logging feature for SQL AUDIT like we have it for transaction and error logs in SQL server 2008 ????
4
votes
2answers
17k views

SQL Server 2008: The database [dbName] is not accessible

I got this error from another person and I've run out of ideas. I can access this database without any problems, the database is in MULTI_USER mode, the person receiving the error is a user, etc. The ...
1
vote
1answer
44 views

microsoft sql server schema [closed]

From blog.sqlauthority.com: Protecting ISV database access: in custom ISV (Independent Software Vendor) applications, the database schemas are usually complex, and in the case of applications such ...
12
votes
1answer
848 views

Best way to write SQL Query that checks a column for non-NULL value or NULL

I have a SP with a parameter that has NULL as default value and then I want to do a query like this: SELECT ... FROM ... WHERE a.Blah = @Blah AND (a.VersionId = @VersionId OR (@VersionId IS NULL AND ...
3
votes
1answer
571 views

SQL Server 2012 Publisher SQL Server 2008 Subscriber version issue

So I have a setup where one virtual machine is a SQL Server 2012 publisher and I need to setup merge replication on a SQL Server 2008 subscriber on a different vm. When setting up the subscription ...
0
votes
0answers
20 views

What is the syntax to sp_recompile logon trigger?

I would like to recompile my logon trigger using sp_recompile. But I can't figure out the syntax. E.g. these don't work: sp_recompile '[Tr_Logon]' sp_recompile '[Tr_Logon]' ON ALL SERVER ;)
2
votes
0answers
30 views

Server Logon Trigger fails after deleting many records in “Logon-Table” -SQL Error 2801

We have a server logon trigger defined that logs every login with information like windows/sql user name, client and time in a table. The table doesn't have clustered index, but it does have a ...
2
votes
3answers
61 views

updating the entire column with datetime values based on other values in the row

I have a very large table [server].[dbo].[MyTable] where I need the SQL statement to fill the column 'ToDateTime' (currently full of NULLs) according to the following logic: Only add a value to ...
1
vote
1answer
25 views

How to update/insert/inject nodes into XML

I have an application which stores optional Site information in an XML field. Sample table: CREATE TABLE [dbo].[Sites]( [SiteID] [int] IDENTITY(1,1) NOT NULL, [SiteName] [nvarchar](80) ...
0
votes
2answers
37 views

How to add deleted user in sql server? [duplicate]

I have set up sql server 2008 in windows server 2008 r2 and it works fine. However, I have deleted the my user from logins in the sql server accidentally and now I cannot find a way to log in to the ...
1
vote
0answers
16 views

Why do async_network_io wait types occur?

Last week something strange happened on our database. All of a suddon, the application blocked for our users which were not able to save new entities etc. After looking at the Activity Monitor of the ...
2
votes
1answer
294 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
9 views

Are tables during a merge function updated dynamically?

If for example, I have a column age in both tables. My merge condition for two tables is when table1.age=table2.age. My WHEN MATCHEDinstruction is to increment table1.age by 1. So if a row with age=5 ...
-3
votes
0answers
22 views

what is other way of use Limit function in MSSQL server [migrated]

How to use LIMIT function in SQL Server. I need to create a pagination using SQL Server data and I need to fetch the data using $start and $pagesize. This is the query used: SELECT * FROM ...
4
votes
1answer
675 views

SQL Server Troubleshooting Replication and Long-Running fn_dblog query

I am trying to get (pull) Transactional Replication working again for a subscriber. Currently, in Replication Monitor I am seeing messages in the Publisher to Distributor tab and the Distributor to ...
22
votes
4answers
52k views

How do I list or search all the column names in my database?

I want to search for a string in the names of the columns present in a database. I’m working on a maintenance project and some of the databases I deal with have more than 150 tables, so I'm looking ...
3
votes
2answers
418 views

Migration to databases Domain Users do not have access

I migrated databases to new servers, however the applications that were previously used with the databases are failing to load. I have changed the connections and etc. The jobs also seem to be ...
1
vote
1answer
33 views

Database compatibility level [duplicate]

We are on SQL Server 2008 R2 and have DBs compatibility level set to SQL 2000 or 2005. What are differences in compatibility level across the versions ? Are there any advantages changing it to 2008 ...
0
votes
2answers
184 views

Error with making backup in sql Server

When I try to backup a SQL Server 2008 database, I have an error. Is anybody know what is the error? TITLE: Microsoft SQL Server Management Studio ------------------------------ Backup failed for ...
3
votes
2answers
651 views

dbcc shrinkfile not reducing size of log file in sql server 2008

I am shrinking log file of SQL Server 2008 R2 database, but it is not working. I was using this code: ALTER DATABASE [database] SET RECOVERY SIMPLE DBCC SHRINKFILE(DATABASE_LOG, 20) ALTER DATABASE ...
2
votes
1answer
30 views

Select columns dynamically from sql server table

Lets say I have a table that actually store record which looks like Create Table dbo.Info ( SN int primary key identity(1,1), FirstName nvarchar(50) not null, LastName nvarchar(50) not null, ...
1
vote
2answers
442 views

After Restore Log_reuse_wait_desc of Replication

I'm wondering if anybody has ran into this issue before and has any suggestions on what's causing it/how to fix it. We are restoring a database into lower Dev environments and after the restore the ...
2
votes
2answers
47 views

JOINS - optimize cost of LIKE/OR criteria

The Active table has the Account Name in 1 column and the Master table has the Account Name in any one of 6 separate address columns and I'm trying to match them. Also, they don't necessarily exactly ...
2
votes
2answers
605 views

parallelism repartitions, ordering, and hash matches

I am just starting to delve into query plans and recently learned that "hash matches" are bad. I know it's rare for something to be so absolute, though, and I want to determine if I have a bad hash ...
2
votes
3answers
3k views

Pros and Cons of SQL Server Database Mail

I'm planning to use SQL Server Database Mail. I'll have a table where mail receiver details and mail content is stored and I want to schedule this mailer (every minute) to send mails to the new ...
8
votes
2answers
3k views

Turning off auto shrink on all SQL Server databases. Why doesn't this work?

I figured I could use sp_MSforeachdb to solve this problem, but I get an error message. sp_MSforeachdb ' BEGIN USE [?] DECLARE @dbid INT SET @dbid = DB_ID() IF(@dbid > 4) BEGIN --PRINT ''[?]'' ...
1
vote
1answer
20 views

Are there security vunerabilites/implications with SQL Mail, currently using SQL Server 2008 R2

I am an accidental DBA and responsible for series of data load jobs. I would like to use SQL Mail for altering me to job failures. SQL Mail is not currently turned on are there security concerns in ...
1
vote
2answers
59 views

Restore SQL Server 2008 database onto SQL Server 2014?

I have a backup file that I need to restore daily on schedule based. But the backup file is from SQL Server 2008 and restore destination is SQL Server 2014. And also the backup file is from cross ...
18
votes
8answers
6k views

Why should an application not use the sa account

My first question ever, please be gentle. I understand that the sa account enables complete control over a SQL Server and all the databases, users, permissions etc. I have an absolute belief that ...
1
vote
0answers
42 views

Database fragmentation and heap SQL Server

We have critical performance issues with our production database (its around 300 gb size) We have run the following script for a few huge tables : DECLARE @db_id SMALLINT; DECLARE @object_id INT; ...
0
votes
2answers
269 views

Auditing the View Server State permissions on Server [closed]

We noticed that one of our servers has permissions granted to "all" users for "view server state". I am wondering if there is a way to audit the DMV's used by the user(s) on a regular basis as this ...