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

learn more… | top users | synonyms (1)

4
votes
3answers
7k views

Users cannot view tables in non-default schema in SSMS

I'm having an issue setting the VIEW DEFINITION permission appropriately at the schema level for one of my users. I've created the schema TestSchema and added some tables. The user currently has ...
0
votes
2answers
60 views

AVG function returns wrong average

I have a simple table with a column of type REAL. There are 2 rows in the table and the values are 0.23 and 0.24. If I run the following: SELECT AVG(MyColumn) FROM dbo.MyTable I would expect to ...
0
votes
1answer
211 views

Single query approach to split and copy string values in a column

This is a part of my previous question. I have the below logic to split a string value from a column in table tblProjects as below: SELECT @Docs = Documents FROM tblProjects WHERE ID = @ID SET ...
2
votes
2answers
511 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
1answer
86 views

How to create a stored procedure with columns from three different tables in SQL Server 2008?

I'm a fresher and I would like to get some suggestions on how to create a stored procedure in which I have to select columns from three different tables. 2 columns customername and customercode from ...
0
votes
1answer
205 views

How can I reseed all identity columns?

I deleted all rows from 20 tables after disabling the constraints, triggers using the stored proc SP_MsForEachTable. Now before the import happens, I need to reseed all the identity columns back to ...
3
votes
1answer
1k views

SQL Server backup devices

I'm trying to find out how to detect if a backup device exists using T-SQL. This is how I create my backup device: EXEC master.dbo.sp_addumpdevice @devtype = N'disk', @logicalname = ...
1
vote
0answers
169 views

sqlcmd returning NUL instead of NULL for tinyint column

sqlcmd -E -d MyDatabase -Q "sp_helpsubscription" -s "," -o file.csv -W -u Column 23 which is suppose to be a tinyint (subscriber_type) is output as NUL instead of NULL when the value is NULL. I ...
1
vote
0answers
93 views

Multiple login mappings for linked server

Can we assign two logins for two different databases on target server. For example, I would like to create a Linked Server between ServerA and ServerB. ServerB has two databases on it and both using ...
0
votes
0answers
65 views

how to restrict user to run sql server database only on partiular ip only?

How to restrict user to run a SQL server database on in particular IP only? I have one database I just want to give to clients. But I wont Restrict that the database should run only on one ...
8
votes
1answer
40k views

ODBC Data Source SQL Server Connection - Login Failed For User

I have moved my database from an SQL 2005 to a server with SQL 2008. I am now trying to create an ODBC Data Source. I am using "With SQL Server authentication using a login ID and password ...
6
votes
1answer
227 views

Convert clustered primary key to non-clustered and use another column for the clustered index

I have a large table with a primary key (GUID) that is also the clustered index. There is already a integer sequence based field. So I want to leave the GUID as the PK and make the integer column the ...
5
votes
2answers
2k views

Find out what setting were used for a given stored procedure

As a follow-up to my previous question: is there a way to need to find out what the ANSI_NULLS and ANSI_PADDING etc. settings where when a given procedure was created in my database ? It seems my ...
1
vote
2answers
69 views

How do I prevent my clients viewing my database definition when it is installed on their server?

I want to give my database to the client to install on their server. However, I want to deny the client rights to see the tables and stored procedures and also want to restrict that database to run ...
0
votes
1answer
20 views

Run SQL requests generated by some script

I'm using this script to delete data from all the tables in my database. It generates DELETE statements in proper order, each request in one row : DECLARE @schema_name sysname; DECLARE @table_name ...
3
votes
1answer
59 views

Full to Simple recovery mode during massive data update?

I`ve got a 50 Gb database, in which a table takes up 43 GB, and it's full of bad data(over 90%), and i plan to clean it. Database is in Full recovery mode. My plan is: 0 - take a backup for safety ...
1
vote
0answers
60 views

Too big database file 90GB [closed]

I just found that one of database growth to size of 90 GB (one file) The biggest table is about 23 GB , mostly containing integer data. Till now I dont have performance problems, but I think that ...
2
votes
4answers
10k views

Get counts grouped by month and year

I am trying to report on data in e-email messaging system and wanted to have SQL tally counts by month/year. SELECT YEAR(crdate) AS y, MONTH(crdate) AS m, COUNT(*) AS tally FROM ...
0
votes
2answers
35 views

Problems with Insert( simple query - can't think something )

I have 2 tables Tblservicedesk and Tblservicedesk_temp Collumns: CodServiceDesk ID_Solicitacao Solicitante Departamento Tecnico Grupo Status Prioridade ...
-1
votes
1answer
18 views

Run Sp on linked server 2008 [closed]

How could i run my sp on a linked server ? Using sql-server-2008 Exec my_sp [db-srv_name].[db-name].[dbo] Please help me i am new in sql
3
votes
2answers
5k views

SQL Server 2008 user, group, or role already exists in the current database

I am trying to migrate an old, problematic Plesk server to a new one but there are some problems with SQL Server 2008. Plesk couldn't migrate one of the DBs. So, I backed up the DB and restored on ...
1
vote
0answers
186 views

How to use HTTP Connection Manager in Script Component? (Not Script Task)

I'm trying to perform the same scenario as in the following link; Create a SSIS Script Component as a Data Source that uses a pre-existing HTTP Connection Manager to retreive a page with GET and emit ...
1
vote
2answers
116 views

How to split url in sql server like this

Here is the url www.abc.com/a/b/c/d/e/f/g/h/i I want result like this www.abc.com/a/b/c/d/e/f/g/h/i www.abc.com/a/b/c/d/e/f/g/h www.abc.com/a/b/c/d/e/f/g www.abc.com/a/b/c/d/e/f ...
6
votes
1answer
3k views

Database Mirroring with TDE

I have the requirement of mirroring a few databases and also using transparent data encryption (TDE) on them as our data must be encrypted while 'at rest'. I have setup TDE on both the principal and ...
12
votes
3answers
2k views

Implications of using a Unique NonClustered Index with Covering Columns instead of a Primary Key

We have a large table [MyTable] which currently has both a Primary Key, and a Unique Non Clustered Index on the same column ([KeyColumn]). The U NC index also has additional covering columns. Having ...
7
votes
4answers
41k views

Easily show rows that are different between two tables or queries

Imagine you have two different tables/queries that are supposed to have/return identical data. You want to verify this. What's an easy way to show any unmatched rows from each table just like the ...
2
votes
1answer
95 views

T-SQL query to find backup folder for specific maintenance plan

I want to check check if backup plans are set up correctly and need to query different servers, mostly SQL Server 2008, but also 2012. Is there any way to query the maintenance plans for databases, ...
3
votes
1answer
8k views

Restore Backup Fails - File 'Db' cannot be restored to '{…}.mdf'. Use WITH MOVE to identify a valid location for the file

I am getting the following errors trying to do a restore using the restore command on a backup file I recieved from a customer. RESTORE DATABASE SFDB FROM DISK = N'C:\Backup\Backup.bak' Any ...
1
vote
0answers
64 views

Rebuild/Re-organize index and Update statistics in SQL server

For many of the sql servers we have seen that the SQL agent Maintenance job first runs the re-indexing and later as a second step it update stats with full scan. I want to figure out if auto update ...
3
votes
0answers
336 views

How unsafe is it to change the data source of repl_distributor on a publisher

I have a long story that I will condense to a few bullet points that leads up to my question Reporting Server (SQL 2008) is distributor/subscriber cluster for a SQL 2000 Transactional Publication ...
0
votes
1answer
1k views

Conversion failed when converting the nvarchar value '0.000000' to data type int

I have a code select CONVERT(NUMERIC, CASE ISNULL(FLEXFIELD4,'') WHEN '' THEN 0 ELSE FLEXFIELD4 END ) FROM DEIT_INDENT_DTL_INBOUND_RXL WHERE IND_INDENT_NO='NEE15SR000003' which is ...
0
votes
3answers
72 views

Keeping an up to date copy of a SQL Server database on another server [closed]

We outsourced some of our business functions to another company. That company makes use of the same software as we do and we would like to keep a update to date copy of their database on our servers ...
1
vote
1answer
92 views

how to group data against more than one conditions

I have a table named Employees with below two columns. ================== Employees 1- EmpId (auto incremental id) 2- EmpName (varchar) There is another table named 'LateDays' with below five ...
0
votes
1answer
82 views

Insert/Update SQL Server Stored Procedure

I have to insert or update into a table depending on a record. I am using a stored procedure to do this. I am very new to SQL Server and stored procedures. I tried to follow various articles and ...
2
votes
1answer
45 views

How to Create Aggregate function in SQL Server 2008

My requirement is to create concatenate function so I am trying to create Aggregate function as shown CREATE AGGREGATE [dbo].[Concatenate] (@value [NVARCHAR](MAX)) RETURNS[NVARCHAR](MAX) EXTERNAL ...
6
votes
4answers
4k views

Passing in the table into a stored procedure

Is it possible to pass in the name of a table into a stored procedure? For example, suppose you have several views of the same table. They all have the exact same structure. You want a store ...
0
votes
1answer
80 views

Update Statistics full scan hung up?

We have a weekly maintenance plan that does your typical Integrity Checks and then goes and does an Update Statistics on all the databases on our server. Over the weekend it appears that our job was ...
4
votes
3answers
16k 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 ...
1
vote
2answers
64 views

Restored database size

I have a large (90 GB) database. About half of the space is taken up by two tables that are no longer used. I intend to truncate these two tables and as I understand things this won't free up the ...
2
votes
1answer
80 views

Job to check the status of another job

I am looking to create a job which would check a status of another job every 5 minutes. select CASE jh.run_status WHEN 0 THEN 'Failed' WHEN 1 THEN 'Success' WHEN 2 ...
4
votes
1answer
166 views

Sql Server - Running a Job on Multiple servers

I am using SqlServer 2008. I have a Sql Server Job that checks Disk Space and emails admins if the space is less than 15% Instead of adding that Job to all servers is it Possible to run the Job on ...
0
votes
0answers
34 views

Why the simple insert into query allocated a lot of tempdb?

I'm using sp_whoisactive to monitor the tempdb and found the following query allocated 30,000,000 tempdb_allocations (BTW, what's the unit of the allocations?) select a.*, b.A, b.C, b.D into c from ...
1
vote
0answers
60 views

Are there limits to SSRS 2014 RDL files going over to SSRS 2008 (or 2012)?

Is there any incompatibility with reports developed in SQL Server SSRS 2014, and then exported to .RDL files that will be imported into SQL Server 2008?
0
votes
1answer
68 views

How to increase the performance of this 3 minute reporting query

We have a table MyTable with about 10.000.000 records which contains a record of all transactions processed in our application. This table has the user Owner who did the transaction, the date ...
11
votes
2answers
3k views

How can I add a rowversion column to a large table with minimal downtime

Using SQL Server 2008 and later, I want to add a rowversion column to a large table however when I simply ALTER TABLE [Tablename] ADD Rowversion [Rowversion] NOT NULL Then the table is unavailable ...
0
votes
1answer
49 views

Numeric Latitude & Longitude values are rounded up when querying SQL Server Database

Developing an application that is using Google Maps. Lagitude and Longitude values are stored in the table as the correct value. However when they are pulled into a view the value is rounded to the ...
-2
votes
1answer
73 views

SQL Server 2008: Is there a graphical reporting tool that shows tps (or select statements) per second for a specific table?

In SQL Server 2008: Measure tps / select statements per second for a specific table? , the question was "how to do it" OK, so I can do it.... but I want automated, pretty charts, so I can easily find ...
1
vote
1answer
34 views

How to get paged resultset from a table?

I am trying to get paged resultset from a table (Notes). So, I am passing the last offset id (@LastOffset) value along with the page size (@Count). My approach to achieving this is: Sort the table ...
6
votes
2answers
21k views

Add a new Column and define its position in a table

I have Table A with 5 columns: TableA -- Name Tel Email Address I want to add a new column (mobile) in between Tel & Email: TableA -- Name Tel Mobile Email Address If I use ALTER TABLE ...
1
vote
0answers
41 views

Renaming databases vs backing up and restoring

I am creating a copy of Database A, restoring as database b, executing a job on database b. Upon successful execution, I would like to delete the database A and rename the database B to what the ...