All versions of Microsoft SQL Server (not MySQL). Please also add a version-specific tag like sql-server-2016 if that is relevant to the question.

learn more… | top users | synonyms (2)

1
vote
1answer
106 views

Must declare the scalar variable “@VarName” [duplicate]

Could anyone shed some light on this error for me? To a beginner of course. This will probably be no more than a simple fix for someone else. I'm trying to read in the Product to be used in the where ...
2
votes
0answers
83 views

Mirroring won't fail back

Got an interesting mirroring issue. I am using SQL Server 2008 R2. Server A mirrors 8 databases to Server B ideally. Right now the 8 databases are principal on Server B (otherwise healthy). When I ...
4
votes
2answers
412 views

Which command Deletes and Frees Space

I'm reading through the 98-364 exam book and there's a question I can't quite answer: "What command would you use to remove all records from the Contact table that do not have a country of USA ...
-1
votes
1answer
277 views

How to reduce the size of ldf file? [duplicate]

We take full backups of the DB every day (in full recovery mode) and keep them for 10 days. Because of our requirements, we don't want to take incremental or differential backups of the DBs. Now we ...
4
votes
1answer
530 views

Count the nodes in a binary tree structure

I need to count the left and right nodes in a binary tree structure (output grouped by joiningDate), given a particular starting node parent id (pid). The tree is stored in the table shown below: ...
1
vote
0answers
47 views

Design of hard drive RAIDs

My task is to reconfigure a main server that houses the company's applications and data. The server is capable of handling everything. I wanted to find out if anyone has a suggestion on the best ...
2
votes
1answer
196 views

Converting old SQL Server outer join syntax

I'm a Power Builder (PB) developer. I've migrated PB from 10 .5 to 12.5, and now I need to migrate the stored procedures from SQL Server 2005 to 2012 as well. I get a invalid expression error when I ...
1
vote
0answers
217 views

Update remote MySQL database upon changes in local SQL Server database

I have a Web store running with MySQL on the remote host. And I have inventory management software using Microsoft SQL Server on my local system. I want to update product stock in the web store when ...
0
votes
1answer
37 views

Backup Principle database in SQL Server

We have set up database mirroring in SQL Server 2005. Is it possible to take full backup of principle database? Does it break database mirroring if I will take full backup of the principle database?
1
vote
1answer
52 views

Sync data in SQL Server 2014 with Oracle database12c

I have installed Oracle database 12c enterprise on my laptop. How can I implement data synchronization between Oracle 12c and SQL Server 2014 that whenever an INSERT,UPDATE,DELETE or ... happend on ...
0
votes
1answer
2k views

Problem when connecting to SQL Server

I'm trying to connect to my SQL Server Express and I get this error. This happens both locally and on client. Connection Timeout Expired. The timeout period elapsed during the post-login phase. ...
0
votes
1answer
62 views

SQL Server 2012 Exclude a value

I'm teaching myself SQL, and I ran into a scenario that I cannot seem to solve. Please any help. I have a table called tblRecord with a column called BuyerID, in this case there are two BuyerIDS, and ...
14
votes
9answers
1k views

Alternative to WHERE clause [closed]

Is there a way to only SELECT rows with certain data in a column, without using WHERE? e.g. If I had this: SELECT * FROM Users WHERE town = 'Townsville' is there a way to implement the WHERE ...
1
vote
0answers
945 views

Change Root Directory on server properties page in SQL Server 2014 SSMS

We did in inplace upgrade from SQL 2012 to SQL 2014 (I know it wasn't the best choice, and it won't happen again, so no need to beat that horse). I have both the MSSQL11.MSSQLSERVER and MSSQL12....
-1
votes
1answer
231 views

How can I generate a script to choose only top 10 items from each table in SQL database?

I know how to generate scripts that would contain insert queries of all records in all tables. But how can I limit this to top 10 records only from each table?I can select "Data Only" when generating ...
0
votes
2answers
199 views

Create a SQL Server Redirect (Alias / Linked Server)

I currently have a database on a named SQL server instance called \Mobius and database name of Mobius We need to move the database to the default (parent) instance but don't necessarily want to ...
0
votes
1answer
608 views

can i shrink log file in full recovery model

i have configured Always on Availability group where iam unable to shrink log file size through simple recovery model. can i shrink the log file size in full recovery model if yes or possible then is ...
3
votes
0answers
2k views

SQL DatabaseMail smtp error 5.7.57 at one site, email works at another with the same details

I have several SQL databases on different servers. I have used a script to set up Database mail. EXECUTE msdb.dbo.sysmail_add_account_sp @account_name = 'Emailer' ,@description = 'Email out ...
4
votes
2answers
3k views

How to avoid a “duplicate key” error?

This statement: INSERT INTO deleteme SELECT #t.id, 'test' FROM #t LEFT JOIN deleteme ON deleteme.id = #t.id WHERE deleteme.id IS NULL; ...will fail with a primary key violation in the concurrent ...
2
votes
1answer
535 views

Unable to Watch Live Data on Extended Events Session

I am running SQL Server 2014 Developer Edition on my PC. I am trying to view the data in the system_health session. In SSMS, I have connected to the database, expanded the server / Management / ...
0
votes
0answers
313 views

Error While restoring backup in SSMS

When I tried to restore a backup an error occurred showing access denied. The error message is as follows: Restore failed for Server 'VAIO\sqlexpress'. (Microsoft.SqlServer.SmoExtended) ...
2
votes
1answer
24 views

Returning XML Value for Extended Event

I would like to get the filepath for my extended event event_file and put it inside a variable but I'm stuck. Here's what I have: DECLARE @ExpensiveXML XML SET @ExpensiveXML = (SELECT CAST(xet....
0
votes
1answer
273 views

Select tree-based structure

I have a table in my SQL Server database with data like this: ID ParentID --- --------- 1 NULL 2 1 3 1 5 1 5 4 5 6 8 6 7 9 9 10 I need a select statement that will return all the ...
4
votes
1answer
85 views

Fractional Transaction Count [closed]

Based on this question I understand the meaning of the following sorts of errors: Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, ...
8
votes
3answers
318 views

Index tuning question

I am tuning some indexes and seeing some issues would like to take your advice On 1 table there are 3 indexes dbo.Address.IX_Address_ProfileId [1 KEY] ProfileId {int 4} Reads: 0 Writes:10,519 dbo....
0
votes
0answers
63 views

Send trigger email when someone connects to a database

How do I set up a trigger or alert so that when anyone connects to a specific database, an email will get send with that user's login credentials?
2
votes
2answers
318 views

how to find the T-SQL of a sleeping session that is blocking some other process?

I have a stored procedure called sp_radhe that I put on my servers and it has been helping me to "see" what is happening internally. here is the code of this stored procedure: USE [master] GO SET ...
0
votes
0answers
51 views

SQL Server 2008 R2 FREETEXTTABLE with FORMSOF(inflectional, string)

Can anyone confirm that using FORMSOF(inflectional, ...) is redundant when querying FREETEXTTABLE in SQL Server 2008 R2? Using FreeTextTable without formsof... gives me the same result list but with ...
0
votes
1answer
106 views

How to insert values through inner join

Could anybody tell me how to do insert statement with inner join. i am trying to insert ProductName,ProductCompany,ProductPrice,ProductQty in Product table and CategoryName in category Table. And i ...
1
vote
1answer
23 views

Is it okay to clear SQL Server Buffer every weekend or month end?

We have started using SQL Server 2014 Enterprise edition on our new server having 32 gigs of RAM, and in less than 3 weeks the server process is accumulated 13 GB and plus of RAM. Including IIS ...
1
vote
1answer
87 views

SQL server 2014 Transaction Log Backup fails

I have setup a Maintenance Plan for my SQL server 2014 Database. This Plan Generates Full Backup every Sunday, and Differential backup every Day and Transaction Log every hour. This Maintenance plan ...
1
vote
1answer
91 views

SQL Server deprecated Functions effect

I'm using SQL Server in various versions. In case of this I'm using functions which got deprecated. My question is, how a deprecated function affects the usage of SQL Server? The functions are ...
2
votes
1answer
84 views

Single row update using primary key affects many rows

I'm doing a very simple datetime update in a table using its primary key: update TABLE set END_DATE = '2015-10-14 00:00:00' where ID = 1165027; I received the message (1 row(s) affected) 183 ...
0
votes
0answers
46 views

Can I use Data Quality Services for checking data integrity?

As Data Quality Services documentation seems too broad, could anyone tell me if the DQS is suitable for solving such issue: find if each row in one table has corresponding row in second table and if ...
3
votes
1answer
121 views

How to call modify() on a nodelist in T-SQL?

I have a process step where I have an XML document in a SQL Server column and I need to alter the text of a node that could occur multiple times. Modify() with 'replace' DML only operates on single ...
0
votes
0answers
28 views

Can you use SQL Server Development Edition for a website? [duplicate]

I've got a website I'm developing, and it's database driven. If I use SQL Server Development Edition as the back end, is that "legal"? I'll be the only developer using the software, but it will be ...
8
votes
3answers
473 views

zero-or-one to zero-or-one

How do I model zero-or-one to zero-or-one relationship in Sql Server the most natural way? There is a 'Hazard' table that lists hazards on a site. There is a 'Task' table for work that needs to be ...
2
votes
1answer
33 views

Selecting closest, non expired value [duplicate]

I'm trying to select multiple rows where the value is closest to the end date but not passed, and return only those rows. Here's a sample of some data: School Code | School Year | Semester | Term | ...
1
vote
1answer
625 views

SQL Server After Insert trigger does not perform actions

I have a problem with 2 After Insert triggers and 1 Instead Of Insert trigger. The After Insert triggers should perform some actions but that actions are not fired. When the Instead Of trigger fires,...
1
vote
1answer
80 views

Sql parent Child Query not working

I want to get parent and their child data which means, parents first, then children. Below is the query that I tried to convert to parent and their children SELECT a.FactorTitle, a.FactorCode, ...
0
votes
1answer
39 views

Pivot Results By Day [duplicate]

I have a table in SQL Server 2008, with mails received from a pop account. The table has the following columns: +------+----+---------+------+------+ | from | to | subject | body | date | +------+---...
18
votes
1answer
447 views

Statistics disappear after incremental update

We have a large partitioned SQL Server database utilizing incremental statistics. All of the indexes are partitioned aligned. When we try to rebuild a partition online by partition all of the ...
2
votes
2answers
555 views

How to increase ETL performance in Informatica for Netezza as a source and SQL Server as a target?

What settings or configuration on the Informatica server, in the Informatica software itself, or on the database servers can be changed to increase Informatica ETL throughput? What are some ...
2
votes
1answer
245 views

How to audit all DML operations on a SQL Server 2008 R2 database

In order to assess options of implementing indexed views, I want to check the usage of DML operations of the tables. The database is new to me as well as the relevant business knowledge, so I just ...
2
votes
0answers
72 views

SQL 2014 Availability Group Cluster or Listener

We recently deployed a setup of SQL 2014 availability groups and we are having some difficulty with the failover. Setup: Two windows server 2012R2 VMs in a windows cluster with a CNAME pointed at ...
0
votes
1answer
166 views

Connecting to the default instance of SQL Server connects to a named instance

I have 2 instances of SQL Server installed on one server: SQL002. One instance is a default instance, SQL2008 R2, and the SQL service is generally not running except for specific tests. The other ...
0
votes
0answers
52 views

SMO Not Scripting DB Objects

I have a PowerShell function included in the allusersallhost profile. It is basically scripting linked servers. When I execute the function from a PowerShell session, it scripts the linked server ...
0
votes
0answers
22 views

Query to get result in Excel file [duplicate]

The following query gives me the error Incorrect syntax near 'OUTPUT'. SELECT * FROM dbo.sales ; OUTPUT USING 'Driver=Microsoft Excel Driver (*.xls); DBQ=c:\\test\\sales.xls; READONLY=0' INTO "...
1
vote
2answers
1k views

Concatenate Columns in Stored Procedure [duplicate]

I have a stored procedure which returns a weekly result from input parameters start date and end date. I am getting multiple rows in the result. Requirement: I want to add the results of columns in a ...
10
votes
1answer
8k views

Cannot execute as the database principal because the principal “dbo” does not exist [duplicate]

I restored a backup of a database from SQL Server 2008 R2 to SQL Server 2012. When I try to access a particular page of my application, I get this error: Cannot execute as the database principal ...