Hi SET NOCOUNT ON stops the message that shows the count of the number of rows affected by a Transact-SQL statement or stored procedure from being returned as part of the result set. a) How can you read these messages using C# and ADO.NET ( I assume C# code reading these messages is the same regardless of whether T-SQL ...
I have 3 tables (tblPreference, tblCustomer, tblCustomerPreference) that look something like the following: tblPreference: ID | Name | DefaultValue (int PK) | (nvarchar(100)) | (nvarchar(100)) ------------------------------- 1 | Preference1 | 1 2 | Preference2 | Yes 3 | Preference3 | 1 ...
Suppose we have a denormalized table with about 80 columns, and grows at the rate of ~10 million rows (about 5GB) per month. We currently have 3 1/2 years of data (~400M rows, ~200GB). We create a clustered index to best suit retrieving data from the table on the following columns that serve as our primary key... [FileDate] ASC, ...
I have a long running job. The records to be processed are in a table with aroun 100K records. Now during whole job whenever this table is queried it queries against those 100K records. After processing status of every record is updated against same table. I want to know, if it would be better if I add another table where I can update...
Hi Friends, Can any body please, give me some ideas here. I have a huge table with 189999 rows. I need to select only the middle values of it. How can I do it. I added unique identifier column. How do I insert the values in it. Please, suggest. i would really appreciate your helps. I have accepted the answers not. I did not know how to ...
I have a job with around 100K records to process. I have got many suggestions to split this job in chunks and then process it. What are the benefits of process smaller chunks of data compared to 100K records? What is the standard way of doing it? e.g. Picking 10K records in a temp table and process at a time? ...
I need to do something like this in sql: declare @StartDate varchar(10) declare @EndDate varchar(10) set @StartDate='12/31/2008' set @EndDate='1/11/2009' Declare @date varchar = @StartDate while (@date <= @EndDate) begin -- some statements set @date += 1 -- basically increment by 1 day end How can I do the above correctly in SQL? B...
Is there a way to format a sql date like this: 3/11/2009 (as opposed to03/11/2009) 20/7/2008 (as opposed to 20/07/2008) Thanks! ...
I am trying to match existing reporting functionality while building up a new cube for a Proof of Concept. Can I retrieve data from my cube using an MDX query and put it into a recordset so I can then populate an Excel Sheet with the data? ...
I'm used to double-clicking *.sql script files to open them in SQL Server Management Studio. I recently upgraded to Win7, and now when I double-click a script file, SSMS opens with an empty environment. I can then use File/Open or File/Recent Files to open my script, but I would much prefer for it to just open whatever I double-clicked o...
Today we had a lot more activity than normal between our Rails app and our remote legacy MS-SQL Server 2005 database, and we started getting the error below intermittently. Any ideas what it is + how to prevent (besides avoiding the situation, which we're working on :) Error Message: ActiveRecord::StatementInvalid: DBI::DatabaseError: ...
Our current app stores all dates via the server's datetime. I'm thinking we need to update all datetime values in the database over to UTC time. Now for displaying these dates back to the user, I know .Net 3.5 has datatypes that are specific to date time offsets. But does anyone see anything wrong with setting an application variable...
Ok.. So I'm trying to improve my SQL skills and have a question. Here is a screen shot of the schema. (http://img509.imageshack.us/img509/97/screenhunter02nov121946.gif) Alright so I'm selecting a bunch of Report Bundles and other rows from a table you can see. I've got these two tables joining together correctly and displaying what s...
I have been trying to execute a MS SQL Server stored procedure via JDBC today and have been unsuccessful thus far. The stored procedure has 1 input and 1 output parameter. With every combination I use when setting up the stored procedure call in code I get an error stating that the stored procedure couldn't be found. I have provided t...
I'm running a query using "EXEC sp_helptext Object", but it returns multiple lines with a column name Text. I'm trying to concatenate that value into a single string but I'm having trouble trying to figure out the best way to do it using T-SQL. ...
Hello. I have several .rdl reports designed for our client. However, they need new ones, that are relatively similar to the old ones. Differences are in the "group" and "page fields" (these can be changed easily). I'm interested in adding new group and page fields. I would like to copy/paste the old report, and then add new (group and ...
We have a (large) SELECT query, that can take ~30 seconds to run. I am told that when placed in a view, it takes less than 5 seconds to run. My assumption is that SQL Server caches query plans for queries that don't change, so why the massive improvement in performance here? Just to be clear, this really is just a case of taking somet...
I have a stored procedure that imports differently formatted workbooks into a database table. does work on them then drops the table. Here is the populating query. SELECT IDENTITY(INT,1,1) AS ID INTO #test101 FROM OPENROWSET ('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=WorkBook.xls',[WorkSheet$]) Some spreadsheets seem to crea...
In our database we have an SQL server account that has the correct roles to access some of the databases. We are now switching to windows authentication and I was wondering if we can create a NT user group and map the privileges of the SQL server account to the NT user group? So if anyone in the NT user group is authenticated they will h...
I'm trying to export some tables from SQL Server 2005 and then create those tables and populate them in Oracle. I have about 10 tables, varying from 4 columns up to 25. I'm not using any constraints/keys so this should be reasonably straight forward. Firstly I generated scripts to get the table structure, then modified them to conform ...