sql

sql server bcp xml data

Hi I have a table which has a column which is of type xml. I have to extract data from this table and load the data into another environment. i am using bcp to extract and laod the target table but there are some special characters that is causing some issues when i bcp them into the target table. are there any workarounds thanks Ben ...

SQL Server simple Insert statement times out

Hi, I have a simple table with 6 columns. Most of the time any insert statements to it works just fine, but once in a while I'm getting a DB Timeout exception: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. The statement has been terminated. Timeout is set to 10 seconds...

Why can't I bind a formview to this datasource?

I've got this code here: SqlCommand CodeStatus = new SqlCommand(SQL, DB); DB.Open(); Reader = CodeStatus.ExecuteReader(); FormView1.DataSource = Reader; FormView1.DataBind(); DB.Close(); It works with a gridview, which then displays the data, but with this formview it doesn't display the data. Why not? The default mode is read...

SQL: How do you handle conditional delete?

I'm puzzle on how to write a store procedure to handle a conditional delete. I have a table with 4 columns (ID, A, B, C) and the data in the table look like this: 1 Debbie Jones " "(empty string) " " 2 John Jones " " " " 3 James Jones " " " " Columns A B C hold person name. How would i write a store procedure that will delete Debbie J...

SQL Server - Syncing two database.

We have a warehouse database that contains a year of data up to now. I want to create report database that represents the last 3 months of data for reporting purposes. I want to be able to keep the two databases in sync. Right now, every 10 minutes I execute a package that will grab the most recent rows from the warehouse and adds the...

How to best handle the storage of historical data?

I'm trying to determine how I should store historical transactional data. Should I store it in a single table where the record just gets reinserted with a new timestamp each time? Should I break out the historical data into a separate 'history' table and only keep current data in the 'active' table. If so, how do I best do that? With...

SQL Server 2005: Determine datatype of variable

Is it possible to determine the type of a local variable at runtime in TSQL? For example, say I wanted to do something along these lines: IF ( @value IS INTEGER ) Or IF ( TYPEOF(@value) = <whatever> ) Does anyone know of any way to accomplish this? EDIT: This is not for a specific task, this is more of a general knowledge questio...

need SQL complex query help

Thanks for your inputs. Full query SELECT * FROM (SELECT mcat.CATALOG_ITEM_ID, mcat.CATALOG_ITEM_NAME , mcat.DESCRIPTION, mcat.CATALOG_ITEM_TYPE, mcat.DELIVERY_METHOD, XMLElement("TRAINING_PLAN",XMLAttributes( TP.TPLAN_ID as "id" ), XMLELEMENT("COMPLETE_QUANTITY", TP.COMPLETE_QUANTITY), XMLELEMENT("COMPLETE_UNIT", TP.COMPLETE_UNIT), X...

What is the difference between Left, Right, Outer and Inner Joins?

I am wondering how to differentiate all these different joins ... ...

Using JDBC, how can I substitute multiple IDs into "DELETE FROM T WHERE id IN (?)"

I have some code that produces a set of primary key values that I want to delete from a database table. long[] keysToDelete = { 0, 1, 2, 3 }; and I'd like to use a PreparedStatement to execute the equivalent of DELETE FROM MyTable WHERE myPrimaryKey IN (0, 1, 2, 3); Any idea how? ...

Ordering the results of a query from a temp table.

I have a SQL query where I am going to be transferring a fair amount of response data down the wire, but I want to get the total rowcount as quickly as possible to facilitate binding in the UI. Basically I need to get a snapshot of all of the rows that meet a certain criteria, and then be able to page through all of the resulting rows. ...

LINQ to SQL query fails using two Contains statements

I have two tables, DH_MASTER and DH_ALIAS. DH_MASTER contains information about a person, including their name. DH_ALIAS contains AKA records about the person. The tables are linked by the Operator field which is a primary key in DH_MASTER. The users want to search by the name stored in DH_MASTER as well as search through all of thei...

retrieving rows with max date

I have two tables like this: Table1 (Number column is unique) Number | date 1234 2008-10-06 17:11:00 5678 2005-10-19 16:20:00 9023 2005-12-09 16:20:00 4243 2009-01-06 17:11:00 5234 2009-01-14 17:11:00 Table 2 Number | code 1234 A1 1234 B1 5678 A1 9023 A1 4243 C1 5234 ...

Is there a PostgreSQL equivalent to Oracle's analytics?

On another thread I saw a answer to a sql problem using Oracle's analytics. Is there a equivalent in PostgreSQL or generic SQL? ...

SQL Server Bulk Insert failing when called from .NET SqlCommand

I have a stored procedure which does bulk insert on a SQL server 2005 database. When I call this stored procedure from some SQL (passing in the name of a local format file and data file) it works fine. Every time. However, when this same stored procedure gets called from C# .NET 3.5 code using SqlCommand.ExecuteNonQuery it works intermi...

Reference generated primary key in SQL script

I'm trying to create a bunch of entries in a database with a single script and the problem I'm encountering is how to reference the generated primary key of the previous entry I created. For example if I created a customer, then tried to create an order for that customer, how do I get the primary key generated for the customer? I'm usi...

Sql Ansi to manage DateTime values

I'm developing a multi-database system. I want the difference between two dates in seconds. In SQL Server I got: DATEDIFF(second,stardate,enddate) In MySql: TIME_TO_SEC(TIMEDIFF(stardate,enddate)) My question: Does Sql Ansi have functions to manage DateTime values? i.e.: There are datetime functions generic for all databases? ...

Determine 'this week' in T-SQL

This is locale specific to the US wherein it considered that the start of a week is Sunday; I want to be able to ask SQL to give me the date of the next Sunday relative to today [getDate()]. If today is Jan 15 it should return Jan 18; if today were Sunday it should return the following Sunday which is the 25th. This would be trivial to w...

Count and group by query for counting rows in database table based on 3 to 4 conditions

I am running a market survey and all the survey data is saved in the database. I need a query for counting the number of rows in which option "1" is selected for question "1", option "2" for question "1" and so on for all questions and options… I need to specify few conditions here, I have to match distinct ID's of 3 tables and display t...

Queries for migrating data in live database?

I am writing code to migrate data from our live Access database to a new Sql Server database which has a different schema with a reorganized structure. This Sql Server database will be used with a new version of our application in development. I've been writing migrating code in C# that calls Sql Server and Access and transforms the dat...