Questions tagged [tsql]
Transact-SQL is the Microsoft extension of ANSI standard SQL.
59
questions
0
votes
1
answer
43
views
Alter Database recovery model & Shrink log, DB dynamically
I normally do on daily basis these operations in GUI
Alter database to simple recovery model
shrink log file
shrink database file
Alter database to full recovery model
I can understand script but ...
0
votes
1
answer
112
views
Detect when scaling Azure SQL database via async TSQL command has failed
I have a scheduled task which runs each night to copy an existing production Azure SQL db to a dev environment. Once copied, I scale the database down (actually I put it into an elastic pool to share ...
- 111
0
votes
0
answers
684
views
php 7 on Fedora 27: Uncaught Error: Call to undefined function sqlsrv_connect()
I have installed sqlsrv & pdo_sqlsrv per
https://github.com/Microsoft/msphpsql/tree/PHP-7.0-Linux
I created a test program that works perfectly from the command line but not when viewed in a web ...
1
vote
1
answer
285
views
How to take the column name dynamically with dates
I have column name with dates which comes from a table and changes from current month to last 41 month. and i have to write the column name again and again to reach the desired output. Is there any ...
- 11
1
vote
1
answer
57
views
Why do I get different memory numbers using TSQL versus the GUI?
I'm preparing to bump up our max server memory value after a RAM upgrade on our server. I'm testing the commands out on a test SQL Server and I'm not sure why I'm seeing different results with T-SQL ...
- 195
0
votes
1
answer
432
views
Interpreting a Deadlock Log / XDL file
I've generated the following XML + XDL:
<deadlock>
<victim-list>
<victimProcess id="processc1eaf13468" />
</victim-list>
<process-list>
<process id="...
- 33
0
votes
1
answer
24
views
Convert into multiple data ranges
I have employee data link below
This is one example in the file a have multiple lines. Initial input can have more than 2 lines for each employee but convert logic will be the same.
Thank you for ...
0
votes
1
answer
292
views
Is it possible to connect to a PostgreSQL server using FreeTDS driver and tsql?
Can I make a connection to a PostgreSQL server using FreeTDS, more specifically tsql command?
I've been trying for a few days now, using many different configurations. Even though I am able to ...
- 3
0
votes
1
answer
8k
views
Bring back to restoring state an online database
I configured logshipping for a database. I did some tests and tried a controlled failover.
During log shipping secondary db is in restoring state.
To have a controlled failover i stopped the primary ...
- 107
0
votes
1
answer
1k
views
T-SQL Code to backup database with multiple files failed
I'm trying to write an automated script that will allow me to backup the database to a NAS with multiple files. I just started to use T-SQL.
My objective is to automatically calculate the size of the ...
- 97
0
votes
1
answer
4k
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 #...
- 483
1
vote
1
answer
534
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 ...
- 113
3
votes
1
answer
8k
views
How to rebuild 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 ...
- 131
6
votes
1
answer
128
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 ...
- 483
4
votes
1
answer
4k
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 ...
- 6,786
1
vote
3
answers
1k
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 ...
- 123
7
votes
2
answers
34k
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
1
answer
571
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
1
answer
211
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 ...
- 11
1
vote
1
answer
430
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 ...
- 169
3
votes
3
answers
5k
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, ...
- 455
0
votes
1
answer
442
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 - ...
- 1
2
votes
1
answer
16k
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 ...
- 48
1
vote
1
answer
265
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 ...
- 161
4
votes
1
answer
103
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 ...
- 460
16
votes
1
answer
53k
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 ...
- 275
1
vote
2
answers
3k
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 ...
- 1,908
0
votes
1
answer
239
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' ...
Stephen
2
votes
1
answer
2k
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,072
1
vote
1
answer
74
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 ...
- 111
0
votes
1
answer
377
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)
...
- 193
0
votes
1
answer
75
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 ...
- 714
1
vote
3
answers
380
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 back-...
- 121
0
votes
1
answer
99
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 ?
- 111
0
votes
2
answers
303
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 ...
- 555
1
vote
1
answer
1k
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 ...
- 357
1
vote
1
answer
1k
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 ...
- 113
0
votes
1
answer
7k
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 [...
- 103
0
votes
1
answer
676
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 ...
- 165
0
votes
1
answer
50
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 ...
- 483
0
votes
3
answers
2k
views
query maintenance plan schedule
can I query the schedule of a maintenance plan? I want to automate checking that backups were run.
Thanks
Jim
0
votes
1
answer
340
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 [...
- 145
1
vote
1
answer
216
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 msdb.dbo....
- 199
3
votes
2
answers
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 ...
- 31
0
votes
2
answers
143
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 ...
- 898
1
vote
1
answer
2k
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 ...
- 7,787
1
vote
1
answer
2k
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 ...
- 165
0
votes
2
answers
4k
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 ...
- 245
1
vote
2
answers
467
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, ...
- 223
0
votes
1
answer
558
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 ...
- 489