Transact-SQL is the Microsoft extension of ANSI standard SQL.

learn more… | top users | synonyms

-2
votes
0answers
8 views

Extracting Data from multiple archive tables(SQL Server) [migrated]

I am developing a report in report builder 3.0 where i am extracting data from a table dbo.odcalls. now the problem is this table is archived on monthly basis on a seperate database(HN_Backup) and i ...
0
votes
0answers
11 views

How to merge exported SQL Data into restored DB updating only changed rows? [migrated]

we've just moved two databases from one Microsoft SQL Server 2008 instance to another using BACKUP DATABASE on the original system and then RESTORE DATABASE on the new one. Everything went fine but ...
0
votes
1answer
30 views

Deal with dashes and spaces in T-SQL String functions

I am searching for the occurrence of a string in a field on a table. The search statement is done through dynamic sql with my statement template looking like this. SET @sSQL = 'UPDATE ...
0
votes
1answer
133 views

MSSQL: Roll back DB from backed up .MDF/.LDF

I've mistakenly deleted a table in Microsoft Server Management Studio, and the restore function was not properly configured. I have older .mdf and .ldf files of the particular db with the deleted ...
0
votes
1answer
71 views

Rebuilding MS Agent Jobs from MSDB table

This morning our DB server died. They restored backups to another server but these didn't include the many MS Agent jobs. Anyway I have managed to get a backup of the old MSDB table where all the ...
6
votes
1answer
75 views

Copy SQL Server Databases

I am looking to "move" databases to different servers with minimal disruption to data and service. These databases vary in size from 5GB to 140 GB. I've seen and even used some of the various data ...
0
votes
1answer
184 views

MSSQL: Restore a DB to the latest file in a backup set

I have a MSSQL 2008R2 DB in SIMPLE recovery mode that is periodically backed up with BACKUP DATABASE MyDB to DISK = 'Z:\MyDB.cbak' WITH COMPRESSION This runs periodically, creating multiple ...
1
vote
2answers
178 views

Spoof database connection to be local instead of remote

I am trying to connect one of our clients "as is" programs to a remote database instead of a local one, they think that they have coded it to work that way, but for some reason the program crashes ...
3
votes
1answer
2k views

dbcc checkdb('XYZ', REPAIR_ALLOW_DATA_LOSS) indicating that XYZ is already open and can only have one user at a time

I have a DB, XYZ, that had a corrupt log file and now since the log file is totally unusable this is rendering this specific DB unusable and I need to be able to rebuild it. I've already done a lot ...
0
votes
0answers
230 views

What is the difference between a Linked Server retruning the message “Out of memory” and “MySQL client ran out of memory”

we have a Microsoft SQL Server running a bunch of jobs every hour, half the jobs call local stored procedures while the other half calls stored procedures on a MySQL Database in the past we normally ...
0
votes
1answer
53 views

SQL for duplicating parent and children records

I'm trying to figure out the best way to create a SQL statement (in SQL Server) to duplicate a parent record and all the children records. I have the below as an example; -- duplicate the order ...
1
vote
1answer
117 views

Unable to change schema of a Publication Database using TSQL (Ready-roll): being forced stop replication

I am trying to apply schema changes using T-SQL (using Ready-Roll database source control tool with Octopus Deployment) to a Publication database however it is forcing me stop Replication for the ...
0
votes
1answer
136 views

Information on TSQL DelBkUps parameter

We are using a TSQL script to back up our database every night, and in this scrip it uses -DelBkUps 1DAYS to remove the old disk copy of the backup. However we are running out of disk space at the ...
3
votes
3answers
794 views

if I select more than 15 fields, I get catastrophic failure

Using sql server 2012 I am connecting to a linked server with Pervasive SQL on it. When I do select * or select field1,field2,field3....field15 I am getting this error: Msg 7399, Level 16, State 1, ...
0
votes
1answer
91 views

MS SQL Server 2008 R2 downloads for practice

I plan to download MS SQL Server 2008 R2 SP1 - Express Edition. I know I need one of the two applications to download with it: 1) SQL Server 2008 R2 Express with Tools and 2) SQL Server 2008 R2 - ...
0
votes
1answer
3k views

SqlCmd : Login timeout expired from localhost

I've setup the instance SQLEXPRESS via SQL Server 2008 R2 installation, added a security login with all server roles, one called 'sqluser'. The server authentication is SQL Server and Windows ...
1
vote
1answer
131 views

Can I split open/divide a large MS SQL disk media set?

MSSQL 2005 An old maintenance plan was backing up a database to a single disk based media set with append. It contains numerous full backups of about 2GB each. I now have a single media set file ...
4
votes
1answer
77 views

Restore database from backup file, where file name matches reg expression

I am doing the below : RESTORE DATABASE UAT FROM DISK = 'E:\Databases\backup\MY_LIVE_20120720_070001.bak' WITH REPLACE But I want to be able to use a file location that ignores the numbers in the ...
7
votes
1answer
7k views

How do you get the minimum and maximum memory allocation of a SQL instance using T-SQL?

I'm managing about 90 SQL Server instances and need a query to determine how much memory has been allocated to each instance. Please note, I'm not asking how to set it, just to see what it's currently ...
0
votes
2answers
461 views

SQL Server 2008 - Backup Databases with more than 50 characters

I am using T-SQL to backup my MS SQL 2008 databases: BACKUP DATABASE @name TO DISK = @fileName The problem occurs when it comes to the databases with their name length greater than 50 characters ...
0
votes
1answer
124 views

Creating a SQL Server 2005 User Login to Backup and Restore on a Single Database

I have a SQL Server 2005 database server with multiple databases on the server. I need to create a user login 'test1'with the following restrictions: Login 'test1' should be owner of database 'ABC' ...
2
votes
1answer
1k views

How can I create a database backup replacing the previously created backup file?

If I backup my SQL Server 2008 R2 database with the following command set @BackupFileName = N'C:\backup\Production-db.BAK' BACKUP DATABASE [Production-db] TO DISK = @BackupFileName and run the ...
1
vote
1answer
49 views

SQL Server database backups storage

This is rather basic question. We do backups of all the databases on the server and size around 20GB every week. All that is scheduled (shrinking,Backing up) We store them on Network drive. Now ...
0
votes
1answer
232 views

Error on creating job SQL Server 2005

Error on creating job: Unable to cast object of type 'Microsoft.SqlServer.Management.Smo.SimpleObjectKey' to type 'Microsoft.SqlServer.Management.Smo.Agent.JobObjectKey'. (Microsoft.SqlServer.Smo) ...
4
votes
1answer
171 views

What is the best way to replicate a large number of SQL 2008 DBs to a failover instance in Amazon Web Services

Our application creates a new sql database for every customer and we will be continually adding new customers and creating new databases every day. These databases can range in size from 3 megabytes ...
0
votes
1answer
23 views

MSSQL2005: is there a sproc to set the default distribution agent profile?

I'm trying to work out how to set the default profile for the distribution agent programatically using t-sql. I see there is the sp_help_agent_profile and sp_help_agent_default sprocs which allow you ...
1
vote
3answers
226 views

T-SQL query to schedule the back-up by using Transactional logs!sql server 2008

Hello i am new to sql server and i don't know any thing about backing up database using tsql i am trying to find out the procedure to do following but unsuccessful. T-SQL query to schedule the ...
0
votes
1answer
62 views

Does SQL Server/ T-SQL really adhere to E F Codd;s Relational Model of Databases? [closed]

The relational model as proposed by Codd heavily relies on Set Theory and Predicate Logic. Does SQL Server really adhere ?
0
votes
2answers
247 views

How to keep Ms-SQL database always UP: mirroring or replicationg?

I have several Tb databases in the Windows Server 2008. I would like to keep alive the service when the server is failing or the raids are needs to be rebuild. Which model to choose Replication or ...
1
vote
1answer
565 views

GRANT PERMISSIONS ON SQL Server 2008,

I got the following issues while querying a database, and somehow I am not yet able to resolve it. 1. The SELECT permission was denied on the object So I executed the following command on SSMS ( I ...
1
vote
1answer
762 views

SQLS Timeouts - High Reads in Profiler

I've audited a SQLS2008 server with Profiler for one day.. the overhead didn't seem to trouble this new client my company has. They are using a legacy VB6 application as a front-end. They're ...
0
votes
1answer
3k views

Attaching MDF File that appears to have been truncated by the OS

I've got an MDF and LDF file from a dead server that I'm trying to attach to another instance of SQL Server. However running the following create database statement USE [master] GO CREATE DATABASE ...
0
votes
1answer
160 views

Verify SQL Server Mirroring failover is successful

We have database mirroring configured as "high safety without automatic failover" on 23 databases. Both partners contain principal and mirror databases. I created two scripts that would failover ...
0
votes
1answer
43 views

Automatically drop FK's to re-create a table and then automatically put the FK's back

I have a number of incidents where SSMS tells me table recreation is required, even for something simple like making a column nullable. If this table is referenced by FK's, I would ideally like to ...
0
votes
3answers
470 views

query maintenance plan schedule

can I query the schedule of a maintenance plan? I want to automate checking that backups were run. Thanks
0
votes
1answer
295 views

how to show table data as columns in tsql

I have 2 tables in sql-server 2005 express DB 1. ItemSize Table CREATE TABLE [dbo].[ItemSize]( [ID] [int] IDENTITY(1,1) NOT NULL, [iSize] [int] NULL ) ON [PRIMARY] 2. SalesLog Table CREATE TABLE ...
0
votes
1answer
119 views

T-SQL Maintanance Plan

This runs as a weelky Maintanace Plan but I am not sure what it actually does. Any clues? declare @dt datetime select @dt = getdate()-30 exec msdb.dbo.sp_delete_backuphistory @dt EXEC ...
3
votes
2answers
1k views

SQL 2008 - Transaction log is full

We have some issues with the transaction log files We have SQL Server 2008 Our situation: Every night, we have many jobs that runs on the server by SQL Agent Some jobs stop because the transaction ...
0
votes
2answers
126 views

Cannot drop a table in SQL 2005

I have a SQL Server 2005 SP3 box that one of my developers created a temp table on that we cannot seem to remove because it somehow got brackets in the name of the table? SELECT Name, object_id FROM ...
1
vote
1answer
734 views

How do I get SQL Profiler to show statements with column names like 'password'?

I'm profiling a database just now and need to see the UPDATE and INSERT statements being executed on a particular table. However, because the table has a 'Password' column the SQL Profiler is being ...
1
vote
1answer
1k views

T-SQL for autogrowth of multiple data files

I can't seem to figure out the problems with my script to alter SQL Server 2008 database and file growth. There are two data files and a log file, all which need to have Autogrowth ON. Does this look ...
0
votes
1answer
1k views

SQL Passing a List<int> as a SqlCommand Parameter

can i pass a List into a SqlCommand as a paramter? DELETE FROM MyTable WHERE Key IN @MyParam command.Parameters.AddValue("MyParam",MyList); i realize i could just loop over the list but i was ...
1
vote
2answers
285 views

TempDB is Full Issue for SQL Server 2000

Even though the log file shows that there is over 1 GB of free space, we start receiving an error message every 3 or 4 days saying that the TempDB file is full. I know cursors impact the TempDB file, ...
0
votes
1answer
438 views

SSRS/Sharepoint - Reports made in Report Builder not being list in Sharepoint Web Part

I followed the steps here to integrate reporting services with sharepoint in native mode. I made a page in Sharepoint with the report explorer web part and everything is working. The issue is when I ...
2
votes
6answers
4k views

Restore Database User to Server Login on SQL Server 2008

We have a database backup we need to restore on a server that doesn't have the logins for the database... all the users are in the database though so we are hoping we can restore it this way? I've ...
1
vote
1answer
289 views

Deleting text files based on date through SQL Server T-SQL

I need to delete .txt files in a particular directory based on date. Can I do this through T-SQL?
3
votes
1answer
319 views

Overflow when converting int to datetime

I'm currently importing a table from MYSQL to MSSQL. There's a column there for storing date, only it's stored as an number. When i import it to MSSQL i get it in as an int data type. When i try to ...
1
vote
2answers
150 views

select values must match the number of insert columns

I have the following query... ive checked and rechecked the types and number of values in both the insert into and the select statements. I can't figure out what is wrong. insert into ...
0
votes
1answer
71 views

How to use variables in inner queries?

I am trying to build a query that has something like this select id, (select top 1 create_date from table2 where table1id = t1.id and status = 'success') ...
0
votes
1answer
174 views

Object definition in MS SQL 2000

I was looking for a way to generate object definition (in my case stored procedure defintion) in SQL 2000 like I can get in SQL 2005 : select definition from sys.all_sql_modules.