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
237 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
142 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
703 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 ...
1
vote
1answer
54 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
50 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
602 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 ...
1
vote
1answer
921 views

Script SQL Server Agent jobs to file

I am busy making a backup of all Agent Jobs by scripting them all to file.`How would i rewrite the below to use BCP instead BEGIN SET @EchoCommand = 'ECHO ' + @EchoField + ' >> ...
0
votes
1answer
491 views

Validate data between two large tables

I have this scenario. Data files are transferred from multiple stores (transactions) to the head office as they happen. For each store the table in question is pretty big in size. At the head office, ...
1
vote
1answer
35 views

Expanding Data File with Log Shipping and Mirroring: SQL Server 2008 R2

I am in a situation where I need to expand a data file by 750 Gb in order to get to 20% free space in the data files. This is a requirement by our third party application developer in order to ...
1
vote
3answers
61 views

What circumstances could a SQL DateTime column hold an invalid date?

I have been asked to write a series of tests within SQL. Many of them are to test if a certain DateTime columns held in the system is a valid date. Given that the all the fields in question are ...
2
votes
1answer
68 views

Vulnerability in Server from SQL Server 2008 R2

We scanned our systems and got a high vulnerability reported in a server. One of the thing that got flagged was MS12-060, which is related to MS Office 2003 Web Components that gets installed along ...
0
votes
0answers
68 views

Make the linked server ask for password

Till today i am using the linked server with the user of sql having same username and password in both the servers. For now on i can not keep the 2 servers password same for some reason and i still ...
1
vote
2answers
405 views

INSERT Statement error message references column I am not including in query

I am using MSSQL 2008. My INSERT statement is as follows: INSERT INTO [DB1].[dbo].[SpotArtSong](ID) SELECT SNG.S_ID FROM [DB1].[dbo].[SpotArtSong] SPOT INNER JOIN [DB2].[dbo].[nt-art] ART ON ART....
3
votes
1answer
77 views

Group together similar queries in sys.dm_exec_sql_text

I'm analyzing a 2008 Instance that supports a 3rd party app. The app will generate the SQL Code then send it to the Database as an ad-hoc query. I'm using this query (based on a Glenn Berry script): ...
6
votes
2answers
1k views

List All Dropped Databases

I need to confirm if a particular table ever existed in our SQL Server. Is there an existing script or method one can use to list all dropped databases in an SQL Server instance?
1
vote
1answer
235 views

Add values from two existing column into new columns for a total

We have two existing columns in our database named 'ClientIncome' and 'PartnerIncome'. We would like to add a new column named 'HouseholdIncome' We would like to add the values from 'ClientIncome' ...
0
votes
1answer
553 views

Microsoft SQL Server error 14545

I was trying to use copy Database wizard from one server to another to make a certain database identical and i got the error (The @flags parameter is not valid for a job step of type 'SSIS'. (...
-3
votes
1answer
142 views

calculate group by running total in sql server 2008 [closed]

I HAVE SOME SAME SAMPLE DATA IN MY TABLE ID | HOUSENO | AREAR | CURRENT | TOTAL_DEMAND | TOTAL_RECIVED ---------------------------------------------------------------- 1 | 1/10/1 | 0 | 165 ...
2
votes
1answer
660 views

Efficient join technique for two very large tables

I am working on a concept for database synchronization recently. The scenario is as follows: there is a master table "Items" with 1M+ rows in it CREATE TABLE [dbo].[Item]( [Id] [...
0
votes
2answers
27 views

SQL Query with Aggregate Count, where at least 1 of the counts has a value in a field

SELECT PERSON_ID,COUNT(APPL_ID) COUNTS FROM APPLICATIONS GROUP BY PERSON_ID ORDER BY COUNTS DESC The above query returns all persons who have applied for all time (essentially a count of the whole ...
3
votes
1answer
484 views

Okay to run sp_refreshview against all views on a regular basis?

Microsoft SQL Server has a system stored procedure called sp_refreshview which updates the metadata of the view that's passed in. Are there any downsides to running this procedure regularly on all of ...
0
votes
1answer
39 views

Statment running significantly slower than component parts and causes high CPU usage

I am running the following statement in SQL Server Studio 2008: Use Systems select subdirectory from [Systems].[dbo].[ScanningArchiveFileSystem] f where f.FileFlag = 1 and SubDirectory not in ( ...
0
votes
1answer
2k views

Evaluating most frequent values of columns in a single query

I have a table describing my application users with some details such as name, surname, birth date, nationality, email, etc... I would like to known for each property, and for each user category, the ...
0
votes
1answer
58 views

How to link data from two spreadsheets imported to sql

I imported 2 spreadsheets to SQL Server tables. One sheet has data containing persons name & surname, the project they are working on as well as there banking details. The other spreadsheet has ...
1
vote
1answer
614 views

SQL Server logical consistency-based I/O error

I have a job on my live environment DB and the job has failed again this morning reporting SQL Server Error 824 (Soft I/O error, where the OS could read the page but SQL has treated it as corrupt): ...
1
vote
1answer
35 views

Optimizing sorted table for join

In our asset tracking system, I have the following 2 tables: item item_location_date Where item_location_date has the following fields: ild_item (FK to Item) ild_location (FK to location) ...
3
votes
1answer
458 views

GZip existing varbinary(max) column in Sql Server 2008+

I have an existing legacy table that is ~180GB in size due to a client application storing PDF files as varbinary. I'd like to be able to compress that column for all rows using GZIP to help save ...
1
vote
0answers
36 views

need help with multiple column aggregate using pivot in mssql

below is the temporary table for class assessments. this table contain list of class assessment which include the assessment code, date of assessment, total item and passing percentage. create table #...
7
votes
1answer
1k views

How to GRANT permission to execute stored procedure?

I have an AD group that is setup as a Windows Authenticated SQL Login on my SQL Server. On DatabaseA it has the db_datareader and public roles. So the users of this AD group only have read access to ...
0
votes
1answer
253 views

SQL Server 2008 Database encryption

In my company there is MS SQL SERVER 2008 standard edition installed. My problem is we want to encrypt whole database but in standard edition there is encryption like transparent-data-encryption(TDE) ...
5
votes
1answer
2k views

How to repair corruption for “Possible chain linkage problem.”

I have large table with 15 000 000 record and suddenly Select Top stopped working. I use MS SQL Management Studio. This is not working, the query return 0 records: SELECT TOP (10) [Id] ...
1
vote
0answers
354 views

How to reset SQL Server Configuration Manager Windows To Original Windows Configuration (as if it had been newly installed)

I have been looking at a lot of articles, and still cannot come up with an answer to the following. I am running Windows 10 workstation, and have installed SQL Server 2008 R2 Developer. This problem ...
2
votes
2answers
374 views

SQL Server : cannot add subscriber to publication

I am trying to configure replication as per the official tutorial from Microsoft. In the section Create a subscription to the transactional publication, see step 5. The name of the subscriber ...
0
votes
1answer
811 views

How to find Linear interpolation without using pivot and unpivot in SQL Server 2008

id x val ------------- 1 1 8.18 2 3 8.35 3 5 8.51 4 7 8.52 5 10 8.54 6 15 8.57 7 1 8.35 8 3 8.60 9 5 8.70 10 7 8.75 11 10 8.76 12 15 8.82 13 1 8.55 14 3 ...
1
vote
1answer
280 views

How do I determine the time period to which the SQL Server i/o warning is referring?

SQL Server log shows batches of warnings, of the type: SQL Server has encountered 18757 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [T:\.. SQL Server has ...
3
votes
2answers
291 views

Can a diagram be created from deadlock xml extracted from the ring buffer in SQL Server 2008?

In later versions of SQL_Server, we view the event file in the object browser to look at deadlock diagrams. This particular client has SQL 2008 (compatibility 2005) and the only copy of deadlocks have ...
3
votes
2answers
360 views

SQL Server Agent Services not Coming Up

After the Windows Server 2008R2 was restarted after some planned downtime, the SQL Server Agent Service is not getting started. There are multiple SQL Server named instances and Agent Services of non ...
0
votes
2answers
2k views

UPDATE trigger causes deadlock

I have a After Update trigger where trigger insert a value in same table to find a duration column.My goal is whenever end_time value is inserted then trigger will find duration in hh:mm:ss format and ...
1
vote
1answer
79 views

Is there a way to check which computer or IP used a particular script?

As the Title suggest, is there a way to check what IP used a particular SQL Script? The scenario is, there is a recreation script in a email and we hypothesize that somehow, that recreation script ...
5
votes
1answer
721 views

Why can't the transaction log shrink during a backup?

I have a large database in Simple recovery model (vendor requirement) and it took 3 days to back it up to the new hosting company's server. The morning of the third day, the DB went offline because ...
0
votes
1answer
26 views

How are they calculating “AssignmentBaseline0Work” in this query?

I just wanted to understand how they are calculating "AssignmentBaseline0Work". (SELECT ProjectUID, CAST(MAX(CAST(TaskUID AS nvarchar(36))) AS uniqueidentifier) AS TaskUID, AssignmentUID, ...
0
votes
3answers
120 views

Odd behavior of between operator when updating

I have to maintain FRP (Financial Resource Planning) Software that was implemented by some shady consultants (they came up with the jobs), and it stores the data in a SQL Server database. The software ...
-1
votes
1answer
1k views

Rewrite query to not use CONCAT function in SQL Server 2008 R2?

I know that the CONCAT function does not exist in SQL Server prior to 2012. How would i optimize my code to not use the CONCAT function? SELECT dbname = DB_NAME(), ...
0
votes
1answer
983 views

Specifying the IP address to be used when connecting to an instance

I have an application that was compiled with a hard-coded connection string (using the server's IP address '192.168.40.201' and not the server name) to an SQL Server. A backup of the database has ...
3
votes
1answer
1k views

Pivot with rollup in sql server

Is it possible to use a cursor to create a dynamic table and then use those columns to aggregate data in SQL server 2008? For example take the following table. CREATE TABLE Billing ( BillingId ...
1
vote
1answer
863 views

SQL Server Agent Alerts using severity 16

My work is currently deleting lot of stored procedures, views, tables and so on. My worry is that if we miss judge a object and it goes into production that we'll only catch the errors when an user ...
1
vote
1answer
100 views

Two Inner join on the same field to get same data

I'm developing a Report in SSRS for MS CRM 2011. There is a table named StringMapBase which contains Option Set we have declared in different entities. This table has Value and AttributeValue column. ...
-3
votes
1answer
36 views

Sql 2008 r2. Nvarchar(50) how many size does it consume on the Hard drive [closed]

im trying to build a program with so many transaction and inputs. the columns is only nvarchar(50). i want to know how much size does it consume in my hard drive?
2
votes
1answer
174 views

When is autoclose OK to be not OK

By chance, I happened to see that the autoclose feature on SQL dbs (I'm using SQL 2008) shouldn't be on and I found that all of my SQL dbs were set to have autoclose on. I've set autoclose to off on ...