My project requires I use VB (5 or 6)* to store a date in an SQL Server database. The SQL datetime type includes the time, which I don't want. I'm also aware that VB's representation of a date doesn't mirror that of SQL Server. So, how can I store a date held in VB's date type in the SQL database, as the datetime at midnight on that dat...
Below is the code I'm using to run the query, parse the result set, and parse the rows (respectively) $exec_ret = $DBS->SQLExecSQL($STMT); while ($DBS->SQLFetch() == *PLibdata::RET_OK) { $rowfetch = $DBS->{Row}->GetCharValue($colname[$i]); } Can I grab the column/field name of a temp table using similar syntax? $colname[$i] is...
Hi, I'm interested in understanding if there is a common technique/pattern for checking data in a database conforms to a set of rules. I'm interested, perhaps, in running a service/job each night that churns through the data and raises exceptions to rules. I would quite like the system to be dynamic, i.e. it's quite easy to define new r...
We have a Sql Server 2000 database application that needs to update our applications Sql Server 2008 database when certain tables are changed. For this, we use a trigger on the Sql 2000 server that fires a stored procedure on our Sql 2008 server. When trying it from SMSS, I get this message: The operation could not be performed becaus...
I'd like to show no more than n characters of a text field in search results to give the user an idea of the content. However, I can't find a way to easily break on words, so I wind up with a partial word at the break. When I want to show: "This student has not submitted his last few assignments", the system might show: "This student ha...
Using the ExecuteScalar method in the SQL Command object, how can you check to see if the result set is empty? I am using ASP.net, C#, and MS SQL 2008. Right now when I run the following code the Response.Write returns a 0 when the resultset is empty. But I would like to differentiate between 0 and empty resultsets because there are a...
Hello. I'm trying to make a report but I'm having problems with my archi nemesis SQL. I have a table where the close date of a transaction is stored. I want to know how many transaction per month there was so I did: SELECT trunct( closedate, 'MONTH' ) FROM MY_TRANSACTIONS I'm using oracle. I'm getting a list like this: 2002-0...
When using the SQL MIN() function, along with GROUP BY, will any additional columns (not the MIN column, or one of the GROUP BY columns) match the data in the matching MIN row? For example, given a table with department names, employee names, and salary: SELECT MIN(e.salary), e.* FROM employee e GROUP BY department Obviously I'll get...
This is a follow up on another problem i had with getting-the-last-record-inserted-into-a-select-query I am trying to edit a query that Andrea was kind enough to help me with yesterday which works fine for one page but I am trying to create a similar query without much luck. What I need to to is for every board display the board name, ...
I have seen SQL that uses both != and <> for not equal. What is the prefigured syntax and why? I like != because <> reminds me of Visual Basic. ...
Let's say there are 2 tables Table1 { ID, Name, Other } and Table2 { ID, Name, Other }. Both of them have identical records with the same IDs except that in Table1 all Name values are NULL. How can I import Name values from Table2 to Table1 using T-SQL (SQL Server 2008)? ...
There is a table with values like this: Sets | Reps -----+------ 2 | 10 3 | 15 3 | 12 4 | 8 I need to combine those two values into one in my SELECT statement, so I could get SELECT ... AS Reps FROM Table Reps ------------ 10,10 15,15,15 12,12,12 8,8,8,8 Any ideas how to do this? Testing script: DECLARE @Test TABLE (...
Hello, i have a situation where i want to check a certain column ( like version number) and then apply a bunch of ddl changes trouble is i am not able to do it with in a IF BEGIN END block, since DDL statements require a GO separator between them, and TSQL wont allow that. I am wondering if there is any way around to accomplish this ...
I want to convert date format from 01/09 to January 2009 , 09/03 to September 2003 etc. Is this possible in SQL? Please let me know if there is a API for the same. ...
I'm working with a 20 gig XML file that I would like to import into a SQL database (preferably MySQL, since that is what I am familiar with). This seems like it would be a common task, but after Googling around a bit I haven't been able to figure out how to do it. What is the best way to do this? I know this ability is built into MySQL...
I have the following table schema: create table SerialNo2( IncarnationID_UID counter primary key, Mark integer ); Now I want to add a new column in the table, let's call it degree. Given that I want to apply an update on production table, I will have to populate the degree column based on the Mark value for all the existing data in th...
OK I'm using MS SQL and I have a table with values like this: ID Date Radio Time 111 4/1/09 1 3:00:00 111 4/1/09 2 4:00:00 111 4/1/09 3 14:00:00 111 4/1/09 1 15:00:00 111 4/1/09 3 16:00:00 222 4/1/09 2 2:00:00 222 4/1/09 1 5:00:00 222 ...
I have tried a bunch of different things but always get syntax errors. I have two tables - tableA and tableB. They both have a con_number field as a unique ID. I want to select all of the rows in tableB that do not exist in tableA. Can anyone please give me this query as it would be in MS Access? I know that using NOT IN is quite inef...
Without thinking to much, it seems to me that a large set of Prolog's functionality could be implemented as relational calculus (a.k.a. SQL). Has anyone heard of any tools to automatically convert Prolog to SQL? ...
Hi, I've got a strange situation with some tables in my database starting its IDs from 0, even though TABLE CREATE has IDENTITY(1,1). This is so for some tables, but not for others. It has worked until today. I've tried resetting identity column: DBCC CHECKIDENT (SyncSession, reseed, 0); But new records start with 0. I have tried doi...