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.
1
vote
0answers
35 views
Database administration in “start-up” companies [on hold]
I am an Information Systems Engineer.
I'v been working with data for over 4 years, mainly in Business Intelligence and Data Science.
I'v been using Tableau, R and all of Microsoft's tools (sql ...
3
votes
0answers
18 views
Managed backup large database to Azure
Got a bit of an issue here that I need some help to solve.
Hosting a database, that is about 400Gigs. This is giving us a little headache getting it to work with Managed Backup to Azure Storage ...
0
votes
1answer
20 views
Log-in as service account in SQL Server Management Studio
The stored procedure that performs a CTE in production is being executed using a service account. However, the stored procedure returns empty and I tried checking this using SSMS but I my domain ...
0
votes
0answers
25 views
IO Test for sql server
I was trying to use Diskspd to check IO subsystem performance but one thing i was confused regarding the T parameter which is as per documentation is the number of threads per file. To my ...
1
vote
1answer
37 views
MS SQL 2017 Batch insert performance degradation
This one's a bit strange so bear with me:
I'm seeing extreme insert performance degradation after an insert was interrupted in a dirty fashion (the transaction was not committed and the connection ...
3
votes
1answer
435 views
Does clustering provide disaster recovery in MS SQL Server?
I mean can the nodes of MS SQL Server failover clustering be geographically separated and besides high availability also provide disaster recovery?
2
votes
1answer
64 views
Can an application connect to databases from on-premise SQL Server Instance and Azure SQL together?
Can a single application connect to two different databases each of which is in different Instances. One instance has on-premise deployment, the other one is Azure SQL(not SQL in Azure VM). So ...
1
vote
1answer
29 views
Simplifying union where limit query
How can I simplify this query. Requirements are always the same as I described below.
(select * from questions where points = 3 and type = 1 order by rand() asc limit 6)
union all
(select * from ...
0
votes
1answer
15 views
how to install mssql on to ubuntu
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Can't open lib '/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.2.so.0.1' : file not found.
pugazhl@pugazhl-LIFEBOOK-LH532:~$ # sqlcmd -S ...
3
votes
1answer
42 views
If at least a negative number, sum; otherwise just show
I have a need to sum values together only if there is a negative value present in a partition. If there are no negative values in a partition, it should just output the row.
Here's what I have right ...
0
votes
0answers
21 views
Troubleshooting Deadlock - Part 2
I am still troubleshooting deadlock mentioned in this questions. In that I got help in understanding why a select would hold on to a shared lock and I was able to test and confirm that also.
Now I ...
1
vote
1answer
33 views
Avoiding code duplication for counting and selecting the same resultset
I have a requirement to return the total number of records and paged data from a stored procedure. The row count should only be computed if a boolean parameter is true. How do I perform the 2 queries (...
0
votes
0answers
20 views
Running SSIS package as SQL Server agent job without installing Integration Services
I have set up a SQL Server agent job that runs an SSIS package stored as a File System (on a shared drive) on my SQL Server 2012 (remote) Production DB Server. I have created proxy account and am ...
0
votes
1answer
23 views
How to Replace Hash Match (Partial Aggregate) with Stream Aggregate
My execution plan shows a hash match (partial aggregate) instead of a stream aggregate even though I have a non-clustered index (which basically means sorting is already in place). It gives me a ...
1
vote
1answer
12 views
Azure server names vs Azure database names
Let's say I have one Azure subscription and 2 Azure instances: Dev-instance.database.windows.net for developer testing, and Prod-instance.windows.net for production work. I want to create a new ...
0
votes
0answers
16 views
Retrieve all system admin users activities in SQL Server 2012
I need to log all the system admin users activities in a SQL Server 2012 server. Is there any T-Sql query I can use to retrieve this information? If not, how can I get this information quickly?
1
vote
1answer
20 views
Migration from SQL Server 2008 R2 to SQL Server 2012 SP2
I have many backups on my SQL Server 2008 R2 system, and I must restore them on new SQL Server 2012 SP2.
My question is, after the restore on new SQL Server 2012, what can I do to verify that ...
1
vote
1answer
30 views
Converting a string to an object reference
Sometimes I end up in a position where I have a string version of my object reference like so:
'server.database.schema.table'
And I need to use it as an actual object reference:
'server.database....
1
vote
1answer
85 views
Should SQL Server 2000 throw an error if update statement fails within a SP?
I'm troubleshooting an issue on SQL Server 2000 where it appears that a update statement in a stored procedure failed, and the check for @@ERROR <> 0 didn't catch it, so the transaction was ...
2
votes
0answers
24 views
Query involving TVF very slow on SQL Server 2008
I have a query that runs in under 1 second on SQL Server 2014. A similar query takes over 12 minutes when run against a table on a linked server running SQL Server 2008. I don't know if it's because ...
1
vote
2answers
24 views
Can't change authentication mode from Windows to both SQL Server and Windows
As the title suggests I'm having problems changing the authentication mode from Windows to both SQL Server and Windows mode in version 12.0.2. I have tried right right-clicking the server, go to ...
0
votes
0answers
18 views
How to best define a Spatial Index
I find spatial indexes a dark art, and difficult to grasp.
I need some help in defining them in the following scenarios.
I have a table, called Positions that holds hundreds of millions ot rows.
In ...
0
votes
1answer
18 views
Delete all records in tables with reference to another reference
Sorry if the title to this question makes no sense. I really wasn't sure how else to word what I needed to do.
So I have the following tables in my database:
customers
customer_id (PK)
...
0
votes
0answers
24 views
Edition upgrade on SQL Server failover clustered instance
We did Edition upgrade on our active node for the SQL Server instance SQL01 and that was successful.
I have a question/doubt --> Do I need to proceed the same on other node as well since SQL Server ...
0
votes
1answer
38 views
Sub query to return same number of records and join
i have this query
SELECT DISTINCT (a.AssetID) AS [AR BarCode],au.[Asset_barcode] AS [Audit BarCode],a.AssetRegisterID, a.Description AS [Asset Description],
a.LocationRoomID,g.[RoomNo] AS [AR ...
4
votes
1answer
164 views
SQL Server - Bulk load blocking queries on heaps
SQL Server books online say in SET TRANSACTION ISOLATION LEVEL:
"Optimized bulk load operations on heaps block queries that are running under the following isolation levels:
SNAPSHOT
READ ...
1
vote
1answer
23 views
Msg 8985, Level 16, State 1,Could not locate file 'MyTal_Log'
Query:
DBCC SQLPERF (Logspace)
USE master;
GO
ALTER DATABASE Tal
SET RECOVERY FULL;
GO
BACKUP DATABASE Tal
TO DISK = 'D:\SQLServerBackups\MyTalLog.bak'
WITH FORMAT;
GO
-- Truncate the log by ...
1
vote
1answer
32 views
How to copy selected table data from sql server to mysql on a daily basis? [on hold]
I am having a retail pos application with MSSQL database. Now I need to transfer selected table data from SQL server to MYSQL database on a daily basis like job.
How to achive this?
6
votes
2answers
644 views
Computed column cannot be persisted because the column is non-deterministic
I know this is not the first time this type of question has been asked.
But why in the following scenario is the persisted computed column being created "non-deterministic". The answer should always ...
0
votes
2answers
39 views
Can both primary and secondary servers be online simultaneously in Log Shipping?
I am new to log shipping and I want to know can both primary and secondary servers be online at the same time like in replication, or only the primary one can be online while others get the log ...
0
votes
0answers
33 views
Cross-database chaining in SQL Server
While doing Audits found the Cross database chaining enabled for the system databases. When I run the alter database query to disable, it says that the value cannot be set.
A few questions :
Why the ...
0
votes
1answer
15 views
Why low cpu usage on sql server developer edition 2014
The CPU usage is low, around 15 %. CPU is 4770K. The SQL server data file is split in 8 parts and are stored on EVO 970 SSD, along with tempdb (also split in 8 parts).
The temdlog and database log are ...
0
votes
2answers
38 views
Insert into a table selecting data from a sub query [on hold]
I would like to insert a few data into a table selecting data from a subquery having multiple joins.
I tried the following way:
insert into mytable (columns)
select * from
(select columns
from ...
0
votes
0answers
38 views
Newbie Clustered Index question - when to NUSE?
I have a bit of a newbie question, which I feel I should really know having dealt with databases for so long, but...
I know the general advice for indexes is NUSE, but a lot of my tables have a non-...
0
votes
1answer
24 views
How can I permanently remove replication by script if I only have access to the subscriber?
We've been building a replication network of DBs to give us some degree of Always On without paying the full price tag. Currently we have a master DB which replicates to a handful of slaves, so that ...
0
votes
1answer
18 views
Export all SSIS packages from SSISDB
We have quite a few SSIS packages on SQL Server 2012 deployed to SSISDB, you can right-click on a project and select 'Export' saved as an *ispac file, however, is there a script to Export All Projects ...
-3
votes
0answers
30 views
SQL Server Bulk Insert Performance: Bulk Logged or Simple Recovery Mode? [on hold]
We are taking data from flat files, and using BULK INSERT to place them into SQL Server tables.
What is faster for bulk insert from text files into database?
Bulk Logged or Simple? Or they the same?
...
0
votes
0answers
30 views
Isolation level of queries to system objects, object catalog views etc
Today an alter procedure statement was called. The alter statement was made, the correct DB using statement was in place, and immediately SSMS returned 'completed successfully' to the DBA. The proc ...
0
votes
1answer
29 views
Grouping on an expression (and possible HAVE)
I have the following query, which I rewrote as a 'simple' example below.
The expression CONVERT(int, RTRIM(' ' + UPPER(CONVERT(char, a+b))))/c is just a weird example of a complex function.
I am ...
1
vote
1answer
14 views
Get data from a MDX linked server and use it on a FROM clause on SQL without OPENQUERY
i have the following SQL query on SQL Server 2012:
WITH MdxQuery (field1,field2,field3) AS (
SELECT field1, field2, field3
FROM OPENQUERY(my_linked_server, 'my_long_mdx_query')
)
SELECT * ...
2
votes
0answers
42 views
Best Way to Index Multiple Effective Date Columns
I've got a table that is primarily to represent relationships between entities (i.e. mostly composed of foreign keys). These relationships change over time, and so the table has a StartDate and an ...
1
vote
1answer
57 views
Slow Query with RESOURCE_SEMAPHORE wait info [on hold]
Hi I see lot of RESOURCE_SEMAPHORE as wait_info when i run sp_whoisactive in my sql database server.I see few CXCONSUMER as well.I learned that CXCONSUMER is because of parallel query execution.How to ...
0
votes
0answers
32 views
Auto Service Restart During SQL Server 2016 Standard to Enterprise Upgrade?
I understand that SQL Server services require a restart in order to activate SQL Server Enterprise edition when upgrading from Standard. But does this service restart happen automatically or does it ...
0
votes
0answers
15 views
Trouble dealing with deadlock in a Dynamics NAV 2009 instance
I'm having a lot of deadlocks ocurring in a Dynamics NAV 2009 instance.
Lots of select * ... and select top 1 * ... with (updlock) coming from the application.
Is there anything I can do without ...
2
votes
1answer
29 views
sparse column or DATA_COMPRESSION?
in one of my databases I have the following table:
CREATE TABLE [app].[applicantSkill](
[ApplicantSkillID] [int] IDENTITY(1,1) NOT NULL,
[applicantID] [int] NOT NULL,
[skillID] [tinyint] ...
0
votes
1answer
24 views
How can I get the last second of the last month as a default parameter value?
I am trying to get the last second of the last month as a default value for a parameter. I can get the last day of the last month from this query on social.msdn.microsoft.com
last month last day ...
1
vote
1answer
25 views
Encrypting table in SQL Server [on hold]
I'm a .NET guy that is trying to start using table encryption on some of my Users tables but I could do with some questions being answered first. I don't want just anyone to be able to see these ...
0
votes
1answer
26 views
Unpivot avoid cartesian product on empty columns
I have an issue with UNPIVOT.
The initial situation is that I receive some files with a fixed number of columns and I have to import them in my Database like that. That means my table has for example ...
0
votes
1answer
26 views
SQL Server 2008 R2 Auditing
I am new to database administration, and I need to implement auditing of all users of multiple databases.
Can we audit multiple users of a multiple database by creating just one audit specification?
...
0
votes
0answers
49 views
Long running SQL Server Query, eats up a lot of resource
I have already posted on stack however as recommended by the guys on there I am now putting it on here.
See my previous posts:
https://stackoverflow.com/questions/52850961/sql-query-long-running-...
