|
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
|
|
|
|
|
thanks alot that is about 90% there it work fine when its just the averages but when i include the matchid(which i need) i still have then same problem this is the query at the moment
SELECT matchid, 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) as t
group by matchid
this is what is returned from this query
814 2.531250
833 2.937500
938 1.187500
but the max average is 2.937500
so i need
833 2.937500
anymore help would be appreciated thanks again
|
|
|
|
|
|
thanks alot for your help with this i have managed to get the min and the max using this
Tim
|
|
|
|
|
I want to execute some tasks peridically on SQL Server 2000 but these are not realted to an action performed on my db's tables (doeasn't works with trigger). I just want to be executed my code regulary from time to time.
Any suggestion is welcome. Thanks.
|
|
|
|
|
SQL Agent jobs.
how vital enterprise application are for proactive organizations leveraging collective synergy to think outside the box and formulate their key objectives into a win-win game plan with a quality-driven approach that focuses on empowering key players to drive-up their core competencies and increase expectations with an all-around initiative to drive up the bottom-line. But of course, that's all a "high level" overview of things
--thedailywtf 3/21/06
|
|
|
|
|
db maintenance plan might work as well, but that really just runs a SQL Job. You can also use a DTS which can be scheduled ( as you've already guessed in a SQL Job).
Look where you want to go not where you don't want to crash.
Bikers Bible
|
|
|
|
|
Thanks for the advice, the SQL Job is a really good thing.
|
|
|
|
|
I'm building a new application that had a table (Tbl1) with 4 date fields in it. Lets call them date1, date2, date3, date4 and the DBA wants me to remove them. He is telling me that it is ineffecient to have 4 date feilds in my table. He wanted me to break them out into two other tables one with the dateType and one with the date itself. I told him that the performance difference will be negligable but it will be easier for me to code. I want to pull back all four dates in one record anyway, so why do I have to break them out. Is it really bad design to have a date field that allows nulls?
how vital enterprise application are for proactive organizations leveraging collective synergy to think outside the box and formulate their key objectives into a win-win game plan with a quality-driven approach that focuses on empowering key players to drive-up their core competencies and increase expectations with an all-around initiative to drive up the bottom-line. But of course, that's all a "high level" overview of things
--thedailywtf 3/21/06
-- modified at 8:41 Wednesday 14th June, 2006
|
|
|
|
|
ToddHileHoffer wrote: Is it really bad design to have a date field that allows nulls?
No.
The main question is what do the dates represent? If date1..date4 represent a continuation of the same thing then it may be better normalised to pull that out to a separate table. If they represent separate things then leave them where they are.
[ADDITION]
And using a performance argument on a DBA is virtually an exercise in pointlessness. In my experience they are hyper on normalisation to the detriment of everything else.
[/ADDITION]
[ADDITION2]
Damn! I just realised I've now got a dual role. I've recently joined a new company and I'm primarily a developer, but I'm also a DBA... GET THAT TABLE NORMALISED MAN!!! JUMP TO IT!!!! JUMP TO IT!!!
[/ADDITION2]
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
-- modified at 11:40 Wednesday 14th June, 2006
|
|
|
|
|
Colin Angus Mackay wrote: GET THAT TABLE NORMALISED MAN!!! JUMP TO IT!!!! JUMP TO IT!!!
----------
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 for the reply, yhey are all different, it is for an insurance ap. It is DateSubmitted, DateQuoted, DateBound, DateDeclined. I saw no point in breaking them out. I know either dateBound or dateDeclined will be null, but I don't care. It is just easier to code.
how vital enterprise application are for proactive organizations leveraging collective synergy to think outside the box and formulate their key objectives into a win-win game plan with a quality-driven approach that focuses on empowering key players to drive-up their core competencies and increase expectations with an all-around initiative to drive up the bottom-line. But of course, that's all a "high level" overview of things
--thedailywtf 3/21/06
|
|
|
|
|
Hi all,
I create a application that use SQL to store documents like picture files, sound files...Should i use image data type to store these files or just the path to the file or something else? please help me to figure out the best solution. Thank in advance!
|
|
|
|
|
Sure you have to use Image datatype.
The Image data type can store up to 2GB of binary data, which also enables it to store standard text data.
|
|
|
|
|
Thank albCode, but can you compare the performance btw 2 solutions:
1. Store the whole file in Image datatype field.
2. Store the path to the file only.
|
|
|
|
|
You need to think about how youe application will be used. Storing the file will make queries etc a lot slower but in some cases it may be the only way. If you store the path only then each client will need to be able to access that path (typically using a mapped drive).
So if you are using a purely server based application like a web app or even a small LAN based client server application storingthe path may be best.
Jon
|
|
|
|
|
If you use SQL2005 you should use varbinary(max). image should not be used any more.
Rainer Stropek
Visit my blog at http://www.cubido.at/rainers
|
|
|
|
|
Have got any one source,example or link about Full Text Search Services in SQL Server 2005.I have been searched that but i found all in sql server 2000...
|
|
|
|
|
hi to all,
I create a web application in asp.net with sql server 2000, I use always close connection in .net code as my consern but when run my application then visit page to page give a error "General network error " message . then i check lots of connection is sleeping state in process info in sql manegment(current activity) Now i open the particular processId in Process info show open connection with any storeprocedure name and show .net sqlclient data provoider referance as open connection which is fetch the data from sql then i check in code find really the connection which is got data by that store procedure is not close properly and i close it. This is ok but one problem is that some procesId show blank info with .net sql client data provoider. so how can i find where these conneciton is open in my applicaton because this processId doesn't give any clue for it , it show nothing only blank but status is sleeping. If any help have this context then quick feedback.
Thanks
hi_arv
|
|
|
|
|
I am working on getting data out of a datagrid, and save it to a SQL data base. I have the following code:
// Create parameters for the QA_Setup dataset via Sqlcommand
SqlCommand cmd = new SqlCommand();
// Define the params for each column in QA_SetUp
// cmd.Parameters.Add("@CompanyName", SqlDbType.VarChar, 50, ds.Tables("CompanyName").column("CompanyName"));
cmd.Parameters.Add("@CompanyName", SqlDbType.VarChar, 50, "CompanyName");
cmd.Parameters.Add("@CompanyCode", SqlDbType.VarChar, 50, "CompanyCode");
cmd.Parameters.Add("@InspectionPrefix", SqlDbType.VarChar, 50, "InspectionPrefix");
cmd.Parameters.Add("@TestPrefix", SqlDbType.VarChar, 50, "TestPrefix");
cmd.Parameters.Add("@DefectPrefix", SqlDbType.VarChar, 50, "DefectPrefix");
cmd.Parameters.Add("@AuditTrail", SqlDbType.Bit, 1, "AuditTrail");
//define connection and stored proc name
cmd.Connection = conn;
cmd.CommandText = sproc;
// call insert command for the cmd
dap.InsertCommand = cmd;
When I run the app I and try and save I get a syntax error(System.Data.SQLClient.SQLException) as follows:
Line1: Incorrect Syntax near 'prc_QA_Setup_ins'. This is the sproc name that is passed in. Not sure what my issue is, any idea?
the sproc is declared as string sproc="prc_QA_Setup_ins"; which is passed over to my DAL which is where the code above resides.
-- modified at 21:52 Tuesday 13th June, 2006
|
|
|
|
|
|
Perfect it was the cmd.CommandType = CommandType.StoredProcedure line that I was missing... thanks!
|
|
|
|
|
hi,
i need to deploy a database with several replications on about 500 machines. does anybody has an idea how to manage or automate that process?
thanks in advance.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
