Tag Info

New answers tagged

0

A JOIN will create a view with all the columns from both views. This will give you duplicate columns and values for the parent records. It sounds like you want to do a UNION without some of the columns in the second view. Just take the Select that generates the 1st view type UNION under it and then copy that select and paste it under the UNION key word and ...


0

You want to use the UNION ALL operator, rather than trying to JOIN on Username. A UNION ALL returns a combination of all results from Query A and Query B. Alternatively, if you only wanted DISTINCT records from the set, you could omit the ALL keyword and just do a UNION. SELECT UserID, Username, OperationFaName, OperationEnName, ...


2

As a matter of logical transactions @AlwaysLoadingData has you covered. But, have you tuned your log-files? Is your log file contiguous. Did you pick a size and per-allocate the file(s) Have you created an appropriate number of log-files do you have an excessive number of virtual log files. Run DBCC LOGINFO and make sure you don't have >1K rows (I think) ...


1

Transaction size is unlikely to be the primary cause of import slowness. There are legitimate reasons to break up transactions, even within a single table or to import a whole set of tables in a single transaction as you do. Also, for most requirements, large or small depends on the number of rows, not the number of tables. Reasons for larger ...


0

I would suggest you to use BCP OUT and BULK INSERT with native mode. Alternatively, you can use SSIS as well - to create a package and schedule it as per your need. BCP OUT and BULK INSERT with native mode - will be extremely fast. You can use my script found HERE (test it and modify as per your need). Also, read up on some tips to improve performance ...


1

There are a lot of ways to do this one that go beyond what I listed, but here's a start. 1) You could try the SQL Server Migration Assistant for Oracle. It's free and usually works well for me for smaller or medium sized datasets. http://www.microsoft.com/en-us/download/details.aspx?id=28766 2) Create a SSIS package. 3) You could create a linked server ...


0

If this is the only subscriber for the snapshot, you could have it execute a script after it has been applied. See Books Online.


0

Moving 1GB is not a trivial operation, though not a scary one either. A simple INSERT .. SELECT would have too much impact on the running server. Batching so only n-thousand rows are moved at a time would be better. 'n' can be tweaked to the idiosycracies of your particular system. Better still would be a dedicated ETL package. You'll need a SQL Server ...


0

You might be able to find something useful in Extended Events. Good luck.


0

If you run the first query twice in a row is the 2nd execution quicker? If so then the reason it is slow at first is because the execution plan is not cached for the first execution. Clearing the chache with DBCC FREEPROCCACHE will cause the problem to happen again.


0

I ran your script with no problems so my guess is that something is wrong with your conformation script. I don't see anything specifically wrong with it but here are a couple of links with additional information about disabling guest. http://blog.sqlauthority.com/2012/02/24/sql-server-guest-user-and-msdb-database-enable-guest-user-on-msdb-database/ ...


0

I hate to answer my own question, but I've decided Option 2 is the way to go for me. I finally found some "best practices" info, although it's related to PCI DSS compliance. It was a great read with some great recommendations. I can't implement all of them, and I may never pass a PCI audit, but I'll most definitely improve security. For what it's worth, ...


0

It looks like you're most of the way there, but the Foreign Key should go the other way around. The Orders table should have a FK reference to Transaction. This allows a Transaction to be connected to 1 or more Orders.


0

The problem is that these deletes are now taking over 10 hours to run. if you are not on Enterprise edition - as Partitioning - switching in and out is only supported in Enterprise edition, I would suggest you to create and ordered view on the top of table that you are deleting data from. This will incur less I/O and generate minimal T-Log. e.g. -- ...


0

Two suggestions come to mind. One is that the clustered index on both ApplicationEvent and ApplicationFault above be on the LogTime column. Assuming the data is posted to the table in chronological order, you'll have reduced fragementation from page splits and benefit from range scans when purging out older time periods. The second builds on the first to ...


0

Have you considered modeling where contact info exists independent of type. Could a contact be used for defense counsel and latter for vendor? Assuming you are trying to fix the data and hide it from the program might i suggest the following: Create a generic contact table and import all contacts here Create bridge tables like tblDefenseCounselContacts ...


0

This is problematic the program is declaring a query whose results will be spooled on the server. The the program will call for one record at a time from the result set. The literal variables (p1,p2,p5,p6,p7) should be related to the configuration of the system stored procedure. The stringed variables (p1-p7) are represented by the last 7 terms of the ...


0

SQL server has some special optimizations for count(*) that postgres does not have so the test may be invalid. I would benchmark the time it takes for the full set from the application point of view. Another fair test would be the time it takes to insert into a scratch table (via INSERT /SELECT)


0

line 1, character 38, unable to switch the encoding I ran in to this a while back sql server wants the source string type to match the utf code. SELECT CAST('<?xml version = "1.0" encoding = "utf-8"?><nobody> </nobody>' AS XML) GO SELECT CAST('<?xml version = "1.0" encoding = "utf-16"?><nobody> ...


3

You need to use a specific style when you expect to keep the same binary value when converting from a string. Otherwise SQL Server tries to encode the string the same way it would encode 'bob' or 'frank'. That said, your input string doesn't look correct - there is either a byte missing or one byte too many. This works fine if I drop the trailing E: SELECT ...


1

Sounds like a bad plan to me - and possibly around statistics. When you insert the data into a temp table, you can get statistics auto created on it. If your statistics think there aren't many records for that combination of stock and year, it could choose a really bad plan. Try updating your statistics and seeing how it goes. Or put a better indexing ...


2

Microsoft has a KB article about this behavior. You got a 32-bit OS with 32-bit application, so the process' address space is 4 GiB. AWE can be used to access more memory, but it is limited only for buffer pool usage. As per the documentation: The SQL Server buffer pool can fully utilize AWE mapped memory; however, only database pages can be ...


0

You can use Data Pump for SQL Server which allows transfering tables from any ADO-compatible data source including Oracle to MS SQL Database. It is not free but can be setup quickly.


4

Try the following: select office_id,[1] as value_1, [2] as value_2, [3] as value_3, [4] as value_4 from ( select office_id, setting_value, row_number() over(partition by office_id order by office_id) rnk from office_settings ) d pivot ( max(setting_value) for rnk in ([1], [2], [3], [4]) ) piv; Or without using PIVOT (generates the same ...


8

1. Does the trigger follow the relational database's ACID principle? Is there any chance an insert might be committed but the trigger fail? This question is partly answered in a related question you linked to. Trigger code is executed in the same transactional context as the DML statement that caused it to fire, preserving the Atomic part of the ACID ...


2

INSERT dbo.mytest(first_name, table_id) SELECT fn, ti FROM ( VALUES('f1', @id_for_test), ('f2', @id_for_test), ('f3', @id_for_test), ('f4', @id_for_test), ('f5', @id_for_test), ('f6', @id_for_test) ) AS x(fn, ti) WHERE NOT EXISTS ( SELECT 1 FROM dbo.mytest WHERE first_name = x.fn AND table_id = ...


6

@Kin is right about getting this info from the default trace, if it is in fact still there (depends on how busy your server is, and how long ago this happened). To get an e-mail immediately, though, instead of having to remember to check the default trace all the time just in case something happened, you can do this from a DDL trigger. Database Mail config ...


6

If you have default trace running and hopefully it is not recycled, it will be logged in there : SELECT SPID ,LoginName ,NTUserName ,NTDomainName ,HostName ,ApplicationName ,StartTime ,ServerName ,DatabaseName ,CASE EventClass WHEN 46 THEN 'CREATE' WHEN 47 THEN 'DROP' ELSE 'OTHER' END AS EventClass ,CASE ObjectType WHEN ...


1

Both missing indexes and long-running queries are available in DMVs, with the caveat that DMVs can get refreshed on some events, such as service restart, certain sp_configure changes, etc. Here's a missing index query from Bart Duncan's blog post, Are you using SQL's Missing Index DMVs?: SELECT migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) ...


2

Individual accounts are going to be much harder to administer. They give you more granular control over access, but you can not keep out your Active Directory administrators. Anyone who is granted local administrator rights on your server can gain sysadmin access to the instance. And, as a very good DBA told me once, if you can't trust your domain admins ...


5

If your log file is corrupt, my concern is that a backup/restore would retain the corruption. My approach (which would probably be faster to complete) would be: Run a full backup. Detach database. Delete/rename the log file. Attach the database and rebuild the log file. Take a second full backup. To attach the database and rebuild the log file, it's ...


2

This is now a known bug (present only in SQL 2012), that Microsoft is currently addressing. Workaround is to give user ALTER TRACE permission until is resolved. You can track status on Connect.


7

The only truly safe formats for DATETIME/SMALLDATETIME in SQL Server are: YYYYMMDD YYYY-MM-DDThh:nn:ss[.mmmmmmm] ----------^ yes, that T is important! Anything else is subject to incorrect interpretation by SQL Server, Windows, the provider, the application code, end users, etc. For example, the following always breaks:* SET LANGUAGE FRENCH; SELECT ...


5

Not knowing your data, the view definition would be: SELECT [SalesID], [Delivery Date], ROW_NUMBER() OVER (PARTITION BY [SalesID] ORDER BY [Delivery Date]) AS DateNo FROM [WhatEverTable] http://technet.microsoft.com/en-us/library/ms186734(v=sql.105).aspx ROW_NUMBER() by itself will return a running count of the rows, but when you add the ...


0

You can download some free administration tool, for example, SQL Manager Lite for SQL Server. It does not require additional client libraries.


0

DBCC CHECKDB makes extensive use of tempdb. It relies on a mechanism similar to snapshot isolation to make sure it always sees a transactionally consistent image of the database, independent what else might be going on. The error you receive is most likely due to the fact that there is not enough room on the drive that tempdb is on. However, the error also ...


0

From what I read, disabling guest account in master, msdb and tempdb is not advisable. For references and additional information; please read the below linked articles. From SQL*Server BOL The guest user cannot be dropped, but guest user can be disabled by revoking its CONNECT permission by executing REVOKE CONNECT FROM GUEST within any database other ...


6

There are couple of ways to do such forensic analysis : Using Default trace (provided that the default trace is running). The default trace create 5 *.TRC file that are recycled when they arrive to 20 MB size. : -- create a test procedure .. so that we can drop it use test_kin go create procedure usp_ABOUT_TO_DELETE as select name from sysobjects where ...


0

osql from the command line should work as well as SQL Server Management Studio...either way you have to download the SQL Server client. TOAD also has a free tool for working with the database.


3

You can download SSMS 2012 or 2014 which is a full seperate download and then use it to access tables, etc link: ssms 2012 can't comment on what/ how your plugin is.


2

If you look at the two execution plans you will see that the estimated cardinality is different in each case. When the actual value is in the SELECT statement the optimiser can use the cardinality of that one, actual value to choose an access path. When the SELECT has a variable the optimiser cannot know whether you have provided a very popular or very ...


0

Restore the DB into another DB , while restoring select "To a point in Time" and select "A specific date and time" and enter the date time before you deleted the record


6

For the future you can use a database trigger to log the DDL events. SQL Server DDL Triggers to Track All Database Changes


4

In order to transfer logins, you will need to use the sp_help_revlogin script and procedure. This will allow you to transfer logins and retain the password from on instance to another.


0

I am going to assume that there isn't an index on the date columns, otherwise I think that the query would have been structured differently. If there is, you can probably find a better performing one than this. The advantage of this query is that it can get all the data in one scan. The disadvantage is that it has to sort the data and join EventEmployee on ...


-1

50,000 rows joined against a million-row table seems to be a lot for any table without an index. It's hard to tell you exactly what to do in this case, since it is so isolated from the problem that you're actually trying to solve. I certainly hope that it's not a general pattern within your code where you're joining against many unindexed temporary tables ...


10

SampleTable is contained on 4714 pages, taking about 36MB. Case 1 scans them all which is why we get 4714 reads. Further, it must perform 1 million hashes, which are CPU intensive, and which ultimately drives the time up proportionally. It is all this hashing which seems to drive the time up in case 1. There is a start-up cost to a hash join (building ...


0

Using tsql: CREATE DATABASE MyDB ON (FILENAME = 'D:\MySQLServer\MyDB.mdf'), (FILENAME = 'D:\MySQLServer\MyDB_Log.ldf') FOR ATTACH;


1

If you have the Enterprise version of SQL Server, then I would recommend doing this using Change Data Capture (CDC). This is much easier and cleaner than setting up triggers to watch for changed data. Plus it reads the transaction log for changes and then logs those changes in system tables. You can use SSIS for your ETL. If you have 2012 installed, there ...



Top 50 recent answers are included