I have 3 tables: users (id, name) currency (id, name) accounts (id, user_id, currency_id, amount) And I want to read the data from accounts and present it in table-like view: owner currency1 currency2 currency3 1 0 0 0 2 10 20 30 3 0 5 10 Where owner is ID of accounts.ow...
I would like to know about how NULL values affect query performance in SQL Server 2005. I have a table similar to this (simplified): ID | ImportantData | QuickPickOrder -------------------------- 1 | 'Some Text' | NULL 2 | 'Other Text' | 3 3 | 'abcdefg' | NULL 4 | 'whatever' | 4 5 | 'it is' | 2 6 | 'technically' |...
My SSIS program reads as input from a .csv file. The file has about 60,000 rows. And my SSIS package fails during the read, saying cannot convert a certain column due to potential loss of data. Now, I am certain that the majority of the rows are correct. As I have tried pasting random subset of the file and the SSIS reads fine. But I ...
This seems like it should be something I already know. We need to run a bunch of sql updates in a transaction, and rollback if one of them fails. We also want to print a status message since we'll be running a large number of these. This would be simple if I were doing it in a general purpose programming language. But I am trying to...
Hey All, I have a stored procedure that works fine on my local SQL Server (2005 or 2008 cant recall off hand) but fails when I try to create the procedure on the Production server (SQL 2000). Any help would be appreciated. TIA. The stored procedure declaration is this: /****** Object: StoredProcedure [dbo].[AssignPCSCheckNumbers] ...
How to call a stored procedure from a user defined function In SQL 2000 ...
How to call a Stored procedure form a user defined function In SQL 2000 ...
When I perform a select/Insert query, does SQL Server automatically create an implicit transaction and thus treat it as one atomic operation? Take the following query that inserts a value into a table if it isn't already there: INSERT INTO Table1 (FieldA) SELECT 'newvalue' WHERE NOT EXISTS (Select * FROM Table1 where FieldA='newvalue...
I have an exception handler for the type SqlException and I'm looking at the SqlException.Number property of the exception to determine if a dead-lock occurred (1205). I'm currious, instead of writing SqlException.Number == 1205, is there an enum I can refer to that would produce something similar to SqlException.Number == SqlExceptionNu...
For a project I am working on, I have been asked to create an audit trail of all changes that have been made to records. This is the first time I have had to create an audit trail, so I have been doing a lot of research on the subject. The application will be developed in PHP/MSSQL, and will be low-traffic. From my reading, I have pre...
I have to develop database development standards for our organisation for SQL Server and any code that interfaces to it. The code used can be anything from .NET code to VBScript to SQL Server Jobs. Does anyone have a good link for this kind of thing? My quick list is follows: 1) Naming Conventions -- Stored Procedures usp_AppName_SP...
I'm trying to figure out which is faster, a clause like "WHERE IN (SELECT 1 FROM MyTable)", or a clause like "WHERE EXISTS (SELECT 1 FROM MyTable). Let's use the query from the SqlServer documentation: SELECT * FROM Orders WHERE ShipRegion = 'WA' AND EXISTS ( SELECT EmployeeID FROM Employees AS Emp WHERE Emp.EmployeeID = Ord...
I am trying to setup a data warehouse app for use my company with a partitioned data import table. I am trying to drop older data off and make room for new data. That is where I am getting this error message: Msg 4947, Level 16, State 1, Line 1 ALTER TABLE SWITCH statement failed. There is no identical index in source table 'AssetServer...
I have a Microsoft SQL server DB that imports some data which needs a bit of cleanup; some fields need to be remapped based on a second table. For example: Table: Data User Country Alice Australia Bob Sydney Carol London Dave London Table: Translations From To Sydney Australia London United Kingdom Unfortunately c...
By edict, we're using stored procedures for all interactions with our database. By organizational directive, we're using table value parameters in some of these stored procedures, specifically the ones I need to use. By legal edict, we can't use nHibernate or anything under LGPL (though, if necessary, we can fight back on that). Are t...
Is there a way to generate the sql statement for tables in sql server compact ? same as for sql server express+ ? Essentially a create table( .... ) statement as output ? ...
In my winforms application i am hashing the connection string in local level. But here a couple of questions. After my app decrypts the connection string, the connection string info is sent in clear text? And since my app is installed locally the man in the middle could be ANY user? How can i protect the connection string, becides th...
I have this c# web app that relies heavily on two dll files that i can't see nor edit. The rest of the app is visible and editable. The app generates SQL exceptions, and i would like to see the queries sent from the DLLs. Is there a way to do that? ...
Hello If I am developing for an environment that will make use of SQL Server clustering, what (if anything) should I be aware of? From what I've seen, I should handle an exception on a database operation and have two options: Display a 'retry' to the user. By the time the user retries the failover node should have kicked in. Wait a ...
Hi guys, We have a cluster SQL Server and we use a SRV DNS entry to locate the service (i.e sql.mycompany.com). MyCompany.com_tcp_http Service Location (SRV) [0][0][1433]sql.Mycompany.com Now, how I can use the .NET SQLConnection to use this dns name (instead of the server name/IP)? Any ideas? or should I use System.Net.Dns to transla...