How to store multiple values in TSQL Variables
I have the following TSQL Statement: SELECT ProductId, OwnerId FROM Product How I can store the ProductId and OwnerId in a TSQL variable and then return it to the calling application? ...
sql
SQL Server 2008 Express one row write problem
Hi everyone, I have the most bizarre problem (at least it is bizarre to me) with SQL Server Express 2008. The problem is the following: On the development machine I use SQL Server 2008 Enterprise....I get some data from a WCF service and write that data to the database (simple as it can be)....I should point out however that the wr...
55 gig - comma delimited file - reading into mysql DB
any ideas on how to open this file? i have no choice whatsoever on the size of this file. its regular text data the reason i cannot break up the file is because it is stored remotely and the ONLY thing i can do is run sql statements on it please help! ...
SQL Query to delete records older than two years
I need to clean out a very bloated SQL database by deleting records that are older than two years from a number of tables. What is the most efficient way of doing this? Thanks in advance. ...
Looping over some selected values in a stored procedure
I'm trying to modify a stored procedure hooked into an ORM tool. I want to add a few more rows based on a loop of some distinct values in a column. Here's the current SP: SELECT GRP = STAT_CD, CODE = REASN_CD FROM dbo.STATUS_TABLE WITH (NOLOCK) Order by STAT_CD, SRT_ORDR For each distinct STAT_CD, I'd also like to insert a REA...
PostgreSQL: How to index all foreign keys?
I am working with a large PostgreSQL database, and I am trying to tune it to get more performance. Our queries and updates seem to be doing a lot of lookups using foreign keys. What I would like is a relatively simple way to add Indexes to all of our foreign keys without having to go through every table (~140) and doing it manually. ...
Help with Parameterizing SQL Query for C# with possible null values
Hello, people. I need help with parameterizing this query. SELECT * FROM greatTable WHERE field1 = @field1 AND field2 = @field2 The user should be able to search for any of the 2 fields, and the user also should be able to search if the field2 has null values. var query = "theQuery"; var cm = new SqlCommand(cn, query); cm.AddPara...
What's wrong with my SQL statement, something isn't working.
I have this very simple method called to check if a user has a correct password. Any help on why it isn't working? This is for Microsoft SQL Server. public bool UserNameExists() { using (SqlConnection con = new SqlConnection("CONNECTION STRING AQUI!")) { con.Open(); try { ...
How can I identify columns when SELECTing from multiple tables with JDBC?
I have two tables that I join on the id-column, they look like: +-------+ | users | +----+--+---+ | id | name | +----+------+ +-------+ | posts | +-------+------+---------+ | id | user_id | message | +----+---------+---------+ And now I want to select all posts and include the username, with: SELECT * FROM posts, users WHERE user_id...
How do you specify 'DEFAULT' as a SQL parameter value in ADO.NET?
I have a parameterized SQL query targetted for SQL2005 which is dynamically created in code, so I used the ADO.NET SqlParameter class to add sql parameters to SqlCommand. In the aforementioned SQL I select from a Table Valued Function with has defaults. I want my dynamic sql to sometimes specify a value for these default parameters, and...
Using nested cfloop queries
I have 2 tables. One is for categories, the second is for Questions. category table: category_id category_name questions table: question_name question_id category_id How can I loop though all the category names and show the questions grouped under each category name? Using Coldfusion, so I am assume I should use CFLOOP Th...
How can I save information to my database when information is to be saved in different tables?
I have to save the following variables to a database: string PoliciaID = Session["username"]; string PlacaAuto = Page.Request.QueryString["placaauto"]; string Carnet = Page.Request.QueryString["carnet"]; string Fecha = DateTime.Now; string TipoInfraccion = Page.Request.QueryString["tipo"]; Polic...
SQL Server 2008 Geography .STBuffer() distance measurement units
I'm working with a geographic point using lat/long and need to find other points in our database within a 5 mile radius of that point. However, I can't seem to find out what the "units" are for STBuffer, it doesn't seem to conform to feet, miles, meters, kilometers, etc. The documentation only refers to them as "units", any suggestions? ...
How to delete parent and child record
Hi I need some help. I have a table that have LibraryID and LibraryParentID. LibraryID int Unchecked Name varchar(50) Checked Description varchar(200) Checked LibraryLevel int Checked LibraryParentID int Checked LibraryTypeID int Unchecked I want to write a sql to delete the currrent select parent and well as all child record, how c...
Use SqlReader to get back multiple tables from a stored procedure
I have a user defined type, which is a datatable in an SqlServer database. I have been using Database, DbCommand, etc to call stored procedures and get a Dataset back. Datasets are handy in that they can contain multiple tables. Now I want to pass a Datatable in, so I tried: string _strComText = "stored_procedure_name_changed_to_protec...
Self-Referential Table Fields In MySQL
I have a table which has a "foreign key" referencing itself. This would be very useful, except I am uncertain how to add the first record to such a table. No matter what I add, I cannot provide a valid "foreign" key to the table itself, having no entries yet. Maybe I'm not going about this correctly, but I want this table to represent...
What is wrong with this sql where-clause?
I am selecting records based on two dates in a same column but my where condition fails to select records on the StartDate and EndDate... where CreatedDate between @StartDate and @EndDate I get only the records inbetween the dates and not the records on the StartDate and EndDate... Consider if I pass the same date as StartDate and EndD...
Is there an open source repository for SQL code?
I find myself writing SQL code (queries or stored procs) to solve problems that can definitely be defined as 'patterns' that occur frequently in business. Rather than having to wrack my brain each time I encounter a new problem (which must have been solved a countless times by other coders/db analysts, I wondered if there was a reposito...
What is wrong with using SELECT * FROM sometable
Possible Duplicate: Which is faster/best? SELECT * or SELECT column1, colum2, column3, etc. I have read the using "SELECT * FROM sometable" is bad practice, esp. if the query is being sent over a network. Specifically what is wrong with it? ...
DELETE records without associated ones.
I have a 'guests' table and 'invitations' table bound with many-to-one relationship in a postgreSQL database. I have removed some guests and now I want to remove invitations that have no guests. I tried using 'COUNT', but aggregates are not allowed in WHERE clause. ...