Hi there, I have a form that is trying to insert some data into an SQL Server 2008 database. The form has a function to get the current date/time and then insert it into the database as follows; $now = date("Y-m-d H:i:s"); $q = "INSERT INTO ".TBL_USERS." ( username, password, userid, userlevel, email, created, updated, timestamp, fulln...
How to create XML/XSD from SQL table with multiple tables what I want to do is create an XML schema from a database (or database schema) This post has some interesting code Particularly DataSet results = new DataSet(); SqlCommand command = new SqlCommand("SELECT * FROM table", new SqlConnection(connectionString)); SqlDataAdapter sq...
I'm writing an SQL data updater to convert old text/ntext/image type columns into varchar/nvarchar/varbinary. The updaters are run within transactions when the system boots to update the database from an older version. I've gotten the SQL to work on its own, but a handful of the columns being changed were full-text indexed, which means I...
I have the following code within a stored procedure. WHERE WPP.ACCEPTED = 1 AND WPI.EMAIL LIKE '%@MATH.UCLA.EDU%' AND (WPP.SPEAKER = 0 OR WPP.SPEAKER IS NULL) AND WPP.COMMENT NOT LIKE '%CORE%' AND WPP.PROGRAMCODE = 'cmaws3' The NOT LIKE statement is not working, and yes before anyone says anything there are items with the COMMEN...
What is the general "best practice" for this type of functionality. I have a table of users a table of polls and a table of poll responses on a website. I want to load a page that loads a poll that a user hasn't yet answer. What is the most efficient and "best" way of going about this. Things I've tried that seems slow/not optimal: ...
Hi, I have a query that returns a result set similar to the one below: Quarter | Count ------- | ----- 1 Q2 2009 | NULL 2 Q2 2009 | NULL 3 Q2 2009 | NULL 4 Q3 2009 | 26 5 Q3 2009 | 27 6 Q3 2009 | NULL I don't want rows 1-3, because their combined Quarter-Count is NULL. But I do want rows 3-6 because at least one ...
Hi, I am using Microsoft SQL Server. I have a Table which had been updated by 80 rows. If I right click and look at the table properties the rowcount say 10000 but a select Count(id) from TableName indicates 10080. I checked the statistics and they also have a rowcount of 10080. Why is there a difference between the Rocount in Prope...
It appears that using the LIKE in a condition with wildcards and a variable inside of dynamic sql doesn't work, although it doesn't give an error. Here's an example. The column called code has values like A0B01C02,A0B02C2D05,A0B02C2D05, etc and I am trying to match on rows containing a subset like 'B1'. When I do this it works and ret...
Hi, Currently I need to run two queries to get both the total amount of items in my resultset as well as the resultset. Is it possible to just get the resultset count as well as the resultset in one pass to the database. I am trying to optimize my code so I don't have to make 2 passes to the database as these individual select stateme...
I need to run a sweepstakes script to get X amount of winners from a customers table. Each customer has N participations. The table looks like this CUSTOMER-A 5 CUSTOMER-B 8 CUSTOMER-C 1 I can always script to have CUSTOMER-A,B and C inserted 5, 8 and 1 times respectively in a temp table and then select randomly usi...
Hi I am trying to calculate duration, I am using MySQL 5.0 but i am getting this error 1305 - FUNCTION smgm.DATEDIFF dose not exist MY QUERY IS SELECT DATEDIFF (MI,timein,timeout)/60 as duration FROM User_smgm where ID=2; my database name is SMGM Any Idea why is this so? Where as when i try simple DATEDIFF its working fine. such...
I am executing this query select category "ROOT/category", question "Category/question", option1 "Category/option1" from testDB2 for XML PATH ('ROOT') , ELEMENTS Presently the database has three entries and the xml file i get is this <ROOT> <ROOT> <category>maths</category> </ROOT> <Category> <question>2+2?</question>...
I'm trying to get a report built up from data mining our accounting software. We have a table that stores the balances of each account in a general ledger for a given period (which is 0-12, 0 being carry over from last year, 1-12 being the corresponding month), the amount, and other data I don't need. I'm trying unsuccessfully to get ...
Is it possible to do something like this? $ sqlplus -s user/pass "select 1 from dual" or $ echo "select 1 from dual" | sqlplus -s user/pass I know I can put select 1 from dual in a file and do this: $ sqlplus -s user/pass @myFile.sql but I'm wondering if it's actually necessary to create a file just to satisfy sqlplus ...
I need help with SQL. I have an sqlite table like so; CREATE TABLE mytable (datetime DATE, type TEXT, amount REAL) I need a query which would sum up amount for each type AND year-month (as you can see the year is also extracted since the data can span several years). I've come to something half-way, but I'm a bit rusty on SQL. sqlite...
As an example, say I have a these tables... person (id, currentyear) personteam (id, workyear, team) A person is assigned a team for each year they work at a company. 'personteam' stores a record of the teams. The insert into 'personteam' is only allowed if the 'currentyear' field in 'person' equals the 'workyear' in 'personteam'. I ...
I need to move a database with a Django schema from Postgres to MySQL, running on Amazon's RDF. I can re-generate the tables using manage.py, but I'm still looking for a way to migrate over all of the row data. Does anyone know a clean way of moving it over? Are there any gotchas to watch out for with Amazon's RDF? ...
I have the following table and trigger. CREATE TABLE LogSchema ( user varchar2(100), date_ date, operation varchar2(100), obj_type varchar2(100), obj_name varchar2(100) ); CREATE OR REPLACE TRIGGER LogSchema AFTER DDL ON USER1.SCHEMA /* can't use ON DATABASE or else it will log everything that happens on all sc...
Table 1 ------- LANG_VALUE LANG_DESC Table 2 ------- EmpId LANG1 LANG2 LANG3 LANG4 LANG5 LANG6 It looks like as below: Table 1 ------- LANGVALUE LANGDESC ------------------- SPAN SPANISH GERM GERMAN Table 2 ------- EmpId LANG1 LANG2 LANG3 LANG4 LANG5 LANG6 ----------------------------------------- 1 SPAN NULL NULL ...
We have several stored procedures marked as articles for replication in our SQL2000 database. If we update any of them via ALTER PROCEDURE, the changes are applied to the master, but never published to subscribers. Am I missing a setting, or does SQL require a complete reinitialization/snapshot to move the changes out? ...