This tag is for SQL Server 2008, major build version 10.00.xxxx.
2
votes
2answers
254 views
How to refresh AD security group on Sql Server permissions
I am using Sql Server 2008 on Small Business Server 2003; client is using WinXP.
I added a user to my Active Directory Security Group; Why can't this user immediately access the database? It seems ...
2
votes
3answers
2k views
SQL Server: How to call stored procedure without schema name?
SQL Server 2005std/2008web edition
db=user=schema=owner='John'
I'm moving website from one server to another. This piece of code works well on old server with SQL Server 2005.
Dim sqlCmdVehicle ...
2
votes
1answer
310 views
SQL 2008 Server agent cant see perfmon counters
I am trying to create a new SQL server agent alert to capture "SQLServer:General Statistics|Processes Blocked||>|5' but it looks like alert creation GUI is not able to see any perfmon counters as in ...
1
vote
1answer
469 views
gain admin access to sql express 2008
I have inherited a Windows 7 Professional pc with an existing instance of SQL Express 2008. I have local admin rights on the machine. I do not know the SA password. I am unable to create databases or ...
1
vote
4answers
1k views
migrating tables from Postgres to SQL Server 2008
(I fear what I wish to accomplish shall not be easy, but a lot of my googling around about it yielded rather dated results, so here it goes...)
I have a Postgres 8.4.x database with over 100 tables. ...
1
vote
2answers
3k views
Attach SQL Server 2008 R2 on SQL Server Express 2008
I have a database on SQL Server 2008 R2.
I want to attach it on a SQL Server Express 2008 instance running on Windows 2003.
I set the SQL server compatibility level on the database to SQL Server ...
1
vote
2answers
405 views
Should I create a maintenance plan for my database or use jobs?
I tried creating a Maintenance Plan on my SQL Server 2008 database and was impressed by some of the things that are available out of the box. However, after it was created, it seemed difficult to ...
0
votes
1answer
97 views
Validate each parent intermediary is also a parent to itself
I want to validate where each parent intermediary is also a parent to itself. So this query should return invalid parents in SELECT if table has any.
I need to do is first of all get all parents ...
0
votes
1answer
109 views
sql server-percentage calculation
Sample data :
LINE_NO E_FIELD F_FIELD G_FIELD HSA_STATUS FAMILY CACHE_FAMILY Count
23053B 00000 00000 00000 S SUMMIT WER 43
23053B 00000 00000 00000 T ...
0
votes
3answers
295 views
SQL services wont start due to incorrect drive letter after windows restore
SQL services wont start due to incorrect drive letter after windows restore
Had a server crash, was successfully able to reinstall windows and restore the server SQL and all the necessary services ...
6
votes
1answer
10k views
How to identify which query is filling up the tempdb transaction log?
I would like to know how to identify the exact query or stored proc which is actually filling up the transactional log of TEMPDB database.
17
votes
4answers
3k views
SQL Server “empty table” is slow after deleting all (12 million) records?
I have a SQL Server 2008 instance with approximately 150 columns. I have previously populated this table with approximately 12 million entries, but have since cleared the table in preparation for a ...
9
votes
3answers
1k views
SQL Profiler tutorials for newbie
The MSDN documentation tough to understand.
Are there any online tutorials available to learn SQL Profiler? If possible, videos or simple blog posts would be preferred.
21
votes
2answers
2k views
Find Queries that Are Causing Waits
Given a certain kind of wait, how do you find which queries are causing those waits safely on a production SQL 2008 R2 Server?
In this particular case I am wondering about async_network_io.
16
votes
4answers
861 views
Does the order of columns in a table's definition matter?
When defining a table, it's helpful to order the columns in logical groups and the groups themselves by purpose. The logical ordering of columns in a table conveys meaning to the developer and is an ...
14
votes
3answers
892 views
Is it OK to blindly add missing indexes?
I often use SSMS to test my slow stored procedures for missing indexes. Whenever I see a "Missing Index (Impact xxx)" my kneejerk reaction is to just create the new index. This results in a faster ...
9
votes
1answer
1k views
How do you ORDER BY a parameter?
Just wondering if I could solicit some feedback on a stored procedure I'm running and whether there's a more efficient way of handling the scenario (I'm pretty sure there will be!).
Basically I have ...
18
votes
5answers
3k views
Why is there still a varchar data type?
Many of my databases have fields defined as varchars. This hasn't been much of problem since I live and work in America (where the only language that exists is "American". ahem)
After working with ...
17
votes
3answers
2k views
Efficency of stored procedures vs raw queries
I have read much on both sides of this debate: is there a signficant performance gain to be had by using only stored procedures over raw queries? I am specifically interested in SQL Server but would ...
7
votes
4answers
12k views
How to add sysadmin to user in SQL Server 2008 when no sysadmin accounts exist
I have a SQL server 2008 instance running. Unfortunately during testing I deselected the sysadmin rights for my login and now can't readd it (because I don't have sysadmin rights).
There are no other ...
5
votes
3answers
9k views
How do I run a batch file from a scheduled Job via SQL Server Agent
I'm attempting to run a (Windows) batch file from within a SQL Server agent job under SQL Server 2008 R2. In the Job Steps, I've created a new step, set its type to "Operating system (CmdExec)". I ...
4
votes
3answers
1k views
How to sync the data between two identical DB
The thing is I've two identical databases, one is in my localhostserver at my laptop and the other one is on the office main server. Is there any automatic way or software that can synchronize the ...
10
votes
3answers
1k views
Why might a table's data space take up 4x the size of the raw data?
I have a table with 490 M rows and 55 GB of table space, so about 167 bytes per row. The table has three columns: a VARCHAR(100), a DATETIME2(0), and a SMALLINT. The average length of the text in ...
10
votes
3answers
544 views
What's the most effective way to duplicate an instance on SQL 2008?
We've got a simple database instance with just enough data in the databases to keep it useful, and we can use it for demonstration purposes, and I want to put a copy of it on my laptop so I can use it ...
7
votes
1answer
371 views
alert when any job in a job category fails
Is it possible to set up an alert in SQL Server 2008 that will send an e-mail anytime a job in a specific category fails?
I am wondering because I would like to set up an e-mail anytime an SSRS ...
6
votes
4answers
4k views
SQL Server database on an SSD - any advantage to a separate file for every table?
I'm creating a database in which there will be around 30 tables, with every table containing tens of millions of rows and each table containing a single important column and a primary/foreign key ...
6
votes
2answers
2k views
SQL Disk Setup Advice - TempDB, Log DB, Data file placement question
We have a very active database server with a ecclectic collection of applications running on it. Two of the busiest are a Laserfiche database that does document scanning and workflow processing all ...
5
votes
3answers
2k views
Will the transaction log shrink automagically in SQL Server?
When SQL Server database in a SIMPLE mode, you don't have to care about the transaction log bakcups. But in a SIMPLE mode, the transaction log seems to grow as it does in FULL mode. Does is truncate ...
5
votes
5answers
1k views
What is the most efficient way to alter a column definition in a table with millions of rows
I need to change a column from NOT NULL to NULL in a table that contains millions of rows. I've tried a simple
alter table Table1 ALTER COLUMN Column1 XML NULL
but it takes forever. So here are my ...
5
votes
4answers
7k views
Replicating a remote MySQL database to MS SQL Server 2008
I want to replicate the contents of a MySQL database to a MS SQL Server 2008 database.
Is this possible? Can anyone outline the steps required in order to achieve this?
Thanks.
4
votes
1answer
825 views
Is it possible to get execution call stack in a trigger?
I have 10 stored procedures and each of them does INSERTs into one tableX.
Is it possible in a trigger body of tableX to get what object causes modification of tableX (stored proc1 or sp2 or....) ?
...
2
votes
3answers
138 views
Working Linked Server Query fails in sp_send_dbmail
Take the following example:
EXEC msdb.dbo.sp_send_dbmail
@recipients = 'me@whatever.co.uk' ,
@query = 'SELECT TOP 10 * FROM LINKEDSERVERA.DATABASE.dbo.TABLE' ,
@attach_query_result_as_file = N'True' ...
2
votes
1answer
189 views
Understanding SQL Server encryption (by password)?
After asking this I still have a question.
A company need to store sensitive data. Please let's not talk about certificate right now.
A DBA does the following :
CREATE SYMMETRIC KEY ...
14
votes
4answers
5k views
What is the best query to use to monitor a SQL Server database's status?
I want to be able to run a query to get the crucial information about a database's status. I.e., I want the query to be able to tell what whether or not the database is in a good state.
This is the ...
12
votes
3answers
4k views
How does shrinking a SQL Server log file affect performance?
I have a SQL Server 2008 database that has a data file of some 2GB in size, but the log file is over 8GB. With pre-2008 databases I could use the 'Backup log' and the TRUNCATE_ONLY option but this is ...
11
votes
3answers
1k views
Anybody using HierarchyId in production? Is it reliable?
Is anybody using HierarchyId in real production with tables of reasonable size, more than a few thousand rows? Is it reliable/performant? So far I have not found anyone not affiliated with the vendor ...
10
votes
3answers
455 views
Executing same request from C# VS SSMS give different execution time
I have a request like this one
SELECT
[EstimateId],
[CreationUserId],
[EstimateStatusValueId],
[LanguageId],
[LocationId],
[EstimatorUserId],
[FilterUnitSystemTypeId],
[EstimateNumber],
...
9
votes
2answers
7k views
How to find the SQL statements which caused the tempdb grew huge?
The tempdb of an server (SQL Server 2008) increases to 500+G several times every month. Is it possilbe to find out which SQL statements cause the problems? (The problem is usually not caused by create ...
9
votes
1answer
1k views
Empty Strings: Why or when is '' equal to ' '?
Who can explain why
select case when '' = ' ' then 1 else 0 end, LEN(''), LEN(' '), DATALENGTH(''), DATALENGTH(' ');
yields
----------- ----------- ----------- ----------- -----------
1 ...
9
votes
2answers
460 views
A Non-DBA Asks: How to Painlessly Copy/Move a SQL Server instance to another Server via Backup/Restore?
We've just got a new server so I need to copy the existing SQL Server 2008 instance over from the old db box.
I normally do this by copying .mdf and log files over and attaching them but I'm not able ...
8
votes
3answers
684 views
Speed impact of using varchar in SQL Server 2008
In the old days, using varchar over char was a big no no, since the varchar was stored on a different page than the rest of the row. Is this still the case today with SQL Server 2008? Will I see any ...
8
votes
2answers
2k views
Why can't I read Oracles CLOB columns via SQL-SERVER 2008 linked server?
I want to access data in an Oracle 11g database from SQL-Server 2008
I set up a linked server and when I execute
select * from [Link_server_name]..Oracle_schema.Oracle_table
and Oracle_table ...
7
votes
1answer
205 views
Tracking stored procedure usage
Besides using SQL Server Profiler, is there any way to track which stored procedures are being used, or at least when they were last executed?
7
votes
3answers
2k views
Emulate a TSQL sequence via a stored procedure
I have a requirement to create a stored procedure which emulates a TSQL sequence. That is it always gives an increasing distinct integer value on every call. In addition, if an integer is passed in ...
6
votes
2answers
233 views
problem running xp_cmdshell when Application Name set in connection string
I have an app on 10 or so servers that runs a few xp_cmdshell statements against MSSQL 2008. It works fine on all the servers except for one. To make things worse, I can run all the commands in SQL ...
6
votes
2answers
432 views
SQL Server 2012 Availability Group is it “AlwaysON”?
In a traditional SQL Server clustering when failover occurs, all client that are connected to SQL Server failover instance looses the connection and each client must reestablish a new connection to ...
6
votes
2answers
892 views
how will nvarchar(max) store data in database will it be fast if some data is less then 4000 characters?
I have to develop a CMS which will support two Language English, Arabic. This CMS will be a sort of Article Publishing site. While designing & analysis i found that some articles are more than ...
6
votes
2answers
186 views
SQL Server Internal Inconsistency on GROUP BY
I recently recovered a database from a hard-drive failure using DBCC CHECKDB WITH REPAIR ALOW DATA LOSS.
Now I can select records from a table-valued function to retrieve sales records, but using a ...
6
votes
1answer
2k views
Database Mirroring with TDE
I have the requirement of mirroring a few databases and also using transparent data encryption (TDE) on them as our data must be encrypted while 'at rest'.
I have setup TDE on both the principle and ...
5
votes
2answers
1k views
Data type for storing an array of flags (a bitmap/bit array)
I need to store a bit array for each record of a table, supporting the following operations:
Testing if a bit is set, and setting a bit (using SQL)
Querying and setting the value using ADO 2.8 (not ...
