|
Somewhere in that godawful load of crap you call a query there is a join to a table that has the city in it, use group by cityname may be useful.
That is one ugly query!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Thanks Mycroft Holmes;
Groupby gives me no count of student in any city
WITH r1
AS (SELECT CAPITALISE_SEMESTRE_1.NUM_INSCRIPTION,
CAPITALISE_SEMESTRE_1.CODE_SEMESTRE , CAPITALISE_SEMESTRE_1.CODE_TYPE_RELATION_SEMESTRE ,CAPITALISE_SEMESTRE_1.ann_uni
FROM CAPITALISE_SEMESTRE AS CAPITALISE_SEMESTRE_1 WHERE (CAPITALISE_SEMESTRE_1.CODE_TYPE_RELATION_SEMESTRE = 4) AND (CAPITALISE_SEMESTRE_1.CODE_SEMESTRE = 2)),
r2 AS
(SELECT CAPITALISE_SEMESTRE.NUM_INSCRIPTION AS Expr1, CAPITALISE_SEMESTRE.CODE_SEMESTRE AS Expr2,
CAPITALISE_SEMESTRE.ann_uni AS Expr4
FROM CAPITALISE_SEMESTRE AS CAPITALISE_SEMESTRE
WHERE (CAPITALISE_SEMESTRE.ann_uni = '2008/2009') AND (CAPITALISE_SEMESTRE.CODE_SEMESTRE = 1))
SELECT r1_1.NUM_INSCRIPTION
FROM r1 AS r1_1 INNER JOIN
r2 AS r2_1 ON r1_1.NUM_INSCRIPTION = r2_1.Expr1
|
|
|
|
|
Hi all,
I'm using OLEDB to reading data from an excel file, using connection string below:
OleDbConnection conn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FileName + ";Extended Properties= 'Excel 8.0;IMEX=1'");
OleDbCommand command = new OleDbCommand("Select * from [ItemDetails$]", conn);
In my Excel sheet, one column contains data like this. :2424245900,PDD012000,IXX088700.
my code reads this value: 2424245900.
But Its return empty string while reading NXX012000,IXX088700
Help me, its urgent.
Thanks in advance..
I couldn't read the file...Please help me!
Thanks in advance.
deesh
|
|
|
|
|
SImple replication question. I am running MS-SQL 2005 on a couple of machines. How can I copy the data from one machine to another. The target machine has some of the data, but I would like to update the dataset with want is on another machine. Simple summary: Replicate the data from production to debug without using backup and restore. There are some tables on the debug machine that are in process of being worked on that have yet to be added to the production, so all I want is productions data to be copied. There are auto incremented keys that need to remain the same.
Thanks,
Leo T. Smith
Program/Analyst Supervisor
|
|
|
|
|
There is no easy way to do this without writing some programming.
Take these things into consideration:
1) What are the dependencies among the tables? This will determine the order of the tables to be processed. For example, Invoice Header is processed before Invoice Details.
2) Auto increment keys could be a killer for you. On production the key may be 1001, but in debug that key is already used. Therefore the program would have to look at the data as a “business object”, meaning it would have to re-create the Invoice Header with a new key and the subsequent invoice details would have to have the proper foreign key to point back to this new Invoice Header.
3) Get yourself a good ER (entity relationship) diagram of your system and start thinking about how you could pull data over in chunks.
Sounds like an interesting challenge , but once you have it made it will become invaluable.
Good luck.
|
|
|
|
|
Thanks for the information. I really was hoping this would not be the case. So then the next questions will to be; Is there a way via stored procedure to:
A. Command for turning on and off constrants?
B. Command to turn off and on auto increment, or would truncating reset increments to 0?
C. Is there a command that will allow for copying data between one DB server and DB another in SQL 2005?
D. Is any of this easier in CLR functions?
Thanks,
Leo T. Smith
Program/Analyst Supervisor
|
|
|
|
|
Hi All,
I have an application (windows forms with .Net 3.5) that is making use of an SQL Server 2008 database (which is installed of a server machine on the network).
Communication between the two is done with the use of System.Data.SqlClient classes.
Now my installation of the application is working fine. But i have alot of things installed on my machine such include a version of SQLExpress which presumably came with a few components required for database connections.
Basically my question is, if I had a freshly installed OS on a brand new machine. What would i need to install on the local machine in order to get my application to work correctly with SQL Server 2008? Naturally i will be installing .Net 3.5 framework. But do I need any SQL drivers/services on the local machine?
Thanks
Life goes very fast. Tomorrow, today is already yesterday.
|
|
|
|
|
As far as I understand, the .net framework runtime provides everything you need to connect to the database, although I'd run a few tests on your target OSs before deploy!
"An eye for an eye only ends up making the whole world blind"
|
|
|
|
|
Thanks for the reply.
I did think that was all required as I seem to remember reading something a while back saying that the server installation of SQL Server 2008 installs the required services and local machines can simply talk to them with no addition drivers. I just couldn't find any conclusive documentation on this.
And don't worry, It will be thoroughly tested before release Which will ultimately answer my question if anything else is required... but that's not for a while yet.
Life goes very fast. Tomorrow, today is already yesterday.
|
|
|
|
|
hi all,
i want to install SQL server Express 2005 programmatically. so i use following statement
str = Application.StartupPath & "\SQLEXPR32\setup.exe /qb ADDLOCAL=ALL INSTANCENAME=" & InstanceName & " SECURITYMODE=SQL SAPWD=password DISABLENETWORKPROTOCOLS=0 "<br />
Shell(str, AppWinStyle.Hide, True)
above code works well. but when i try to Create backup pro grammatically, then it create backup only in Backup folder of SQL server. if i specify other path(other then Backup) then Backup process failed.
Please suggest
|
|
|
|
|
Hey everyone,
Any good experience on installing Oracle on Windows Vista?? I'm running "Vista 32-bit Home Basic"
and I just cant install Oracle 10g, Oracle says I have to get a 10.2.0.4 patchset or higher which I cant lay my hands on because I'm no Oracle Partner and my country is not even listed there
Please advise!
All generalizations are wrong, including this one!
(\ /)
(O.o)
(><)
|
|
|
|
|
You can install Oracle on 32-bit Vista.
If you do not have a support contract, you cannot get patchsets such as 10.2.0.4 or any other type of product update. You can only install the products that are available as downloads on OTN. Everything else is in MetaLink, which requires payment for support.
Kind regards,
Russ
|
|
|
|
|
hi all,
i want to create backup of my database (related to SQL server Express 2005) from .NET application. Application is install on SERVER and some Client computer(which is connected to SERVER). I want to create backup from SERVER and client.
for backup purpose i use following statement
Dim cmd As SqlCommand<br />
cmd = New SqlCommand("Backup Database abc to disk = @BackUpPath")<br />
cmd.CommandType = CommandType.Text<br />
<br />
Dim RootPath1 As String = DestinationPath 'Path for create Backup<br />
Dim shellCommand As String = RootPath1 & "abc.bak"<br />
cmd.Parameters.AddWithValue("@BackUpPath", shellCommand)<br />
ExecuteRestoreCommandQuery(cmd, "master")
and this is the procedure ExecuteRestoreCommandQuery
Private Sub ExecuteRestoreCommandQuery(ByVal comm As SqlCommand, ByVal DatabaseName As String)<br />
Dim con1 As SqlConnection = Nothing<br />
Try<br />
con1 = New SqlConnection("Data Source=" & PublicServerName & "\" & PublicInstanceName & ";Initial Catalog=" & DatabaseName & ";User ID=sa;Password=password")<br />
comm.Connection = con1<br />
con1.Open()<br />
comm.ExecuteNonQuery()<br />
con1.Close()<br />
<br />
Catch ex As Exception<br />
msgbox(ex.Message.ToString)<br />
If con1.State = ConnectionState.Open Then<br />
con1.Close()<br />
End If<br />
End Try<br />
<br />
End Sub
but when i run above code then it Gives error
"Can not open backup device" path ... Operating System Error 5(Access is denied.). BACKUP DATABASE is terminating abnormally."
so how can i create backup from application which is installed on SERVER and CLIENT.
|
|
|
|
|
Are you sure that the account that SQL Server is running under has write permissions to wherever you are trying to back up to?
Scott
|
|
|
|
|
scottgp wrote: Are you sure that the account that SQL Server is running under has write permissions to wherever you are trying to back up to?
no idea about this thing.
How can i find that whether any account have write permission?
BTW i use sa account for create backup
|
|
|
|
|
You may be connecting as sa, but I believe the backup is actually executed by the account that the SQL Server service is executing under. What version of SQL Server are you using? If it's 2005, you can use the SQL Server Configuration Manager to view and change what account it's using, or you can use the Services tool under Administrative Tools.
Scott
|
|
|
|
|
scottgp wrote: I believe the backup is actually executed by the account that the SQL Server service is executing under
how can i get the information about the account?
scottgp wrote: If it's 2005, you can use the SQL Server Configuration Manager to view and change what account it's using,
yes it is, It is Built in Account under which network service is selected.but how it affect backup process?
|
|
|
|
|
I guess the question is - does the Network Service account have access to whatever RootPath1/DestinationPath is in your code? Is DestinationPath on a network somewhere?
Scott
|
|
|
|
|
one more silly question
scottgp wrote: does the Network Service account have access to whatever RootPath1/DestinationPath is in your code? Is DestinationPath on a network somewhere?
how can i find whether it is have access to given path?
however an Access denied message is appear when i select any path except Backup folder of SQL Server
|
|
|
|
|
The normal way that you check permissions in Windows. Right-click on the directory in Windows Explorer to bring up the context menu and select properties. Then select the security tab. You can then add the Network Service account and grant it write permissions. Alternatively, you could run SQL Server under another account - one that already has write permissions.
Scott
|
|
|
|
|
I'm using SQL Express server and i linked table in the db with table in the dataset
which is linked with gridview
and i write procedure to insert records to the table in the database but the grid does not display the new records
why?
|
|
|
|
|
Hi, can you post your code?
Hope this one can help.
Thanks
Hi, Please select Good Question if my answer are fit to your Question.
|
|
|
|
|
Hi All,
Is there a way that i can convert nonnumberic nvarchar to float, such as: Convert(float, 'H1234), I searched on net, even msdn says "SQL Server returns an error message when nonnumeric char, nchar, varchar, or nvarchar data is converted to int, float, numeric, or decimal".
Thanks heaps.
|
|
|
|
|
I have to say, this is one of the most ridiculous questions I have seen in a long time - and there are some pretty stupid ones posted here. How do you expect to turn non-numeric data into a number? What do you expect to get from H1234?
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
what the f... r u talking about, dumb sick c***? Any realistic is from ridiculous thougt, such as ages ago ppl was saying that the Earth is round rather than square. If you idiot don't know, that's fine, jut get the f... out of here, you, MVP?, my goddness, where does this sort of rubbish come from?
|
|
|
|