sql

Am I safe against SQL injection

Hello I would like to know if I'm safe against SQL injection when I use something like that with PostgresSQL: CREATE or REPLACE FUNCTION sp_list_name( VARCHAR ) RETURNS SETOF v_player AS ' DECLARE v_start_name ALIAS FOR $1; r_player v_player%ROWTYPE; v_temp VARCHAR; BEGIN v_temp := v_start_name || ''%''; ...

Catching SQL unique key exceptions in .NET

I was wondering if anyone had a more elegant way to check for unique key exceptions from SQL in .NET other than parsing the error message? Right now I am calling the sproc in SQL, then using a try catch block in .NET. In the try catch block I parse the error message and it is a Unique key error I throw an instance of a custom error to ...

SQL Query causing page to go blank

I'm making a website using php and I'm having some trouble with an SQL query. $dataArray = array(); $result = mysql_query("SELECT * FROM test WHERE web_id='$websiteID'") or die(mysql_error()); while ($row = mysql_fetch_array($result)) { $k = $row['kfoo']; $v = $row['vbar']; $dataArray[$k] = $v; } That's the code as it...

Is querying a single record from a MySQL view including thousands, equivalent to querying those thousands + 1?

I have always hoped and assumed that it is not - that set theory (or something) provides a shortcut to the result. I have created a non-updateable view that aggregates data from several tables, in a way that produces an exponential number of records. From this view, I query one record at a time. Because the underlying dataset is small, ...

Dealing with SQLCMD :r in a post-deployment script

We use a Visual Studio Database Project for the source control of the schema of our database. We also want to script certain data (configuration values etc.) from the database, so we use a custom command line utility for dumping data into insert scripts. One file represents one table's scripted data. There is a certain SQL script, calle...

Script Size Limit in SQL Server Management Studio

For example: how many lines of insert into foo ( '1','2' ) could one have in a file. I remember it being around 100k but searching with google has proved fruitless thus far. ...

Best Data Type for Currency

What is is the best SQL data type for currency values? I'm using MySQL but would prefer a database independent type. ...

tsql : how do I join this table?

I have the following sql statement: SELECT TOP (100) PERCENT inv.CompanyID, cust.CustID AS ClientID, cust.CustName AS CustomerName, inv.InvcKey, inv.PrimarySperKey AS SperKey, inv.TranID AS InvoiceNumber, in...

Left Joins are what I want but they are very slow?

Overview: I have three tables 1) subscribers, bios, and shirtsizes and i need to find the subscribers without a bio or shirtsizes the tables are laid out such as subscribers | season_id | user_id | bio | bio_id | user_id | shirt sizes | bio_id | shirtsize | And I need to find all users who do not have a bio or shirtsize, ...

SQL query - Join that returns the first two records of joining table

I have two tables: Patient pkPatientId FirstName Surname PatientStatus pkPatientStatusId fkPatientId StatusCode StartDate EndDate Patient -> PatientStatus is a one to many relationship. I am wondering if its possible in SQL to do a join which returns only the first two PatientStatus records for each Patient. If only one Patien...

How should I optimize multiple calls in my .net code to a trivial stored procedure ?

I've got a very simple stored procedure : create procedure spFoo(v varchar(50)) as insert into tbFoo select v I've got 50 values to insert into tbFoo, which means in my c# code I call spFoo 50 times. This is a pretty inefficient way of doing this, especially if there's some lag between my program and the database. What do you usuall...

SQL Server not configured for data access

I m running an SQL Server 2005 database from a VB 6 Application. I connect to the database thro ODBC DSN. I renamed the computer on which Im running the a default instance of SQL server from 'Software' to 'IT' .I then run the 'sp_dropserver' & 'sp_addserver' stored procedures to rename the instance. On restarting the SQL server service...

Deploying / Creating Analysis Services Database using an ASSL Script

Im trying to deploy as Analysis Services database using an ASSL script. I can create the script using "script as" in management studio. But I can't find out how to run this script? ...

SQL Server 2000 command to disable user account

I need to disable some user accounts within a sql server version 2000. the following sql command is giving me an " incorrect syntax near 'Login' " error. The user name is valid and spelled correctly so I'm wondering if the command syntax is different for version 2000. ALTER LOGIN exampleuser DISABLE ...

Problem with "IN" while executing query.

Hi, I am trying to search employees based on their code and department. I triggered a query using IN operator which has 1200 employee codes. When i executed the query I got an exception ("Maximum 1000 records allowed in IN "). Can anyone help me out with this issue. ...

How do I delete duplicate data in SQL Server?

I have sql table but, some value added more than once, I need to delete one of them. Can you give me a simple query? ...

Removing nonnumerical data out of a number + SQL

I'm trying find the best way to remove nonnumerical data from a varchar in SQL e.g. '(082) 000-0000' to '0820000000' or '+2782 000 0000' to '0820000000' The difficulty is i'm not always sure what number formats are coming in, as shown above, so I'd like like everything that is not a number removed essentially. Thanks in advance for a...

SQL date selecting

I want to be able to select all database rows where the month and year are the same as what I am searching for. Since the DATE field has year, month, and day, how do I search with year and month? ...

SQL Server - Selectively inserting fields into temp table

I am executing a SP within a SP. The SP returns say 10 params. I am interested in only 5 of them. How do I insert only these 5 into the temp table. The code I have so far: DECLARE @tmpUnion TABLE ( UnionCode VARCHAR(10), UnionDate DATETIME, UnionPosition VARCHAR(30), UnionInitFees BIT, UnionDues BIT ) --get...

Joining temp tables with different structure

I have 2 temp tables. One has data column wise. OrderCode OrderType ABC 1000 One has data row wise FieldName Value ORDER_CODE ABC ORDER_TYPE 2000 I need to write a query that essentially joins both of them and updates the value if it exists in table 2. Recordset result needs to be OrderCode OrderType ABC 20...