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

learn more… | top users | synonyms (1)

1
vote
1answer
254 views

Found errors in table after executing DBCC CHECKTABLE command

I am trying to alter table to add primary key ALTER TABLE GeoCity ADD CONSTRAINT PK_GeoCity PRIMARY KEY(locId) and got this error message Msg 1750, Level 16, State 0, Line 1 then I run ...
0
votes
1answer
28 views

SQL problem with checking next and previous rows

I have a list and the returned table looks like this. I took the preview of only one car but there are many more. What I need to do now is check that the current KM value is larger then the ...
12
votes
2answers
2k views

Why does SELECT 1/2 return 0?

As the title says. I even tried SELECT CONVERT(NUMERIC, 1/2) which also returned 0. I am using SQL Server 2008.
0
votes
1answer
37 views

How can I choose the best autogrowth option for my new database? [duplicate]

I want to create a new database that I can use for testing, restoring it from a full backup from a production database. These backup file sizes are around 45,486KB, and the production database size is ...
4
votes
2answers
427 views

How do I query only for committed data while avoid a deadlock

I have a system writing batches of queries as transactions (to maintain coherency), these can be sets of 30-100 queries per transaction. These are done on multiple tables At the same time there are ...
1
vote
1answer
521 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 ...
0
votes
1answer
9 views

Building test site, update dates (back date records)

I have 3 tables that all connect and I need to back date them in an MS SQL 2008 setting. I was thinking about how to do this and not sure if I am on the correct path. The big problem I have is I have ...
1
vote
1answer
69 views

Delete files from disk, with a job, respecting some rules ( SQL Server 2008 )

We have An application that our customers use to download and upload files. I need to delete these files from disk after 30 days, of if it was downloded, in 7 days. How can i Use the script below, ...
0
votes
1answer
37 views

SQL update next row in select

I have ran into a problem with SQL. I have a list with car and mileage data. What I need to do is for the empty fields to calculate data. Each row has a field for KM (mileage in KM metric unit), ...
7
votes
3answers
33k 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 ...
3
votes
4answers
16k views

How to drop multiple tables with common prefix in one query?

I'm using Microsoft SQL Server 2008.My question is:How to drop multiple tables with common prefix in one query? something like that table names: LG_001_01_STLINE, LG_001_02_STFICHE
0
votes
1answer
36 views

Merging two Select queries with different where statements

I have this query below in which I am trying to get results for the month and year to date (account balance). SELECT T3.AcctCode, T3.AcctName, SUM(T1.Debit - T1.Credit)* -1 AS 'Balance' FROM OJDT T0 ...
3
votes
3answers
4k views

bcp in of datetime type data results in an “Invalid date / time format”

I am familiarizing myself with the bcp utility, and I have a large table containing datetime data awhich I bcp out in several ways: native, character, delimited. I then bcp in to a truncated table ...
0
votes
1answer
62 views

Automate SQL Server restore from Prod to Dev

I would like to setup an automated restore job which takes the latest timestamp .bak file and restores it into the DEV instance. I currently have a job on the DEV box that uses the UNC path to the ...
1
vote
1answer
21 views

Update incoming rows based on % calculation

Below is the business requirement which I have to achieve... As a Sales Data Operations process/product owner, I want to be able to identify share class conversions (aka "wash trades") coded as ...
0
votes
1answer
34 views

copy table with all constraint

how I would copy a table with all constraints? I run select * into myNewTable from baseTable where 1=0 This copy a table but no with constraints
1
vote
2answers
338 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
1answer
93 views

Microsoft SQL Server 2008 encryption over multiple DB versions

I have about 5 clients deployed running different versions of SQL Server 2008 (Some standard, some enterprise, etc.) And I am being tasked with encrypting the data so: The software we are using can ...
6
votes
1answer
4k views

How to clone a user in SQL Server 2008 R2?

Is there a way to clone users' security and permissions in Microsoft SQL Server, preferably using the SQL Server Management Studio GUI?
3
votes
2answers
381 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
votes
2answers
44 views

How to run multiple select statements in Data flow task in SSIS [closed]

I am migrating data from one database to another database using SSIS package and i need to write multiple select statements for same table depending on conditions as mentioned below. Actual Queries i ...
0
votes
1answer
31 views

Dynamically change a db in transact sql

I am trying to run a script against a database that is dropped from a third party daily so the name of the database changes daily. I can get the name of the new database using the sys.databases ...
2
votes
2answers
709 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 ...
0
votes
0answers
19 views

Procedure or function 'sp_InsertImages' expects parameter '@Img_path', which was not supplied [migrated]

i have passed img_path in my code nd procedure but it show me error please anyone help me. my procedure is : create proc sp_InsertImages ( @Img_path nvarchar(max), @product_id numeric(18,0), ...
3
votes
2answers
13k 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 ...
5
votes
7answers
3k views

Stored Procedures under Source Control, best practice

I am currently using Tortoise SVN to source control a .NET Web Application. What would be the best way to bring our SQL Server stored procedures into Source Control? I am currently using VS 2010 as my ...
-2
votes
0answers
12 views

How to Generate Replication Reports from SQL Server

I have number of databases being replicated over night. I need to know if there is a way to generate report or Query their Start Time and End Time for each one. Secondly, is there a way to get / Query ...
1
vote
1answer
238 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 ...
0
votes
1answer
55 views

Subtract next row's datetime with current row's datetime based on condition

It might sound complex - I think it is. I have an output like this: | EmployeeID| DateAndTime | Type | AbsenceID | DateEntered | LastModifiedDate | minutes | DN | | ...
1
vote
0answers
1k views

SQL Server TDE Stuck at Encryption_State 2 and 0% on a Tiny DB

I'm having a heck of a time with this issue and can't figure out what is wrong. I'm not sure how long this tiny DB has been in 'encryption_state = 2' from query: SELECT * FROM ...
8
votes
4answers
6k 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 ...
0
votes
0answers
36 views

lock request time out period exceeded. When revoking db_owner access

I am trying to revoke db_owner access of an user and I am getting this below error! I have no active transactions User (whom I want to revoke access) is not connected to any db DataBase owner is ...
2
votes
2answers
94 views

Cannot generate SSPI context ( SQL Server 2008 ) ( RESOLVED )

When someone tries to connect to a SQL Server instance, the error shows up: It's not possible do generate SSPI context. Yesterday we had a blackout (don't know how to say this expression in ...
0
votes
1answer
958 views

SQL Server to MySQL sync, using Workbench, FreeTDS, and iODBC on 10.9

I've been struggling to get the MySQL Workbench to behave properly to complete the following: With Mavericks (OS X 10.9), connect to a SQL Server and create a migration job to a MySQL server. The ...
2
votes
0answers
45 views

Case Statements, subqueries, and datediff

First time posting on this website so please let me know if you require any other information. This first section of code worked perfectly and resulted in correct information. [Field 1] = (CASE ...
1
vote
3answers
50 views

Script to drop all databases except one not working

I'm trying to drop all databases but one on my test server. For this, I'm using the below script but it gives error: EXEC sp_MSforeachdb ' USE [?] IF (DB_ID(''?'') > 4 AND DB_NAME()!=''ABC'') ...
0
votes
1answer
34 views

Prevent record from being inserted in the table in After Trigger

I have a scenario where I have to insert a record in TableA only if the RecordId exists in TableB. I have written an Instead of Trigger on TableA for Insert, in which I am checking if the RecordId ...
0
votes
1answer
33 views

Best practice to recover an CRUD statement to a linked server if connection is lost

I am looking for the best practice for the following scenario. We have a CRM in our company. When an employee updates the record of a company, there is trigger that fires a stored procedure which has ...
0
votes
1answer
15 views

What can cause an Index “total writer” to be the same in every index?

I have a table, with some Indexes ( created by me, others by SQL Server Profiler (SqlServer 2008)). I use this query to analyze unused Indexes: --Possible bad Indexes (writes > reads) SELECT ...
1
vote
0answers
44 views

Inserts/updates are extremely slow with Merge Replication in SQL Server 2008

I got hired into a new company and they had merge replication already in place. They have had issues with it to the point I have to evaluate whether to keep it or replace it. The main issue is it ...
11
votes
4answers
10k views

SQL Server Max Memory not limiting use of RAM

I would like your input on this. I have a sql server 2008r2 Ent. Ed. 64bit with 16 cores and 64GB RAM. There is one instance of SQL server patched fully as of 20111014. The max ram is set to 60000MB. ...
6
votes
3answers
1k views

How recording of every change of a row in a database is generaly stored?

In a project I'm working on, every change to the rows in some tables of the database must be tracked for further audit or rollback. It must be easy to find who modified the row, from which IP address ...
2
votes
3answers
3k views

Transaction log has grown huge after maintenance plan to rebuild idex

A couple of days ago I was testing some stuff using the Maintenance Plans in SQL Server 2008. I created one to rebuild indexes and take a full backup every week. (I know, bad thing I've found out ...
0
votes
2answers
41 views

Is it possible to connect to Oracle 9i data source in SSIS?

We're doing a migration from Oracle 9i to SQL Server 2008. So I'm thinking of a way to move the data from Oracle and I thought about SSIS. I'm new to this Microsoft's technology and I would like to ...
1
vote
1answer
1k views

Is there a way to truncate table that has foreign a key?

Is there a way to truncate table that has foreign keys ? Delete and reseed can take too long. Is deleting and recreating keys only way ? If so is there a tool that does this ?
0
votes
1answer
60 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 ...
1
vote
1answer
38 views

How to distinguish between system and user defined roles in a single database?

Is there a way to distinguish between a system role/user and a user defined role/user? As system role/user I understand a role/user which is installed/assigned implicitly at the time the database has ...
1
vote
1answer
24 views

What User Level is needed for Transactional Replication

I've been setting up Transactional Replication between two database servers I have running. My first experiment with setting it up I simply used SA and local Admin account for all of the agents. ...
11
votes
4answers
2k views

Make SqlClient default to ARITHABORT ON

First things first: I'm using MS SQL Server 2008 with a database at compatibility level 80, and connecting to it with .Net's System.Data.SqlClient.SqlConnection. For performance reasons I've created ...
0
votes
0answers
30 views

Trying insert into a temp table only if the id does not match the id from another table I am inserting from

I am very new to this... I am trying to insert certain values from a table into a temp table. Table applNoteText_dealerCmt ( my temp table) Table ApplNotes This is what I have so far USE ...