|
Thank you Colin,
but my application uses a dataset, built by dataset wizard (Add datasource)from an Access db, what I want to do is to find a way to switch to SQL server at runtime using the same dataset. Changing the connection string the dataset's query don't function. I've used standard sql. There's a way to use an xml file that can change the query in dataset (according to the rigth database)set designer at compile time.
Best regards
Armando
|
|
|
|
|
armax75 wrote: built by dataset wizard
Well, there's your problem right there! Don't rely on wizards, learn how it actually works so you can leverage the technology to do what you want rather than a narrow set of possibilities provided by a dumbed down wizard.
armax75 wrote: what I want to do is to find a way to switch to SQL server at runtime using the same dataset
You can do that with the solution I proposed - if you do away with a reliance on wizard generated code.
armax75 wrote: Changing the connection string the dataset's query don't function
Of course not. There are many forms of the SQL langauge, each database implements its own varient.
armax75 wrote: I've used standard sql
Really? I wasn't aware that Access or SQL server used "Standard SQL". You might like to take a look at an open source project called NHibernate that uses its own SQL variant and translates that into the specific dialect used by the database system to which it is connecting.
armax75 wrote: There's a way to use an xml file that can change the query in dataset (according to the rigth database)set designer at compile time.
Datasets are database agnostic. They don't know anything about the database - that is the job of the Data Adapter.
You can use an XML file to contain the various dialects of SQL you might like to use, but the solution would be more useful at run time. If you are looking for a compile time switch why not look into #define[^], #if[^] and #endif[^]. But I'm confused, you said earlier you wanted to switch at run time. Which is it?
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
My: Website | Blog
|
|
|
|
|
Take a look at MicroSoft's Data applicatiom block, Generally called Enterprise Library.
http://msdn.microsoft.com/practices/[^]
Look where you want to go not where you don't want to crash.
Bikers Bible
|
|
|
|
|
Have several tables on SQL Server from a Web Hoster.
I have no way of actually coping the tables.
So using INSERT SELECT I thought I could just copy the entire table to tableName2 for back up.
I tried
INSERT Category2
SELECT * FROM Category
But it wants the table already defined. I know theres a way to create the table in the statement also.
Can anyone help.
Thanks,
Nick
1 line of code equals many bugs. So don't write any!!
|
|
|
|
|
SELECT * INTO Category2 FROM Category
----------
There go my people. I must find out where they are going so I can lead them.
- Alexander Ledru-Rollin
|
|
|
|
|
|
Hi
I have SQL server 2000 on server machine. SQL client tools are not installed there. I want to detach a database on server. How can I do it?
Any suggestions?
Thanks
|
|
|
|
|
You can use Enterprise Manager from any machine that can see the server the database is on. You can then remotely detach the database.
To get Enterprise Manager to access a remote server, right-click SQL Server group and select New SQL Server Registration...
Follow the wizard (You can add the server name manually if it doesn't show up in the list on the Select a SQL Server page)
Now you have the server registered with Enterprise Manager you can operate on it - Just remember that any file paths for things are from the point of view of the remote server, not local paths.
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
My: Website | Blog
|
|
|
|
|
|
You can use the sp_detach_db stored procedure.
Note:
Only members of the sysadmin fixed server role can execute sp_detach_db.
----------
There go my people. I must find out where they are going so I can lead them.
- Alexander Ledru-Rollin
|
|
|
|
|
Hi guys,
I started my project on VB, stating it was a Database application.
Doing that , the project was embedded to my database.mdb file, so now I could see a nice GUI of all my tables.
The thing is that I need to choose the mdb in runtime, using a "File Open" dialog box.
Me.ProductOrdersTableAdapter.Fill(Me.PlugDataSet.ProductOrders)
Me.PORowsTableAdapter.Fill(Me.PlugDataSet.PORows)
Me.PINSerialTableAdapter.Fill(Me.PlugDataSet.PINSerial)
Me.CustomizationTableAdapter.Fill(Me.PlugDataSet.Customization)
Me.FirmwareTableAdapter.Fill(Me.PlugDataSet.Firmware)
- All these (for example) have been added to my form1.vb code. the thing is that PlugDataSet and ProductOrdersBindingSource were all generated inside the project files. All I want to do is change the DB source in runtime.
How can I change the connection string for the mdb file and not affect all my code ?
Thx
-- modified at 12:34 Wednesday 10th May, 2006
|
|
|
|
|
hi all,
There is tabled called "dbo.MarkOne".
There are 20 columns there with a AutoNumber Column(AutoNum)
Now I need to get Auto Number with Minimum Price Grouping DestinationAirport,DepartureAirport,AirLineCode,FareClass Column Details
query is like this.
SELECT MIN(Price),AutoNum AS NEWPRICE FROM dbo.MarkOne
GROUP BY DestinationAirport,DepartureAirport,AirLineCode,FareClass
This returns a Error--
Column 'dbo.MarkOne.AutoNum' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
How do I fix this?.I need to show Auto Number with Minimun Price.
Can anyone please help me
|
|
|
|
|
I don't have your data so I will take a guess without testing:
SELECT
AutoNumber,
MarkOne.Price
FROM
MarkOne
INNER JOIN
(SELECT
DestinationAirport,
DepartureAirport,
AirLineCode,
FareClass,
MIN(Price) AS Price
FROM
dbo.MarkOne
GROUP BY
DestinationAirport,
DepartureAirport,
AirLineCode,
FareClass) gMark
ON(gMark.DestinationAirport = MarkOne.DestinationAirport AND
gMark.DepartureAirport = MarkOne.DepartureAirport AND
gMark.AirLineCode = MarkOne.AirLineCode AND
gMark.FareClass = MarkOne.FareClass AND
gMark.Price = MarkOne.Price)
|
|
|
|
|
Hi
Include AutoNum in the GROUP BY
SELECT MIN(Price),AutoNum AS NEWPRICE FROM dbo.MarkOne
GROUP BY DestinationAirport,DepartureAirport,AirLineCode,FareClass, AutoNum
Chinna Srihari
Tech Lead, Capgemini
|
|
|
|
|
I wan to use xp_cmdshell to copy an sql file on another machine (which has a linked server). I use it like this:
EXEC master.. xp_cmdshell 'copy c:\test\query.sql \\Dell7\test' --Dell7 is the other machine
when i apply this query to copy in the local server, it functions, but for the linked server i always have this error :
-----------
Access is denied.
0 file(s) copied.
NULL
---------------
Some friend say with me
Your sql service account needs permission to create file on dell7.
but i dont know Where permission for my service
And my netWork is Domain. I have Account Administrator
Some body help me
|
|
|
|
|
The copy comand will be run as the user that the SQL Server is running as - If you open up the Task Manager, click on the processes tab and look for sqlservr.exe it will tell you what account it is being run as (if you have the User Name column on)
If this is a Domain account you can give the account permission to write files in the destination.
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
My: Website | Blog
|
|
|
|
|
Hi,
1.In what scenarios we prefer to use UDFs instead of Stored Procedures?
2.UDFs won't allow out paramaters like Stored Procedures.Other than that how really an UDF differ from Stored Procedure.
3.In ADO.NET,Functionality wise,how a datalist differ from datagrid?
Thank you,
Deepa!
Be the Change you want to see!
|
|
|
|
|
Q.2: UDFs are structurally similar to stored procedures. Also like a stored procedure, you can declare variables and use other functions in UDFs, though there are limitations.
Unlike stored procedures, UDFs can be used in the FROM clause of a SELECT statement.UDFs are also more functional than stored procedures in some other interesting ways. As with a correlated subquery, single-value result sets can be used as parameters of UDFs.
Q.3: Visit this link
http://msdn.microsoft.com/msdnmag/issues/01/12/asp/[^]
Chinchu Raj S
|
|
|
|
|
Deepasubramanian wrote: 1.In what scenarios we prefer to use UDFs instead of Stored Procedures?
2.UDFs won't allow out paramaters like Stored Procedures.Other than that how really an UDF differ from Stored Procedure.
Some things that pop into my head:
UDFs cannot do many things that a stored procedure can. They have to be deterministic (which means that given the same set of inputs the same output always happens). A stored procedure doesn't have that restriction. e.g. GETDATE() is a non-deterministic function because its result is different each time it is called. You can use GETDATE() in a stored procedure but not in a UDF.
UFDs can be used in a SELECT statement, Stored Procedures cannot. e.g.
SELECT a, b, dbo.MyUDF(c, d) FROM MyTable
A UDF returns something, a stored procedure doesn't have to return anything.
You cannot modidfy the database in a UDF
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
My: Website | Blog
|
|
|
|
|
Hello gurus. I am hoping that one of you will know the answer to this question, as I am at a complete and total loss (even google, ultimate mensa of the net, seems to have a hole in its memory). So, without further ado, here is the problem:
I have a simple web app. Its currently 2 pages, written in ASP.NET 2.0 using C# and ADO.NET 2.0. The data store is SQL Server 2000, with a meager 10 tables. All of the tables except one have integer identity PK columns, and the one unique one has a uniqueidentifier (guid) PK column. If I query any table by ID directly from SQL Server (enterprise manager or query analyzer) I can query all of the tables without problems. HOWEVER, when I query the tables from ADO.NET 2.0 (or directly from Visual Studio 2005's server explorer, which I beleive uses ADO.NET 2.0), I am getting odd behavior from the table with the uniqueidentifier PK. Oh, and BTW, all of the queries are in stored procedures, which are called from C# code.
When I query the uniqueidentifier table with a simple stored proc containing one parameter (for the guid ID of the row I want to retrieve) and a SELECT statement, ADO.NET 2.0 can't seem to handle this properly. Instead of getting a single row for the record that has a matching ID, I get a result set that looks valid, except it has no data. When I try to use a SqlDataReader to read the fields of the row thats returned, everything seems to work right, but I ultimately get an InvalidCastException. An example (or rather, actual) stored procedure is:
CREATE PROCEDURE dbo.spDSB_GetSuperbillHeader
(
@AppointmentID uniqueidentifier
)
AS
BEGIN
SELECT
a.AppointmentID,
a.ApptDate,
a.PatientID,
a.DoctorID,
a.FacilityID,
a.ApptReason,
a.ApptComments,
a.RefDocName,
a.RespPartyName,
a.FeeSchedule,
a.PriInsName,
a.SecInsName,
a.Copay,
a.PtBalance,
a.AcctComments,
d.DoctorName,
f.FacilityName,
f.Address AS FacilityAddress,
f.CityStateZip AS FacilityCSZ,
f.PhoneNo AS FacilityPhone,
f.TaxID,
p.PatientName,
p.Address AS PatientAddress,
p.CityStateZip AS PatientCSZ,
p.HomePhone,
p.BusinessPhone,
p.MobilePhone,
p.BirthDate,
p.SSN
FROM
tblAppointments a
INNER JOIN tblDoctors d ON a.DoctorID = d.DoctorID
INNER JOIN tblFacilities f ON a.FacilityID = f.FacilityID
INNER JOIN tblPatients p ON a.PatientID = p.PatientID
WHERE
a.AppointmentID = @AppointmentID
END
The resulting error details are:
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.InvalidCastException: Specified cast is not valid.
Line 364: return defVal;
Line 365: else
Line 366: return m_reader.GetInt32(idx);
Line 367: }
Line 368: catch (IndexOutOfRangeException ex)
[InvalidCastException: Specified cast is not valid.]
System.Data.SqlClient.SqlBuffer.get_Int32() +121
System.Data.SqlClient.SqlDataReader.GetInt32(Int32 i) +39
MedfordMS.DigitalSuperBill.QuickDataReader.GetInt32(String fieldName, Int32 defVal) in F:\Programming\MedfordMDS\Current\DigitalSuperBill\MedfordMS.DigitalSuperBill\MedfordMS.DigitalSuperBill\_Database\QuickDataReader.cs:366
And, for reference purposes, here is what is returned if I execute that procedure with a valid GUID directoy from VS2005's Server Explorer (Note that it returned the column headers but no data, and normally it never returns column headers unless there is data, too):
Running [dbo].[spDSB_GetSuperbillHeader] ( @AppointmentID = d1f880bf-dcf6-4563-a01c-0153df0f16ea ).
AppointmentID ApptDate PatientID DoctorID FacilityID ApptReason ApptComments RefDocName RespPartyName FeeSchedule PriInsName SecInsName Copay PtBalance AcctComments DoctorName FacilityName FacilityAddress FacilityCSZ FacilityPhone TaxID PatientName PatientAddress PatientCSZ HomePhone BusinessPhone MobilePhone BirthDate SSN
-------------------------------------- ----------------------- -------------------- ----------- ----------- -------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- ---------------- ---------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------- -------------------------------------------------- ---------------------------------------------------------------------------------------------------- -------------------------------------------------- --------------- --------------- -------------------------------------------------- ---------------------------------------------------------------------------------------------------- -------------------------------------------------- --------------- --------------- --------------- ----------------------- ----------
No rows affected.
(1 row(s) returned)
@RETURN_VALUE = 0
Finished running [dbo].[spDSB_GetSuperbillHeader].
Thanks for any help.
|
|
|
|
|
Well, for anyone who runs into the same problem, the solution is simple, if odd. Seems you can't directly pass a Guid to a stored proc using an ADO.NET parameter. To get the stored procedure to work, I had to do the following:
cmd.Parameters.Add("@param", SqlDbType.VarChar, 40);
cmd.Parameters["@param"].Value = myGuid.ToString("D");
This sends the guid as a string value in the form {xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx}. You must be sure to explicityly set the varchar length to 40, as setting it to 38 (which I did when I originally tried this, since I thought the bounding { and } would be invalid) will still cause the call to fail.
You can keep the type of the parameter in your stored procedure as uniqueidentifier. You do not have to change it to varchar, and in fact, if you do, the stored procedure will fail since varchar(40) won't match the uniqueidentifier type of the column.
|
|
|
|
|
I have Parent child table as decribe below:
Parent Table name = TESTPARENT
1. counter bigint isIdentity=Yes Increment=1 Seed=1
2. customer nChar(10)
Child Table name = TESTCHILD
1. counter bigint
2. qty numeric(3,0)
I want to insert new record with code below:
void button1_Click(object sender, EventArgs e)
{
// Define object to catch @@indentity
object myCounter;
// Connect to database & open
myConnection = new SqlConnection("Data Source=54ND1\\SQL2005;Initial Catalog=Axioma;User ID=sa; Password=sandi");
myConnection.Open();
// define transaction
SqlTransaction myAtom = myConnection.BeginTransaction();
SqlCommand myAtomCmd = myConnection.CreateCommand();
myAtomCmd.Transaction = myAtom;
// Start insert to database with transaction mode
try
{
// Insert parent new record
myAtomCmd.CommandText = string.Format("insert into TESTPARENT (customer) values ('{0}')", tbCustomer.Text);
myAtomCmd.ExecuteNonQuery();
// Get Indentity
myAtomCmd.CommandText = "SELECT @@identity from testParent";
myCounter = myAtomCmd.ExecuteScalar();
// insert child new record
myAtomCmd.CommandText = string.Format("insert into TESTCHILD (counter, qty) values ('{0}', {1})",
Convert.ToInt64(myCounter.ToString()), tbQty.Value);
myAtomCmd.ExecuteNonQuery();
// Commit transaction
myAtom.Commit();
}
catch
{
myAtom.Rollback();
MessageBox.Show("Data not inserted");
}
}
I already try with 2 workstation and 1 server, that code working well (not duplicate in parent and insert right relation
child parent record in child table ).
If, i run with many many user, I am not sure that code will stay stable.
Please advice, that code is the right way to archieve parent child relation insert table??
I using C# and SQl Server 2005
Thank,s and regards 
|
|
|
|
|
|
thank for your advice Mackay 
|
|
|
|
|
wow!, i am amazed at how micro$oft can charge for sql licences. My qusetion is pretty simple but no one can answer it!
If i am using asp.net via sql server 2005, so imagine 20 people via the intranet or lan accessing the same page. How many CAL's do i need?
It should be pretty trival, but i can't find the answer anywhere?
Microsoft say you need a cal for every user or device making a direct connection to sql server or even indirect connections, like pooling or multiplexing. See this page http://www.microsoft.com/sql/howtobuy/multiplexing.mspx[^]
I know of certain application like Cimplicity by GE Fanuc that do this, ok they are running scripts as part of a user action but still 20 people could be making the same connection using the same CAL, surely this is illegal?
Why it is ok for some and not other -- are GE in bed with Microsoft!!
thanks -- just standing on my soap box
can anyone give my an answer?
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
