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
29 views

Multiple inserts using select row and variable combination with one SQL statement

I'm working with SQL 2008 R2 and have a need to find a specific row in a table that has three fields and then insert multiple rows into the same table with each insert using two values from the found ...
1
vote
1answer
137 views

I want to change to Dynamic SQL Query instead of CASE statement [closed]

I have two tables as below and description is given in below Employee table id EmployeeID departmentid department1 department2 department3 1 300 100 IT Finance ...
6
votes
1answer
1k views

Why am I getting the error “non-boolean type specified in a context where a condition is expected” for this request?

I am getting this error : "Error 102 : non-boolean type specified in a context where a condition is expected" for this request : DECLARE @num_dossiers TABLE (num_dossier INT,indice NVARCHAR(3)) ...
0
votes
0answers
47 views

Why can't SQL Server handle into's in 2 parts of a script? [duplicate]

I noticed some behavior I found weird while editing a co-workers stored procedure: create procedure weird_behavior (@switch bit) as if (@switch = 0) begin select * ...
2
votes
1answer
239 views

Table Fragmentation

If I execute the following: SELECT dbschemas.[name] as 'Schema', dbtables.[name] as 'Table', dbindexes.[name] as 'Index', indexstats.alloc_unit_type_desc, indexstats.avg_fragmentation_in_percent as ...
3
votes
5answers
440 views

Order table by two columns

I would like to order this table but I can not find a way to make it work. Can you help me? I have this table: I need this: I have a table where documents are listed, the first field is the id of ...
0
votes
1answer
64 views

Conversion of Row into Column

I'm in very weird situation and i have researched a lot but not able to find anything similar to this scenarios. Input looks something link this. Where Each patient has Primary Dx code, Admit Dx ...
7
votes
2answers
2k views

Why is my log file so massive? 22gb. I am running log backups

I can't seem to figure out the answer. I've seen multiple answers like this: Why Does the Transaction Log Keep Growing or Run Out of Space? and everyone talks about running back ups on your log file ...
1
vote
0answers
136 views

SSMS - Data load Option

I have recently installed the newer version of SSMS on my system. But the option to load the excel file is grey out. I'm not able to load BULK Load I tried to run DTSWizard.exe, even it doesn't give ...
0
votes
0answers
128 views

Loading 'Image Service Package' crashes Management Studio

I'm getting the following error on my SQL Server when I log into the system: I ran a repair on SSMS successfully, but it did not resolve the issue. The following error message is recorded in Event ...
0
votes
2answers
143 views

How to grant or allow a particular SQL Server Login/User to always view a Column Level Encrypted data?

I used Column Level Encryption to encrypt one of the columns in my SQL Server database. Now everytime I want to SELECT to see that encrypted data, I have to open the symmetric key to decrypt the ...
0
votes
2answers
169 views

Disk Separation DB installation and data files

I am wondering whether it is a good idea to separate the database installation media from the actual data files. In our current environment we have: C: - Windows Server D: - MS SQL Installation &...
0
votes
1answer
1k views

SELECT failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'

Whats wrong with this statement? why it gives below error message Error Message: SELECT failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'. Verify that SET ...
-1
votes
1answer
41 views

SQL Admission Count using row_number

I'm new in SQL Server learner and I'm stuck in simple problem to count the admission using SQL Server in healthcare data set. Sample table: create table admissions ( HIC char(1), DateFrom date, ...
0
votes
1answer
31 views

Joining on empty rows

I've got two tables. | tbAccountValues | +-----------------+ | Branch | Month | Values | +--------+---------+--------+ | 0001 | January | 1000 | +--------+---------+--------+ | tbMonths | +----...
0
votes
2answers
553 views

Using Cursor to loop through a Table variable in SQL Server

I have a parameter in the Stored Prod which gets some data in the format 1/1/2018-2/1/2018,2/1/2018-3/1/2018,3/1/2018-4/1/2018,4/1/2018-5/1/2018,5/1/2018-6/1/2018,6/1/2018-7/1/2018,7/1/2018-8/1/2018,...
1
vote
2answers
62 views

Is it possible to run query against only part of the Central Managed Servers?

For example, I want to run this query to get the status of some servers having high availability group set up: SELECT s.role_desc, g.name, r.replica_server_name , GETDATE() as checkTime FROM ...
2
votes
1answer
136 views

Auto generated statistics by query optimiser

Can we consider removing the auto generated statistics if the update statistic within a given database is taking too long? We do a weekly maintenance using UPDATE STATISTICS WITH RESAMPLE but often ...
1
vote
1answer
93 views

SSMS is crashing and SSMS Toolbar

I'm new in SQL Server and i have installed SQL Server on my system for testing purpose. Issue 1: I want to convert the name of all the toolbar in menu tab to icon. I have no clue from where does it ...
-1
votes
1answer
38 views

Sql selection of text file

I am new in SQL and need a small help in solving this. I have a column which has a file path name "C:\Users\akumar\Desktop_Claim Files\AMSO_CLAIMS_YYYYMMDD.txt". And, I need to create a column (...
0
votes
1answer
341 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
96 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
595 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
19 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
54 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
83 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
626 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
151 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
443 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
25 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
177 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
98 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
383 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
106 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
72 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
43 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
151 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
46 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
153 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
93 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
400 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
208 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
51 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
181 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 ...
3
votes
2answers
3k 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
100 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
183 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
46 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
291 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 ...