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

learn more… | top users | synonyms (1)

19
votes
8answers
7k views

How do long columns impact performance and disk usage?

In our current project it just happens too often, that we need to extend columns by a couple of characters. From varchar(20) to varchar(30) and so on. In reality, how much does it really matter? How ...
0
votes
2answers
79 views

Calculate Time difference between a datetime format with the current datetime

Hi I would like to calculate the difference between a past datetime with the current datetime with the result as a [hh]:mm format.
1
vote
1answer
50 views

SQLDriverConnect fails when attempting to connect to Failover_Partner

SQLDriverConnect fails when attempting to connect to Failover_Partner. Connection string being used: Driver={SQL Native Client};Failover_Partner=tcp:dby;Server=tcp:dbx; ...
0
votes
2answers
67 views

Check Constraint with Case Statement

I want to add a check constraint on a column if entered value for column is greater than 3 than it should be saved as 1 else entered value. For that scenario I am using below query but on insertion of ...
0
votes
1answer
50 views

deleting a table referenced by many foreign keys

I have a table that I want to drop and create again, because I want to add the IDENTITY property to a column, wich is not possible directly. The problem is that I can't drop it, because my column is ...
0
votes
0answers
29 views

Update existing (old) rows vs delete and insert when needed

I'm building a database system that needs to store login tokens for a system that validates if a users login token is valid or outdated. The table is pretty simple UserID int Primary Key LoginToken ...
4
votes
4answers
10k views

T SQL Table Valued Function to Split a Column on commas

I wrote a Table Valued Function in Microsoft SQL Server 2008 to take a comma delimited column in a database to spit out separate rows for each value. Ex: "one,two,three,four" would return a new table ...
0
votes
0answers
59 views

Will SQL Server 2014 not cause problems with a SQL Server 2008 DB?

I will be doing development work on a database created in SQL Server 2005 that will be moved to SQL Server 2008. I have no problems moving them; I've done it before and it's always been painless ...
1
vote
1answer
102 views

Best approach for populating date dimension table

I am looking to populate a date dimension table in a SQL Server 2008 database. The fields in the table are as follows: [DateId] INT IDENTITY(1,1) PRIMARY KEY [DateTime] ...
0
votes
0answers
24 views

SQL Server 2008 ENT + add SSL to Named Instance (not default)

Need to know if anyone has seen or knows how to load an SSL Cert for a named instance in SQL Server 2008 or above? I have loaded many on the default instance but not on a Named Instance. Go through ...
0
votes
1answer
30 views

Count Function with Aggregate function inside the query

Hi my actual code is below: Select M.TicketID,M.CreatedMoment, Max(L.StatusChangeMoment)AS StatusChangeTime, Elapsed_time_in_Hours_Minutes = CONVERT(NUMERIC(18,2),DATEDIFF(minute, M.createdmoment, ...
2
votes
3answers
5k views

SQL Server 2008 Database Restore Error - Cannot open backup device 15105

I am trying to restore from a backup and getting the following error Msg 3201, Level 16, State 2, Line 1 Cannot open backup device 'Z:\FULL_BACKUP.bak'. Operating system error 3(failed to ...
1
vote
2answers
156 views

Improving a search query

I have a query for search function. Basically search function allowed user to define what they "have" and "want". Then this query will filter out all the possible result which created by other user. ...
0
votes
2answers
300 views

Log shipping - switchover and failback

We are doing a planned failover and switch back from the primary to DR with mirroring. We do not have a separate monitor instance. Please help me with the following 1. Is it possible to switch back ...
0
votes
2answers
67 views

How to provision the SQL Server Agent service after SQL Server Edition upgrade

I have upgraded SQL Server 2008 R2 Express to SQL Server Standard Edition. After I have upgraded it, the SQL Server Agent was stopped. I have tried to start but it fails. Only when I have changed ...
1
vote
2answers
339 views

Need advice on abnormal login auditing

Recently i am facing some slow issue for my web application. And i have check my visitor report and it shows normal traffic rate as usual. And then i check my db login auditing and find below login ...
1
vote
3answers
46 views

Can set unique constraint in table have data not unique SQL Server? [duplicate]

I have a table with has many rows, I want to set unique constraint for this table, but the column that must be unique, has data is not unique. Is any way to set unique constraint for this table for ...
1
vote
4answers
1k views

SQL Agent still attempting replication for deleted publication

We recently retired a replication publication, deleting each subscription (push subscriptions) and then deleting the publication, all from within SSMS. However, we continue to get the following ...
3
votes
1answer
337 views

SQL Server: deadlocked on lock communication buffer resources

what could be possible reason for this deadlock type? (not deadlock in general) lock communication buffer resources is this indicated system is low in memory and buffers count ran out of limit? ...
1
vote
1answer
49 views

Cursors and concurrent users

I typically avoid cursor operations like the plaque, however, I have come across my first problem where using a cursor outperforms a query. So I am forced to use it. I have created an elaborate ...
0
votes
1answer
62 views

syspolicy_purge_history job failed at step 3

In one of my sql 2008 sp1 standalone instance, 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 2008 Setup Error 0x80070490

I am trying to install SQL Server 2008 x64 on Windows 2008 R2 and keep getting the following error: SQL Server Setup has encountered the following error: Element not found. (Exception from ...
2
votes
3answers
295 views

How to pull rows only from the weekends?

My table data is like this: ID Name salary date 1 a 100 1/08/2014 2 b 200 2/08/2014 . . . . 26 z 2600 26/8/2014 Now I want to return only rows from Saturday or ...
2
votes
2answers
158 views

Table with 4 million records - different size and performance in development and production

At my company we use SQL Express 2008 R2, which supports databases up to 10GB. We have 2 databases with almost the same number of rows in all tables. But, one of them is 4.5 GB in size (which is in ...
4
votes
1answer
714 views

Why can't I read my transaction log backup file using fn_dump_dblog?

I'm using this blog on sqlskills.com as a guide to practice finding the relevant LSN in a transaction log backup for point in time recovery. After backing up the transaction log, I attempt to read it ...
1
vote
1answer
197 views

SQL Server replication conflicts after migration from 2000 to 2008

I got a suggestion over at Stackoverflow to post here....greatful for any and all help. Please bear with me I think this might take a while to explain. For many years now my company has hosted a ...
3
votes
1answer
602 views

Changes the default database owner in SQL Server 2008

How can I change the default database owner when creating a new database in SQL Server 2008.
2
votes
2answers
253 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
233 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 ...
2
votes
5answers
4k views

Small sample database for practicing database administration?

I'm looking for a portable sample database that can be created from a script has a simple ER model has little data for database administration practicing purposes. I know that there is e.g. ...
0
votes
2answers
98 views

How to verify data dumps between two MS SQL and MySQL

I'm trying to verify data exported from MSSQL and imported into MySQL is 100% accurate using MD5 hash. On Linux, I'm using md5sum and WinMD5 on Windows. The encoding is the same: UTF8. I tried both ...
0
votes
2answers
48 views

Is there a way to do a textual search through all stored procedures in a table?

I've just inherited a database with a number of tables and stored procedures. I use Microsoft SQL Server Management Studio. One, or possibly more, stored procedures update a specific table. If the ...
1
vote
1answer
94 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 ...
0
votes
1answer
168 views

Pentaho 4.4 Database connectivity issue

I am getting the following in one of my transformations. I am trying to extract data from SQL server and pushing to mongodb. 2014/04/03 13:11:10 - EMPLOYEE TABLE Input.0 - linenr 700000 2014/04/03 ...
1
vote
1answer
6k views

How do I remove a SQL Server 2008 instance from command prompt w/o removing the server?

I want to remove a SQL Server 2008 instance (just the instance, not the server). I know how to do it in SQL Server 2005, its "C:\Program Files\Microsoft SQL Server\90\SetupBootstrap\setup.exe" /QUIET ...
0
votes
1answer
67 views

Best way set clustered primary key on a partitioned table?

I have the following table. create table T (K1 date, K2 int, K3 varchar(10), C1 int, C2....) The table will be partitioned by K1 (K1 has low selective. the data will be appended in the order by ...
1
vote
1answer
94 views

Pre-formated varchar variable does no work in select query

I am trying to run a select query that receives in the where clause the @Sites variable. The query at the moment is returning empty because the following line AND S.[Name] IN (@Sites) is not being ...
4
votes
1answer
96 views

Why is SQL Server's online indexing causing blocking?

My understanding is that online indexing acquires locks at the beginning (Preparation) and end (Final) phases. However, we see a lot of DML queries blocked during the Build phase. A sampling of the ...
2
votes
2answers
604 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 ...
1
vote
1answer
41 views

Group by alternative [closed]

I have a table , that has a generic set of actions for EVERY user. However if there is a user that needs to have a unique action, that replaces an existing action, then in my query, I want to ...
0
votes
1answer
48 views

Delete from table where not in another table using two columns

I am trying to get my head around this delete statement I am trying to accomplish. I understand how to delete from table where column not in ( subquery to table2) I have two tables with multiple ...
0
votes
1answer
285 views

SQL Server Error - HRESULT E_FAIL has been returned from a call to a COM component

I have a huge 200MB .SQL file. When I try to open this in SQL server management studio 2008, I get the error -Error HRESULT E_FAIL has been returned from a call to a COM component. What could the ...
3
votes
2answers
137 views

What are the best ways to thrash SQL Server?

I have an issue where a software driven replication system will fail to fully replicate when the main DB is under heavy load. For example if we execute a sproc 500 times, maybe 450 will actually ...
0
votes
1answer
183 views

Validate each parent intermediary is also a parent to itself

I want to validate where each parent intermediary is also a parent to itself. So this query should return invalid parents in SELECT if table has any. I need to do is first of all get all parents ...
2
votes
0answers
357 views

Transactional Replication Error adding new subscription (null) article

When attempting to add a new subscription to an existing Transactional publication on 2008. It is all configured on 1 server as it is for Pre-Production testing. I get the following error when ...
3
votes
1answer
100 views

Why isn't my new index/table being created on the filegroup I specify?

Results from @@Version Microsoft SQL Server 2008 (SP3) - 10.0.5500.0 (X64) Sep 21 2011 22:45:45 Copyright (c) 1988-2008 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 ...
2
votes
1answer
941 views

SQL Server 2008: What are the English Word Breakers?

I'm trying to replicate what SQL Server 2008 is doing when generating full-text indexes, but I'm having a hard time finding a list of which characters SQL Server 2008 considers to be word breakers. ...
0
votes
0answers
43 views

Subscription fails with Incorrect syntax near '9'

In my Distributor To Subscriber History I get the following error: Command attempted: 9 (Transaction sequence number: 0x0018E94F0000207A006200000000, Command ID: 59) Error messages: ...
1
vote
0answers
44 views

Update on a small table times out

We use Microsoft SQL Server Express Edition with Advanced Services 2008 R2 SP2 We have two locations. Each location has one sql server that serves about 20 client computers. Client computers are ...
2
votes
1answer
70 views

size of a primary key and foreign key

Do a primary key and a foreign key referencing it must have the same column size in sql server 2008 ? could someone indicate me a link to a documentation explaining this ? Thank you for help.