Questions tagged [sql-server-2008]

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

Filter by
Sorted by
Tagged with
0
votes
1answer
222 views

SQL PRINT vs SQL EXEC

When executing the query below, if I use PRINT it prints correctly. I can copy and paste the printed code and execute it. However, if I use EXEC I get the following error: Is there a way of ...
0
votes
2answers
62 views

Can sp_Blitz, sp_BlitzCache, or sp_BlitzFirst identify what queries caused a major growth operation to occur on a particular log file?

I'm currently logging the outputs of sp_Blitz, sp_BlitzCache, and sp_BlitzFirst regularly (via SQL jobs). A particular database's log file grew somewhat exponentially and I believe I can see the ...
3
votes
1answer
361 views

Added .Net assembly to SQL CLR without turning TRUSTWORTHY ON

I'm trying to add "System.Messaging.dll" using the guidance provided by Solomon Rutzky in his post Assembly deployment with permission UNSAFE or EXTERNAL_ACCESS using asymmetric key but I'm failing at ...
1
vote
1answer
15 views

Remove text from columns that may or may not be present multiple times

I have the following table in SQL Server 2008. create table OrgUnits ( OrgUnitId int not null constraint PK_OrgUnits1 primary key, OrgUnitName varchar(100), OrgUnitLevel int not null, ...
0
votes
2answers
44 views

SQL Server 2017 Express edition Bulk load

I am working on the SQL Server 2017. I need to import 20 text file into one table. Every text file has the same data type and column name. I have checked the data and they have in the same order also. ...
0
votes
1answer
70 views

Using IF clause or while clause in OUTER APPLY

I want to select dates between @startdate and @enddate but I get an error when a month doesn't have 31 days. DECLARE @StartDate date, @EndDate date set @StartDate = '2019-04-01' set @EndDate = '2019-...
0
votes
1answer
253 views

Calculate Average between two dates of different rows

I have the below table BIDID AppID AppStatus Time 23390 16 In Review 2017-07-03 23390 16 Approved 2017-09-03 23390 16 In Review 2017-10-11 23390 16 Approved 2017-12-11 ...
1
vote
1answer
85 views

Comparing two pairs of columns between two tables and returning the column from a third table

I have three tables: Table1 table1 can have anywhere from 50K to 800k unique rows. By this, I mean that the combination of all the values in each row are unique, although sometimes, some columns ...
0
votes
2answers
276 views

Subtracting sum of previous rows

I want to create total field in this table. But in SQL server 2008 can't use 'order by' in OVER clause. I have same PO and difference INV. I want to Qty field minus Quantity field and next row Total ...
0
votes
1answer
24 views

Login mapping only works for the first query in a window

I have a really odd login mapping error for one DB that I can't seem to figure out. When running a simple select statement while impersonating a login (A) on DB(1), it appears to work fine. However ...
2
votes
1answer
127 views

How can I identify non deterministic updates occurring in my SQL Server?

SQL Server books online states Use caution when specifying the FROM clause to provide the criteria for the update operation. The results of an UPDATE statement are undefined if the statement ...
1
vote
1answer
65 views

Convert SQL Server results to a text file that PowerShell can access and use

The query below is used to create a text file with a list of server names. The problem I have is that the text file gets created like Server, Server, Server etc. but I need it as follows Server ...
13
votes
1answer
349 views

SET NOCOUNT Error in handling SQL call after upgrade

We are upgrading our test environment with a new server and updated version of Microsoft SQL Server and have run into an issue. On the new server, our old code will get "operation is not allowed ...
0
votes
1answer
72 views

How can I restore a backup without overwriting ALL of the tables?

I have a SQL Server database with 3 tables. I need to restore a backup that contains 4 tables, while still keeping the old data in the original 3 tables. Is this possible?
0
votes
2answers
64 views

select query with where clause having variable taking more time to execute

I have a query which selects a couple of rows from a view based on the variables declared. I ran the same query in lower environments and to my surprise, I got the result in less than 3 minutes ...
0
votes
1answer
36 views

Questions on database table and index partitioning in SQL Server

I have developed Transact-SQL script to partition the table. To apply the partition, I am creating one non-clustered index for my partition key column and associating it to partition scheme. I have ...
3
votes
1answer
124 views

Why would writing the CTE to an actual table provide a performance improvement?

I'm a huge fan of CTE's. They are the best. You know it, I know it, we all know it. Today, I got burned with a query that is about 1000x faster if the CTE is written to a real table. The query ...
3
votes
1answer
43 views

Select/Join multiple row values from Column A if dates in Column B are within 'x' seconds of each other

I have the following Database structure and need a query to work in SQL Server 2008 and also SQL Server 2014. Database: ASSETS Table: COMPUTERS PK (GUID) (nvarchar) (nvarchar) | ...
1
vote
1answer
92 views

How do I shrink the MASTER log file in SQL Server 2008?

This is for a non-system database: USE AdventureWorks GO ALTER DATABASE AdventureWorks SET RECOVERY SIMPLE GO DBCC SHRINKFILE (AdventureWorks_Log) GO ALTER DATABASE AdventureWorks SET RECOVERY FULL ...
2
votes
2answers
82 views

What is the best way to calculate year over year inflation?

My ultimate goal is to calculate money from previous years as 2019 dollars. I've got these numbers from the BLS, and create the CPI_U in sql like so: create table CPI_U (year int, dec decimal(4,...
2
votes
1answer
260 views

Splitting a year into pieces

I want to split a given year into given number of date range pieces using SQL. Following points need to be considered while splitting it: Given year will always start from 01-01 and end at 12-31 and ...
0
votes
3answers
149 views

Deprecated raiserror detection

I want to upgrade SQL server from 2008 to 2014 versiyon. I checked all of the deprecated features including raiserror syntax. But I have many stored procedures to check. Some of them use new syntax, ...
1
vote
1answer
40 views

SQL Server 2008 onward - Globalization with Date Time

My application is standalone windows application. Database is SQL Server 2008 onward. The application will be deployed on systems around the globe. User should view the date/time values in their own ...
1
vote
1answer
112 views

Correcting and Detecting overlapped dates with CTE

I have a CTE that marks (as bits) overlapped records based on certain criteria. After filtering, I need to update these records to "correct" them and make them sequential, but I'm getting a 4104 on ...
1
vote
1answer
46 views

Where to install or use SQL Server?

New to the database side of IT and I have a few questions... I'm trying to figure out what servers and databases I can access through SSMS. What is the best practice for installing/using SQL Server ...
2
votes
2answers
1k views

Select data from another database instance on the same server in sql server

I have a query shown below: select count(*) as Count, datepart(yyyy, [LogDate]) as [Year] from ViewAssociate..Auth_Log where ActionCode = 12 group by datepart(yyyy, [LogDate]) order by [Year] This ...
0
votes
1answer
65 views

MS Access Foreign key not working in SQL Server

In MS Access I have a 'multi column - One to Many' foreign key between two table. When I try to create the same relationship in SQL Server I get a message " The columns in table 'Laser_Status_info' do ...
0
votes
2answers
139 views

What is the best way to remove large amount of data from large tables?

I have two large SQL 2008 tables: table_header with 7 columns and ~ 2 million records table_detail with 22 columns and ~ 78 million records The common field between the two is called item_name ...
1
vote
1answer
38 views

How to Convert List of values datatype in oracle to equivalent or valid datatype in sql server?

I want to recreate the table below in SQL server I have tried using SSIS but it does not know how to handle the lOV Data type how can I recreate this table in SQL server and migrate the data to it. ...
0
votes
2answers
167 views

Create SQL login to multiple instances at once

I have a AD group that needs to be added to multiple SQL instances, I am wondering is there a quicker way of adding this group at once rather than having to login to each server and creating a login ...
1
vote
2answers
513 views

Bulk insert not retaining special chars of UTF-8 Encoded txt file Sql Server 2008

I have a stored procedure that bulk imports a text file and inserts it into my database. CREATE TABLE DBO.TEMP_STORE ( ID nvarchar(max), [MONTH] nvarchar(max), [...
-2
votes
1answer
31 views

Get Average of a column excluding the current month [closed]

I would like the average to be average excluding the current month counts. I.e for November the average would calculated based on Jan-September values. How do I do this using SQL Server 2008?
1
vote
2answers
208 views

The merge process could not connect to the Publisher ' '. Check to ensure that the server is running. (

I have 2 servers with 2 kinds of replications between them. One is Transactional and the other is merge. The Transactional replication is working fine but in the merge replication, I'm getting error ...
0
votes
2answers
40 views

Get data from both tables what join should I use

I have T1 as table 1 ---------------- field1 | field2 --------------- val1 | val2 I have T2 as table 2 ---------------- field1 | field2 --------------- val11 | val22 I want to write a query ...
1
vote
1answer
46 views

Using LIKE for a Range of Numbers

In my WHERE clause, I'm trying to use LIKE for a range of numbers. For example: WHERE rr.sequence LIKE '05600%' OR rr.sequence LIKE '05601%' OR rr.sequence LIKE '05602%' OR rr.sequence ...
1
vote
1answer
39 views

Instead of Delete trigger

I want to pass one validation DML statement on the trigger. Condition is below DML statement select distinct t1.cid from table1 t1, table t2 where t1.cid=t2.cid and t1.status<>'...
1
vote
2answers
65 views

Select rows that have multiple required filters

How can I select artists who have punk and rock in their musical genres? I need artists with both genres. A example: SELECT * FROM artist_genre WHERE genre_id IN (1,2) In this way I recover all ...
3
votes
2answers
207 views

How often should you update your stats?

How often should you update your stats? What is "too seldom"? How often is "too often"? The answer is "it depends" on your database, users, data, etc. So I've tried to log what our stats look like ...
1
vote
1answer
50 views

Error on CDC enabled Table

I am trying to create an SP on a CDC enabled database. SP contain an update statement for CDC enabled table using UPDATETEXT command but CREATE PROCEDURE statement has generated an ERROR Message. "...
1
vote
1answer
53 views

Migrating SQL server 2008 to 2016 [duplicate]

Is there any check list for how to do migration from SQL Server 2008 to SQL server 2016 ? I am building a new server for the 2016 want to move all the DBs from the old server 2008 to the 2016 any ...
3
votes
1answer
75 views

DBCC unique cluster index corruption

I am very new to the world of DB maintenance and recently ran to an issue that will appreciate your advice on. I have a Database that the earliest solid backup file I can see without any corruption ...
1
vote
2answers
101 views

sp_blitzfirst results to table on a linked server

I have been all over looking for the solution to logging sp_blitzfirst results to table on a linked server. I see github posts where a contributor was adding this to sp_blitz, but that's been closed ...
0
votes
1answer
210 views

Why does msdb.dbo.backupset have entries for each database every 15 minutes?

I have 134 separate databases on a SQL Server. Every 15 minutes an entry appears in the msdb.dbo.backupset table for each database on the server. Why does this happen every 15 minutes? What is ...
1
vote
2answers
170 views

Shrinking a SQL Server 2008 Standard database to move it into SQL Server 2014 Express

I need to shrink a popular CRM database with SSMS to move it onto a new server. I am going from SQL Server 2008 Standard to SQL Server 2014 Express. Database is in Simple Recovery Model. It needs to ...
1
vote
1answer
12 views

Switch publisher - subscriber side

I have 2 server using merge replication. One acts as a Publisher and one acts as a Subscriber. One day I want to switch the side, publisher become subscriber and subscriber become publisher. In my ...
1
vote
2answers
343 views

ghost cleanup, delete and reindex

I know when you delete data, that data is only "marked" as "deleted' for faster rollback. The only way to really delete this data is to run something that reads the datapage, such as a table scan or ...
1
vote
1answer
56 views

Find all occurrence of value from column b in column a

So basically I have 2 tables which I merge by SELECT t2.columnA , t1.columnB, t1.description FROM Table1 as t1 LEFT JOIN Table2 as t2 on t1.GIDNumber=t2.TwrNumber I need to display all occurrence ...
0
votes
1answer
53 views

CPU and Hard Drive Spike for 20-30 Minutes

I'm collecting data on the performance of our Microsoft SQL 2008 server in preparation for moving it to Azure. It's a very low load server (currently max concurrent transactions max out at 3), ...
0
votes
1answer
967 views

Remove punctuation marks, special chars and space and compare cols

I have a table with new and old long description columns and I want to compare them by removing space, punctuation marks, and special characters for differences. Create table DESCR_COMP ( HCPCS Char(...
0
votes
2answers
815 views

Error cs1567 while installing ms-sql on windows 8.1

I tried to install sql server 2008 and 2012 on my windows 8.1 laptop, but at both times I faced with the same error: TITLE: SQL Server Setup failure. SQL Server Setup has encountered the ...