What is the best way of transcribing the following Transact-SQL code to Informix Dynamic Server (IDS) 9.40: Objective: I need the first 50 orders with their respective order lines select * from (select top 50 * from orders) a inner join lines b on a.idOrder = b.idOrder My problem is with the subselect because Informix...
The consensus seems to be that all foreign keys need to have indexes. How much overhead am I going to incur on inserts if I follow the letter of the law? NOTES: Assume that the database is a good design, and that all of the joins are legitimate. All Primary and Foreign Keys are of type Int. Some tables are lookup tables, with fewer t...
Hi there, I am about to move servers and i was talking to somebody and they suggested using sql server express 2008 installed on the servers. I have full access to the server. Does this express engine work at the same speed (performance) as a true sql server 2008? I know about the limitations i..e max 4 GB per DB ... and max 1 GB of r...
I'm working on a stored proc that executes some dynamic sql. Here's the example I found on 4GuysFromRolla.com CREATE PROCEDURE MyProc (@TableName varchar(255), @FirstName varchar(50), @LastName varchar(50)) AS -- Create a variable @SQLStatement DECLARE @SQLStatement varchar(255) -- Enter the dynamic SQL statement i...
Assuming that best practices have been followed when designing a new database, how does one go about testing the database in a way that can improve confidence in the database's ability to meet adequate performance standards, and that will suggest performance-enhancing tweaks to the database structure if they are needed? Do I need test d...
Hi, Is it possible to install sql server 2005 express and sql server 2008 developer edition in one machine ? Any gotchas ? Michael ...
I am trying to perform a bulk insert onto SQL Server: BULK INSERT SampleData FROM '<UNC_Path>' WITH ( FIELDTERMINATOR = '|', ROWTERMINATOR = '\n' ) This works running against my local database, but when I try to run against our dev server, I am getting the following error: "Cannot bulk load because the file "..." could not be opened....
I have a mdx query that returns valid results in Sql Server Management Studio and would like to automate the execution of this query and put the result into an email. SSIS seems to be the natural fit for this. I have been able to run the mdx in an "Execute SQL Task" and populate an object variable with the result, but I am unsure how to...
I need to generate invoices in large batch which will be transformed to EDI and transported to our HQ. There is an order table with all the orders coming in. At some point in the day I have to grab a set (about 1k) and generate the invoices. Where would be the best place to generate the invoice numbers? On SQL Server backend? Or grab ...
I'm running a query that is timing out for first two times and returning results on the third time. How do I tell SQL Server to wait until the query is completed instead of timing out? ...
I've got a situation like this: Table: FunTable ItemKey.....ItemName.....ItemPriceEffectiveDate....ItemPrice 11.....ItemA.....6/6/2009.....$500 12.....ItemA.....6/15/2009.....$550 13.....ItemA.....9/9/2009.....$450 14.....ItemB.....3/9/2009.....$150 15.....ItemB.....9/9/2009.....$350 I need to do the following: Select ItemName as It...
My database has a table with thousands of records. The primary key is an integer. There's a lot of foreign key constraints associated with this column. I want to change this column to become an identity key. What's the best way to do it? I also need to send this update to our clients installations. Bonus points for an answer that work...
is there some way to tell sql server to use the (nolock) hint or every select in a stored procedure? is pretty tiresome to add it to each an every select.... ...
Hi there, I have an sp in SQL Server that when errors returns -4 what does -4 mean? Is there a table somewhere explaning what the possible return values are? ...
We are currently using Stored procs for most of our reporting, but I would like to have the flexibility of being able to join results sets with further criteria. To do this with stored procs, I need to store the resultset from the storedproc in a temp tables as illustrated below: CREATE TABLE #Top_1000_Customers ( CustomerCode BIGINT...
I have a table with three fields, FirstName, LastName and Email. Here's some dummy data: FirstName | LastName | Email Adam West [email protected] Joe Schmoe NULL Now, if I do: SELECT CONCAT(FirstName, LastName, Email) as Vitals FROM MEMBERS Vitals for Joe is null, as there is a single null field. How do you ov...
I think the answer to this is no going in, but I could use a sanity check. I have a proc that returns 3 tables. Table1 has 23 columns. Table2 has 12 columns. Table3 has 2 columns. I need to run this proc and dump the results of Table1 into a temp table. I created a @table and then did this: insert @myTable exec [myDb].dbo.[multitabl...
We currently use excel automation to calculate time series statistics and store the results in our SQL Server 2008 database for easy display/sorting/etc. later. I'm currently redesigning the home screen of our app to present the most important information (as identified by the team using the app) in dashboard form. I'd like the display...
Hello, we have an issue with an access database we are upgrading to use SQL Server as its data store. This particular database links to 2 sql databases, so I thought to simplify things, we have a view in the main database that linked to each table in the secondary database. That way access would only need to talk directly with one SQL...
How to get Previous Column Value? IIf id1 = id2 then display previous column id1 value id1 id2 1001 1001 1002 1002 1003 1003 so on... select id1, id2, Iff id2 = id1 then disply previous id1 value as idadjusted Output id1 id2 id3(Expected) 1001 1001 **1000** 1002 1002 **1001** 1003 1003 **1002** so on... ...