sql

Batched output in SQL Server

I have a tables like the following CREATE TABLE Company ( Id INT ) CREATE TABLE CompanyNumbers ( CompanyId INT, NumberText VARCHAR (255) ) What I want as an output is this in pseudo code Give me all the numbers for company A as a single comma separated string, if the string contains more than 150 numbers output another row with next...

Can parameterized queries have output parameters?

In SQL Server is there a way to have an output parameter return the total number of records (for paging) in a parameterized query? ...

Grab SQL being executed in a shell script

Given a shell script containing a bunch of SQL statements, is there an option to redirect just the SQL statements to stdout/file? The structure of the script is something like this: ... for i in *list* do isql *credentials etc* <<EOF > a.out select *about 100 cols* from $i + "_TAB" go EOF done ... Query has been...

How do you change specific data in a datatable column?

I've populated a DataTable with a DataAdapter but I would like to change the values dynamically for all of the rows in that column in the DataTable. How do I go about doing this? Here is my current code: SqlConnection conn = null; string sSQL = ""; string connString = "Datasourceetc"; sSQL = "SELECT TEST_ID FROM TEST_TABLE"; SqlData...

Using backquote/backticks for mysql queries

Hi, I've been having a dilemma for a while now, so I said I'd see what you guys thing before I rest my case. Ever since I started playing with MySQL I was building query how I though was "the right way", with backticks. Example: SELECT `title` FROM `table` WHERE ( `id` = 3 ) As opposed to: SELECT title FROM table WHERE ( id = 3 ) ...

How can I write a T-SQL query to do a "like in"?

I need to write a valid T-SQL query version of the following pseudo-code: select * from newTable where [name] like in ( select [name] from oldTable ) I'm not sure how to go about this. Any help (even directing me to an existing question) would be great. Thanks! Edit: Per some comments I will clarify this particular case. The t...

How do I figure out if a column size is in bytes or characters from the column metadata?

I am working on some software that has to create dummy entries in various databases (Oracle, DB2, SQLServer) temporarily. Every column in the the row is filled with random data. The code uses java.sql.DataBaseMetaData class in java to get the COLUMN_SIZE attribute to figure out how large of a random string to store in the VARCHAR2 and ...

query a table not in normal 3rd form

Hi I have a table which was designed by a lazy developer who did not created it in 3rd normal form. He saved the arrays in the table instead of using MM relation . And the application is running so I can not change the database schema. I need to query the table like this: SELECT * FROM myTable WHERE usergroup = 20 where usergroup fiel...

copying stored procedures each into its own file using ant

I want to be able to have an ant task which would connect to a remote oracle instance and copy the stored procedures each into its own file. I know I can have an ant sql task which will do SELECT object_type, object_name, dbms_metadata.get_ddl(object_type, object_name) object_ddl FROM user_objects WHERE OBJECT_TYPE in ('INDEX', 'TRI...

How Many Days have occured during a Given Timespan

If we have date range such As: 1st October 2009 - 20th October 2009 Can we calculate how many Mondays have occured during The answer to this would be (3) M T W T F S S *1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 *20 21 22 23 24 25 26 27 28 29 30 31 Can we do this...

Crystal Reports -- filtering data from a database

I have a crystal report that is connecting to a database. The datatable from which this information is coming from is rather large and I need to know how to filter information. I have a field in my report that needs a list of requirements. The datatable i'm pulling from has all these extraneous requirements for other departments that ...

Distinct Route Pairs

I'm trying to find a way to get distinctive pairs. Suppose the table has 3 fields: id, city1 and city2. Sample Data: 1, New York, Los Angeles 2, New York, Philadelphia 3, New York, Houston 4, Los Angeles, New York 5, Los Angeles, Houston 6, Houston, New York 7, Houston, Los Angeles I would like the output to only include distinct pair...

Calculating Consecutive Absences in SQL

I need to calculate all Employees that have X number of consecutive absences within a date range in SQL. We have an Absences Table with 1 record for each day an employee is absent and a Calendar Table with the work days for the year. tblAbsences EmployeeID int AbsenceDate datetime tblCalendar WorkDay datetime Does anyone have any ...

Conditional SELECT MySQL

Don't know if this is possible, but I'd like to select records based on the field value of recur_type, where the 'm' is the day of the week. If it's a weekly recurring event, I need to make sure this is a day it recurs on, otherwise, I want to return all days. however, I'm getting an empty result set: SELECT * FROM wp_fun_bec_events W...

SQL datetime value

I have data formatted "2009-07-17T00:00:00-05:00" in varchar variable. How can I convert this data to datetime field in MS SQL server using query or TSQL? ...

TSQL Group By including all columns

I have a TSQL Query that does something like this: SELECT SUM(s.Amount) as TotalSales, p.ProductName FROM SALES s INNER JOIN Product p ON s.ProductID = p.ID GROUP BY p.ProductName The resulting output is TotalSales Product ----------------------- 123.45 Apples 234.56 Oranges 345.67 Grapes What I would like to do is...

finding instance of text in sql files after WHERE clause

Hi guys, I have a VS2008 solution with a database project, in it is a folder that holds a whole bunch of sql stored procedures (we are talking 100's). Due to a change that has been made to the ANSI_NULLS setting for all the sprocs I need to update all instances of '= null' to 'is null'. I cannnot do a "find and replace all" on .sql fi...

Forcing a SQL Remote Query to filter remotely instead of locally

I have a MS SQL Query that is pulling data via from a remote server. The data that I'm pulling down needs to be filtered by a date that is determined at run time.. When I run the query like this: SELECT * FROM SERVER.Database.dbo.RemoteView WHERE EntryDate > '1/1/2009' then the filter is applied remotely... However, I don't actua...

Create XML from SQL Server 2005 data using FOR XML

I'm trying to create an Excel XML that I want to store in an XML Field in SQL Server 2005. I have gotten this far: WITH XMLNAMESPACES ( 'urn:schemas-microsoft-com:office:spreadsheet' as "s", 'urn:schemas-microsoft-com:office:office' as "o", 'urn:schemas-microsoft-com:office:excel' as "x" ) select 'Order' as "@s:Name", ( selec...

How to rebuild view in SQL Server 2008

There is a view in my DB that someone defined with a * from one table. I just added a new column to that table and I want the view to reflect the new column. Besides re-executing the view creation script, is there another way to rebuild the view? I am looking for something similar to how sp_recompile will recompile a stored procedure ...