sql

mssql_bind empty string converting to NULL

I am currently using SQL Server 2000 Stored Procedures with PHP. Following the PHP doc, I use mssql_bind to assign value of parameters and then execute the Stored Procedure. The problem is that I got this bug which prevents me to bind empty strings to parameters (they are converted to NULL when the Stored Proc gets called) I dont't wan...

Insert port traffic into SQL table

I'm working on a project where a hardware device will be sending small chunks of text data (around every 5 seconds) to a port on the Windows 2003 server. I need to capture that data and insert it into SQL server for later retrieval, display, analysis etc. Obviously I need something to monitor the port for the incoming traffic, and then ...

Microsoft SQL Service Management Studio 2005 'Script XXX as CREATE To' problem

When using the 'Script Index as CREATE To' to duplicate an index from one table to another table I am not able to run the script. Without editing the generated code I get an error. I get the same error when working with any 'Script XXX as CREATE To' generated code. Any comments/suggestions to solve this problem? ERROR: Msg 170, Leve...

What happens to an uncommitted transaction when the connection is closed?

Are they rolled back immediately? Are they rolled back after some period of time? Are they left in an uncommitted state? Is the behavior the same if connection pooling is used and the connections are simply reset? ...

Using OPENROWSET in an INSERT statement while specifying the values of another column?

I'm using OPENROWSET(BULK ...) to insert the contents of a file into my table. The problem is that I also need to specify the value of another column in the same INSERT statement. I have something like this: INSERT INTO MyTable SELECT * FROM OPENROWSET(BULK 'c:\foo.bin', SINGLE_BLOB) I'm sure there's a way to also specify the value ...

Paging and selecting rows starting with a letter from a DB

I have a table where each row is a username with associated vote count The goal is to do paging by username so you can click on the letter C and get to the C's But if there are only like 2 C's, then you wanna show 18 D's as well or 12 D's and 6 E's So how do i find where to start the query like, select from whatever limit XX, 20 how ...

How do I clear a table with a lot of references in oracle?

For instance, suppose I have table A. Then I have tables B-Z that have a foreign key to table A's primary key. Then perhaps there are also some tables that have a foreign key constraint to a table in B-Z's primary key constraint. Is there any easy way to clear out table A and all of the tables that refer to A (or that refer to a table...

SQL select groups of distinct items in prepared statement?

I have a batch job that I run on a table which I'm sure I could write as a prepared statement. Currently it's all in Java and no doubt less efficient than it could be. For a table like so: CREATE TABLE thing ( `tag` varchar, `document` varchar, `weight` float, ) I want to create a new table that contains the top N entries for ev...

Set 1 Column of a MYSQL Query to Null

I have the following MYSQL query which when executed shows me all the invalid Comment_IDs in the table Order_Info that don't exist in the Order_Comments table. How would I modify this query to set the Comment_ID value to NULL for each record found? SELECT Order_Info.* FROM Order_Info LEFT JOIN Order_Comments ON Order_Info.COM...

How to execute SQL with comments and GO statements using SqlConnection?

I can't seem to execute SQL that creates a database using a DbCommand object. What am I doing wrong? Here's my code: DbConnection connection; // initialized and opened elsewhere DbCommand cmd = connection.CreateCommand(); cmd.CommandText = sql; cmd.ExecuteNonQuery(); Here's the error: The query syntax is not valid., near term ...

In ORACLE, How do I determine the fields that compose the primary keys of tables or views?

I'm doing an heterogeneous replication app in Django and need to determine the primary key fields of the tables that are going to get sync'ed. ...

SQL join puzzler

Given pricetable sym(k) pricedate(k) price msft 1/2/2009 33 msft 1/3/2009 34 msft 1/4/2009 35 ibm 1/2/2009 66 ibm 1/3/2009 65 ibm 1/4/2009 64 and datestable pricedate(k) 1/1/2009 1/2/2009 1/3/2009 1/4/2009 I would like to get this result set sym pricedate price msft 1/1/2009 null...

Update with SELECT and group without GROUP BY

I have a table like this (MySQL 5.0.x, MyISAM): response{id, title, status, ...} (status: 1 new, 3 multi) I would like to update the status from new (status=1) to multi (status=3) of all the responses if at least 20 have the same title. I have this one, but it does not work : UPDATE response SET status = 3 WHERE status = 1 AND title...

SQL server table population source

Hi, I have an Audit database(created by someone else). Something is polulating it, with table sizes data (which makes sense as it is Audit database). The SQL server has too many jobs. I want to know what is populating the audit tables. Is there anything like sys.comments etc? which can tell me what is populating tables or do I have ...

SSIS Package won't execute when called.

Hi, I have an 2005 SSIS package that I'm calling in a service created in VS 2005. The package will not run. The purpose of the package is to parse a file and put data into a "Load Table". The package runs perfectly on its own, but will not run at all when executed programatically - when I'm stepping through the code. The Event Viewer ...

Need to add "rollup" of entire dataset to bottom of DataGrid that is paged

In ASP.NET 3.5 I have a datagrid that is bound to a somwehat dynamic datatable. The datatable is created by taking three different tables returned from a dataset and combining them to create one table. It goes like this. The first datatable returns a list of the columns that will be in the final datatable. The second datatable return...

How to speed up LIKE operation in SQL (Postgres preferably)

Hi there, We have a Postgres database which contains 2 millions entries. We have tried using equality search and it works instantly (SELECT * FROM a WHERE b = "asd") But we would like "LIKE '%asd%'" operation to be fast too. How do we do that? ...

Using Alias with MySql

I want to add an amount to the rows returned from a select. I've been trying things along the lines of: select *, 3 as amount from products where etc.... ...and it works. However, I want to do the same thing for lots of rows in one go along the lines of: select *, 3 as amount, 2 as amount, 4 as amount...

Guides for PostgreSQL query tuning?

I've found a number of resources that talk about tuning the database server, but I haven't found much on the tuning of the individual queries. For instance, in Oracle, I might try adding hints to ignore indexes or to use sort-merge vs. correlated joins, but I can't find much on tuning Postgres other than using explicit joins and recomme...

sql 2005 force table rename that has dependencies

How do you force a rename??? Rename failed for Table 'dbo.x. (Microsoft.SqlServer.Smo) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.0.2531.0+%28%28Katmai%5FPCU%5FMain%29.090329-1045+%29&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&Evt...