This tag is for SQL Server 2008, major build version 10.00.xxxx.

learn more… | top users | synonyms (1)

2
votes
2answers
1k views

How to log successful login attempts to a database?

Is there a way to log database name when auditing logins in SQL Server (2005 and above)? I basically want to log the username and the database name the user successfully connected to. I have tried ...
2
votes
1answer
241 views

Flat query to Column structure

I am trying to structure a query for a calendar view. I want each row to return the details for a person and what they are doing for 7 days (split into AM and PM) So the result set might look like ...
2
votes
1answer
2k views

Configure second SQL Server Alias for named instance

In my devopment environment (Win 2008 server) are running two sql server 2008 instances (default and named instance). Already one Sql alias is configured and pointing to the default instance. I want ...
2
votes
1answer
450 views

How would you implement conversation groups/ locking for multiple users of a Service Broker Queue?

How do you implement conversation groups for multiple users using their own instance of the user applications but sending related messages to the service broker queue? Any good example of this kind ...
2
votes
1answer
102 views

Associate a piece of data with a session

I think I want to associate a piece of data with each session. The association must have the following properties: The data being associated is provided either as a part of connection string (if ...
2
votes
1answer
396 views

Stored Procedure doesn't show result in SQL Server

I have created a stored procedure (to be used in SSRS) that has parameters whose values are more than one. When I execute this stored procedure in SSMS by providing the values for that parameter, SP ...
2
votes
4answers
11k views

How to create and send mail in SQL Server 2008?

I want to send E-mail from SQL Server 2008. What are the basic steps for that? Can you explain me if someone used this facility. Any help will appreciated. this link will helpful as commented by ...
2
votes
1answer
266 views

Health checking via Database Mail in SQL Server

I have a few Sql Scripts that I would like to run every day on my Sql Server(s). How can I query multiple times and have the output similar to the following: Health check for <server> Query ...
1
vote
4answers
207 views

Large table with historical data allocates too much of SQL Server 2008 Std. memory - performance loss for other databases

Based on this question and the answers given: SQL 2008 Server - performance loss possibly connected with a very large table I have discovered memory allocation problem with SQL Server 2008. ...
1
vote
2answers
58 views

How to get the first or last row in ordered result set, depending by column value?

I have the follwoing result set from the table PUNCHREPORT EMPLID Name PUNCHDATE PUNCHTIME DEVICETYPE .................................................................. 70 ...
1
vote
1answer
2k views

SQL Server 2008 server cannot connect to itself with alias name or “localhost”

We have a SQL Server 2008 ENT 64bit SP3 server running Server 2008 R2 Standard SP1 64-bit. This is also a windows/sql clustered environment. There is the cluster alias for the server, and then we ...
1
vote
1answer
126 views

Recover Jobs from a MSDB

I need to recover the jobs from a MSDB file. I had to fully reinstall a new SQL Server and Windows Server, but apparently I forgot to ever do a MSDB backup. Yup, big blunder. Now I need to restore it ...
1
vote
1answer
106 views

SQL Server log file continues to grow? [duplicate]

I'm having an issue with a SQL Server log file which will not maintain a steady amount of space held on a HDD which it has been issued. The drive is ~50GB and the log file is growing to occupy this ...
1
vote
4answers
805 views

SQL Server Page Life Expectancy sharp drop

I'm trying to put my hands on the reason this SQL Server slows down sporadically. There are no patterns detected so far. It's a SQL Server 2008 R2 running on a Windows Server 2008R2 VM on top of ...
1
vote
3answers
2k views

Import .bak file in Microsoft SQL Server 2008 Service Pack 3 [duplicate]

I have a backup of my database as a TestDatabase.bak file but I am unable to restore it. When I try to restore I receive the following error message in SSMS: RESTORE could not start database ...
1
vote
1answer
184 views

Relative cost of two similar queries involving XML columns

I have two queries as shown below which do the same thing. Here xmlcolumn is a column with datatype XML. I use these queries to search for a string anywhere in the XML column. I checked the execution ...
1
vote
1answer
236 views

Database denormalization: multiple rows into single column. How bad is it? [duplicate]

We have few join-heavy queries to our SQL Server 2008 database and in an attempt to optimize them I thought of having a view that would produce this OrderID Number Traveller 1 102 ...
1
vote
2answers
639 views

SQL Server 2008 sp_send_dbmail

I'm using SQL Server's sp_send_dbmail to distribute mail. Everything is working fine; BUT I tried to test a bad email address and it always reports success; and nothing is in the failed sent ...
1
vote
2answers
3k 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: ...
1
vote
3answers
429 views

How to create new users for restored databases?

I am not too familiar with SQL Server and am facing the following problem: The server was freshly installed and the only existing login is sa. Then a couple of databases were restored from earlier ...
1
vote
3answers
367 views

Performance issues moving 1 million rows between tables

I want to replicate 1 million rows from table1 to table2. I have written a job with a query like this: delete from table1 OUTPUT *.delete into table2 I am executing this job every hour, but it ...
1
vote
3answers
538 views

Is transaction log lost after backup on SQL Server?

I have database that has regular nightly backup (full recovery, full backup). I noticed some irregularities in data and wanted to check out transaction log to see what happened. I restored earlier ...
1
vote
1answer
199 views

SQL Server replication conflicts after migration from 2000 to 2008

I got a suggestion over at Stackoverflow to post here....greatful for any and all help. Please bear with me I think this might take a while to explain. For many years now my company has hosted a ...
1
vote
1answer
165 views

table design to track significant events associated with an item

An Orders table I'm dealing with at present in design phase has about 10 or more columns. Each OrderID in the Orders table will have certain significant events or milestones. The number of Orders ...
1
vote
1answer
485 views

TSQL DateTime DataType - fractional seconds vs milliseconds

Wanting to get all records from a single day. So everything between 2013-03-05 00:00:00.000 and 2013-03-05 23:59:59.999. We're getting overlap at the backend with things that happened at 2013-03-06 ...
1
vote
3answers
1k views

SELECT Data Set Using Stored Procedure with BCP Error

All, I have a BCP export query. It is failing with the useless error mesage on BCP usage: usage: bcp {dbtable | query} {in | out | queryout | format} datafile [-m maxerrors] [-f ...
1
vote
0answers
488 views

SELECT value from database over ODBC wont reach the software [closed]

SQL Server 2008 R2 latest updates. SQL server ODBC driver version 6.01.7601.17514 PS!!! native drivers are incompatible with the software so i cannot use them. I cannot edit or change the server ...
1
vote
1answer
158 views

Splitting a table based on business logic

This is mostly a design question and I'm looking for any feedback about the merits or flaws of this approach. Typically, I break database design into two categories, Business Logic and Recorded Data. ...
1
vote
2answers
289 views

Editing built-in templates in Management Studio

I'm running SQL Server Management Studio 2008 (10.0.5500.0) and was attempting to edit the built-in template "Create T-SQL Trigger (New Menu)". I open the Template Explorer, right-click the template, ...
1
vote
1answer
205 views

drastically different runtimes in BIDS and the SSRS Web Portal

I have a report in a BIDS project that has been deployed to our SSRS Web Portal. On the web, it renders within seconds. In BIDS, it takes upwards of seven minutes. I can see that the query runs ...
1
vote
1answer
358 views

Going from separate databases to shared databases and schemas (Multi Tenant Data Architecture)

I am eagerly trying to design the perfect multitenant data architecture for my database. I am getting inspiration from an article posted by Microsoft, based on scenario 3. I have been given all my ...
1
vote
3answers
397 views

SQL Server 2008 eval expired

I have (more than one instance) some of my experiments with SQL Server 2008 eval (why eval --> brutal bureaucracy about using licenced version for testing or short life-cycle experiments), and after ...
1
vote
2answers
143 views

CTAS with clustered index on SQL Server

Is it possible to create a table with a clustered key from a query in a single step or do I have to do this: select 1 as id into foo; create unique clustered index pk_foo on foo(id)
1
vote
2answers
305 views

Best type to use in a composite key when one of the values varies in type

I am building a data store for the content of multiple blog sites which have been scraped. Each of these sites is going to have an entry in a Blog table BlogId Url ...
1
vote
3answers
3k views

Why are my SQL Server 2008R2 databases not mirroring?

I'm trying to set up database mirroring in SQL Server 2008R2. I've taken a full backup and a transactional backup from my principal database and I've restored both WITH NORECOVERY. However, my mirror ...
1
vote
1answer
520 views

Issue with SQL Server 2008 replication

I'm doing merge replication between two SQL Server 2008 machine.But,when I attempt to do any modification on system,I got the following error. The Merge Agent failed because the schema of the article ...
1
vote
5answers
4k views

How to restore database using old full backup and current log file

What is happened? My client has lost the hard disk. He has to restore database. He has the full backup of database 15 days old. He has ldf (log) file of this database. He has NOT mdf file (it was ...
0
votes
4answers
64 views

SqlServer: can one table's PK be the FK of other two table's? [duplicate]

I have three tables project, users and proj_user. The PK of proj_user table is the FK of project and users table's pk e.g.(project_id,user_id) My question is, is it possible for the project_id and ...
0
votes
2answers
68 views

How to provision the SQL Server Agent service after SQL Server Edition upgrade

I have upgraded SQL Server 2008 R2 Express to SQL Server Standard Edition. After I have upgraded it, the SQL Server Agent was stopped. I have tried to start but it fails. Only when I have changed ...
0
votes
1answer
767 views

SQL Server error importing an Excel sheet

I am facing a problem while loading & importing an Excel sheet into a SQL Server database. The error is The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" reported an ...
0
votes
1answer
200 views

How to restrict SQL server profiler to show my symmetric key password while executing query from C# code?

I used symmetric encryption to encrypt sensitive data in my application. I don't want anyone see my password for decrypting the data. But system admin can able to see symmetric password in SQL Server ...
0
votes
2answers
443 views

What does the Service Master Key protect?

I am looking for a complete list of items that the Service Master Key is used to protect in SQL Server 2008.
0
votes
1answer
566 views

How to create a trigger in a different database?

Is it possible to create a stored procedure that creates a table trigger (DDL) in a different database than the one the stored procedure itself resides. The databases are on the same server instance. ...
0
votes
3answers
81 views

Access to tables in SQL Server

I would like to know if there is a table where accesses to tables are saved, per user or per system. I'm using both SQL Server 2005 and 2008.
0
votes
1answer
245 views

Why is CDC capture failing due to a missing dbo principal

I am having a problem running the CDC change capture job against some of my test databases. The error message I'm getting is the following: Executed as user: NT AUTHORITY\NETWORK SERVICE. Cannot ...
0
votes
2answers
4k views

How to import data from a pair of .mdf/.ldf files into a MySQL database [duplicate]

I have two Microsoft SQL Server database files named "xxx.mdf" & "xxx.ldf" and I want to import that database into MySQL so I can run queries against it using phpMyAdmin. How would I go about ...
0
votes
2answers
557 views

migrate SQL Server geography data to PostGIS geometry data

I'm trying to use SSIS to transfer point data from a Geography column in SQL Server 2008 R2 to a geometry column in a Greenplum (Postgres 8.2.15) database with PostGIS. I know there's a way to do the ...
0
votes
3answers
1k views

INNER JOIN making COUNT(*) slow

I have a very simple query: SELECT COUNT(*) FROM messages INNER JOIN users ON messages.user_id = users.user_id With the join it takes 1146 ms and without it takes 220 ms (220 ms still seems slow to ...
0
votes
1answer
314 views

Implementing Pub/Sub initiated from the database

I have a number of state-full Windows Services that currently poll the database looking for new or changed records in specific Tables and/or Views. For obvious reasons this limits scalability and is ...
0
votes
1answer
1k views

Deny INSERT/UPDATE/DELETE to all users for objects in a specific schema

I've got a payroll-related schema that I need to secure. I figure if I prevent all users from directly modifying the tables, and create a set of procedures to handle all data modifications, we should ...