Tagged Questions

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)

4
votes
1answer
359 views

SQL Server 2005 Replication

I am in the process of creating Replication between 2 Remote Servers, server 1 is the Distributor and Publisher and server 2 is the Subscription. server 1 windows 2003 server 192.168.10.1 connected ...
0
votes
1answer
182 views

Backup failure in SQL Server 2005

I'm being unable to perform a backup in SQL Server 2005 installed on Windows 7 local machine. I was initially able to do so but as of now, every time I try I get the following message: Backup ...
3
votes
1answer
2k views

SSIS 2008 Package Configurations being ignored

With the change to package configurations in 2008 compared to 2005 when I specify /ConfigFile something.dtsConfig on the command line, variables defined in the package are keeping their design-time ...
6
votes
2answers
2k views

Is there a better option than Union All for multiple selects from the same row?

Example Schema: CREATE TABLE [dbo].[Base]( [ID] [int] IDENTITY(1,1) NOT NULL, [Option1ID] [int] NULL, [Option2ID] [int] NULL, [Option3ID] [int] NULL, [Option1Name] [varchar] NULL, [Option2Name] ...
7
votes
2answers
3k views

How might a corrupt partition in TempDB result in DBCC CHECKDB reporting no issue?

One of our SQL Servers reported the following error recently: DATE/TIME: 2/25/2013 9:15:14 PM DESCRIPTION: No catalog entry found for partition ID 9079262474267394048 in database 2. The ...
6
votes
4answers
500 views

Is a join optimized to a where clause at runtime?

When I write a query like this... select * from table1 t1 join table2 t2 on t1.id = t2.id Does the SQL optimizer, not sure if that is the correct term, translate that to... select * from table1 ...
0
votes
3answers
2k views

What is ERROR_STATE() in SQL Server and how it can be used?

I read that ERROR_STATE() can help to distinguish between different states/locations in the source code where same type of error can occur. But it is not really clear to me that how it can be useful. ...
12
votes
4answers
14k views

SQL Server 2012 slower than 2008

I migrated a large website and database from an older server (Windows 2008 / SQL Server 2008 / 16 GB RAM / 2 x 2.5 GHz Quad Core / SAS disks) to a newer, much better server (Windows 2008 R2 / SQL ...
4
votes
3answers
2k views

Create procedure in else block on SQL Server

I have a SQL script in which I generate a database and its tables, stored procedures, views, etc. I have used following script to generate the database and all the things. I was able to generate ...
2
votes
1answer
2k views

Select the top 5 records and the specific column from each table in the same database

I have 50+ tables in a database. I don't want to use the following SQL on each table in a database. select top 10 * from {table_name} So, is it possible to display the first 10 records PLUS only ...
3
votes
2answers
2k views

difference between UPDLOCK and FOR UPDATE

given the following code: Set conn = CreateObject("ADODB.Connection") Set RS = CreateObject("ADODB.Recordset") conn.BeginTrans ... //This is the line it is about RS.Open "SELECT a,b FROM c FOR ...
3
votes
3answers
602 views

How to check for mutiple rows with same data?

Can anyone advise how do I check multiple rows with same data? E.g. I have 2 rows with following values: Cola Colb Colc TYPE_APPLE 123 abc Colb Colb Colc TYPE_APPLE 123 ...
0
votes
2answers
64 views

some tips on setting up sql server database server

I am currently setting up a new sql server database server with databases. It is supplied by a third party company. I need to request access for them around ports etc. I would like to be able ...
17
votes
5answers
6k views

Why would I NOT use the SQL Server option “optimize for ad hoc workloads”?

I've been reading some great articles regarding SQL Server plan caching by Kimberly Tripp such as this one: http://www.sqlskills.com/blogs/kimberly/plan-cache-and-optimizing-for-adhoc-workloads/ Why ...
0
votes
2answers
403 views

Is there a sortable ('order by') alternative to CURRENT_TIMESTAMP

I'm in the process of looking for a way to find a sortable alternative to a column that is populated with CURRENT_TIMESTAMP and I'm stumped at the moment. I have a web based form with multiple ...
1
vote
2answers
171 views

Suggestion for storing a lot of simple data

I'm looking to crawl a lot of webpages (500,000,000,000) records and be able to store the linking structure for a later date. The way I planned on laying out the database was as follows: 2 Tables ...
5
votes
1answer
118 views

SQL Server 2005 not collecting logins in the log file

I am currently running SQL Server 2005 Standard Edition on a Windows 2003 server machine. I have gone to the properties of the server and confirmed that the Login Auditing is set to both failed and ...
6
votes
2answers
698 views

Is there any difference between putting a column alias at the start or the end of the column definition?

I've always seen and written my column aliases as SELECT 1 as ColumnName but today came across a query that used SELECT ColumnName = 1 Is there any difference in how these two queries get ...
4
votes
2answers
743 views

What utilities can I use to simulate I/O load for a DB restore and DBCC check?

I'm trying to figure out the peak load I can sustain on new hardware I procured for validating backups and performing DBCC checks. I've been using Crystal Diskmark to get throughput stats which helped ...
2
votes
1answer
567 views

Pervasive linked server provider missing

I am having the same problem as this question. I installed Pervasive v11 (which does have 64bit ODBC drivers) and I still do not see Pervasive listed as a provider when trying to create a linked ...
2
votes
2answers
323 views

Reseed a newly created table

I have create a new table on SQL Server 2008. This table has an identity column, defined like this: [ID] [int] IDENTITY(1,1) NOT NULL However when I insert any data, the first record starts from ...
0
votes
2answers
520 views

Output count percentage in 2 decimal places

I have the following sql query code and I can not get to execute the desired output. SELECT Deal_type, Price_type, cast(sum(Deal_type)/count(Deal_type)*1.0 as decimal(12,2)) as results FROM ...
2
votes
1answer
180 views

Log all than queries taking more than X seconds

I have a database Microsoft SQL Server 2008r2. I have some applications in different programming languages. I would like to log all the queries that take more than X seconds. I guess that the I ...
4
votes
1answer
845 views

Specifying the failover partner when using Availability Groups

We have a situation where we are using replicated databases in an environment that's making use of Availability Groups. As we wanted no manual intervention when failing over, we decided to set up ...
4
votes
2answers
2k views

Why is SQL Server memory showing in task manager

I have worked with SQL Server for a while now, and I understand that SQL memory is inaccurately reported by the task manager. However I have recently been doing some testing on SQL Server that ...
5
votes
4answers
4k views

SQL Server - Separating data, log, and TempDB files on a SAN

I have a SQL Server connected to a SAN. Our new storage vendor recommends that all LUNs span the entire disk array, which is RAID5. I would normally request 3 separate LUNs (data, log, and TempDB) ...
0
votes
2answers
84 views

How to organize a single query to several sql servers?

I need to make an inner join sql query to several SQL Servers (one is 2000 and another is 2008 R2). The query is like the following: SELECT k.ID, k.NAME, k.BIRTH, l.PROFILEID FROM ...
2
votes
3answers
3k views

How to import table's data in MySql from Sql Server?

I am trying to export table from SQL Server 2008 R2 TO MySql 5.5. For this I am using SQL Server Import and Export Wizard of Sql Server 2008, but it is giving this error. Here this error may be ...
4
votes
2answers
1k views

Subquery in the VALUES clause

Here's a real-world schema: CREATE TABLE [dbo].[Setting] ( [ID] [BIGINT] NOT NULL, [Module] [NVARCHAR](400) NOT NULL, [Property] [NVARCHAR](400) NOT NULL, [Value] ...
4
votes
1answer
389 views

How do I create a CLR UDF with external name specifying namespace, class, and subclass?

I am creating a CLR UDF using external name and second level nested class names seem not to be permitted. Is this the case? When using the following syntax and including a first level class name and ...
3
votes
1answer
288 views

Named Pipe Provider Error code 40

I have literally tried everything, from enabling named pipe to adding exception to ports in the firewall, to everything possible in surface configuration. I can connect to the SQL instance(using TCP ...
3
votes
1answer
72 views

Locating row in index page

In clustered index, we have root, intermediate and leaf level pages. Every page has few records where indicating range of pages from level below. How SQL check whether some index key belong to ...
1
vote
1answer
680 views

Add Check Constraint On Varchar Column to enforce no duplicates

I have a varchar(max) column where users will populate a pre-given key from another system. I want to enable that the users are forced to enter a unique key which means no duplicate values on that ...
0
votes
1answer
196 views

What is the significance of root and page level fragmentation in a clustered b-tree index?

In investigating fragmentation on some of our large tables in a SQL Server 2005 database, I was using a query based on sys.dm_db_index_physical_stats. The query returns a row for each level in the ...
6
votes
2answers
2k views

Transaction Log maintenance on Mirror database

SQL Server Version: 2008 R2 Enterprise SP2 I am trying to get a handle on our SQL Server maintenance and I came across something I think is incorrect. We have a single production instance with 3 ...
4
votes
1answer
259 views

What are the best practices for backing up a secured SQL Server 2012?

I want to state right off the bat that I'm not looking for information on how to backup a SQL Server. I've got that taken care of. I'm looking for some form of documentation that will tell me the ...
2
votes
1answer
1k views

How to include the value of the query parameter in the email body using sp_send_dbmail

I am calling sp_send_dbmail and setting the query parameter. The resulting email includes the results of the query. I want the actual query (the value of the query parameter) to be appended to the ...
7
votes
2answers
425 views

Does SQL Server use pointers instead of storing duplicate rows?

I'm using the built in sp_spaceused stored procedure before and after performing a operation in our software to see which tables have row inserts and how the size of each table changes. What I'm ...
2
votes
2answers
1k views

Database mirroring - link failure between principal and witness

I am using synchronous database mirroring with a witness and am testing the various failure scenarios and the impact on the mirroring session, such as whether failover occurs. Starting with the ...
4
votes
1answer
4k views

Difference between Owned Schemas and Role Membership for Microsoft SQL Server

Are there differences between Owned Schemas and Role Members? From my understanding, the Role Members are compulsory - user need to have at least one of the check box under Role Members checked. ...
4
votes
1answer
178 views

Understanding WAIT_TIME_MS

Test scenario: I am only one user on database. I reset sys.dm_os_wait_stats. I run a SELECT query (table scan). Result: Execution time = 20 minutes, but sys.dm_os_wait_stats -> PAGEIOLATCH_SH ...
6
votes
1answer
6k views

The login already has an account under a different user name

When I execute this SQL: USE ASPState GO IF NOT EXISTS(SELECT * FROM sys.sysusers WHERE NAME = 'R2Server\AAOUser') CREATE USER [R2Server\AAOUser] FOR LOGIN [R2Server\AAOUser]; GO I get the ...
2
votes
1answer
4k views

Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself

I encounter the following error: Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself. when I was trying these commands USE ASPState GO GRANT ...
5
votes
3answers
3k views

Choosing the right algorithm in HashBytes function

We need to create hash value of nvarchar data for comparison purposes. There are multiple hash algorithms available in T-SQL, but which one the best to choose from in this scenario? We want to ensure ...
4
votes
1answer
377 views

Query records that chain together based on date gaps

I have a table in SQL Server 2012 with the below simplified schema and I need to define a query that grabs the most recent record and then returns the chain of records that have less than 30 days of a ...
1
vote
2answers
3k views

SQL Server - backup / restore schema only (not a duplicate if that's what you're thinking!)

We have a DB on over 100 servers of which we want to backup / restore the structure of just 1 at a time to a specific UAT database. They're all called the same thing but vary in terms of schema - ...
2
votes
2answers
4k views

Automatically restart SQL Server 2008

Is there a "best" way to automatically restart SQL Server on a regular basis? I read that I could create a batch file to net start, net stop the agent and service in a batch file and schedule that, ...
3
votes
2answers
1k views

Partitioning on a single filegroup

I have some very large tables in my database, but a substantial chunk of this data is "old". Due to circumstances beyond my control, I am not allowed to remove this "old" data. The other ...
2
votes
1answer
57 views

Advanced column in MS-BIDS

Because we are having the same problems as in http://social.msdn.microsoft.com/Forums/en/sqlintegrationservices/thread/e51b775a-8601-491b-8ad4-a71b0f0d59ce we have the following question: That is when ...
4
votes
2answers
243 views

How can FLOOR(3) equal 2?

I'm trying to find a reliable, efficient expression to calculate how many decimal digits it takes to write a positive integer. Mathematically, the number of decimal digits in an integer n is 1 + ...