sql

How to check that there is transaction not yet commit in SQL server 2005?

anyone know what is the command to check un-commited transaction? ...

Is it possible to easily detect or predict a Primary Key violation in SQL?

My issue is that I want to run a query to check if the data I am using will be a duplicate of something already in the database, for example I have a Product Code and want to stop the same item being listed multiple times, and this Product Code is the Primary Key. Can I avoid doing this: SELECT ProductCode FROM tblProducts WHERE Product...

Date Range

Hello everyone, Is there a way to write a mysql query that takes two dates as inputs and gets the dates for each day in between those dates? Exact Duplicate: http://stackoverflow.com/questions/510012/get-a-list-of-dates-between-two-dates ...

simple text matching alogritm for use in stored procedure

I have a table with two fields in an sql server database and my asp.net application calls a stored procedure with a '@SearchString' parameter and the stored procedure finds all records where the @Searchstring value is found in the concatenation of two fields in the table, call them 'Field1' and 'Field2' So the logic looks like this(I ha...

How to update another table with the most recent data in SQL?

I'm trying to update Table1 in DB1 with data from Table2 in DB2. I can connect and get the data from DB2 Table2 into DB1 Table1, but the issue I'm having is getting the MOST RECENT data from DB2 Table2. I'm looking at 3 fields in DB2: f1, f2, & f3. f1 contains duplicates (and is where I'm matching from DB1 Table1) and f3 is a date f...

DB2 Print analog

What's the DB2 Version of TSQL Print? I've tried Print 'TableName:X' And Select 'X' As 'TableName' ...

How do I subtract using SQL in MYSQL between two date time values and retrieve the result in minutes or second?

I want to subtract between two date time values using SQL in MySQL such that I get the interval in minutes or seconds. Any ideas? I want to run a SQL query that retrieves uses from a database who have logged in like 10 minutes from the time. ...

DB2 SQL code to extract stored procedures

My colleagues and I have several hundred SQL stored procedures sitting on a hosted DB2/z database (version 8.1). We have no administrator rights and our access to the database is via QMF screens. Downloads are done through the 3270 terminal session with the TSO FT command. Is there a simple/efficient way to extract the definitions/text ...

Can I create a One-Time-Use Function in a Script or Stored Procedure?

In SQL Server 2005, is there a concept of a one-time-use, or local function declared inside of a SQL script or Stored Procedure? I'd like to abstract away some complexity in a script I'm writing, but it would require being able to declare a function. Just curious. Thanks!! ...

solr query with ruby - need to compare date time

My Lucene/Solr database contains a date column (created_at) that I need to use as a condition in a query. I'm new to RoR and assume that RoR automatically uses its own date object upon anyObject.save, and that Solr in turn reindexes that column in its own way. Regardless, the date is in this format: "2008-06-03 11:15:20" I can write ...

Checking database for NULL boolean value

I have a field in a table that is boolean, a range of records have no value (not true or false). How do I write my SQL statement to find these? I have tried the following SQL statements without success: 1) SELECT * FROM table WHERE field = NULL 2) SELECT * FROM table WHERE field != TRUE AND field != FALSE Any help would be greatly ap...

What is your preferred syntax for SQL aliases?

I have seen many different syntax styles for aliases in SQL. Are there any advantages/disadvantages to any of these syntax styles? What is your preference? Alias only: SELECT patient.nm_first patient_firstname, patient.nm_last patient_lastname, physician.nm_first physician_firstname, physician.nm_last phys...

tsql string concat with select and order by does not work with function in order by clause?

Consider the following tsql... create function dbo.wtfunc(@s varchar(50)) returns varchar(10) begin return left(@s, 2); end GO select t.* into #test from ( select 'blah' as s union select 'foo' union select 'bar' ) t select * from #test; declare @s varchar(100); set @s = ''; select @s = @s + s from #test order by s; selec...

the multi-part identifier could not be bound

select (case when t.freeplayabandoned != f.freeplayabandoned then 'freeplayabandoned' when t.freeplaydownloaded != f.freeplaydownloaded then 'freeplaydownloaded' end) from testtable t where not exists (select * from freeplay.egmfreeplay f where f.freeplaydownloaded = t.freeplaydownloaded) I was trying to get the col...

SQL Server 2000 > EXECUTE AS

Hey all. Could someone tell me how to elevate the execution of a stored procedure in SQL Server 2000? I've tried using EXECUTE AS, but that was added in SQL SERVER 2005. ...

Is it possible to have a default parameter for a mysql stored procedure?

I have googled this and keep coming up with "No it is not possible" but these posts were dated 2005-2007 so I'm wondering if this has been changed. A code example: CREATE PROCEDURE `blah` ( myDefaultParam int = 0 -- This breaks the code for some reason ) BEGIN -- Do something here END One of the solutions has been to pass null and...

How can I use namespaces in a SQL XML query with the "nodes" command?

Hello, I'm trying to query fields from the following XML query, (which is really a web service call): <soap:Envelope xmlns:xsi="[schema]" xmlns:xsd="[shema]" xmlns:soap="[schema]"> <soap:Body> <RunPackage xmlns="http://tempuri.org/"&gt; <xmlDoc> <Request> <SubscriberCode>543253</SubscriberCode> <...

Tool to convert T-SQL to graphical model?

Sometimes I am looking at complex SQL Server SQL statements and wondered if there's a tool which can represent the query into a graphical model. For example: You have a select query which joins (could be inner + left and right joins) with 10 tables. Is there a tool to take this query, represent the 10 tables graphically and draw the di...

SQL update query using joins

I have to update a field with a value which is returned by a join of 3 tables. Example: select im.itemid ,im.sku as iSku ,gm.SKU as GSKU ,mm.ManufacturerId as ManuId ,mm.ManufacturerName ,im.mf_item_number ,mm.ManufacturerID from item_master im, group_master gm, Manufacturer_Master mm where im.mf_...

SQL query to return one single record for each unique value in a column

I have a table in SQL Server 2000 that I am trying to query in a specific way. The best way to show this is with example data. Behold, [Addresses]: Name Street City State -------------------------------------------------------- Bob 123 Fake Street Peoria IL Bob 234 Other...