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

learn more… | top users | synonyms (2)

2
votes
2answers
155 views

SQL Row concatenation with XML PATH and STUFF giving aggregate sql error

I am trying to query two tables and get results like the following: Section Names shoes AccountName1, AccountName2, AccountName3 books AccountName1 The tables are: CREATE TABLE dbo....
0
votes
0answers
20 views

MS SQL data synchronization question

I'm in charge of modifying an application which was intended to run Microsoft SQL Server Compact 3.5. The application must run on a local database during fieldwork when there is no network ...
0
votes
3answers
46 views

Minimum value within a set (or window) within a three column table

I only have SQL Server 2008R2, therefore I do not have all the window functions available since SQL Server 2012. I have a table that looks like this: I would like to retrieve a result set like ...
0
votes
2answers
43 views

How to separate values from one column into two columns in SQL Server [closed]

I have this dataset as input: Name Contact No ------- ---------- Deepika 75865558 Deepika 207586558 Rnveer 746655558 Rnveer 946655558 vaibhav 2356841 vaibhav 6655558 I want to get ...
1
vote
0answers
917 views

SQL server 2008 querying very large large tables

I've recently started a new job, the table row counts are huge compared to where I was before (11 billion rows in one of our main tables). I've been told there isn't time to update the statistics on ...
3
votes
1answer
70 views

Amazing improvement of script execution speed when split - not understood

I have an ETL job that merges data into about 20 columns of a fact table, all integer non-null (default 0) columns. There are 8 merge statements to completely update all the columns and they involve ...
0
votes
0answers
30 views

Permissions required to add user to fixed server role

We want to take a group out of sysadmin but grant them a role so they can do the following: add, change, remove database access on prod servers for users, groups and service accounts. create and ...
0
votes
1answer
31 views

Script to Delete Tables With A Prefix

I am running SQL Serve 2008 and we have a few test tables that were obviously used for testing. The prefix of the tables are Test_, my question is, will this script delete all tables with the prefix ...
0
votes
1answer
52 views

OLEDB provider for linked server reported change of schema version error

I am trying to run a report but its giving this message: An error has occurred during report processing. (rsProcessingAborted) Query execution failed for dataset 'ParamYear'. (rsErrorExecutingCommand)...
4
votes
2answers
112 views

How to rewrite a query with MAX in WHERE clause

I have a query with a max in the where clause, which is slow. select count(*) from TableName tbl1 where tbl1.id = ( select max(tbl2.id) from TableName tbl2 ...
1
vote
0answers
20 views

msdb.dbo.backupset.compressed_backup_size is inaccurate?

I thought the value in msdb.dbo.backupset.compressed_backup_size represented the size, in bytes, of the full, differential, or log backup file (that is, the BAK, DIFF, or TRN file). However, although ...
5
votes
1answer
158 views

how to ignore the SSMS (sql server management studio) sql server ansi settings?

The options in Management Studio On Management studio Tools->Options->Query Execution->SQL Server there are options that are set and these prevail over the same options that are set on database level....
3
votes
1answer
58 views

Apply SQLServer Service Pack to a Single Node Cluster

I have to apply a SQL Server 2008 R2 Service Pack to a Single Node Cluster. The other node died and new hardware has been ordered. Is there anything weird to worry about? Normally we'd apply the ...
0
votes
0answers
33 views

Incorrect database backup file size in SQL Server 2008 R2

I am getting different sizes of the database backup file from SQL Server 2008 R2 even when backing up at very granular or frequent intervals. Scenario: I have taken database backup at 8:00am = ...
1
vote
2answers
58 views

SQL Server engine in one place with database files on NAS

My office uses accountant programs which run on SQL Server 2008 R2. The SQL Server engine must be installed on one of the laptops (called "server") and the other laptops (called "clients") connect to ...
1
vote
2answers
81 views

How can I improve performance on a 3 table query?

I'm attempting to analyze a resource drawdown (the percentage of time individual units are being utilized during a reporting period, e.g. 2-weeks or 1-year). I need the aggregation down to the minute....
0
votes
1answer
35 views

Can't list columns or query in MS Access 2003 linked server in SQL Server 2008 R2 using Microsoft Jet 4.0 OLE DB Provider?

I can however list the tables... If I run Exec sp_tables_ex 'LSTest' I get a result with a list of tables. But if I run Exec sp_columns_ex 'LSTest', '<one of those table names>' I get an ...
2
votes
1answer
36 views

SQL Server 2012 In-Place Upgrade and Timing of Database Version Change

We are updating several SQL Server 2008R2 instances to SQL Server 2012 SP3 CU2. I know that the database versions will be changed from 100 to 110 during this process (e.g. once finished, the databases ...
2
votes
1answer
47 views

Permissions on Trigger Problem

I am working with a database in MS-SQL 2008 R2, and I'm running into a problem. The basic setup is this: There are two tables of interest: dbo.Assesments and audit.Assesments. dbo.Assesments is the ...
3
votes
1answer
48 views

Can't set default_schema_name for Windows User Group?

When I try to set the default schema for my Windows Role/Group for Windows Server 2008 R2, it won't allow me to do so...I run: ALTER USER [UCPSAR\Service Provision] WITH DEFAULT_SCHEMA=[...
-2
votes
2answers
118 views

Split URL string into columns

Given the string: URL:'ID=1!&user=xxx&depart=21&companyId=43&workOrder=67' I need to get the result: Sno ID user depart companyId workOrder 1 1! XXX 21 43 67 ...
1
vote
2answers
120 views

Validation of Partitioned table Performance

We are testing partitioning on a table which contains a datetime column (DateColumn). Partition function I am using is: -- 138623 + 16774 records CREATE PARTITION FUNCTION pf_TableName_DateColumn (...
0
votes
0answers
60 views

Suggestions for indexes or how to partition a table in SQL Server 2008R2

Table structure and DBMS SQL Server 2008 R2 I have a single table structure (flat) that has summarized sales data (YTD,MAT etc) by unique combinations of "Customer", "Product" and "Territory". ...
6
votes
1answer
545 views

AND conditions within unbracketed ORs work but why?

I have very similar code to the first block below. It is in production in a critical process, and I am not confident that it will always work. I would like to know why it does. It uses or (1=1) to ...
1
vote
2answers
107 views

Databases in Suspect state after enabling TDE

A few of my databases are in a suspect state. How can I get them online? I don’t know what info is relevant and what isn’t, but I’ll give as much as I can. Here is what happened: I enabled TDE in ...
0
votes
1answer
86 views

T-SQL script doesn't delete files when run as an agent job - does if run manually

I'm trying to write a script to copy the last 'good set' of SQL Server backup files to another drive (it's a long story - we're using a suppliers R1Soft for backup and it won't allow us to exclude ...
1
vote
0answers
36 views

Autocomplete in SQL Server Management Studio showing too much

I'm new to using SQL Server 2008 Management Studio as I just started working with it last week. Yesterday when I was working in one of our databases I remember that I used the auto complete ...
1
vote
1answer
66 views

Use of Trigger on table which can have 100 hits / second. Will it hamper performance?

Currently in one of our projects we have the following situation. The server computer is Windows Server 2008 Standard SP2. Processor is Xeon (R) 2.13 Ghz. We are running SQL Server 2008 R2. We need ...
1
vote
1answer
34 views

linked servers between availability group to a server

I had three servers(server1, server2, server3) in availability group. And the listener name is "Production". I have a server name "Acceptance". I need to run a script that which will copy the tables ...
2
votes
0answers
106 views

Database suddenly in recovery-pending

We have a client running 70 databases on a SQL server. Last month they called us that suddenly database wasn't accessible. Database was in recovery pending mode. Today they called us again ...
-3
votes
1answer
37 views

How to use a conditonal update using cases

Case when oldvalue like 'val1','val2','val3' then newvalue = 'newVal1' Case when oldvalue like 'val4' then newvalue = newvalue + 'newval2' else newvalue = newalue + 'othernewval' Then I would need ...
0
votes
0answers
40 views

SQL Server 2008 R2 Developer edition - upgrade or install SQL Server 2016

Currently I'm working with SQL Server 2008 R2 Developer edition. I have several databases on it more than 500GB. Now I want to upgrade or install SQL Server 2016. What is going to happen to my ...
0
votes
1answer
125 views

Identify SQL Error - Transaction count mismatching number of BEGIN and COMMIT statements

On SQL Server 2008, the sproc below runs as part of a sql agent job every 4 hours. Six times in the last two months, the error below has been logged in the agent job history: 04/07/2016 20:00:00,...
1
vote
1answer
28 views

Is it possible to create a multi-layer schema in SQL Server 2008 R2?

For instance so you can specify a department, and then maybe have finer levels of granularity? CREATE SCHEMA hr; CREATE SCHEMA hr.reportl; CREATE SCHEMA hr.report2; The last two statements fail.
0
votes
0answers
32 views

Run Select Statement Based Off Data In Field

Below is sample DDL of what I am trying to accomplish. What I need to happen is if region = 1 only pull data for PP or if region = 2 then only pull data for CC How would I set that up in my query? ...
0
votes
1answer
56 views

slow count on text column

Table t1 with 500k rows, 2TB in size, one text column. SQL Server 2008 R2 SP3. CREATE TABLE [dbo].[t1]( [TId] [int] IDENTITY(1,1) NOT NULL, [Weight] [int] NULL, [TDate] [datetime] NULL, ...
0
votes
0answers
24 views

How does “OtherServer” in SSDT database references work?

I want to create a SSDT database project that is supposed to be deployed on a Sql Server that has several linked servers to other servers. The database ("MyBusinessDB") on those remote servers is in ...
2
votes
1answer
55 views

Temp DB space allocated- will it be reclaimed ever?

I have observed that the tempdb in the production environment has grown over to 100 GB after initializing it. I understand that due to server load and some expensive queries have resulted to the ...
-1
votes
1answer
123 views

How to search a given date value in all tables of present in database

I need a query to search for a particular date 2017-04-01 in every table in my database. My database has many tables and I don't know in which table this value 2017-04-01 exists. Please suggest a ...
3
votes
1answer
62 views

What sort of SPN / AD Computer Delegation do you need for delegation of credentials to a linked server that connects to a file share?

The idea here is to perform a double hop and delegate the credentials of the client (leeand00@example.org), that have been sent to the SQL2.example.org server to SQL3.example.org (admittedly this is ...
2
votes
2answers
104 views

What Microsoft SQL Server 2008 R2 features do you need to install to get OLEDB Provider Linked Table Connectivity?

Which features do you need to install in your SQL Server 2008 R2 instance to get OLEDB Provider Linked Table Connectivity? Specifically I'm trying to install the VFPOLEDB provider. (And something ...
0
votes
1answer
40 views

is there a max number of rows in a SQL Server 2008/2012 and MySQL table? [closed]

Im evaluating a RDBMS for a bank app solution and i need to be sure about these performance parameter. There is a lot of info on internet that is confusing me :O. Max number of concurrent ...
0
votes
0answers
13 views

Find minimum value in multiple column in SQL Server [duplicate]

How to find the minimum value in multiple columns in SQL Server? Example there are 10 column in table how to find minimum values in all records? Condition is greater than zero value
2
votes
0answers
43 views

Does HOBT shared lock prevent writes

I am attempting to execute long running reads against a table which is also the target of insert bulk queries from sqlbulkcopy. In order to achieve this I have added the with(nolock) to the query ...
1
vote
1answer
42 views

Return Number Not String In Dynamic SQL

This is my SQL syntax and (this is only a snippet unfortunately I am stuck running dynamic SQL in this instance) - but my issue is at the very end of my statement when I do the Select @numreturned ...
5
votes
1answer
186 views

How to index for inequality queries?

I have query that is excluding data based on the value of a float column select * from My_Table where my_Float_column != 0 and my_Float_column is not null I don't want to index a float type if I ...
0
votes
3answers
47 views

Is it possible to make an Update query right after Insert returned new Id's

I am using MS Sql Server 2008R2. For example I need to insert many rows inside TableA and I want to make an Update another TableB with newly inserted Ids from TableA. Is it possible to do in one SQL ...
1
vote
2answers
40 views

Excluding Null Values

In my syntax why does the NOT IN statement not exclude NULL values? To me, if NULL does not exist in #GoodAnswers it should return the dv Below is syntax, can someone help explain this to me? ...
2
votes
2answers
199 views

Using DateTime Variable In Where Clause

I am trying to pull all data where a sale date is >= to a certain datetime. When I execute my query I get an error of: Msg 241, Level 16, State 1, Line 15 Conversion failed when converting date ...
0
votes
1answer
22 views

Accepted Values Stored in Variable

I am trying to write a re-usable query and this is pseudo-code of what I am after. Is this possible to do in Sql Server 2008 or do I just need to write a different stored procedure for each ...