Hot answers tagged

24

@@identity returns a numeric(38,0). You'll need to cast it to get it to an int. SELECT CAST(@@identity AS INT) Also, try using scope_identity instead. If you have any triggers on the Customers table, you may end up getting the last identity from another table. Finally, since you're using dapper, you'll want to wrap all of that inside a stored procedure ...


10

It depends on what the queries are. ORMs are usually really good at CRUD, as they are usually simple. The more complex the query, the greater the chance of a bad query. You can tweak the generated queries by tweaking the LINQ statements. Sooner or later though, you'll get tired of fighting and use SQL queries or stored procedures for anything that is ...


9

Create table says: "IDENTITY Indicates that the new column is an identity column. When a new row is added to the table, Microsoft® SQL Server™ provides a unique, incremental value for the column. Identity columns are commonly used in conjunction with PRIMARY KEY constraints to serve as the unique row identifier for the table. The IDENTITY property can be ...


8

First, you should always have proper transaction handling in all of your procedures so that it does not matter if they are called by app code, by another procedure, individually in an ad-hoc query, by a SQL Agent job, or some other means. But single DML statements, or code that doesn't make any modifications, doesn't need an explicit Transaction. So, what I ...


8

According to the DB2 documentation, it is a weird isolation level, where changes are committed to the database as soon as you execute them (for others to see). It doesn't say explicitly, but, there is no roll-back, either, as far as I can tell. Basically, it means no transaction at all. "You say 'transaction', but, I don't really care". Thus -> chaos. ...


7

Even though the statements are executed in sequence, if they exist inside the same transaction, you must issue some kind of checkpoint between queries or tweek the transaction isolation level before starting the transaction. There are four values for tx_isolation: READ-UNCOMMITTED READ-COMMITTED REPEATABLE-READ (default) SERIALIZABLE You could set the ...


7

You can't use it against SQL Server. It was in DTS: Chaos Isolation Level In SQL Server - SQL Server Code,Tips and Tricks, Performance Tuning And is in SSIS: DtsContainer.IsolationLevel Property - MSDN More references, originally added by Nick Chammas in a comment: Rollback is not supported SQL Server and Oracle don't support the chaos isolation ...


6

Here is what I am seeing I see three queries, all almost identical. UPDATE people SET company_id = 1610, name = '<name>', password = '<hash>', temp_password = NULL, reset_password_hash = NULL, email = '<redacted>@yahoo.com', phone = NULL, mobile = '<phone>', iphone_device_id = 'android:<id>-<id>', iphone_device_time = ...


6

The SqlCommand.StatementCompleted Event will fire after each statement in a batch, and one of the properties of the event (well, pretty much the only property) is the number of rows affected by the statement that fired the event. Some notes: A requirement of getting this info is that you did not specify SET NOCOUNT ON;, or conversely, you did specify SET ...


6

It looks like you have an application that isn't properly closing or disposing of the SqlConnection objects. By default, SqlConnection has a max pool size of 100. The fix here would be to work with the application to find out why connections aren't being cleaned up, as they are still "active" in the particular connection pool, which is why you are unable ...


5

DB2 has a transaction isolation level called "Chaos": DB2Connection.ConnectionString property - IBM Knowledge Center Perhaps it's meant to be used for that. I'm really not sure, but I would assume it means no definable transaction isolation.


5

First of all I agree completely with Aaron Bertrand when he suggested in the comments you move the SQL into a stored procedure. This is also the clear winner as far as "the proper way to wrap a set of SQL operations in a .NET program" by the way. Since you're worried about managing code in two places you should check out database projects in Visual Studio. ...


5

DBMS_OUTPUT.GET_LINES is the way to get them back programmatically. Call it the same way as any other procedure. Or for a single line (which is probably easier to code) DBMS_OUTPUT.GET_LINE


5

"Why would SELECT @@IDENTITY return a decimal" Because it might be too large to fit in an int - it does not match the type of the identity column but as Richard says returns a numeric(38,0) (numeric and decimal are synonyms)


4

Individual queries are ok One of the biggest performance 'issues' with ORM tools (Entity Framework, Linq, LLBLGen, NHibernate, etc...) is not so much the performance of the individual queries that are executed (most are just CRUD calls which are retrieving a single record back based on a primary key). Incorrect use of Lazy Loading = Bad Performance It ...


4

First, tell your customer to get a real disk. Using a remote disk this way is exactly why you are having difficulty at this stage, and it could very well lead to a corrupt / unrecoverable database. You are rolling the dice every time this drive goes offline, even if only temporarily. Next, check the status of the database: USE master; GO SELECT state_desc ...


3

Syntax errors Immediate causes for the error: WHERE id = select currval('mytable_id_seq'); And a missing semicolon at the end of the DO statement: ... $do$; Better query On a closer look, this does not seem to be another case of UPSERT. It would seem one of the inserted columns needs to depend on multiple input values in combination with the freshly ...


3

If I understand you correctly: You'd use BatchSize to break the load into chunks eg 10k rows UseInternalTransaction determines if each batch is a transaction So: BatchSize = 0 -> UseInternalTransaction is irrelevant BatchSize > 0 -> Apply UseInternalTransaction is specified FYI, to monitor a SQL Server load in a non-blocking fashion select count(*) ...


3

An alternative, is if you are processing a higher volume of records, you may actually be getting a misleading error. It could be that you have used up all of your ports on your PC, you can check this by running Sysinternals TCPView. If you see thousands of TIME_WAIT, this is a possible cause. In essence, after your application closes its connection to the ...


3

So it turns out these two queries were at least part of the problem we were having. We have modified these two queries from: db.Execute("UPDATE people SET iphone_device_id=NULL WHERE iphone_device_id=@0 AND people_id<>@1", DeviceID, m_User.people_id); db.Execute("UPDATE people SET company_id = 444, name = 'Dad', password = '<pass>', ...


3

The 'answer' was that the ADO.Net providers in SSIS are basically useless and an embarrassment to the '.Net' moniker. They flake at more than a few thousand rows and are so hobbled as to be effectively useless. Basically, just stick to the old OLEDB providers and everything will be fine.


2

Here is the complete PowerShell code. It is inspired by this C# example. It was a bit difficult to find, because an unsolved question was spamlike reposted a hundred times. if ($ora_loaded -eq $null) { $ora_loaded = [System.Reflection.Assembly]::LoadWithPartialName("Oracle.DataAccess") } $ConnectionString = "Data Source=YOUR_TNS;User ...


2

If you are using csv , you can not do this. But if you are willing to use other tools like Microsoft SQL Server Reporting Services or Crystal Reports you may do this. SSRS Chart Example, here another one with code example. If you can not install Reporting Services Server or do not want to, you can still use it for basic reporting purposes. Take a look at ...


2

Another way, similar to what I proposed to your previous question: Return a set of well known type. Since your column list is dynamic, create a temporary table for the purpose. This announces the type to the system. As a side-effect you get a temp table to keep results for the duration of the session - like you needed in your last question. CREATE OR ...


1

At the time of this writing, there is not public repository for Connector/NET, if you have performance/scaling improvements you are welcome to contribute them (together with a bug report) at bugs.mysql.com.


1

As Pablo Montilla mentioned in a comment on the question, I should have added a code sample to indicate that I was using Dapper. (I have since added the sample.) The solution was to pass an argument to Dapper's Query method to tell it not to buffer all of the results before letting me iterate over them. The default behavior of Dapper is to buffer the ...


1

I would create One stored procedure with optional parameters. Also instead of using WHERE @Var is NULL or Column = @Var use dynamic sql to build your query. This kind of query where clause results in a very unefficient execution plan. Also Sql server doesn't do any shortcircuting when evaluating OR conditions in where clause, for example even if @Var is ...


1

Large, general-purpose search queries often produce an inefficient plan. This can be ameliorated by adding OPTION (RECOMPILE) to the query to invoke parameter embedding. Now your problem is that the general-purpose query is recompiled with every execution, which is itself an overhead. Avoid this by pulling out the common sets of parameters and have ...


1

You could enabled RCSI (Read Committed Snapshot Isolation) which should allow users to access a snapshot of the tables that are being updated. I would look into the import process as RLF mentioned. If the inserts and updates are being done from a single connection and that connection wraps everything it does in a transaction that is not committed until all ...


1

Try making your user account a member of the msdb database's SQLAgentReaderRole. The rights granted by that role membership are described here: https://msdn.microsoft.com/en-us/library/ms188283.aspx Look at the description of rights under SQLAgentReaderRole Permissions. You will see that it has rights to read the information on the jobs. This does not ...



Only top voted, non community-wiki answers of a minimum length are eligible