sql

SQL to MS Access export

I am trying to replace a DTS access exporter package with a exe we can call from our stored procedures (using xp_cmdshell). We are in the middle of a transition between SQL 2000 and SQL 2005, and for the moment if we can not use DTS OR SSIS that would be the best options. I believe I have the following options: Using a SQL data reade...

Sparsely populated lookup table in SQL

I'm trying to write a lookup method for determining a SMS message to send to a user based on a few parameters associated with the user/system. We will have a default message that will be used as a last resort, but there are multiple ways to override the message by various parameters. Here is what I have so far for the lookup query - ar...

Insert values into multiple SQL tables

What is a good method to insert table data that replies on many tables in C# and SQL server? So for example say I have the following tables: Products: PK=PID, Product_Name Build: PK:BID, Build_Number, FK:PID Files: PK:FID, File_Name, FK:PID FileDetails: PK:FDID, FK:FID, FK:BID, File_Size, File_Version So all the Primary Keys are auto...

How can i pass a null value for date in vb.net to sql stored procedure?

Hi guys, My application is asp.net with vb. In my page I have a textbox for passing date. If I didn't enter date and on clicking submit, I have to pass null value to the stored procedure. I tried following codes such as DBNull.Value and DateTime.MinValue. In that case instead of null ,"#12:00:00#" is passing. I have to pass Null. So can...

ADO.NET TableAdapter parameters

I have a query that I wish to run through an ASP.NET TableAdapter that contains an 'IN' clause which is to receive it's values through a parameter. My question is, how do I specify this parameter? I thought of writing the conditional statement like this: AND b.group_category_id in (@ParamList) where the @ParamList is a String of the ...

More elegant solution for pulling value from DB using recordset

Is there a better solution (using less code) for the following code snippet. It is something that I find myself doing a lot of in VB6 and was hoping to trim it down. As I understand it Connection.Execute will not work SQL = "SELECT SomeID FROM TableA" RecordSet.Open SQL, Connection, adOpenStatic, adLockOptimistic, adCmdText ...

How to execute an .SQL script file using c#

Hi I'm sure this question has been answered already, however I was unable to find an answer using the search tool. Using c# I'd like to run a .sql file. The sql file contains multiple sql statements, some of which are broken over multiple lines. I tried reading in the file and tried executing the file using ODP.NET ... however I don't ...

In Postgres / SQL how can I search for names that doesn't start with a letter?

I want to find all the names that start with numbers, weird chars (.,-#$, etc) and everything else that isn't a letter. For example, i have 3 names: John, #1 John and 2John. What I want to get is the last 2 names. (and I don't know what weird chars the names can start, so it must be something like ![a-Z]).. I'm using postgresql. ...

Best Schema to represent NCAA Basketball Bracket

What is the best database schema to represent an NCAA mens basketball bracket? Here is a link if you aren't familiar: http://www.cbssports.com/collegebasketball/mayhem/brackets/viewable_men I can see several different ways you could model this data, with a single table, many tables, hard-coded columns, somewhat dynamic ways, etc. You n...

OleDbException: Data type mismatch in criteria expression.

I read data from MS Access using C#. But get the OleDbException trying to execute such query: SELECT * FROM Flats WHERE Flats.VersionStamp <= [theDate] AND Flats.Flat=[theFlat] OleDbException: Data type mismatch in criteria expression. On the other side, any one of the following queries works fine: SELECT * FROM Flats WHERE Flat...

SQL 2000 Table Name as variable in stored procedure.

Table Name : RM_master Fields : cust_no acct_no acct_code Question is, I want to make the table RM_master as a variable in the parameters in the stored procedure? This has no syntax error but when I execute this in the query analyzer by right clicking on the stored procedure name the variable table name (RM_master) ...

Get month from DATETIME in sqlite

I am trying to get extract the month from a DATETIME field in SQLite. month(dateField) does not work as well as strftime('%m', dateStart). Any ideas? ...

ADO.Net Entity Framework: What's this?

Hi! I'm just starting developing with ADO.NET Entity Framework and I don't know how I would write this: ClientEntities clientContext = new ClientEntities(); ObjectQuery clientQuery = clientContext.Client.Where("it.email = @email AND it.password = @password", new ObjectParameter("email", "[email protected]...

MySQL query (mixing an insert with select)

I have a bunch of rows in a table with columns a, b, c. I'd like to be able to SELECT all rows where say a = 1, and reinsert them with a = 2. Essentially keeping all the rows where column a exist as is, and having a new batch of rows having as a = 2. What's the best query to establish such a multi-INSERT query? This is all happening in t...

SQL queries not in one place but assembled dynamically while passing through several system layers. Is this a good practice?

I personally find that makes the life of a developer who has recently joined the project a very sad one. Without almost perfect understanding of the framework mechanisms it makes developing and debugging like a torture, since whenever I get an error or an unexpected behavior I have not a slightest idea where to look. In some rare cases C...

Missing Join Predicate Warning

I'm running a SQL Server 2005 trace using profiler. I'm seeing some missing join predicate warnings, but I can't determine where its occurring with the data they are giving me. All I have is the transactionID. Any way to find out what object is causing this? ...

Linq2Sql: Help with SqlException about UNION, INTERSECT and EXCEPT

Could someone help me with this exception? I don't understand what it means or how to fix it... It is an SqlException with the following message: All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists. I get it when running a query in psudo code looking like t...

Is it possible, with the Python Standard Library (say version 2.5) to perform MS-SQL queries which are parameterized?

While the particular data I'm working with right now will not be user-generated, and will be sanitized within an inch of its life during my usual validation routines, I would like to learn how to do your basic INSERT, SELECT, etc. SQL queries while protecting myself against SQL injection attacks, just for future reference. I'd rather le...

ASP Membership Odd Problem

I am using the Membership API in ASP .NET and I have encountered the following problem on my staging server. The application works fine on my local machine. The data tables are stored on a SQL Server. Both my local and staging server point to the same DB server. When I deploy to my staging server I get the following error: Parser E...

T-SQL query with funny behavior

Let's say you have the following two tables: X Table X_ID Y_ID_F X_Value 1 1 Q 2 1 G 3 1 T 4 2 W 5 2 K ... Y Table Y_ID Y_Value 1 A 2 B ... You want to query only those properties whose Y parent's value is A and update them so you write a query as follows (I realize there is a bette...