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
75 views

SQL Trace for compatibility check?

My idea is to trace a few days all statements (scripted the replay template) on a SQL Server 2008 r2 and replay those against a deployed version of the db on a newer version (SQL Server 2014). Is a ...
3
votes
0answers
166 views

Install SQLServer 2000 on Windows 10 [duplicate]

I need to install SQL Server MSDE 2000 SP4 on Windows 10. I have other machines that run Windows 7 and 8.1 and SQL Server MSDE 2000 works fine (with merge replication configured too). Now I testing ...
0
votes
0answers
25 views

Ways for a Windows Administrator to get sysadmin [duplicate]

Based on a question why Builtin\Administrators don't get sysadmin by default, I want to ask which ways are known to get sysadmin rights as a normal windows Administrator without any given rights ...
3
votes
3answers
515 views

“Specify SQL Server administrators” - unusual default value

When installing SQL Server, you get to specify SQL Server administrators, i.e., the list of users initially in the sysadmin role. By default, this list contains the current user. (Image quoted from ...
2
votes
2answers
291 views

avg_page_space_used_in_percent and fill factor

sys.dm_db_index_physical_stats contains column avg_page_space_used_in_percent. How does this fit in with the fill factor of an index? For example: I have index idx_HeaderInfo I create the index ...
4
votes
1answer
101 views

Should Service Broker use SQL or Windows Authentication?

I was curious what the best practice would be, for properly setting up Service Broker in SQL Server 2008. Should I use a SQL authenticated user, or should I use a Windows NT Authenticated user based ...
2
votes
0answers
1k views

How to resolve COM server application local activation permission for machine account

I am trying to enable SSIS package logging to event logs. When an attempt is made to write to the event log the following error appears in the system event logs: The application-specific ...
1
vote
1answer
151 views

Step by step establish a connection between SQL Server 2014 and Oracle 12c

I have two servers (both servers are on Windows OS): Server_A running Oracle 12c Server_B running SQL Server 2014 I want these servers to be able to see each other, so I can SELECT anything I want ...
2
votes
1answer
55 views

remove maintenance plan

I have the following error while trying to delete some old Miantenance plans on the MS-SQL server: Please help.
1
vote
1answer
104 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
81 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
410 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
260 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
526 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
189 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
214 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
51 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
920 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
222 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
195 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
600 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
523 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
297 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
269 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
308 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
50 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
103 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
90 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
45 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
119 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
468 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
624 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
77 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 | +------+---...