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).
2
votes
4answers
27 views
Backup very large table in SQL Server
everyone.
I'm deeply sorry if this is an amateurish question, but I have to update certain values of a large table (for the sake of a presumed example, it is called 'Resource' and it is over 5M rows) ...
3
votes
3answers
80 views
Backup very large table
I'm deeply sorry if this is an amateurish question, but I have to update certain values of a large table (for the sake of a presumed example, it is called 'Resource' and it is over 5M rows) and thus I ...
1
vote
1answer
371 views
SQL Server Alert Based On Severity Level Not Working
I am new to SQL Server alerts, and I am trying to setup an alert That will run a job and send a notification, based on severity level.
In order to test if the alert works correctly I have downloaded ...
7
votes
2answers
75 views
Is there any way to prevent the memo structure from being pruned?
We know that the memo structure is pruned and some expensive alternative plans are discarded during optimization. I was wondering if there is any way to prevent this and let the optimizer just ...
-1
votes
1answer
45 views
SQL agent job step's connection managers
Is there any way with T-SQL to list the connection managers that are used in an SQL Agent Job step?
6
votes
1answer
41 views
Dropping Hypothetical Indexes
In the past I thought I'd deleted hypothetical indexes using either a DROP INDEX statement for clustered indexes and DROP STATISTICS statement for non-clustered indexes.
I have a database that is ...
4
votes
1answer
69 views
General tool to load dump files
I am a big fan of Postgres both for its price but also for its features.
I am going to have to need to upload into it both Oracle dump and SQL Server files.
I will try to ask and beg for plain .csv ...
7
votes
1answer
201 views
Hard limitation for merge replication article count?
A bit of background. We developed an app that uses merge replication. Right now, we are currently publishing about 212 articles, and everything is fine, as it appears to fall into the 256 article ...
1
vote
2answers
46 views
Update my local db based on scripts executed in my server database
I need to create an solution that update my local db version based on changes that was made on my server database.
First, I need to check the differences that has exists between these databases and ...
2
votes
1answer
40 views
Start service error code 3417 with SQL Server 2012
I cannot start the SQL Server 2012 service. The event log says the service-specific error code is 3417.
In the Event Viewer:
The SQL Server (MSSQLSERVER) service terminated with the following ...
3
votes
1answer
27 views
SP with Encryption Read/Writes Stats
If a stored procedure in SQL Server 2005 is created with the syntax "WITH ENCRYPTION" ,are the reads and writes of the encrypted stored procedure recorded in the sys.dm_db_index_usage_stats? I am ...
1
vote
1answer
36 views
Startup procedure for availability replica becoming primary
Is there any way how to create a startup T-SQL script/procedure for starting availability replica (when state has changed from secondary to primary)?
I want to disable SSIS jobs specific for related ...
1
vote
0answers
18 views
PowerPivot doesn't install in SharePoint mode on an Active/Passive Cluster
We a provisioning a new SQL Failover Cluster. We have successfully configured the SQL instance, and have moved on to configuration of the PowerPivot for SharePoint instance. We run the configuration ...
1
vote
3answers
612 views
Taking a backup of a single table in the SQL Server
I know how to take a backup of a database in SQL Server and restore it. But how can I take a backup of a single table, and then restore it? Is it possible?
I have a database with two different names ...
0
votes
1answer
26 views
SQL Server TDE - add master key and certificate [closed]
I want to add multiple master database encryption keys and certificates.
Can I do that?
3
votes
0answers
18 views
custom population of full text index
Is there any way to populate a full text search index without storing the backing information in the database?
I don't mean using remote-blob-storage or FILESTREAM types. The backing information is a ...
3
votes
2answers
564 views
Linked Server Query Results: OPENQUERY vs Distributed Transactions
I have two databases. They are on different servers. One is named REPORTDB and the other is named TRANSDB. TRANSDB is referenced as a linked server on REPORTDB named TRANS.
I execute a query like ...
-1
votes
1answer
26 views
Why does fn_my_permissions return empty rowset? [closed]
If I run this query:
select * From fn_my_permissions(NULL, 'SERVER')
This is what I'm getting:
entity_name subentity_name permission_name
------------- --------------- ...
2
votes
0answers
27 views
Server permissions of an activation stored procedure of a Server Broker queue
I have a stored procedure that queries the sys.dm_exec_requests view. In the stored procedure the view only returns one row, while the stored procedure needs to see all of them. The MSDN article on ...
2
votes
1answer
34 views
Is this a good strategy for importing a large amount of data and decomposing as an ETL?
I have a set of five tables (a highly decomposed schema for an ETL if I understand the nomenclature) that I'm going to load via bulk import, then run some inserts from those five tables into a SLEW of ...
4
votes
1answer
63 views
Is it “legal” TSQL to CREATE TABLE #SomeTable and then DROP TABLE #SomeTable repeatedly in a script?
Is it "legal" TSQL to CREATE TABLE #SomeTable and then DROP TABLE #SomeTable repeatedly in a script?
I've got my code sort of segregated as "coherent blocks" that I can insert into a longer ...
24
votes
6answers
4k views
What's the difference between a CTE and a Temp Table?
What is the difference between a Common Table Expression (CTE) and a temp table? And when should I use one over the other?
CTE
WITH cte (Column1, Column2, Column3)
AS
(
SELECT Column1, Column2, ...
5
votes
3answers
47 views
Should we periodically 'refresh' the mirrored copy of a SQL Server database?
When you have SQL Server mirroring set up, do you trust that your mirror database will stay the same as the primary, even after months and months of mirroring? I know we all check the status, ...
1
vote
3answers
117 views
Logging queries and other T-SQL
I would like to know if SQL Server 2008 R2 has a default logging scheme for SELECT statements (or any other T-SQL for that matter).
If yes, where can I see it? If not, how could I set it up?
1
vote
2answers
84 views
Database running out of space
My database has 16MB of space left.
I used to just truncate as I was taught but I found these links that advise against truncating:
...
3
votes
1answer
167 views
Applying user-defined fields to arbitrary entities
Currently we have an old (rather crude) system that has user-defined fields, which are mapped against rows in arbitrary tables. This was an after-the-fact modification based on a customer request, and ...
2
votes
1answer
21 views
Use users / logins from another database
Is it possible to use logins/users from one database(F) in another(S) one? I know I can copy it but, when I add user to (F) database I have to copy again them all (or add the same user to (S) ...
1
vote
2answers
38 views
Replicating schema changes between production and development databases [duplicate]
Not too sure if this would be a good and proper way to go about things, so I apologize if I'm kind of far off.
Currently running Microsoft SQL Server 2005, and in this we have a database for a ...
3
votes
1answer
44 views
Convert SQL Server 2012 SSIS package to 2008
I've created a few SSIS packages in SQL Server 2012 that I need to run on SQL Server 2008. I've been told that it is impossible in their current state.
Is there a way to convert the 2012 packages to ...
7
votes
3answers
328 views
Worse performance on a new server
We've been on a dedicated server (single quad-core, 6 GB RAM) and are moving to a new dedicated server (2x hex-core, 32 GB RAM). Both are Windows Server 2008, SQL Server 2008. The performance on the ...
-4
votes
0answers
44 views
How to group by the data by monday to sunday as a week? [closed]
How to group by the data by monday to sunday as a week ?
lets take one example :
take month march 2013, if i use the week function then it will group by
week 1 (3 days data)
week 2 (5 days data)
...
7
votes
1answer
120 views
Automatic decimal rounding issue
The question is relatively simple. I need to calculate 3 columns where the mid results are huge decimals, and I'm running into a problem early on with SQL Server basically rounding the decimals ...
1
vote
1answer
28 views
mdw_purge_data_ long runtime
I have a SQL Server 2008 R2 that is running several databases and has also been configured as our central MDW.
When the management data warehouse is created it creates a number of agent jobs one of ...
1
vote
3answers
54 views
Restore database with zero size log file [duplicate]
SQL Server backups contain meta-data about the size of the original log (LDF) file.
So if you backup a database that has a 5GB LDF file, when you restore it, you will get an (empty) 5GB LDF file. ...
1
vote
1answer
28 views
Full-text search migration 2005 -> 2012 (.ndf file?)
So I'm trying to figure out the details for migrating from SQL Server 2005 to SQL Server 2012.
I tried to do restore and backup of a DB from 2005 to 2012. This worked well and for the text search ...
1
vote
1answer
67 views
Use a condition to limit the databases a policy checks
I'd like to create a Policy that checks, on demand, one database in an instance for tables that have no index. I can see how to create a Condition, linked to the 'Database' Facet and checks for my ...
2
votes
2answers
87 views
How to check if I am hitting the Express Edition size limit?
I am confused. AFAIK SQL Server 2005 Express has a limit of 4GB database data size. However I have the following results from sp_spaceused:
How can I check if my DB is hitting the size limit?
Is ...
2
votes
0answers
30 views
Coerce columnstore to operate in batch mode
I want to try the column store index in SQL Server and im trying to get my queries to run in batch mode, i rewrote my query this way( it had an outer join before) but the execution plan still show ...
2
votes
4answers
67 views
Auditing specific database events
For auditing purposes, we have a requirement in which we have to enable Auditing for theses events:
DBA Logins
Changes made by DBAs LIKE INSERT, DELETE, UPDATE, ALTER (etc.)
Changes made by our ...
0
votes
1answer
48 views
Autogrowth of database and log file
I am confused what this autogrowth property does for any database or log file in SQL Server.
According to my understanding:
If we set autgrowth for database as restricted then its size will ...
3
votes
1answer
39 views
Create Scheme that allows for fluidity between entities
We are a small rapidly evolving company and recently investigated switching databases from SQL Server to a graph database. We like the graph approach because of the fluidity and openness that it ...
2
votes
2answers
144 views
Best practice for tempdb log file
I have read many blogs here and there about how to configure tempdb data files but i didnt found any information regarding the tempdb log file.
Here's the strategy that I'm presently using with my ...
1
vote
1answer
83 views
Compress backup file using T-SQL
Is it possible to compress a backup file to .zip (or another) in a stored procedure invoked from a maintenance plan?
My maintenance plan contains these steps:
check DB (consistency, re_index, maybe ...
1
vote
1answer
53 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
49 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
1answer
12 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 ...
3
votes
1answer
57 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 ...
1
vote
2answers
101 views
How to backup to local drive and network drive simultaneously?
I can backup to a local drive and mirror:
BACKUP DATABASE [xxx]
TO DISK = N'XXXX.bak'
MIRROR TO DISK =N'Network path \XXX.bak'
WITH INIT
--directly to network drive
BACKUP DATABASE [xxx]
TO DISK ...
2
votes
2answers
73 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 ...
7
votes
1answer
318 views
SQL Server 2008 R2 corrupted after changing MAXDOP
My SQL Server 2008 R2 doesn't work after changing the MAXDOP parameter on instances.
I have 6 instances located on the same physical server and I changed MAXDOP from 1 to 2 on 5 instances. After ...
