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
2answers
70 views
Is there a fast way to change column type without dropping the clustered index?
I have a large table that is clustered index on a bigint. We would like to change it to just an int to reduce the space and improve the performance. However, dropping the clustered index and recreate ...
2
votes
0answers
29 views
How to remove leaf member values?
I have a dimension hierarchy that is related to multiple fact tables with different granularities. In the case of one fact table, it relates to the middle level of a three level hierarchy.
When you ...
4
votes
1answer
392 views
CTE memory space
How do CTE memory space work? When you have complex SQL statements executing with the CTE and you get unusually large result set, can it increase the logical reads of the query?
I have a CTE that is ...
2
votes
1answer
30 views
Audit full text activity
I need to find when and which user created full text catalog, and who added/modified/deleted the columns (that were full text indexed) in that full text catalog.
10
votes
2answers
236 views
How to avoid using variables in WHERE clause
Given a (simplified) stored procedure such as this:
CREATE PROCEDURE WeeklyProc(@endDate DATE)
AS
BEGIN
DECLARE @startDate DATE = DATEADD(DAY, -6, @endDate)
SELECT
-- Stuff
FROM Sale
...
-1
votes
1answer
51 views
Single quote issue
I have to update a column where I am giving query which is
Update Table
Set col = '[189] IS NOT NULL and [189] <> '''
Where colid= 198
But it's giving output:- [189] IS NOT NULL and ...
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 ...
-1
votes
3answers
80 views
How to restore SQL Server 2008 R2 backup to SQL Server 2008 [duplicate]
I am unable to restore a backup from a 64-bit instance onto a 32-bit instance.
Source Server:
Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64)
Destination server:
Microsoft SQL Server ...
2
votes
0answers
40 views
The Login is from an untrusted domain - domain setup with computer level login
I have a Sql Server 2008 R2 database I'm attempting to connect to using ADO.NET using integrated security in the connection string.
The database resides on a 64 bit Windows Server 2003 machine. The ...
-1
votes
1answer
39 views
SQL Server does not exist or access denied [closed]
Anybody can assist on how to resolve this kind of error? I have already checked the windows firewall in the server but still fails to connect. I have also tried to do TELNET and fails to connect also.
...
3
votes
2answers
57 views
Table partitioning filegroup
Can I use the same filegroup for multiple tables when partitioning a table? I partitioned a items table by year. It created five filegroups. Can I use the same file group for orders table or will I ...
0
votes
1answer
49 views
Can I use the database and query it while full-text indexing?
I need to index my database, but I need to query it while it is indexing, it may take a long time, can I do that?
0
votes
2answers
54 views
Running a query within a scheduled job
I am looking at running the following query within a scheduled job weekly:
Select TOP 10
s.database_name,
m.physical_device_name,
CAST(DATEDIFF(second, ...
2
votes
1answer
40 views
BULK INSERT Data from file but ignore the first occurence of field terminator
I have a file that has data arranged in the following style:
1 1 0.5102 0.4898 0.0000 0.0000 0.0000
1 2 0.3114 0.6886 0.0000 0.0000 0.0000
I need to load ...
1
vote
1answer
67 views
SQL Server clustered index high fragmentation
I've got a Table with an Integer (4bytes) as primary key. it's defined as an identity. it is also the clustered index.
Inserts are working perfectly fine. After inserting 2000 rows the fragmentation ...
2
votes
2answers
69 views
How to rollback the identity seed after deadlock
Now that's an approximate sequence of operations Im performing:
SET IDENTITY_INSERT <table-name> ON;
INSERT SOMETHING to <table-name> with explicitly specifyed id
DECLARE @oldID bigint
...
4
votes
1answer
42 views
Recommendations on how to organize Queues in Service Broker
I have a service broker application that currently has 5 or 6 queues on two servers.
The general workflow is:
Server A
User populates some tables
User fires stored proc which puts a message into ...
2
votes
1answer
113 views
Cannot connect to SQL Server after setting sa password to blank
I have installed SQL Server 2008 R2 on a Windows 2008 server. I need to set the sa password to blank for some reasons to make an application works; but it seems like the password changes automatically ...
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
54 views
CREATE DATABASE permission denied in database 'master'
I've developed a desktop application using WPF and Entity Framework code-first.
In my application the user can create multiple databases at run-time.
But Entity Framework can't create database if ...
-1
votes
2answers
42 views
SQL Server installation prerequisite fails
When I install SQL Server 2008 R2, I get this message:
Previous release of Microsoft Visual Studio 2008 -- Failed
(screen shot)
I can't proceed to the next step. How can I fix this?
2
votes
1answer
64 views
excessive CPU utilization by SQL server
I have an issue with SQL server 2000. Its CPU utilization is high from 9.00 AM to 5.00 PM. Three applications use this server through three different instances but when the CPU utilization is high one ...
1
vote
3answers
70 views
Improve performance by partitioning
I'm using SQL Server 2012. I’m using CTE to create an orders table from the items table. The final count for the orders table is around 120 million. It is taking about 2 hours for the whole process.
...
1
vote
2answers
83 views
Missing or lost transaction and no errors to show what went wrong
I have 2 missing records in SQL Server and I am trying to identify the problem that caused this issue.
New records are inserted using a stored procedure, which will return an error code to the ...
-2
votes
3answers
68 views
Left Join not giving desired result
I have two Datasets
FEES PAYMENTS
CrsCode InstNo FEE Regno CRSCODE Instno Payment
CA1 -2 100 R1 CA1 -2 100
CA1 -1 200 ...
-1
votes
0answers
28 views
Using If statements in stored procedure breaks it [migrated]
This is my stored procedure:
USE [Hires_new]
GO
/****** Object: StoredProcedure [dbo].[ps_selectNewHireWorkPeriodsSQL] Script Date: 05/14/2013 12:59:28 ******/
SET ANSI_NULLS ON
GO
SET ...
-2
votes
1answer
23 views
Adding Roles in SQL SERVER 2012 failover clustering [closed]
Is the Application Role required for SQL Server failover cluster
0
votes
0answers
20 views
How to merge a select statement with a calculated value as new columns? [migrated]
In my sql server code, I have this select statement
select distinct
a.HireLastName,
a.HireFirstName,
a.HireID,
a.Position_ID,
a.BarNumber,
a.Archived,
...
0
votes
0answers
41 views
How to loop through a select statement? [migrated]
I have this select statement
declare @t table (Percentage float)
DECLARE @acc INT
SET @acc = 1
DECLARE @max INT
select @max = max(HireID) from NewHire
WHILE (@acc <= @max)
BEGIN
IF ...
-1
votes
2answers
56 views
How to do division from select statements in sql server?
I am trying to create a select statement that can return a table of 1 column.
Basically, I am looping through all the records of NewHire, and then using its id, to select stuff from another table. I ...
2
votes
1answer
63 views
Reorganize full text catalog is offline or online?
I need to schedule a Full Text Search Maintenance and I was reading this link: http://msdn.microsoft.com/en-us/library/ms176095.aspx. Reading this documentation I am supposing that I need to ...
3
votes
3answers
91 views
Fixed length text file insert into SQL table
I am not a DBA on this one and I will not have access to BCP or anything like that. So I'm wondering if there is still a way to do it in SQL keeping it very basic.
I have 100's of text files that ...
2
votes
1answer
74 views
Check programmatically if SQL Server needs restarting
I have to check programmatically if the SQL Server service needs restarting after series of changes in configuration.
Is there any indicator (e.g. in the system registry) that allows to obtain this ...
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 ...
0
votes
0answers
51 views
Transfer tables from multiple servers into one single server [duplicate]
I have to migrate similar tables present in multiple different servers (30+) into our new server using SSIS package. What would be the right approach towards this?
I was successfully able to transfer ...
-1
votes
1answer
61 views
Validate each parent intermediary is also a parent to itself
I have a table for which I want to validate where each parent intermediary is also a parent to itself. How to write query to validate this?
COLUMN DATATYPE DESCRIPTION
...
2
votes
0answers
92 views
Where and how to store my weird datamart
I could really use some help here.
Here's my situation.
I'm building a datamart in SQL Server 2005, which will furnish a single report (for now). We have 26 clients (medical organizations) that ...
2
votes
1answer
61 views
If the users need INSERT/UPDATE/DELETE permissions, is Windows auth still more secure than SQL Server auth?
Some background first:
The problem described below wouldn't exist at all if the database in question would have been built with a DBA's mindset:
Data access only through views and stored procs --> ...
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 ...
3
votes
0answers
61 views
When SQL Server uses Multi Page Allocation
In SQL Server 2008 R2
I always wonder what can be consumer of MPA (multi page allocation) in SQL Server? I know data page is always 8K so is there a situation where data/index page make use of MPA? ...
-1
votes
1answer
57 views
How to merge data from 2 databases [duplicate]
I have a SQL-Server database that crashed while I was away on vacation. All info from 5/2/13 is missing from our current database because my replacement implemented a back-up of the database from ...
6
votes
2answers
98 views
SQL Server 2012 Simple Recovery Model with LOG_BACKUP log_reuse_wait_desc
While I'm doing my own investigation, does anyone know why a database in SIMPLE recovery model has a LOG_BACKUP for the log_reuse_wait_desc?
SQL Server 2012 SP1. No replication, no mirroring, no log ...
3
votes
2answers
88 views
Execute a T-SQL job with a different connection string
Is it possible to execute a stored procedure from a SQL agent job while specifying a custom Application Name, similar to specifying the Application Name in a connection string?
The reason I want to ...
0
votes
2answers
64 views
Change SQL Server settings without restarting services
I can change SQL Server settings using Management Studio or programmatically as described e.g. here.
I would like to make this changes WITHOUT the need to restart SQL Server services. Is there any ...
3
votes
1answer
41 views
Scheduled stored procedure on all databases in my server
On SQL Server 2012 (not express edition) I'd like to schedule the execution of a stored procedure every hour.
On the same server I have lot of databases with the same structure, and I'm constantly ...
1
vote
1answer
11 views
Create File Stream Enabled Database in Network File (NAS Server)
I want to create a filestream enabled SqlServer database using NAS server as the storage. I already passed the creation of DB step. Look at my previous question Creating SQL Server database with data ...
3
votes
1answer
31 views
How can I enforce data integrity in this model with a M:2 relationship?
This model describes a part of my database (somewhat simplified):
A "Tumor model" describes an experiment a researcher does on animals. The "Animal line" is a breed of lab animals, e.g. a Black 6 ...
0
votes
1answer
25 views
distribution.dbo.MSdistribution_history comments explanation
The comment column of distributor.dbo.MSdistribution_history contains comments such as
<stats state="2" fetch="11554" wait="214007" cmds="17898546" callstogetreplcmds="106880">
...
2
votes
1answer
34 views
One database or multiple referencing one?
Database design: one database or multiple databases, which is best?
We have a database which has about a 100 or so tables, accessed by about five different applications. Five different applications ...
1
vote
3answers
70 views
Document database structure for auditors
I am DBA for a Microsoft SQL Server 2008r2.
Due to an audition, I need to provide data structure of my database, to people without an explicit IT background.
I guess they would like the traditional ...
