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).
0
votes
1answer
20 views
Can you grant an “Active directory Group” rights to linked server in SQL server 2008 R2?
My company uses sql server 2008 R2. I am trying to grant rights to an Active directory Group rights to a Linked Server. I am unable to do it, it only allows me to grant rights to individual active ...
1
vote
1answer
26 views
Backup on an empty SSAS database hangs
I created an SSIS package to iterate through all databases on an SSAS instance. It grabs the DatabaseId using AMO in a vbscript, then generates the XMLA to backup each database.
Most databases ...
2
votes
0answers
11 views
Debugging SSAS Timeouts
I have a drill-through problem apparently related to security. Users in one role are seeing timeout failures when trying to invoke a drill-through action. They have permission on the action through ...
0
votes
1answer
26 views
SQL Sever - Remove Duplicates in order
When removing duplicate rows, using the below stated query, how would I go about forcing which of the found duplicates to remove?
I am using this query, from this tutorial:
DELETE FROM ...
9
votes
3answers
255 views
Updating a table with millions of records, its been 4 days
I am currently updating a table with millions of records, its been 4 days and query is still executing.
I checked the activity monitor its shows that query is running.
In event log there is no ...
1
vote
0answers
30 views
Troubleshooting a SQL Server instance that won't start
I've had a problem with one instance of SQL Server 2008 (the free version) not starting automatically. I always have to right click on the icon in the task bar and start it manually. It is set to ...
-2
votes
0answers
26 views
SQLYzer experience [closed]
recently found and installed the 30 days free trial version of Precise SQLYzer (just google it and you'll find it easily). I tried it on my UAT env and so far I find it really great. I'm wondering ...
1
vote
2answers
53 views
Remove scientific notation from an XML column
I have a XML column as shown below:
<Root>
<Row>
<Rowid>1</Rowid>
<date>2013-05-06</date>
<Balance>1.0002E7</Balance>
</Row>
...
0
votes
1answer
29 views
Upload (someone else's data) to Postgres, SQL Server
I work for a company that does program evaluations for state agencies. Thus, I frequently work with someone else's data. They send me the data via sftp:/, mail, etc. and I load it into either SQL ...
3
votes
4answers
203 views
Query without having to specify table's schema
I imported a bunch of tables from SQL Server 2000 to my 2008 database. All the imported tables are prefixed with my username eg: erpadmin.tablename.
In the table properties it lists 'erpadmin' as the ...
1
vote
0answers
31 views
Record versioning and promotion
Let's say we have this hierarchy:
-World
--USA
---WA
----Seattle
-----Downtown
------1st Ave
-------945 1st ave
------3rd Ave
...
2
votes
2answers
41 views
Partial rollback doesn't decrement trancount
Suppose I have an open SQL Server session, and do the following:
begin tran
insert into People (Id) values (1)
select @@TRANCOUNT -- Prints 1
save transaction tt
begin tran
select @@TRANCOUNT ...
2
votes
2answers
22 views
Delete SQL Server logins on replicated server
We have a production database which is replicated on another server at a remote location. The local database users (not logins) are also replicated in this database. However, the SQL logins are not ...
0
votes
2answers
31 views
Where should I look for details about 'access denied' errors?
This question derives from Deploying a new database on a new server, an msbuild ssdt rookie issue.
Since I have a permission denied error, I expect to find which user is failing to access so I can ...
1
vote
0answers
45 views
Generate usage log
I'm trying to automatically generate an events log report in sql server that does the following:
Records events including successful login, Create/Delete Account, An Account was Unlocked, Account ...
2
votes
3answers
96 views
Which is better: one database per application, or just one database? [duplicate]
I ask this specifically for SQL Server, the advantages that I can see for multiple databases are:
Logs are smaller (so one runaway transaction cannot cause others (replication etc) to stall)
A page ...
2
votes
2answers
39 views
SQL Server 2012 installation silently fails
I am trying to install SQL Server 2012 running setup.exe on 64-bit windows 8. After the "please wait" dialog, the installation center comes up. I chose "New SQL Server stand-alone installation", ...
2
votes
4answers
73 views
When should I use the shrink option
I have seen a lot of blogs stating that shrinking is not a good habit as it will reduce the performance of the system. I agree with all those things it will lead to side effects like fragmentation, ...
1
vote
2answers
47 views
Can I trust the Database Engine Tuning Advisor recommendations?
Our database has become large, and we are trying to optimize performance.
Can we follow the recommendations provided by the Database Engine Tuning Advisor by supplying it a .trc file generated with ...
-1
votes
4answers
65 views
Dropping and recreating indexes [closed]
I most often come in a situation where the users move there database from one server to another and immediately after they move they face performance problems(slowness).
I somehow manage to rebuild ...
5
votes
2answers
94 views
Does an index with multiple columns make a similar index redundant?
Let's say I have a table that looks like this:
CREATE TABLE Activity (
ActivityID int primary key identity(1,1) ,
ActivityName nvarchar(10),
InactiveFlag bit
)
with an index that looks ...
4
votes
1answer
64 views
How to get a row_number to have the behavior of dense_rank
I have a stored procedure will be used to page through data. One of the requirements of the procedure is to have a parameter that will be used to sort two columns of data, the sorting should be used ...
2
votes
1answer
41 views
Can I decrypt TDE transaction log files for use with 3rd party software?
Is there a way to decrypt TDE log files on disk for use with 3rd party software? I have the original key and certificates. I am trying to use the transaction files with a 3rd party tool in order to ...
0
votes
0answers
17 views
XML data source with XSD validation importing the same data despite different files
Please bear with me, I've never used XML as a data source, nor am I familiar with XML itself, so I'm not entirely familiar with the nomenclature.
I have a series of XML files with data to be imported ...
4
votes
2answers
81 views
Why do linked servers have a limitation of 10 branches in a CASE expression?
Why does this CASE expression:
SELECT CASE column
WHEN 'a' THEN '1'
WHEN 'b' THEN '2'
... c -> i
WHEN 'j' THEN '10'
WHEN 'k' THEN '11'
END [col]
...
1
vote
1answer
46 views
Generate List of Missing Relationships
I have two tables, CLUSER and CLUSERRX. CLUSER contains all the employees at my company and CLUSERRX contains a list of all the people in CLUSER with a specific workgroup and all of the users from ...
1
vote
2answers
58 views
View column has an 8000 character limit?
In SQL Server 2008 R2 we are using an updateable view to update a varchar(max) column. When we update the column with text and then check the number of characters, it is showing a length of 8000 ...
5
votes
1answer
62 views
Troubleshooting SOS_SCHEDULER_YIELD wait
Running our corporate ERP (Dynamics AX 2012), I noticed our production environment seemed much slower than our development systems.
After performing the same activities in both the development and ...
1
vote
2answers
41 views
Is it possible to remove a database server from a cluster resource, but keep it running on the node
I was wondering if anyone knew whether it was possible to delete the cluster resource, but keep all of the disks and sql instance running on the node instead? So if I was to delete the cluster ...
2
votes
2answers
56 views
Performance effect of indexing foreign keys
My database is for an HR application with performance reviews and I am interested in whether I should index my foreign key.
There are two tables involved. The first is a ratings table with 2 columns: ...
0
votes
1answer
33 views
How to do an in-place upgrade of SQL Server 2005 Express to SQL Server 2008 R2 Express
I would like to upgrade my SQL Server 2005 Express instance which is installed using this command:
SQLEXPR.EXE/qs /action=INSTALL /IACCEPTSQLSERVERLICENSETERMS
/FEATURES=SQLEngine ...
0
votes
1answer
45 views
Migration to databases Domain Users do not have access
I migrated databases to new servers, however the applications that were previously used with the databases are failing to load. I have changed the connections and etc. The jobs also seem to be ...
2
votes
1answer
54 views
How do I turn off ALTER DATABASE script output in VS 2012?
I am using SQL Server 2005 in conjunction with Visual Studio 2012 and a SSDT database project.
When I publish my VS project and generate the script to run against my database, VS includes the ...
1
vote
0answers
46 views
Is there a way to generate an execution plan for a stored procedure before executing it?
We have a very large (many millions of rows) table that contains some (few million) rows with invalid values in particular columns.
A stored procedure exists to correct the invalid records but it ...
0
votes
1answer
15 views
How to achieve this sql server replication scenario?
I want to achieve below illustrated scenario via sql server replication.
Client A and Client B are client databases containing same article Table A. This article needs to be published by Client A ...
1
vote
1answer
31 views
Migration of create statement from SQL Server to MySQL
I have a table that have the following table structure
CREATE TABLE [dbo].[ASAT](
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[ASATID] AS ('ASAT'+right('00000000000000000000'
...
0
votes
0answers
41 views
How to improve the performance for SQL Server Count(distinct()) and Sum() functions? [closed]
The below one is my query. It's taking 12 seconds for the execution process. The count(distinct()) and SUM() functions are taking long time for execution. I tried it after create the non-cluster index ...
-1
votes
1answer
28 views
data warehouse construction from mdf and ldf file via etl
I have sample data warehouse stored in the form of mdf and ldf format. I want to use any ETL tool to extract data and then load datawarehouse on the specified database with username, password and ...
1
vote
2answers
62 views
How to attach an .mdf file that was not properly detached?
One of our clients had a SQL Server 2000 database, on a system which is no longer running. They have no idea what was in the database. They have given me the .mdf and .ldf files, and want us to ...
1
vote
0answers
35 views
Job accessing linked server yields error 7391
I created a stored procedure which queries data from a linked server to insert into a local table.
I tested this stored procedure by directly executing it; it works fine.
I have to run this stored ...
-2
votes
0answers
43 views
Unexpected index scan against partitioned table [closed]
I have a set of partitioned tables with CIX on (ID, DATE) and partitioned on DATE.
There is another identical set of non-partitioned tables with CIX on ID.
Case A: T1 and T2 are non-partitioned ...
3
votes
2answers
46 views
checksum(newid()) as primary key - what happens if a collision occurs?
Inspecting a rather critical database that is used by some software on my system, I found that one of the tables had a primary key on the column Id, where Id is calculated using checksum(newid()). ...
7
votes
2answers
133 views
Is there any way to prevent the memo structure from being pruned?
We know that the memo structure is pruned and some expensive alternative plans are discarded during optimization. I was wondering if there is any way to prevent this and let the optimizer just ...
1
vote
2answers
55 views
Update my local db based on scripts executed in my server database
I need to create an solution that update my local db version based on changes that was made on my server database.
First, I need to check the differences that has exists between these databases and ...
1
vote
2answers
17 views
Tracking down program from Event Viewer
The event log has hundreds of login failed errors for SQL Server. How do you find out the application generating them?
There are lots of potential candidates on the server and since it's logging in ...
2
votes
2answers
45 views
Restore database created in SQL Server 2008 R2 to SQL Server 2008 R2 SP2
I have a .bak file created by some other user in (I think) SQL Server 2008 R2 (10.50) and I need to restore it in SQL Server R2 SP2 (10.52). I used a hex viewer to look into the .bak file and found ...
1
vote
0answers
25 views
PowerPivot doesn't install in SharePoint mode on an Active/Passive Cluster
We a provisioning a new SQL Failover Cluster. We have successfully configured the SQL instance, and have moved on to configuration of the PowerPivot for SharePoint instance. We run the configuration ...
2
votes
1answer
56 views
Start service error code 3417 with SQL Server 2012
I cannot start the SQL Server 2012 service. The event log says the service-specific error code is 3417.
In the Event Viewer:
The SQL Server (MSSQLSERVER) service terminated with the following ...
3
votes
1answer
34 views
SP with Encryption Read/Writes Stats
If a stored procedure in SQL Server 2005 is created with the syntax "WITH ENCRYPTION" ,are the reads and writes of the encrypted stored procedure recorded in the sys.dm_db_index_usage_stats? I am ...
0
votes
1answer
29 views
SQL Server TDE - add master key and certificate [closed]
I want to add multiple master database encryption keys and certificates.
Can I do that?
