|
*solved* this is what you get for posting questions at 8:30pm local time *sleepy*
after some more digging i have noticed a second column that has a int, 0, 1 or 2, and it exactly correlates with the "fuzzy date". so a second flag is being used to achieve this
***
i am trying to convert data from one system (stored in MS SQL) into a different system stored in MySQL.
in the MS SQL some records are stored with a "fuzzy date", they only has a year, no day and no month. we have the same concept the new system, where we store the date as 00/00/1988
using a SQL client on MySQL with one of these dates i am told it is 01/01/1988, but if i issue the same query on the MySQL command line i am told the date is 00/00/1988, so i have to distrust what clients are telling me.
in the MS SQL database i have tried "enterprise manager version 8" and it is telling me the date is 01/01/1988, but the software (i have NO access to any source code for the old system) reports the date as "1988", no day and no month.
either the old system uses some flag somewhere to say "this is a fuzzy date" or the MS SQL client is "protecting" me from the invalid data.
in MSSQL the column is of type "datetime", and i have found dates that show as "01/01/1985" and "03/2001" in the old system, so i can rule out the theory that you cannot record data on the 1st January.
any pointers or bright ideas would be most welcome. i know something about MySQL but am learning MSSQL as i go along
zen is the art of being at one with the two'ness
-- modified at 15:34 Thursday 15th June, 2006
|
|
|
|
|
Look for ISDATE(), CAST(), CONVERT() DATEPART() and DATENAME() functions in BOL.
Farhan Noor Qureshi
if (this == this) thow this;
|
|
|
|
|
Hi,
I am passing the customer id to DataBase and I want it should return the recordset. Can you pls let me know how to do it in ESQL. I am new to ESQL. It would be grate if you also let me know how to access each individual field.
Thanks
|
|
|
|
|
Does this "ESQL" thing come with any sort of docs?
Farhan Noor Qureshi
if (this == this) thow this;
|
|
|
|
|
Hi all,
Please help me its very urgent for me..
How we can disable and enable the unique constraint for an index.
I have tried ALTER Table_name disable CONSTRAINT constraint_name; But it is giving error no such constraint is present..
This unique constraint is on index...and not on single index it is on multiple index...
Please try to solve my problem
|
|
|
|
|
This link[^] gives a pretty good explanation for all uses of constraints.
Chris Meech
I am Canadian. [heard in a local bar]
When no one was looking, every single American woman between the ages of 18 and 32 went out and got a tatoo just above their rumpus. [link[^]]
|
|
|
|
|
What DB are you using?
Farhan Noor Qureshi
if (this == this) thow this;
|
|
|
|
|
|
i have a data base called DBTest on machine one and contains a table called DTTable1
and i have the same database DBTest on machine Tow.
now i filled the data into DTTable1 on machine one from "A" to "K"
and i filled the data from "L" to "Z" on machine Two.
What i want is to Combine the data from DTTable1 in the Machine One with the DTTable1 on Machine Two.
How that can be done?
Faris Madi
Nothing Comes Easy (N.C.E.)
|
|
|
|
|
Hi!
It depends if you want to combine the content within a SQL statement without transferring the data permanently or if you want to create a third table that physically contains the combined data.
If you need a query and you do not want to tranfer the data
1. create a linked server from machine one to two (or the other way round; you can do this in SQL Server management studio (2005) or Enterprise Manager (2000)
2. select * from DBTest.dbo.DTTable1 union all two.DBTest.dbo.DTTable1
As an alternative to 1 you can also use OPENQUERY or OPENROWSET (see SQL Server Books Online for details).
If you want to move the combined data into a third table use DTS (SQL 2000) or SSIS (SQL 2005). With these tools it is quite easy to move data from one table into another.
Hope this helps.
Regards,
Rainer.
Rainer Stropek
Visit my blog at http://www.cubido.at/rainers
|
|
|
|
|
Hi,
I am new for database query. I am using Ms Access to store data. In Ms Access the date is in format of MM-DD-YYYY. I want to retrive that date in DD-MM-YYYY format. Can any body tell me how to convert this date format.
Thanks in advance.
Mike
|
|
|
|
|
Format$(mydate,"dd-mm-yyyy")
----------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
Hi,
I'm wondering if it's possible to join ALTER TABLE statements so I don't have to have a bunch of ExecuteNonQuery commands.
ALTER TABLE Products ALTER COLUMN ItemNumber TEXT(15)
ALTER TABLE ItemsSold ALTER COLUMN ItemNumber TEXT(15)
thanks,
Ron
|
|
|
|
|
As far as I know this is not possible.
Regards,
Rainer.
Rainer Stropek
Visit my blog at http://www.cubido.at/rainers
|
|
|
|
|
|
You could create this stored procedure, then just execute it with a delimited list of table names:
CREATE PROCEDURE AlterMyTables
@cTablesToAlter varchar(500)
AS
declare @cSQL varchar(100)
declare @nLoc int
declare @nLoc2 int
select @nLoc = 1
while (@nLoc<len(@cTablesToAlter))
begin
select @nLoc2 = CHARINDEX(',',@cTablesToAlter,@nLoc + 1 )
if (@nLoc2=0)
select @nLoc2 = len(@cTablesToAlter) +1
select @cSQL = 'ALTER TABLE ' + SUBSTRING(@cTablesToAlter,@nLoc,@nLoc2-@nLoc) + ' ALTER COLUMN ItemNumber TEXT'
exec(@cSQL)
select @nLoc = @nLoc2 +1
end
----------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
Thanks Eric, I'll give it a try.
Ron
|
|
|
|
|
I'm working on my first data base project. We're recording some historical information from a product, and it seems natural to go ahead an put it in a data base for later retrieval.
Here are some of the things I'm considering:
ODBC: I want independence from the actual DBMS. I'll provide a 'default' data base with the product, but want to give the customer the option of having us record our information directly in his data base.
SQL Server 2005 Express: I'm looking at this for my 'default' data base. It's free, and the install appears to be easy.
MFC: Yeah, I know MFC is horribly old school, but this is an add-on to an existing MFC-based product. .NET is out of the question. We're developing using VS.NET 2003, by the way.
Service: I don't know if it makes a difference, but this will probably be implemented as a service.
Do you guys have any sources or suggestions for 'best practices', tips, or "do's and don'ts"?
Software Zen: delete this;
|
|
|
|
|
Gary Wheeler wrote: ODBC: I want independence from the actual DBMS
I'd ignore ODBC, and go for implementing a provider model. You build a DAL (Data Access Layer) that you can swap out and replace with a new one if you change your database. You are likely to have to change a lot of code anyway even if you do go the ODBC route. Different databases use different dialects of SQL so any advantages of using a generic connection like ODBC or OleDB are usually just a matter of perception rather than of reality.
The way I usually go about a DAL is to have it handle the database connections internally (an abstract base class), getting its settings from a configuration file. The public methods on the DAL classes are just proxies to the stored procedures and there is very little processing going on other than marshalling datatypes.
The abstract base class has some generic functionality that calls the stored procedures and returns the data. The public methods will take parameters and pass them to the stored procedure. The results are then returned from the method (as generic types - not database specific types).
This way, if you change your database all you need to do is change the DAL classes.
If you write your DAL against SQL Server 2005 Express Edition then it will work without change if you migrate to a fuller SQL Server edition.
Scottish Developers events:
* .NET debugging, tracing and instrumentation by Duncan Edwards Jones and Code Coverage in .NET by Craig Murphy
* Developer Day Scotland: are you interested in speaking or attending?
My: Website | Blog
|
|
|
|
|
Thanks, Colin .
I was hoping that, since I'm only inserting data into the data base, that ODBC was sufficiently portable. If I don't issue SQL I construct myself, would that be sufficient?
Colin Angus Mackay wrote: If you write your DAL against SQL Server 2005 Express Edition then it will work without change if you migrate to a fuller SQL Server edition.
That was part of the appeal to SQL Server 2005 Express.
Software Zen: delete this;
|
|
|
|
|
Hello Everyone
Iam working(porting) on a legacy application which uses MFC DAO based classes to access MS-Access. I need to execute stored procedure on the SQL Server database how can I do that using CDaoQueryDef?
Thanks in advance for your time and help.
regards,
KS
|
|
|
|
|
I have a app with a grid and am working on updating data from the grid to the data base. the update is triggered off a menu item. the code is as follows:
try
{
// New data entered to Contract QA_Setup table
DataAccess da = new DataAccess();
string sproc = "prc_QA_Setup_upd";
string dbAction = "Update";
string tableName = "QA_Setup";
// Update to find grid changes
//ug.Update();
ug.UpdateData();
// to retrieve ds with modified rows to grid
ds.GetChanges(DataRowState.Modified);
// Call to DAL to update QA_Setup with new data
da.updateDB(ds, sproc, dbAction, tableName);
}
This passes to the DAL and tries to run:
private void prepareUpdate(string sproc, string tableName)
{
// Create parameters for the QA_Setup dataset via Sqlcommand
SqlCommand cmd = new SqlCommand(sproc, conn);
// call set cmd properties
defineParams(tableName, cmd);
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = sproc;
//RunProc(sproc);
//RunProc(cmd);
dap.UpdateCommand = cmd;
}
However I get a System.Data.DBConcurrencyException, what am I missing?
|
|
|
|
|
I'm retrieving data from a mdb table and inserting it into a sqlserver table. The tables source and destination tables are identical. The mdb table has a PK that is set to autonum in the mdb and identity=yes in sql server.
The tables in the sql server are all empty. I want to simple insert all the data from the datatable into the sql server table including the original PK and autonum values.
I'm using a sqldataadapter and a commandbuilder to automatically gen the insert statements. But the cb's strips off the pk field from its statement. I basically want to invoke the adapters.update method against the datatable and have all the rows inserted. I've played around with SET IDENTITY_INSERT table ON but still the same issue. Seems to be that the commandbuilder is querying the schema of the table and noting the pk is an identiy column and is stripping it out of the insert statement.
PS I've already thought about manually generating the sql statements but its very slow as I have to cycle through each row and do this.
Dim cnn As New SqlConnection("server=glenn;database=test1;uid=sysadm;pwd=sysadm")
Dim adp As SqlDataAdapter
Dim cbd As SqlCommandBuilder
strSql = ""
strSql = "SET IDENTITY_INSERT " & c_strTable & " ON"
cmd = New SqlCommand(strSql, cnn)
cnn.Open()
cmd.ExecuteNonQuery()
adp = New SqlDataAdapter("select * from table", cnn)
cbd = New SqlCommandBuilder(adp)
Console.WriteLine(cbd.GetInsertCommand.CommandText) ' missing pk column? why?
adp.update(datatable)
Thanks
Glenn
|
|
|
|
|
Hi i am tryin to get the max of collection of calculated averages but when i try to use the simple option of using the max outside the avg function i get this error "Server: Msg 130, Level 15, State 1, Line 2
Cannot perform an aggregate function on an expression containing an aggregate or a subquery." so im just wondering if anyone has any idea how to avoid this problem
this is my code
Select 'Team Averages ' =
max(avg(case Code when 'A' then 4.0 when 'B' then 3.0 when 'C' then 2.0 when 'D' then 1.0 else 0.0 end))
From ScoutingReport sr
Where sr.Type = 1
And sr.ScoutId = 11619
group by matchid
and this is my output at the moment there are 3 teams and the averages are
-----------------------------
Team Averages
1.812500
2.937500
1.187500
-----------------------------
thanks in advance Tim
|
|
|
|
|
You've not told it how to group the averages, so it does not know of which to return the MAX() .
SELECT matchid, AVG((CASE Code WHEN 'A' THEN 4.0
WHEN 'B' THEN 3.0
WHEN 'C' THEN 2.0
WHEN 'D' THEN 1.0
ELSE 0.0 END)
FROM ScoutingReport AS sr
WHERE sr.Type = 1
AND sr.ScoutId = 11619
GROUP BY matchid
The above will return the average for each matchid
If you want to find the MAX() of that then
SELECT MAX(average)
FROM
(
SELECT matchid, AVG((CASE Code WHEN 'A' THEN 4.0
WHEN 'B' THEN 3.0
WHEN 'C' THEN 2.0
WHEN 'D' THEN 1.0
ELSE 0.0 END) AS average
FROM ScoutingReport AS sr
WHERE sr.Type = 1
AND sr.ScoutId = 11619
GROUP BY matchid
)
If that doesn't give you the answer you want then you might need to explain more how you want the calculation to work.
Scottish Developers upcoming sessions include:
.NET debugging, tracing and instrumentation by Duncan Edwards Jones and Code Coverage in .NET by Craig Murphy
My: Website | Blog
|
|
|
|