sql

Using preg_replace to format

Having trouble with pattern and replacement. How can I make the replacement echo a final product such as INSERT INTO `table` (`person`, `file`) VALUES ('test','test'), ('test2','test2'), ('test3','test3'); I am trying to insert the string into SQL but I need to format the current string below to do so, and I need to have the last part...

Inserting Randomly Selected Records SQL

I'm using Microsoft SQL Server 2005. I'm creating a random record generator that will insert 10 records randomly into a temporary table. The records in the temporary table will then be used to update records in the table in memory. Here is the statement that is giving me some troubles (assume the temp table is already created). i...

SQL Server add a column constraint to limit data to -1 to 1

Hello, I want to constrain a SQL Server decimal column to only allow -1,0,1 as valid values. Can you show me the SQL syntax for adding such a constraint. (I would like to know how to do it in both the CREATE TABLE statement and/or the ALTER TABLE ADD CONSTRAINT). Or can this only be accomplished in a trigger? Seth ...

Recover from SQL batch-abort errors inside a transaction? Alternative?

I'm looking for a way to continue execution of a transaction despite errors while inserting low-priority data. It seems like real nested transaction could be a solution, but they aren't supported by SQL Server 2005/2008. Another solution would be to have logic to decide if an error is critical or not, but it would seem that's not possibl...

What is the Sql Server equivalent for Oracle's DBMS_ASSERT?

DBMS_ASSERT is one of the keys to prevent SQL injection attacks in Oracle. I tried a cursory search...is there any SQL Server 2005/2008 equivalent for this functionality? I am looking for a specific implementation that has a counterpart of all the respective Oracle package members of DBMS_ASSERT. NOOP SIMPLE_SQL_NAME QUALIFIED_SQL_N...

Creating multiple stored procedures from SQL executed by powershell issue

Ok, so I've got a bit of a SQL and Powershell problem. There are 2 SQL scripts, one to setup 4 different global stored procedures. Another to execute them and manipulate data before returning it to PS to be placed in a CSV file. The reason I'm not putting them into a single file is for readability. The procs are enclosing huge chunks of ...

mysql_num_rows does not work

this is my php code $sql="SELECT * FROM table_name WHERE FROM_UNIXTIME(date, '%Y') = $q order by id desc"; $q = mysql_query($sql) or die(mysql_error().$sql); $sql1="SELECT * FROM table_name WHERE FROM_UNIXTIME(date, '%Y') = $q"; $query=mysql_query($sql1); this gathers the results correctly (everything is correct) but when i use this...

How can I get dead lock in this situation?

In my client application I have a method like this (in practice it's more complex, but I've left the main part): public void btnUpdate_Click(...) { ... dataAdapter.Update(...); ... dataAdapter.Fill(...); // here I got exception one time } The exception I found in logs says "Deadlock found when trying to get lock; try restartin...

MySQL count problem

I have a table course: CREATE TABLE course (COURSE_NO NUMERIC(8,0) NOT NULL ,DESCRIPTION VARCHAR(50) ,COST NUMERIC(9,2) ,PREREQUISITE NUMERIC(8,0) ,CREATED_BY VARCHAR(30) ,CREATED_DATE DATE ,MODIFIED_BY VARCHAR(30) ,MODIFIED_DATE DATE ,PRIMARY KEY (COURSE_NO) ,INDEX (PREREQUISITE) ) TYPE...

A View Over a XML Data Type Column

I have a table that contains a column of XML Datatype (column name FileContent). I want to create a view that queries the contents of the XML datatype column so that I no longer have FileContent column but two additional columns called FuelPathwayCode and PhysicalPathwayCode from the underlying XML document. Is this possible with SQL S...

Web Site Administration Tool: Unable to connect to SQL Server database (using SQL Server Development Edition)

Hello: I am using the ASP.NET 2.0 and I am using local server --> SQL Server 2005 Development Edition. WHen I go into VIsual Studio 2.0 Professional and go to menu item WEBSITE --> ASP.NET CONFIGURATION, the ASP.NET Web Site Administration Tool shows. When I click on the Security link, after about 30 seconds an error pops saying Unable...

Searching in subordinate table in an SQL GROUP BY

A few weeks ago I asked a question about eliminating duplicate records in a SQL INNER JOIN. The code I ended up using is similar to the following answer: SELECT ROW_NUMBER() OVER(ORDER BY " + orderField + @") AS RowNum, mt.ID AS mt_ID, mt.title AS mt_title, [...] MAX(st.title) AS st_title, -- Other ag...

Timeout exception causes SqlDataReader to close?

I'm trying to pull some binary data from a database and write them to pdf files. For the most part, this is going along swimmingly, but the occasional row of data seems to throw a particular error - Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. Keep in mind, this onl...

MySQL Select Statement - Two Tables, Sort One Table by Count of Other Table

So I have built a voting system for a custom post system i wrote. I want to be able to sort by "most voted", "Most liked", etc. I have two tables. Entry: ID, Title, Post Vote: ID, EntryID, Result I want to be able to query the vote table for each entry and see how many vote's there are, and then sort the entry's by how many vote's e...

Stored procedure not updating table when called from C#

I have a stored procedure that should update a table. When I run it directly I get “Command(s) completed successfully.”, but when called from C# it does not change the table. What is going wrong? set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go -- ============================================= -- Author: Mohamamd Ibrahim Tasal -- Create ...

Converting an ID into a name in ASP.Net MVC

I'm trying to convert an ID into a name when I render a "detail" view in my application. I have successfully been able to display the name in my "edit" and "create" views by using the code: In my controller: ViewData["countyViewData"] = new SelectList(db.Counties, "CountyID", "CountyName"); In my views: <%= Html.DropDownList("Count...

Help me to join to tables ?

I have 02 tables. table1 id_user id_group 0 32 0 31 0 33 62 32 62 31 62 33 120 31 120 33 table2 id parent_id name 31 18 AAA 32 18 BBB 33 18 CC...

What is the difference between GROUP BY and DISTINCT?

HI.. I have the table with the following data empid empname deptid address aa76 John 6 34567 aa75 rob 4 23456 aa71 smith 3 12345 aa74 dave 2 12345 a77 blake 2 12345 aa73 andrew 3 12345 aa90 sam 1 12345 aa72 will 6 34567 aa70 ra...

How to change the sharepoint Configuration on sqlserver namedInstance to default Instance??

Hi I have installed sqlserver as namedinstance first .say(sqlnamed).later i have installed sharepoint and configured named instance as database server for sharepoint Now i have installed sqldefault instance. Now How to configure the sharepoint server to use default instance instead of namedinstance. What is the Procedure i ...

SELECT (* - some_columns) FROM TABLE in SQL

Hi, I have a table with many columns among which I'd like to omit a few alone in my 'select' query. Something like select (* - columns_to_be_omitted) from myTable. Is there a way to do this, other than listing all the other columns in the query? This is a one-time manual query, so I'm not that concerned about performance. Ease of use ...