|
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?
|
|
|
|
|
|
But intranet is not internet, its an internal network. Yes you could say that for big companies having customers connect to their website and using the database via asp.net. But what about internal customers and employees?
What about the small that can't afford to pay for the processor licensing model!
|
|
|
|
|
Use SQL Express or move to one of the other free alternatives (MySql, etc.)
Having used SQL Server for many years, I found it fairly cheap for what it does. It is the only server I have that has never 'burped'. We used to buy CALs many years back but, switched to per processor when SQL 2000 came out. At 2 processors running for 6 years with 30+ active users/devices that comes out to about $50 bucks per user per year with no license managment headaches. I am not even counting our Internet hits. Granted, coming up with the $10K in the beginning was tough.
I think you will find the pricing competitive with Oracle and other mature datbases.
|
|
|
|
|
Hi all,
I have a problem regarding data bindings. I am using Ms Access database and I have an application which loads the tables in datagrids and textboxes.
I used joins since there are relations between tables. For example I have a table called "Alterations" and in this table there is a field called "Client" which brings the name of the client from the table "Clients" .
When running the application an error occurs stating that "no value given for one or more required parameters"
Some one knows what this error is please??
Thanks alot
|
|
|
|
|
check syntax or can u display ur problem here??
|
|
|
|
|
My problem is that when I run the application an error occurs stating that "no value given for one or more required parameters"
And I am not understanding the error!
|
|
|
|
|
check syntax if any parapmeter is without value.
when u call method and possiblity u forgot to give value for parameter.
|
|
|
|
|
Can u pls tell me, wether following SQL string format is correct or not.
textWord.text,comboCat.text are string variables of C#.
indexInCategoryTable and SubCategoryNo are integer variables.
"select * from Words where
word = N'"+textWord.Text+"' and
CategoryTableName = '"+comboCat.Text+"' and
IndexInCategoryTable = 'indexInCategoryTable' and
SubCategoryNo = 'SubCategoryNo'";
|
|
|
|
|
cshivaprasad wrote: Can u pls tell me, wether following SQL string format is correct or not
No. You should use parameters rather than inject values into the query - See SQL Injection Attacks and Tips on How to Prevent Them[^] unless you want your database attacked by a rampaging mob of mallicious attackers.
"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
|
|
|
|
|
Procedure: We have two tables namely table A and Table B. Table A contains a field called ID Table B contains field ID and field 1,field 2,field 3.The procedure is to pick value from table A ID field, increment it by 1 to it and update the table A, then insert data into table B with this incremented value and other data.
Problem: When multiple concurrent users perform updation/insertion of data into table A,table B duplicate IDs are getting created in table B even though the statements are written within ADO.NET BEGINTRANSACTION,COMMIT/ROLLBACK.
Thanks
Mahesh
-- modified at 2:11 Tuesday 9th May, 2006
|
|
|
|
|
Study up on Holding Locks and SET TRANSACTION ISOLATION LEVEL in SQL Server Books Online.
----------
There go my people. I must find out where they are going so I can lead them.
- Alexander Ledru-Rollin
|
|
|
|
|
Ex: SELECT DAY(GETDATE())
its display 9 but i need to show tuesday.
can anybody help?
raj
|
|
|
|
|
i found out myself. thanks
the answer is below:
SELECT datename(dw,getdate())
raj
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
