sql-server

SQL Server parallels to Oracle DBMS_METADATA.GET_DDL ?

I'm looking for command line or scripted solutions to pull the DDL out of SQL Server 2005+ for all database objects: tables, stored procs, views, indices/indexes, constraints, etc. GUI tools are not of interest. Preference is for built-in tools, since that would be the most comparable to Oracle's DBMS_METADATA stuff. Also, preference f...

SQL 2008 stored proc returns nothing if CROSS APPLY is nothing. How can I fix?

The below stored proc returns data from a number of related tables and also does a cross apply on the votes table. This cross apply returns an average of all truthfulness values in the truthid column associated with a particular articleid and the same for the relevanceid column. This works great except for when there are no votes yet cas...

can i combine several select queries in .net so they are returned in 1 batch?

i want to combine several select sqls into 1 connections to the database (SQL Server). the queries will contain different tables data so it must not done using sql. basicly i want to do that follwoing select * from Table1 Go Select * from Table2 Go Select * from Table3 then send this query to the database and the i want to result to b...

How secure is using an encrypted appSettings element in your app.config?

In a CodingHorror blog post a commenter made the observation that it is more difficult to obscure sensitive configuration information (e.g. SQL Server connection strings) in a program than it used to be, because the obscuring algorithm can be disassembled quite easily with Reflector. Another commenter suggested that encrypted appSetti...

is it possible to execute several Sql Inserts in the same batch the same way as its possible to execute several selects using SqlDataReader.NextResult()?

i want to execute several inserts at the smame batch to speed up the program. so its possible to do the following? Insert Into Table1 (a) Values (@a); Insert Into Table2 (b Values (@b); Insert Into Table3 (c) Values (@c); and if its possible then how should i pass the parametrs to the Sql Parameters? basicly i want to do like this ex...

Why am I getting InvalidCastException when casting object to integer?

I had an odd problem, here's the setup: ASP.NET 3.5 app / MSSQLSERVER 2008 back-end. I called ExecuteScalar from my code, which returned an object, then I tried casting that object to int (i actually have a generic method T ConvertDBValue<T>(object o) {...} , but that's not as important). ExecuteScalar is hitting the following spr...

How to implement a do-while loop in tsql

I'm trying to figure how to implement this in TSQL do update stuff set col = 'blah' where that_row = 'the right one' select trash from stuff ... until some_condition The only iterative control flow sentence provided by Transact-SQL is while (condition) sentences that first evaluates the condition and if that condition is true the...

Sql Query Optimization

I am not so proficient in TSql as of now (writing since last 4/5 months) but I have written many queries. Although I have given the outputs, sometimes I feel that the queries are not so optimized. I searched in google and found lot of stuffs about query optimization, and they ask to look into the query plan(actual & estimated) for the ...

Query timeout, depending on dates

Hello, we have some user forms that include a 'period' menu, where the user can request the server to return data for a specific date range, such as "Purchase Orders issued between the 1st and the 10th of October 2008". The logic is then to add "on the fly" the date range to the original sql query and requery the data. The syntax of t...

SQL Server StoredProc vs UDF inline table

I have written a complex query that will return me a list of IDs. Now I want to re-use this query so that I join the results with another query. For that I plan to put this into a Stored Proc or an UDF and then use it to insert into a temp table. Something like below 1) Put the query in a Stored Proc and insert it into temp table INSE...

List order - What UI is best

Hi people, I deal with a lot of lists of data in many scenarios CRM's, CMS's etc etc However i now require the need to have a UI in the administration area in order to arrange the display order on the navigation menu for example on the front facing website. I already have a column in my sql database [Order] as an int. I am building in...

SQL server licenses/prizes (on dedicated web server)

Hi I bought my own dedicated server to host my websites. I want to use SQL server as database platform. However.. de licensing options are not transparent Can someone help me ? What I found until now : 2005/2008 express version - Free - Limited to 1CPU/1GIG MEM/4GB filesize What are the other options ? ...

How do I return a value to a sqldatareader if value is null?

I am currently using a sql data reader (in vb.net) to extract an article object via a stored proc from a SQL Server 2008 database. Part of this object includes the two properties shown below: theArticle.Truthfulness = ((myReader.GetInt32(myReader.GetOrdinal("Truthfulness")))) theArticle.Relevance = ((myReader.GetInt32(myReader.GetOrdina...

Join characters using SET BASED APPROACH (Sql Server 2005)

I have a table where the data are like Data a b c I need to write a SQL Query to bring the following output Data abc Note:~ The number of characters are not constrained to 3. I have solved using while loop. But I have to do it using set based approach.So how can I join those characters? I tried using COALESCE but no luck Pl...

Picking info using junction table(SQL SERVER 2005) [ SET BASED]

I have 3 tables 1) tblPurchaser having 2 columns: PurchaserId PurchaserName 1 A1 2 A2 3 A3 2) tblCar having 2 columns: CarId Carname 11 C1 12 C2 13 C3 14 C4 And the last is a junction table tblInformation where the information about those persons are given who has purchas...

How do I insert information in a SQL table if the table has a foreign key in it?

Say I have a user table with the fields: Name, Email, Telephone, IDCareer In this table IDCareer is the foreign key. I have another table called Career with the fields: IDCareer, CareerName My plan it to have a ComboBox on my form and pulls a list of available careers directly from my DB. My problem is this is my first time using SQL ...

Using LEFT on a TEXT-field in SQL Server

In a table, I have a text-field. I need to be able to select only the first 200 chars of the field - but LEFT does not work with TEXT-fields. What to do? ...

SQL Server TSQL conventions/patterns on SET, WITH and ENABLE

After years of using TSQL, I still cannot figure out when to use SET, WITH or ENABLE. When you read TSQL statement like, ALTER TABLE Person.Person ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ON); It looks more intuitive and readable if it was written like (invalid query below), ALTER TABLE Person.Person SET CHANGE_TRACKI...

SQL UPDATE - IS there a more elegant and efficient way of doing this?

I have 3 tables Site, Price and PriceMonth. Site has many Prices (joined on siteId). PriceMonth holds a date which is a looked up by Price (joined on PriceMonth). The idea is that the price records are created when a site is created as place holders. When a user clicks a Price row in a view - I need to update the prices for the res...

ID should not display between the specified date?

Using SQL Server 2000 Database1.Table1 ID Name Date title 001 Ravi 23-02-2009 Accountant 001 Ravi 24-02-2009 Accountant 001 Ravi 25-02-2009 Accountant 002 Ram 21-02-2009 Supervisors 002 Ram 25-02-2009 Supervisors 002 Ram 26-02-2009 Supervisors So on…, Database2.Table2 ID Name Date1 Date2 001 Ravi 23-02-2009 ...