Microsoft's SQL Server is a relational database management system (RDBMS) that runs as a server providing multi-user access to a number of databases. It originated from the Sybase SQL Server codebase, which is why both products use the extension of SQL called Transact-SQL (T-SQL).

learn more… | top users | synonyms (2)

0
votes
1answer
202 views

Recover data from accidental update

I have done an incorrect update and hence I restored the DB with a backup from the previous day. I have not taken a copy of the corrupted data before restoring the backup. Is there any possibility to ...
2
votes
2answers
2k views

Using T-SQL to dynamically create Extended Properties

I would like to add Description and Status in the Extended Properties of a large database to all my tables and columns so that someone can fill in the values. It would be very laborious to add these ...
1
vote
3answers
72 views

What is the benefit of two FK, when one of them can be deduced from another table?

The third party Microsoft SQL Server database I'm working on is using the structure which can be illustrated with this example: Let's take three tables: Shop which is the topmost level and ...
0
votes
2answers
1k views

Running a Job from a Stored Procedure in another server?

How can I run a job from another server using a stored procedure? Let's say server 1 : db1 server 2 : db2 username : testssis password : testssispass sqljob found in db2: job1 Here is the part ...
2
votes
1answer
3k views

INSERT INTO #temp taking 1000 times longer than SELECT INTO #temp

I am shredding an xml blob that is being passed to me and loading a temp table for further processing. One of the things I noticed while working through the process is that performing an INSERT INTO ...
2
votes
2answers
393 views

Can view adapt to implicitly reference user's default schema?

A bit of background. I have a base application and most clients use it as standard. However some clients have small code and database customisations. Each of these clients has their own branch and ...
4
votes
2answers
7k views

After moving database (backup, restore), I have to re-add user

I occasionally move a database (SQL Express 2012) from a development machine to a server, or vice versa, using database backup and restore in SQL Server Management Studio. Whenever I do this, the ...
1
vote
2answers
1k views

Shrinking log files with SMO

I know I can use the following t-SQL to shrink a log file: BACKUP LOG db1 TO DISK = '\\server\share\db1_log1.trn'; DBCC ShrinkFile([db1_log], 0); How do I do the same with SMO? I tried: $server = ...
7
votes
2answers
3k views

How best to maintain SQL log file sizes

I'm somewhat of a new DBA and I'm managing a SQL Server 2012 instance that has a fair amount of activity. I'm running in Full Recovery mode because we need point in time recovery. Right now, I'm ...
2
votes
3answers
2k views

Msg 21, Level 21, State 1, Line 1 Warning: Fatal error 9001

We recently moved to a SQL Server that's on a VPS and every now and then we get a 9001 error when trying to do an update. Rebooting the server fixes the problem. I changed the maximum server memory ...
4
votes
2answers
2k views

Error while taking backup using SSMS

I am using SSMS. I have about 5 logins. For a particular login I have all server roles other than sysadmin. It is a sql login (not windows authentication). In user roles it has all permission for ...
2
votes
1answer
386 views

Paging in SQL Server 2012 vs. previous versions

I have used lot of paging code in SQL Server 2008. The format is With CTE( -- SQL With RowNumber ) -- SELECT ALL Page N This is working fine. But I have heard that SQL Server 2012 has better ...
2
votes
2answers
1k views

Executing a table type parameter stored procedure or function within a select clause

Just a quick question. I have a stored procedure that takes two parameters, a varchar (table name) and a tabletype (range of primary key ID values), and uses them to return all FK connections to and ...
1
vote
0answers
55 views

Slow Response: Initially it's slow and on subsequent restart of the host it works normally

We have SQL Server 2008 R2 32 bit installed on the following hardware: CPU: Intel Xeon E5310 @ 1.60 GHz RAM: 4 GB HDD: Raid Server: HP Proliant 120 OS: Microsoft Windows Server 2003 R2, Standard ...
1
vote
1answer
112 views

Key lookup partition pruning

I have a query inner joining multiple tables all of which are partitioned and have 10 partitions each: SELECT A.COL1, B.COL2, C.COL3 FROM A INNER JOIN B ON A.ID = B.ID INNER JOIN C ON A.ID = C.ID ...
0
votes
1answer
509 views

Using a table swap for a data load but I have triggers

I'm working on re-writing a table load. The current method is a daily job that deletes from the table, then re-loads it. The complaint is that if the load fails the users have no data. The users ...
8
votes
2answers
5k views

ALLOW_SNAPSHOT_ISOLATION and READ_COMMITTED_SNAPSHOT

Most of the forum and example online always suggest to have both ALLOW_SNAPSHOT_ISOLATION and READ_COMMITTED_SNAPSHOT set to ON whenever someone is asking snapshot, row versioning or similar question. ...
1
vote
2answers
129 views

Dictate edition during unattended install?

I am creating an automated build of SQL Server installation which is run from a batch file. Is there any way to change the registry, or other method, of deciding which SQL Server edition (Either ...
0
votes
2answers
371 views

Generic SQL Job Scheduler for multiple RDBMS's?

I have been searching for an answer to this, but can't seem to find anything. So my problem is this - we have an environment with MS SQL Server 2008, MySQL, and RedShift, and have some complex ...
1
vote
1answer
73 views

How can I identify the number of times a View is called in SQL Server?

How can I identify the number of times a View was called in SQL Server? I am looking for an answer that is similar to this below which shows how it can be done for stored procedures. How can I ...
0
votes
3answers
160 views

T-SQL Issues With Defining 'AS'

I am creating a fully dynamic application but have ran into a bit of a hiccup. There are multiple 'undefined' fields that can be defined by the users. The only problem is redisplaying them. Currently ...
2
votes
2answers
2k views

Can I copy the table structure of a #temp table to a new physical table?

I have a #tempTable that was created using SELECT * INTO #tempTable FROM OPENROWSET('Microsoft.Ace.OLEDB.12.0', 'Excel 8.0;Database=MyFileName.xls', 'SELECT * FROM [Sheet1$]') Is there an easy way ...
0
votes
1answer
150 views

Query not returning items in the original order

Consider the following set, which is the content of the table variable @ProductContent that was just created for the query that runs against it: Notice how items are sorted by media_type_id, ...
2
votes
2answers
96 views

Identity proceeds as normal, but with a leading 10…?

So I noticed a very strange thing when tinkering with my database. I had just inserted a row which was ID 47, then after inserting some more, I realised that somehow my Identity had skipped all the ...
1
vote
3answers
677 views

Is transaction log lost after backup on SQL Server?

I have database that has regular nightly backup (full recovery, full backup). I noticed some irregularities in data and wanted to check out transaction log to see what happened. I restored earlier ...
2
votes
2answers
1k views

SQL Server Replication: “ALTER TABLE ALTER COLUMN” is not propagated to subscribers

We are running SQL Server 2008 R2 SP1 as publisher & distributor, and SQL Server 2005 SP3 as subscriber. The replication of schema changes is activated, and the replication has been running for ...
0
votes
1answer
44 views

Getting the name of the column that was used in a join?

This may be a bit of a weird question: Our project's datamodel has multiple tables with multiple columns any one of which can be used to join with one settingtable. The question here is how to get ...
1
vote
1answer
407 views

How to enhance the SQL query performance for thousands and millions of huge data

Now Our team are involved in a forum data analysis project. Its target data is so huge(I think). We should find the potential informations from it. Such as the popular products,the customers ...
1
vote
1answer
151 views

Can I drop and recreate a system extended stored procedure?

I am having a problem with one of my servers where I cannot execute xp_cmdshell under certain conditions. The full problem can be found here problem running xp_cmdshell when Application Name set in ...
1
vote
1answer
124 views

SSIS Suddenly Requires DCOM Permissions

I have recently deployed some SSIS packages. I made a job to run them one after another. However, I am now allowed to use MSDTC. So I chained the jobs together like I have done before, but now I am ...
1
vote
1answer
7k views

How to override SSIS 2008 package config file path?

I have an SSIS 2008 package with a single configuration named Config. I would like to use the dtexec /SET switch to change the path of the config file that it uses so our developers that build the ...
1
vote
1answer
515 views

What are the hazards of upgrading a SQL Server service pack?

Are there any specific pitfalls/standard considerations when upgrading an instance of MS SQL Server to a higher service pack? Is there anything special that is generally done besides making sure there ...
1
vote
0answers
34 views

Does SQL Server place shared locks on scanned records when using REPEATABLE READ [duplicate]

Assume a SQLCmd session which is using transaction isolation level REPEATABLE READ. In this session I start a transaction and execute an UPDATE statement with a WHERE clause on a non indexed column. ...
4
votes
2answers
428 views

Does SQL Server place shared locks on scanned records when using REPEATABLE READ

Assume a SQLCmd session which is using transaction isolation level REPEATABLE READ. In this session I start a transaction and execute an UPDATE statement with a WHERE clause on a non indexed column. ...
3
votes
1answer
543 views

Does creating / updating statistics have an impact on the transaction log?

Does creating or updating statistics, assuming on a large table, create significant (or at all) transaction log churn? I would think not as it does not change the underlying data or structure. If it ...
6
votes
1answer
3k views

Can I shrink the transaction log file on a mirror database?

This is a followup question to a previous question about why I couldn't shrink the log file on the principal database. To make a long story short, I setup database mirroring but forgot to make sure ...
7
votes
3answers
546 views

Header and line item data source mismatch

I'm working with a star schema for a data warehouse and I am running into a problem with header and line items from different data sources. CREATE TABLE DataSourceAHeader ( OrderId INT NOT NULL ...
5
votes
1answer
2k views

How do I shrink the physical Transaction Log file when it's the principal in a mirror?

We setup database mirroring over the weekend, and forgot to re-enable the job that backs up the transaction logs. When I came in this morning, the transaction log had ballooned to 58GB, and was taking ...
3
votes
1answer
785 views

Referencing system views in SSDT?

I've imported a database into SSDT containing a reference to a system view (specifically, sys.columns). The problem is, that I then get warnings about unresolved references when I build the project ...
-1
votes
1answer
363 views

Deleted rows but database size is still large [duplicate]

I am using SQL Server 2008 Express which has maximum database size limit of 10 GB. Every month I hit the limit and the application can't store more data. To overcome this problem we run a job every ...
7
votes
2answers
2k views

Updating a table with more than 850 million rows of data

I have been tasked with writing an update query to update a table with more than 850 million rows of data. Here are the table structures: Source Tables : CREATE TABLE [dbo].[SourceTable1]( ...
2
votes
3answers
3k views

Getting 'specified network password is not correct' when trying to change password

After changing my network password, I went straight to the SQL Server Configuration Manager (SQL Server 2008) to update the passwords for the SQL Server and SQL Agent services. (Background info: the ...
0
votes
1answer
792 views

How to fix filestream?

I have SQL Server 2008 R2, when I try to access any value stored in filestream I get the following error: Msg 233, Level 20, State 0, Line 0 A transport-level error has occurred when receiving ...
1
vote
0answers
157 views

how can Audit All Login To SQL Server BY ODBC Connection

I MAke Report By Access Form On SQL database I make ODBC Connection For login To Update the data From Database Now I want Audit All logins To know who make Update today Or who make Update Now ? ...
2
votes
2answers
140 views

After moving a DB to a new disk, the log file isn't dumping data to the DB

I recently added a new disk to our SQL Server box and followed these steps from Microsoft to move the .mdf file to the new drive. I opted to leave the log file on the original disk for performance ...
0
votes
0answers
182 views

SSIS Data Flow Task Slows Down [closed]

I am trying to transfer data from one table to another in the same database using SSIS. It seems to go fine (I can see the record count going up) until it gets to about 30 million records, then it ...
1
vote
1answer
1k views

How do I specify a linked server to a remote database over tcp/ip?

I have a remote database running on an IAAS provider and I need to be able to execute joined queries from my local workstation. I am having trouble because when I attempt to create the link it is ...
0
votes
1answer
100 views

Accidentally Deleted SQL Server Log Reader Service Account [closed]

Uh, I accidentally deleted the Security Login in my SQL server that allows for the reading of log files. Now, I can no longer see any of the log files in my SQL Server Logs node of SMSS:( What ...
1
vote
1answer
715 views

sql web-application Connection String for member of a domain group [closed]

In my testing environment, I have a local sql express (2008R) server instance. I've created a domain group and given it a login in the database. But now, how do I consctruct the connection string ...
4
votes
1answer
312 views

Accidentally turned down SQL memory too low to log in, how do I fix?

I turned down the Maximum Memory of the my SQL Server instance to 15 MB... Now I can't log in to increase it. How can I increase the maximum memory without logging in? Version is SQL 2008 R2