Is there a way when executing a stored procedure in Management Studio to get the data types of the result sets coming back? I'm looking for something like functionality of when you pass a table name to sp_help ...
Is there a way to call out from a TSQL stored procedure or function to a webservice? ...
I have the following script. It replaces all instances of @lookFor with @replaceWith in all tables in a database. However it doesn't work with text fields only varchar etc. Could this be easily adapted? ------------------------------------------------------------ -- Name: STRING REPLACER -- Author: ADUGGLEBY -- Version: 20.05.2008 (1.2)...
If I select from a table group by the month, day, year, it only returns rows with records and leaves out combinations without any records, making it appear at a glance that every day or month has activity, you have to look at the date column actively for gaps. How can I get a row for every day/month/year, even when no data is present, i...
I'm maintaining some code that uses a *= operator in a query to a Sybase database and I can't find documentation on it. Does anyone know what *= does? I assume that it is some sort of a join. select * from a, b where a.id *= b.id I can't figure out how this is different from: select * from a, b where a.id = b.id ...
I've worked with T-SQL for years but i've just moved to an organisation that is going to require writing some Oracle stuff, probably just simple CRUD operations at least until I find my feet. I'm not going to be migrating databases from one to the other simply interacting with existing Oracle databases from an Application Development per...
What is the best way to get identity of inserted row? I know about @@IDENTITY and IDENT_CURRENT and SCOPE_IDENTITY but don't understand the pros and cons attached to each. Can someone please explain the differences and when I should be using each? ...
Is there a graceful way to handle passing a list of ids as a parameter to a stored procedure. For instance, I want departments 1, 2, 5, 7, 20 returned by my stored procedure. In the past, I have passed in a comma delimited list of ids, like the below code, but feel really dirty doing it. SQL Server 2005 is my only applicable limitatio...
I'm trying to determine the best way to truncate or drop extra decimal places in SQL without rounding. For example: declare @value decimal(18,2) set @value = 123.456 This will auto round @Value to be 123.46....which in most cases is good. However, for this project I don't need that. Is there a simple way to truncate the decimals I...
Ideally, this script could be run multiple times, as new tables were added to the database. SQL Server Management Studio generates scripts for individual database objects, but I'm looking for more of a "fire-and-forget" script. ...
I'm looking to convert a SQL like statement on the fly to the equivalent regex i.e. LIKE '%this%' LIKE 'Sm_th' LIKE '[C-P]arsen' What's the best approach to doing this? P.S. I'm looking to do this on the .Net Framework (C#). ...
When should I choose one or the other? What are the implications regarding space and (full-text) indexing? BTW: I'm currently using SQL Server 2005 planing to upgrade to 2008 in the following months. Thanks ...
I have never "hand coded" creation code for SQL Server and foreign key deceleration is seemingly different from SQL Server and Postgres...here is my sql so far: drop table exams; drop table question_bank; drop table anwser_bank; create table exams ( exam_id uniqueidentifier primary key, exam_name varchar(50), ); create table qu...
Hi, I'm still fairly new to T-SQL and SQL 2005. I need to import a column of integers from a table in database1 to a identical table (only missing the column I need) in database2. Both are sql 2005 databases. I've tried the built in import command in Server Management Studio but it's forcing me to copy the entire table. This causes error...
I'm looking for suggestions on how to debug and chase down deadlock issues in an SQL Server 2000 database. I've had it recommended to me to use trace flags 1024 and 3605, which I have found give me the following: 1024 - this trace flag returns the type of locks participating in the deadlock and the current command affected. 3605 - th...
If you're creating a temporary table within a stored procedure and want to add an index or two on it, to improve the performance of any additional statements made against it, what is the best approach? Sybase says this: "the table must contain data when the index is created. If you create the temporary table and create the index on an e...
Hi folks - I'm just wondering if there's a better way of doing this in SQL Server 2005. Effectively, I'm taking an originator_id (a number between 0 and 99) and a 'next_element' (it's really just a sequential counter between 1 and 999,999). We are trying to create a 6-character 'code' from them. The originator_id is multiplied up by a...
We all know T-SQL's string manipulation capabilities sometimes leaves much to be desired... I have a numeric field that needs to be output in T-SQL as a right-aligned text column. Example: Value ---------- 143.55 3532.13 1.75 How would you go about that? A good solution ought to be clear and compact, but remember there is s...
What would be the best way to calculate someone's age in years, months, and days in TSQL (SQLServer 2000)? The datediff function doesn't handle year boundaries well, plus getting the months and days separate will be a bear. I know I can do it on the client side relatively easily, but I'd like to have it done in my sproc. ...
This one will take some explaining. What I've done is create a specific custom message queue in SQL Server 2005. I have a table with messages that contain timestamps for both acknowledgment and completion. The stored procedure that callers execute to obtain the next message in their queue also acknowledges the message. So far so good. We...