Tag Info

New answers tagged

5

Sure : select * from database_n.dbo.Table_1 UNION ALL select * from database_n+1.dbo.Table_1 UNION ALL select * from database_n+2.dbo.Table_1 -- etc.


5

The CPU time is tiny compared to the amount of actual time. The second time the query runs, it's fast - presumably once the data is in RAM (being one of the few things which benefits from the second run). Sounds to me like the problem isn't SQL, but the disk. Notice the PAGEIOLATCH waits increasing while your query runs. Have a look at what's happening on ...


4

You will need to create a master key on the new server - if it does not exit already. USE MASTER; GO if not exists (select 1 from sys.symmetric_keys where name = '##MS_DatabaseMasterKey##') CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Strong_Password'; Then you have to restore your certificate: CREATE CERTIFICATE serverCert FROM FILE = ...


0

Well, in the article you reference, he specifically states "The first category of scripts fall out of the realm of database changes." That said, I've worked with plenty of legacy codebases that had select statements in source control; they were just in the application code. (So, subject to all the problems you mention and hidden from the DBA.) Before ...


0

This is my effort. I have a longer script which loops through all of the DBs too, let me know if that's more useful: SELECT @@Servername as ServerName ,DB_NAME() AS DatabaseName ,d.name AS DatabaseUser ,ISNULL(dr.name, 'Public') AS DatabaseRole ,dp.permission_name as AdditionalPermission ,dp.state_desc AS PermissionState ,ISNULL(o.type_desc, 'N/A') AS ...


0

What is the best way to go about this? If database user dbuser02 is created and mapped to aduser01, what are the possible states dbuser01 could be left in? You can't create different database users mapped to the same login because a database user can be mapped to no more than one login. The CREATE USER statement for dbuser02 failed so dbuser01 is ...


0

FYI: this thread was referenced on the Spiceworks.com forums on a discussion on the same topic. I posted this answer there, and I'm sharing it here as well. I got confirmation from the SQL team that TLS 1.2 is now supported starting with SQL Server 2014 CU6. Be aware that CU6 is a post-SP1 build so it won't be supported in SQL 2014 SP1 until CU1.


3

It's not exactly the wanted output but pretty close: Query for versions 2012+: ; WITH invoices_payments AS ( SELECT StatementDate = InvoiceDate, InvoiceNo, TotalDue, TotalPaid = 0.0, ID = InvoiceID, Type = 0 FROM Invoice_TBL WHERE ClientNo = 1 UNION ALL SELECT PaymentDate, '', 0.0, TotalAmountPaid, ID = ...


1

Schema generation will totally depend on the amount of objects (tables, SPs, triggers, indexes, etc) in database. Schema is just a skeleton of the database without data. If you are concerned about using SSMS GUI, you can use PowerShell - which will definitely scale better - and can be automated easily as compared to SSMS GUI. I have tried scripting out ...


0

You'll need to deny permissions on the master database Add a new role DenyExecRole on master DB Add the desire login to that role Deny execute permissions: USE master; DENY EXECUTE TO DenyExecRole; GO


0

I believe it should be something like: USE <database name>; DENY EXECUTE ON OBJECT::<object name> TO user name; GO OR DENY EXECUTE TO <user name> Alternatively you can create a DATABASE role and assign Table rights to this role then attach role to user: Create a new Database Role named appropriately. USE ...


4

Order of table scan is never guaranteed. Order in which rows are locked is not guaranteed either. In addition, SQL Server has lock escalation, so you can't really say what the engine decides to lock, row, page, or table itself. Thus, deadlock may happen even if values of @id are different in concurrent sessions, but rows of interest happened to reside on ...


0

Not sure what you expect from partial backups, but they are only useful for read-only filegroups: partial backups are designed for use under the simple recovery model to improve flexibility for backing up very large databases that contain one or more read-only filegroups. Differential backups are a different story, but they are not used as you use ...


0

If you tell us if you did something before this error message appeared it will be easier to help. If you did an update, you can try folloying this KB article. If it's not, you could try, as specified in this article, to run the following command as an Administrator in the SQL Server Setup location: Setup /QUIET /ACTION=REBUILDDATABASE ...


2

Step A in Question 2 is not necessary. The SSDT-BI package contains Visual Studio 2013 shell and will install it for you if it isn't there (see the additional information). As for will it work? Kinda. You will be able to run packages which load data into SQL Server 2008 R2, but you will not be able to take advantage of the project deployment model ...


1

You are correct.. Install SQL Server on the new nodes Use SP_HELP_REVLOGIN to copy the user account to the secondary node Take a full backup and a single log backup of the primary server and restore on the secondary with no_recovery Break the mirror and remove the secondaries from the primary server Create the endpoints with TSQL or use the Mirroring ...


0

SELECT PP.PRODUCT ,PP.START_TIME ,PP.END_TIME ,PP.PLANT_QTY AS PLANED ,COUNT(PAD.WORKING_TIME) AS ACTUAL FROM PRODUCT_PLAN AS PP LEFT JOIN PRODUCT_ACTUAL_DETAIL AS PAD ON PP.PRODUCT = PAD.PRODUCT AND PP.START_TIME<=PAD.WORKING_TIME AND PP.END_TIME>=PAD.WORKING_TIME GROUP BY PP.PRODUCT,PP.START_TIME,PP.END_TIME,PP.PLANT_QTY ...


1

There is some Technet information on the intended behaviour of the export: https://technet.microsoft.com/en-us/library/dd255234(v=sql.105).aspx Images, charts, sparklines, data bars, maps, gauges, indicators, and lines are positioned within one Excel cell but they sit on top of the cell grid. Lines are rendered as cell borders.


3

First things first, before you restore anything - back up your log with no_truncate. If your database is offline, then take a copy of your LDF file first, then bring your database online, ignoring any errors, and then do your log backup. This log backup is then the last one in your recovery process, which involves a full database backup which doesn't have ...


2

You start by making a tail of the log backup of the damaged database, backup log database to disk = 'disk' with no_truncate. You then restore the database from your last full backup backup, with no recovery and then restore your all your log backups ending with the one you made with no_truncate see this kb article: ...


1

This is not native functionality. You can encrypt: Nothing, Sensitive items, Everything. There is no capability to assign one key for development and one for execution. Instead, the way to restrict execution is through proper security, which is never the last item. If the SSIS package is loading a database, you ensure that only the correct user(s) can do ...


0

There may be problem in port allocation. If more then one instance running on single server OS gives separate ports for each instance other then 1433 (default port). TO resolve the issue go to SQL Server Configuration Manager TCP/IP change to fix port and give a port no the (TCP/IP) Network provide. And use same port No. in connection string of your web ...


4

Adding explanation and a fiddle: http://sqlfiddle.com/#!6/c92b2/5. The query bellow: 1. uses a subquery to select the min and max versions by date (min and max are applied to integers to guarantee that for instance 6 < 16) 2. Then Selects the year (to group later), Date (to order) and the min - max versions SELECT LEFT(UPG_DATE, 4) AS Year , ...


18

This is a bit messy to get the final result because you have multiple SCHEMA_VER for each date. Before I demonstrate how to do this with dynamic SQL, I'll first show how to do it with static code to get the logic correct. In order to get the final result you can utilize both pivot and unpivot. But first, I'd change your original query to use the following: ...


1

Creating the schema - Tables that will be used for the example: DECLARE @table_1 TABLE ( someText VARCHAR(10) ) INSERT INTO @table_1 values ('abcdefghij') INSERT INTO @table_1 values ('fajiefhda') INSERT INTO @table_1 values ('hurineajsd') DECLARE @table_2 TABLE ( value VARCHAR(10) ) INSERT INTO @table_2 values ('d') INSERT INTO @table_2 values ...


0

Script out schema using SSMS GUI. And using this script create the database on the destination server. Run below script on the source Server in SSMS with Text output CTRL+T: select '"C:\Program Files\Microsoft SQL Server\100\Tools\Binn\bcp.exe" '-- path to BCP.exe + QUOTENAME(DB_NAME())+ '.' -- Current Database ...


2

If you want to be sure that you're backups are going to tape, you need to periodically recall the tapes and restore the backups. We recall tapes once every three months to do this, it seems like a lot of effort but it is not overkill if you value having offsite backups.


1

Figured it out -- Windows credential manager didn't have the credentials stored for the share. After adding the proper creds to the credential manager it worked ok.


4

No, absolutely impossible. Each sqlcmd invocation is a different session and transaction cannot span sessions. Your use of a transaction on the script that invokes the sqlcmd is completely useless, it has nothing to do with the scripts being run.


0

You can use any of the two methods below to connect to SQL Servers in a remote domain and continue using Windows Authentication. 1) runas /netonly 2) Through credential manager You can achieve this as explained here


0

You will have to setup a linked server forwarding the user credentials between the instances. There are step by step instructions here: It boils down to running this on DB_INSTANCE1\DB1 EXEC sp_addlinkedserver @server=N' DB_INSTANCE2', @srvproduct=N'', @provider=N'SQLNCLI', @datasrc=N' DB_INSTANCE2\DB2'; sp_addlinkedsrvlogin @rmtsrvname = ...


3

Here is what I do to ensure that I get the ONE (1) connection for SINGLE_USER. USE MyDB; -- Use the database you want to make SINGLE_USER GO ALTER DATABASE MyDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE; GO RESTORE DATABASE ----; /* Or whatever you need to do. */ GO ALTER DATABASE MyDB SET MULTI_USER; GO The trick is to be in the database already when you ...


0

If your databases are in full recovery mode you will need to backup the transaction logs, when that is done you can shrink the log files and setup a regular log backup to minimize growth and keep good backups. I would reccomend that you read this: https://technet.microsoft.com/en-us/magazine/2009.07.sqlbackup.aspx but first things first. The first step is ...


1

My users are finding the "XML PATH" solution and trying to use it, but they are not programmers, and become confused. Instead of trying to help them learn to use XML PATH, I created a procedure that takes the name of a table and returns a string containing the HTML markup to display the table as an HTML table. My solution dynamically derives the column names ...


4

In some cases, soft delete is not meant to be permanent. You can just defer the delete to some background job (say, right before you reorganize / rebuild indexes) so that the originating transaction doesn't have to wait for the deletes to occur (especially if you have cascading deletes, triggers, etc). In other cases, a soft delete is not a performance ...


2

For one, you can certainly simplify your query, for example (I've added schema prefixes, which you should always use): Select A, B, C From dbo.DocumentInfo As Di INNER JOIN dbo.TransmittalSheet TS ON TS.TransmittalID IN (Di.DocumentTransmittal_IMP, Di.DocumentTransmittal_OBS) Where TransmittalNumber = 'TS- 0001' UNION Select A, B, C From ...


2

After finding out exactly what the data was I had to modify the code given from Aaron Bertrand to handle "over punch dibol ascii" DECLARE @x TABLE(col VARCHAR(15)); INSERT @x(col) VALUES ('00000000014545p'),('00000000012645v'), ('00000000012345w'),('00000000012845x'), ('000000004123450'),('000000004512345'); WITH x AS ( SELECT col, switch = ...


3

You should really consider fixing this data before it gets into your tables, because working around bad data is cumbersome. DECLARE @x TABLE(col VARCHAR(15)); INSERT @x(col) VALUES ('00000000014545p'),('00000000012645n'), ('00000000012345p'),('000000004512345'); ;WITH x AS ( SELECT col, switch = CASE WHEN RIGHT(col,1) NOT LIKE '[0-9]' THEN 1 END FROM ...


2

While it's true the SQL needs one of these enabled, there's a workaround. Open gpedit.msc. In the Local Group Policy Editor, double-click Windows Settings under the Computer Configuration node, and then double-click Security Settings. Under the Security Settings node, double-click Local Policies, and then click Security Options. In the details pane, ...


5

The problem is that the trigger was initially written with very simplistic logic and with minimal testing - it assumed that the update or insert could only ever affect a single row at a time. The line you are asking about is attempting to assign a single value to a variable from a table that may contain more than one row (triggers in SQL Server fire per ...


0

Folks, I was able to resolve the issue by repairing SQL Server installation in Add/Remove Program. I restarted the service in SQL Server Configuration management and SQL Server Service,SQL Agent started and the application is also up and running. Hope this will help out someone. Thanks



Top 50 recent answers are included