Questions tagged [sql-server]
All versions of Microsoft SQL Server (not MySQL). Please also add a version-specific tag, like sql-server-2016, since it is often relevant to the question.
33,669
questions
2
votes
1
answer
6k
views
SQL Server Error 18483 - When Setting Up SQL Distribution
I'm trying to synchronize a couple tables in one SQL Server 2000 database into another SQL Server 2005 database using replication.
When I am attempting to set up my SQL Server as a distributor, I'm ...
- 145
0
votes
1
answer
2k
views
32-bit versus 64-bit ODP.NET to Oracle connection
I seem to be having an issue connecting a 32-bit .NET application to a 64-bit server, without a full Oracle client.
If I build a 64-bit application, using the 64-bit ODP.NET dll's or a 64-bit instant ...
- 175
5
votes
1
answer
996
views
What units is Page Life Expectancy in?
I'm currently using the following query to get the Page Life Expectancy from SQL Server:
declare @PLE int
select @PLE=cntr_value
from psql1.master.sys.dm_os_performance_counters
where counter_name=...
- 3,028
6
votes
3
answers
2k
views
Possible to detach a database from SQL Server while retaining file permissions?
When a user detaches a database from MS SQL Server, it sets the owner of the data and log files to that user. It also removes all non-owner permissions from the files. This is partially explained at ...
- 609
6
votes
1
answer
324
views
SQL Server 2008 Replication - Fixed Overhead of Replication?
We are using SQL 2008 Transactional replication to replicate data from our production database to our reporting database.
We recently created a new 'Global' database that we would like to have ...
- 2,002
2
votes
1
answer
467
views
SQL Server 2008 R2 DETA Error: Can not get minimal database information in allotted time
I have a 18,6 GB SQL Server 2008 R2 profiler trace file. Trace file recorded at tuning profile. When I submit this trace file to database engine tuning advisor I get this error:
Error: Can not get ...
- 553
2
votes
2
answers
10k
views
What does `x` stand for in this query?
select
convert(varchar(10), TotalSeconds / 3600) +':'+
convert(varchar(10), TotalSeconds % 3600 / 60) +':'+
convert(varchar(10), TotalSeconds % 60) as Seconds
from
(select
DateDiff
(second, ...
- 23
1
vote
1
answer
652
views
Query optimization: Which is better in my case - CURSORs or a Set-based approach?
First of all, I can describe the schema of the table, but my table itself is around 80GB so I am hoping some expert eyes can look at any obvious optimizations that can be performed. I am carrying out ...
- 111
4
votes
2
answers
3k
views
Does the compatibility level for sql server work reliably for scripts?
We have some DB scripts that need to be migrated from SQL Server 2008 R2 back to 2005, often scripts created in SQL Server 2008 / 2008 R2 won't run on a SQL Server 2005 installation (which some ...
- 161
0
votes
1
answer
1k
views
Using Sync Orchestrator to Syncronize two SQL CE 3.5 Databases
I am trying to set up synchronization between two SQL Compact 3.5 databases using SqlCeSyncProvider. I am able to create a scope for the two databases, which creates a meta table. When I try to run ...
- 123
9
votes
3
answers
7k
views
SQL Server - what isolation level for non-blocking select statements?
I have a long running transaction (called, say, T1) that performs some deletes, updates and inserts on a table in SQL Server 2008 R2. At the same time, another process periodically runs select ...
- 375
6
votes
1
answer
2k
views
How can I design a table for quick versioning of textual data?
Version i am talking about here is not of version control systems. Like in SO i got some textual data(compare them to questions) that could be versioned(in the sense edited, saved, rollback). Content ...
- 493
9
votes
3
answers
20k
views
How to free SQL Server memory?
I have a SQL Server sandbox installation on my local computer. I have set the memory SQL Server instance can use to 1000 mb. When running an intensive operation, memory usage rises up to 1000 mb. When ...
- 5,333
1
vote
2
answers
3k
views
SQL Server 2000: Can't DROP VIEW?
I have SQL Server 2000. I want to clean it by dropping obsolete and unused objects. One of the views causes me an error. I try to give more information but I still have no clue what the cause is.
...
11
votes
3
answers
462
views
Included Column versus Filtered Index
We currently work with table named tb_tranfers. This table has 40 million rows and is ~26 GB in size (11 GB data, 15 GB indexes).
10 to 15% of the rows are soft-deleted rows (DeletedDate is not null)....
thyagu pg
12
votes
9
answers
8k
views
Is changing the SQL Server port really that much safer?
SQL Server's default port is 1433. I've been told by our admin that the port has to change "for security reasons".
Is it really that much safer to change the port? If the server is behind a firewall ...
- 245
0
votes
1
answer
372
views
DBCC CHECKDB Data Purity errors but no rows found
Any idea why the value -96.00 is out of range for the normalization_data column (defined as numeric(14,2) )?
DBCC CHECKDB with DATA_PURITY flags this row:
Msg 2570, Level 16, State 3, Line 1
Page (1:...
- 1
4
votes
2
answers
33k
views
SQL Server 2008: How do I change the default schema of the dbo?
When I attempt to change the default schema of the dbo in SQL Server Management Studio, the ALTER fails and states that the dbo account's schema cannot be changed. Are there any tricks that can make ...
user962143
2
votes
2
answers
9k
views
SQL Server 2008 collation arabic
We are currently having a problem loading arabic into a SQL Server 2008 database.
We want to load
بِسْمِ اللَّهِ الرَّحْمَٰنِ الرَّحِيمِ
but in the database it changes to
بِسْمِ اللَّهِ الرَّحْمَ?...
- 23
4
votes
1
answer
2k
views
Why are Stored Procedure schema changes not being replicated when using ”initialize with backup”?
In SQL Server I have set up transactional replication using the "initialize with backup" option. Data is being replicated fine, as are schema changes to tables. However, schema changes to ...
- 293
8
votes
6
answers
16k
views
Any way around unique index 16 column max
According to the CREATE INDEX documentation:
Up to 16 columns can be combined into a single composite index key.
We've got a table with ~18 columns that need to form a unique combination. This ...
- 181
7
votes
2
answers
527
views
SQL Server schema intact but every record from every table is gone
We recently set up a new virtual machine running Windows Server 2008 and slapped a copy of SQL server 2005 on it. The server had running and in use without a hitch for about two weeks. The only real ...
- 327
3
votes
3
answers
203
views
SQL Server Express acting strange
I'm not an IT pro, but a web developer that happens to have his own blog.
The blog is hosted on a private VM on which I have both IIS and SQL Server.
Sometime my blog, which is not super busy stops ...
- 131
0
votes
1
answer
775
views
Small Squares or Blocks after string in table field
My problem. I'm running SQL Server 2005 in a Development (Dev) and Production (Prod) environment. I created a view that extracts data from a Navision database to SQL using a pass-through query ...
Chris D.
12
votes
6
answers
2k
views
Identical(?) SQL Server 2005 machines; query takes 2sec on one, 15min on the other
The Environment:
We have two 32-bit Windows Server 2003 R2 machines running SQL Server 2005.
The hardware configurations are identical servers with Xeon 5160 CPU, 4GB RAM, and 13GB RAID0. AWE and /...
- 221
10
votes
2
answers
1k
views
Mirroring the Distributor
Has anyone had success with mirroring the distribution database? We have a dedicated server as a Distributor. It handles all our push replication from prod to reporting. We'd like to build an ...
- 322
2
votes
1
answer
3k
views
SQL Server Replication causing locks on table
we have three MSSQL 2005 servers (database is set to compatibility level 80 for legacy reasons) which we have just set up Transactional Replication w/ Updateable Subscriptions on. Two which are ...
- 21
2
votes
2
answers
59
views
SQL Server 2000 - Is this table needed?
SQL Server 2000
I've been tasked with cleaning up an SQL database of which I have no history working with so I'm not familiar with the tables within. Short from dropping a table and seeing if anyone ...
- 121
3
votes
1
answer
13k
views
SQL Server Agent Job Step "Run As" dropdown
When creating a job step in SQL Server 2008 R2 Agent, the third dropdown in the General page is "Run as:" - but this is empty, and seems to always be empty! What is it for?
Thanks
- 1,875
5
votes
3
answers
1k
views
Does SQL server optimize or pre-parse stored procedures?
Despite what DBAs will tell you ("use stored procedures because the server will optimize them thus they are faster than ad-hoc queries"), I have heard that SQL server does not actually optimize stored ...
tenfour
8
votes
2
answers
8k
views
Two relationships to same table
i have one transaction table, each transaction has 5-6 contacts involved. each contact has the same fields like name, address, cell, email etc.
So the best thing I guess is to use the same contact ...
- 652
3
votes
2
answers
265
views
How to know dependency on a table
I have a Database Called Total and I have about 40 tables in it.I am trying to move the tables in the database into a different server So I have prepared all the create table scripts but the problem ...
- 151
13
votes
2
answers
23k
views
Difference Between Row Level and Page Level Locking and Consequences
When attempting to run my Maintenance Plan, I receive the following error:
Executing the query "" failed with the following error: "The
index "" (partition 1) on table "" cannot be reorganized
...
- 1,099
6
votes
3
answers
8k
views
Trigger shouldn't block insert
I have a trigger on insert but if the trigger fails the insert fails too.
is there a way to let the insert proceed even if the trigger fails?
EDIT: I use a trigger to send a email when a new record ...
- 652
10
votes
2
answers
2k
views
Code creates different plan when ran ad-hoc vs. in a stored procedure
I have a delete statement that is using a bad plan when run inside a stored procedure, but is choosing a much better plan when run ad-hoc.
I have rebuilt all the indexes for the tables used by the ...
- 664
0
votes
2
answers
12k
views
Hide column if any value is null or zero via proc sql or view [closed]
Given either of the two following datasets
Kağıt Cinsi Sarı Yeşil Mavi
Kalın 4 0 5
İnce null 0 3
or
Kağıt Cinsi ...
- 1,078
3
votes
3
answers
81
views
Listing Non-Record
I have two tables.
1st table:
called kimlik
id ad
---------------
1 ahmet
2 mehmet
3 ali
2nd table:
called siparis
id kimlikid tarih ...
- 1,078
6
votes
4
answers
7k
views
What impact do indexes have on the performance of table modifications? [duplicate]
I have always wondered what the effect indexes have on table modification in SQL Server. Will modification of a table will be slower with an increased number of indexes applied to that table?
My gut ...
- 119
2
votes
1
answer
555
views
stored procedure times out while subsequent runs take 1/6 the time
So I have this stored procedure which is running very slow (and timing out) in one particular production environment. This stored procedure produces an xml of profile data. In all other environments,...
- 121
3
votes
1
answer
701
views
How to get SQL Server Profiler to show amended tables
The ASP.Net application I'm maintaining at the moment has a lot of activity in the database when ever a postback occurs. For the example I'm currently looking at, Profiler shows a mass of activity ...
- 133
10
votes
7
answers
1k
views
Scheduled backup task doesn't always backup all databases despite always saying job successful
I have a job in SQL 2008 that runs a stored proc to backup all databases. This runs daily via sql server agent job.
It quits with success every day but some days it quits with success only after ...
- 316
7
votes
6
answers
11k
views
CXPACKET Waits performance tune for SQL Server 2008
I have a SQL Server 2008 query that is working on millions of records. The query is w/in a proc that is run nightly by a job. The query can take a full day to run when I first put it on the server, ...
- 71
6
votes
3
answers
458
views
Is there some drawback to define SPARSE columns?
I have a table which has a lot of NULL values in columns. But some columns don't contain NULLs at all (although nullable). Is there some drawback to declare all of these columns as SPARSE?
- 5,333
1
vote
1
answer
635
views
Running a sql server Trace of 1 GB using sqlcmd
I am load testing two sql server instances. I have a trace file from sql server 2000 database with a file size of little less than 1 GB. I am running this using the sqlcmd utility and it is been ...
kla
15
votes
3
answers
20k
views
What is a "heterogeneous query"?
I got the following error message regarding a SQL query I'm running in a program. SQL Server 2005 T-SQL.
Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the ...
- 2,063
10
votes
2
answers
8k
views
How do I clear a table from SQL Server's cache?
I have a few tables in my database that should not be cached.
How do I tell SQL Server not to cache a table's pages or how do I flush a single table from the cache?
Flushing all the cache is not an ...
- 186
2
votes
4
answers
29k
views
How to get table columns with datatypes (and lengths) using PowerShell?
I'm trying to get column names with data types (and lengths) for SQL Server instance using PowerShell. I got this far:
#Load PSSnapin
Add-PSSnapin *SQL*
#Get column names
$colNames = dir 'SQLSERVER:\...
- 5,333
4
votes
1
answer
94
views
Internals of Windows-to-Windows SQL Server data transfer
This is a sort of generic question and I don't require a very specific answer, but let me ask it by describing a scenario.
You connect to a remote db in SQL Server Studio 08, and run a select * ...
- 41
5
votes
1
answer
366
views
SQL Server transactional replication - behaviour of a MERGE command on the subscriber(s) vs. publisher
Background
I have two instances of SQL server running on two different machines:
One is a publisher, the other is a subscriber for a single (large) database. The replication type is transactional.
...
- 153
3
votes
3
answers
552
views
Same tables with same data, different SQL Server stored procedure performance
I have 2 SQL Server 2008 instances installed on the same server. They have same table structures with same data. I have a stored procedure deployed on both of them. The stored procedure performs ...
- 2,602