Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

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

1
vote
2answers
41 views

MAX DATE With NULL VALUE

Trying to get the max date from my table, i can't retrieve the value when the date is null. Sample : the following table : +----+--------+------+------------------------+ | id | parent | Date ...
0
votes
1answer
24 views

Frequent Remote connectivity issue for SQL Server 2008 SP3

I follow all your blogs all helped me in my real-time issues. From last 2 days I'm facing connectivity issues to SQL server even though all the parameters are correct and cross verified with your ...
1
vote
1answer
29 views

Pages Input per Second Spiking Daily

I'm still learning a lot about SQL Server and pretty green, but doing the best I can as an accidental DBA. We have recently purchased a SQL monitoring software package, and after adjusting the ...
-2
votes
0answers
23 views

Restart SQL Services without disconnecting application [on hold]

I was asked by Ola to post my question I emailed on here. I have a batch file which restarts SQL services but disconnects the application when this is done. @ECHO OFF NET STOP SQLSERVERAGENT NET ...
2
votes
2answers
211 views

How do I use 'N' in my prepared SELECT statement

I'm connecting to an SQL Server 2008 database using pdo and the ODBC Driver 13 for SQL Server. I'm trying to run the following query which searches the database for Chinese characters: SELECT TOP (...
1
vote
2answers
85 views
+50

Backing up a SQL 2008 DB, then restoring, gives a SQL 2005 (compatibility level 90) DB

We have four different databases on a SQL Server 2008 (not SP2 - don't ask me why) database server that have an odd problem. When listed from sys.databases, these databases report that their ...
2
votes
2answers
333 views

How to get the date part from a date with timestamp column in SQL Server?

I want to get the date in DDMMYYY format from a field that stores date in YYYY-MM-DD Timestamp format. (For example, '2018-11-09' should display '09Nov2018'). I know that one way is to use the ...
1
vote
1answer
31 views

MS SQL 2008 Databse in Suspect mode and CHECKDB returns error: Object ID 3 has chain linkage mismatch

We did a disk expansion for VM DB server. However, after this operation the DB became suspect mode and cannot be open. Then we tried somethings like that; https://www.sqlrecoverysoftware.net/blog/...
1
vote
1answer
68 views

How to join global temp tables in SQL Server?

I have four global temp tables (##A,##B,##C,##D) on which I want to perform a left join in SQL Server 2008. By global temp tables I mean I created them using a code like this: SELECT * INTO ##A ...
0
votes
1answer
44 views

What is PROC FREQ equivalent in SQL?

I want to use the functionality that PROC FREQ provides in SAS. PROC FREQ helps in giving the statistics such as frequency, percent, cumulative frequency etc. of a variable or variables. The link ...
0
votes
1answer
62 views

How to remove temp tables from SQL Server?

I want to remove temp tables that I was using in my script in SQL Server 2008. I can't find a way to remove it without writing a few lines of code. I have not been able to find code that works ...
2
votes
1answer
30 views

How to execute a query based on the condition if a column exists?

I want to run the following query: CREATE TABLE dummy AS SELECT x, y, z FROM table1 WHERE y!=0 then I want to do the following: if z column has a non-null entry do: 1. copy the content of z into A ...
5
votes
2answers
354 views

How to import CSV file in SQL server 2008?

I am trying to import a CSV file in SQL Server 2008. BULK INSERT is a way to go but it is applicable for CSV from SQL Server 2014 onwards. What would be an alternative way to achieve this goal? Any ...
1
vote
1answer
40 views

How to set the first row of CSV data as column name in SQL Server?

I am using BULK INSERT to read a csv file in SQL Server. Is there a way to keep the first row as column name while I am reading the file? If not, after reading the data from the csv (from second row),...
0
votes
1answer
57 views

Help me with sql backups please

I'am facing some difficulties regarding my Sql Backup method, i am not sure if my method is correct, i am using a backup method described below; Created a Full backup named Xbackup in Maintenance ...
0
votes
1answer
23 views

What is the infile SAS equivalent in SQL Server?

I am trying to read a csv file in my SQL Server Script. In SAS, I can read it using the infile option. (http://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/viewer.htm#a000146932.htm) ...
1
vote
2answers
59 views

How to define macros in SQL server?

I want to define a macro in my SQL script and later based on my conditional query I want to create a new variable name which will be 'X'+macro name. For example, say I define a macro 'month' which ...
2
votes
1answer
72 views

SQL Server - Remove all logins that they are asossiated with orphan users

I have an SQL Server 2008R2 that had lots of databases. The procedure was that a logins were created and users were mapped to databases. Most of these databases have been removed from the server ...
0
votes
1answer
36 views

How to get current month and year of the database as a macro in SQL server?

I want to get the current month and year of the system into a macro variable and keep using that macro variable to create related new variables. The purpose is that if I run my script every month, I ...
0
votes
2answers
54 views

Query to get all the columns in a view

I've got this little ditty to concatenate all the columns in a table, for writing scripts quickly: create function [dbo].[get_columns] (@table_name varchar(255)) returns varchar(max) as ...
0
votes
1answer
53 views

Validation of data with checksum vs Binary_checksum

Based on this post, I was able to solve the performance issue by adding indexes and thought I completed my project until I ran into an issue with the checksum... I then did research (should have done ...
1
vote
1answer
21 views

Migration from SQL Server 2008 R2 to SQL Server 2012 SP2

I have many backups on my SQL Server 2008 R2 system, and I must restore them on new SQL Server 2012 SP2. My question is, after the restore on new SQL Server 2012, what can I do to verify that ...
0
votes
1answer
58 views

How can I get SQL to do the sensible thing without resorting to a query hint?

I have two tables, Items and Grouping. Items has about 7 million rows whereas Grouping only has 300,000. Of the two conditions on my query, the 'human obvious' thing is to evaluate G.StatusID IN (1,2)...
0
votes
1answer
11 views

ACE.OLEDB.12 installed but everything has a 0 on sp_msset_oledb_prop

So I installed ACE.OLEDB.12 to import excel files into sql server. After this, I can acces this procedure to see if everything is ok, but it's not. I have Zeroes on everything. This is a simple ...
3
votes
0answers
41 views

Moving to version 2016 from 2008 and Breaking Change Alert from Upgrade Advisor

DBMS: SQL Server Upgrading from version 2008 to 2016. I have this part of the code that is being flagged by the Upgrade adviser as Breaking Change. Error: uses a constant expression which are ...
3
votes
2answers
142 views

New SQL Server with slow Index Spool (Eager Spool) and old server used index

So yesterday I posted this question regarding that we have upgraded from SQL Server 2008 to SQL Server 2017 and that we excperience some performance problems. I got the answer to try updating ...
0
votes
2answers
58 views

What's the best way to get the top N, then everything else as n + 1

For example, if I've got this query: select top 10 medication_name, count(1) from DimMedical group by medication_name order by count(1) desc I can easily get a count of the ten most ...
0
votes
1answer
17 views

Counting entries of a unique entry of another field and concatenate the result with the unique field in a new column [closed]

I want to create a query that will search the table and find the following: If a POL_N entry has ENDO_N entries then the COUNT ENDO_N field will start counting the ENDO_N entries and will get the ...
1
vote
1answer
96 views

Migrate on-premise SQL Server databases to Azure SQL Database

We're in the process of a server migration from an on-premise server (Win2008R2) to Azure PaaS. To move the databases, we used the Microsoft Data Migration Assistant (DMA) tool, which worked great ...
0
votes
1answer
32 views

Database Mail works in Production but not in Test Database

I have a Production database that is able to send mail. And a Test that is not able to. They are both on the same SQL Server. The error is (SQL Server 2008): Msg 229, Level 14, State 5, Procedure ...
-1
votes
1answer
38 views

Queries involved in deadlock [duplicate]

Is it possible to send queries involved in a deadlock via db mail alert in SQL Server 2005 on mail? If it is possible, then what is the best way to do this?
1
vote
1answer
27 views

What are implications for changing encryption from TRIPLE_DES to AES_128?

As title asks, what are implications for changing encryption from TRIPLE_DES to AES_128? I have an older database I've been tasked with hardening, and I need to updated the algorithm used for ...
4
votes
2answers
305 views

The disappearing act of the “Invalid length parameter passed to the LEFT or SUBSTRING function” error

I'm running into the "Invalid length parameter passed to the LEFT or SUBSTRING function" error, but it goes away and the query works when I include the column I'm passing into those functions, any ...
0
votes
2answers
34 views

How can I only UPDATE if the user was created?

I got A cursor with some ifs like this one: IF @ROLE='RW' BEGIN SET @COMMAND =' USE '+QUOTENAME(@DATABASE)+' CREATE USER '+QUOTENAME(@...
0
votes
3answers
52 views

Column in INSERT query needs to increment if matching data on rows is different

I tried to simplify what I am trying to do. This is for work, we sell metal. When an order is placed material, coating, masking, etc. is chosen. Then someone allocates material for the order. They ...
0
votes
1answer
25 views

Mail Alert when threshold of 10000 pendingcmds are breached

I currently have the below running to monitor replication and send a mail when the threshold of 10000 pendingcmds are breached, however the alert mail is generated every 5 minutes regardless of the ...
1
vote
1answer
31 views

Total_worker_time over SQL 2008 linked server

I’ve come across a lengthy SP that is being recorded as generating a high amount of total_worker_time in sys.dm_exec_procedure_stats. (But I think it might be a red-herring) The SP contains cursors ...
1
vote
1answer
56 views

Best way to insert error information from catch block in the table for stored procedure?

I have stored procedure that process single row transaction. I would like to catch any errors and insert in Error table. Then use nightly process to forward the information from error table to ...
2
votes
3answers
168 views

SQL How to Convert Single Tenant Databases to a Multitenant Database

We have a legacy application. For some reason, someone decided to create a database for each customer. So we have 500+ databases with the same schema. I would like to convert into One multi-client ...
0
votes
2answers
44 views

Select repeating number x times and then increment

I need a select query that can generate z amount of rows starting from 1, repeating x times and then increment to the next number. For example 10 rows repeating 4 times would result in the following: ...
1
vote
2answers
66 views

How to show how many times the server was restarted on SQL Server?

Is there any way to show how many times the server was restarted this month? I'm using SQL Server 2008. Thanks
0
votes
2answers
44 views

Update data from one table to another in sql server

I have a log table where accidentally one date column got updated with current date for 9 Million records. Luckily I had a backup table, so now I want to populate data from backup table to current ...
0
votes
1answer
47 views

Difference between changed compatibility level database and new database

What is the difference between SQL Server databases A & B where: A. Database restored from lower level compatibility(100) to higher compatibility (130) B. New Database created in higher ...
1
vote
1answer
91 views

Best way to prevent deadlocks and server locking for single row Insert/Update transaction?

I have Update transaction that will run for single row at the time. However, my application is multi thread and there might be more than one user at the time trying to access same table. While doing ...
4
votes
3answers
177 views

Does MERGE prevent deadlocks and server blocking?

I have started looking at MERGE as an option that I would use in my application to process UPSERT transactions. Seems that a lot of SQL Server experts are recommending this approach. However, while ...
0
votes
0answers
23 views

full backup after failover cluster stuck for 11 hours

We have 2 nodes in a cluster environment one is a physical node and the other is virtual and both servers are windows 2008R2. the client performed a manual failover at night at about 7:30 pm(to the ...
1
vote
1answer
53 views

Generating maintenance database from maintenance tables currently in master and msdb

I want to create a maintenance database that i can clean up master and msdb, i am trying to collect all the table info from master and msdb that are not Microsoft tables, bearing in mind that some ...
0
votes
1answer
44 views

Instead of C:\SQLDBA\ServerName\Instance rather C:\SQLDBA\ServerName_Instance

I am trying to create a folder based on the servername\instance to backup up to, i want the folder to create as servername_instance. Could you please point out where i am going wrong as it creates as ...
1
vote
1answer
138 views

Split datetime records that fall between 10pm to 7am into new record [closed]

I am very new to SQL Server. I am trying to split time in time out records that fall between night shifts from a employee shifts table but I am having a difficult time doing so. Night Shift runs from ...
0
votes
1answer
25 views

data translating issues [closed]

I have these two table here in ssms: one is called client table, which have a column called city, and it is really city name, like new york, etc. Another table is called city table, it has city's ID ...