Questions tagged [sql-server-2008]
SQL Server 2008 (major build version 10.00.xxxx). Please also tag with sql-server.
3,877
questions
5
votes
1
answer
4k
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 ...
- 53
1
vote
1
answer
28
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,
...
- 1,110
0
votes
1
answer
211
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-...
- 11
0
votes
1
answer
9k
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
...
- 243
1
vote
1
answer
5k
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 will ...
- 167
1
vote
2
answers
4k
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 ...
- 11
0
votes
1
answer
241
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 ...
- 3
3
votes
1
answer
929
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 ...
- 33
1
vote
1
answer
675
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
...
- 294
13
votes
1
answer
720
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
1
answer
1k
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?
- 11
0
votes
1
answer
604
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
1
answer
1k
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 ...
- 2,612
3
votes
1
answer
97
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)
| ...
- 137
1
vote
1
answer
833
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
...
- 11
2
votes
2
answers
470
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,612
2
votes
1
answer
2k
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
3
answers
734
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, ...
- 349
1
vote
1
answer
151
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 ...
- 121
1
vote
1
answer
63
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 ...
- 11
6
votes
2
answers
25k
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 ...
- 243
0
votes
2
answers
718
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 ...
- 11
1
vote
1
answer
201
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.
...
- 141
0
votes
2
answers
1k
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 ...
- 15
1
vote
2
answers
9k
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),
[...
- 111
-2
votes
1
answer
255
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?
- 119
1
vote
2
answers
2k
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 ...
- 211
0
votes
2
answers
51
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 ...
- 119
1
vote
1
answer
3k
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 ...
- 189
1
vote
1
answer
461
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<>'...
- 11
2
votes
2
answers
102
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 ...
- 23
3
votes
2
answers
466
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 ...
- 2,183
1
vote
1
answer
1k
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.
"...
- 157
1
vote
1
answer
714
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 ...
- 13
3
votes
1
answer
114
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
2
answers
398
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 ...
- 33
0
votes
1
answer
357
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
2
answers
184
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
1
answer
90
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 ...
- 135
1
vote
2
answers
2k
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 ...
- 7,386
1
vote
1
answer
92
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 ...
- 11
0
votes
1
answer
99
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), ...
- 121
0
votes
1
answer
7k
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(...
- 105
2
votes
1
answer
743
views
SQL Server 2008 Version of rows between... preceding and current row
Being a beginner, I'm having a hard time coding this particular scenario in SQL Server 2008
As you can see, The SUM column for July 2017 for example is equal to the following: August to Dec 2016 + ...
- 119
0
votes
1
answer
2k
views
Most efficient way to execute DELETE and INSERT operations in the same Table
I have the following structure:
Table1 Table2
C11, C12 C21
1 A A
1 B B
1 E W
2 F
2 B
3 ...
I need to ...
- 149
1
vote
0
answers
19
views
Joining 02 details tables, remove duplicated values from both sides in SQL Server 2008
In my case, both tables are details, sometimes it has 01 item in Tables A with mutiple items on Table B and vice versa.
SELECT DISTINCT
x._PROCESS_INST_NO,
x._GOODS_CD,
...
- 11
1
vote
0
answers
126
views
Filegroup per Table partition - remove filegroup and re-add filegroup
I've seaching all over the web for a solution, if it is even possible... I'm not a savvy in Partitioned tables in different filegroups...
Our software is supported by SQL 2008 R2 SP2 to SQL 2017 for ...
- 1,081
0
votes
1
answer
98
views
MS SQL: How to get Employee Status from two seperate tables
I am new to MS SQL so please bear with me.
I want to get the Company Code and Company Short Name of an employee DEPENDS on it's each employee status and stored it into one table.
If Emp_Status = FT, ...
- 3
3
votes
2
answers
20k
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 ...
- 45
0
votes
1
answer
37
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 ...
