|
use if else statement in stored procedure and use this condition
If @UserID IS Not NULL
'write your query
Else
' write query without where clause, it will display all the records
Do good and have good.
|
|
|
|
|
Thanks for your reply
But as i explained in my post, i might have up to 5 optional parameters and if i use if else stataments ill end up having more than 20 almost equal combinations, which i dont want to do, not beacuse i cant (they would be almost the same), but because i believe that there is a better solution for this problem
Thanks
Alexei Rodriguez
|
|
|
|
|
Hello,
I need some help with a SQL query.
Example table:
Table myTable
Columns
myId, myDate
If I have rows in the table with the same id like this:
myid mydate
---- --------
1 01/01/1999
1 blank
1 01/01/1999
2 01/02/1999
I would like to filter out the id completely if I find the id has a record with a date and a record with a blank date.
If the filter works my results should be:
myid mydate
---- --------
2 01/02/1999
Id 2 remains because it does not have records with a blank date.
Thank you so much for the help.
|
|
|
|
|
Your request is somewhat confusing but, I think I grasped the basic concept. I assume you are using blank as a term that represents NULL.
SELECT
myid,
mydate
FROM
myTable
WHERE
myid NOT IN (SELECT
myid
FROM
myTable
WHERE
mydate IS NULL)
|
|
|
|
|
Try
SELECT myid, mydate<br />
FROM myTable mt1 <br />
WHERE not exists (select 1 from myTable mt2<br />
where mt2.myID = mt1.myid and mt2.mydate is null)
Assuming the table is properly indexed this should execute quicker as a not exists is quicker than a not in.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Does anybody know where there is an article about how to write a .HLP help file? It is my understanding that it can be done in Windows and can also be done in a web type application.
modified on Wednesday, March 5, 2008 5:27 PM
|
|
|
|
|
new_phoenix wrote: Does anybody know where there is an article about how to write a .HLP help file? It is my understanding that it can be done in Windows and can also be done in a web type application.
This is a SQL question...how?
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
Hello there,
I have an application in VB.Net in which I import data from Excel files to SQL server database.
I'm trying to retrieve a table in Excel 2007 worksheet, which has more than 255 columns, using Ado.net 2
The problem lies in the "Select * from [Sheet1$]" statement, which will only pick a maximum of 255 columns from the Excel sheet, even though the sheet has around 260 columns.
here is my code for your review:
'*****************************************************************************
'The connection string.
Dim xlCon As String = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & lblImportFile.Text & _
";Extended Properties=""Excel 12.0;HDR=YES"""
'Excel "SELECT" statement
XlsSQL = "Select * from [Sheet1$]"
'Declare and instantiate the connection object.
Dim con As New OleDbConnection(xlCon)
'Declare and instantiate the command object.
Dim com As New OleDbCommand()
'Declare and instantiate a DataAdapter which fill the dataset with data.
Dim da As New OleDbDataAdapter
'Add values to the command object's properties.
With com
.Connection = con
.CommandType = CommandType.Text
.CommandText = XlsSQL
End With
With da
.AcceptChangesDuringFill = False
.SelectCommand = com
'Add data to the dataset.
.Fill(dsXLData, "Ratings")
End With
da.Dispose()
con.Dispose()
|
|
|
|
|
This is a limitation that you will have to work around. Maybe you could split the upload into 2 files, each with 130 columns.
However, maybe you should consider using a different file format. A text-based flat file wouldn't have the same problems.
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
Thanks Paul for your reply.
I take it that you are dead sure that no way on earth could a "Select" statement pick more than 255 columns. Right?
Your suggestion of splitting the upload seems to be the best work-around for that limitation.
Thanks again for your help. Greatly apprecaited.
Hani
|
|
|
|
|
Mehawitchi wrote: I take it that you are dead sure that no way on earth could a "Select" statement pick more than 255 columns.
Certainly not on earth.
Maybe in a galaxy far, far away... 
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
Hi again Paul,
I followed your suggestion and split the uploads.
The problem now in the rows of second upload, instead of adding them to the same rows of first upload, they are bieng added on top of them.
To explain further, the excel sheet has 280 columns x 100 rows. In the first upload, I fill the dataset with 255 clms x 100 rows. In the second upload, I fill the dataset with 25 clmns x 100 rows, but in the end result I get a dataset with 280 clmns (cool) and 200 rows (Not cool)
Is there any way perhaps in a galaxy not far away  , that I enforce the dataset to add the rows of the second upload into those of the first upload
Greatly appreciate your help
|
|
|
|
|
Perhaps this might work better:
SELECT * from [MyNamedRange]
or
SELECT * from [Sheet1$A1:B10]
|
|
|
|
|
Good afternoon All
i have a DTS Package from a Different Server and i want to Execute it from inside a Stored procedure and execute it in vb. The Vb part i will handle, i would like to know how am i going to achieve that in SQL
Thanks
Vuyiswa Maseko,
Sorrow is Better than Laughter, it may Sadden your Face, but It sharpens your Understanding
VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
vuyiswam@tshwane.gov.za
|
|
|
|
|
Did you try searching Google before posting?
I did and got several relevent results[^].
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
Good Afternoon Paul
i always Google, but the one Find was complicated, they pass a Parameter, but i managed go sort it out like this and its working fine, please Comment on it.
<br />
Create procedure sp_Call_Refresh_ValuationRoll_dts <br />
AS <br />
declare @Command varchar(1000) <br />
SET @Command = 'DTSRun /S SGIIRCONETGS01 /U sde /P topology /N Valuation_Roll_Refresh' <br />
<br />
EXEC master.dbo.xp_cmdshell @Command <br />
GO <br />
Thanks
Vuyiswa Maseko,
Sorrow is Better than Laughter, it may Sadden your Face, but It sharpens your Understanding
VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
vuyiswam@tshwane.gov.za
|
|
|
|
|
It looks OK. Does it work for you?
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
i get some Permission Errors like
<br />
xp_cmdshell failed to execute because LogonUserW returns error 1909. please make sure the service account SQL Server running under has appropriate privilege. For more information, search Book Online for topic related to xp_sqlagent_proxy_account.<br />
Msg 50001, Level 1, State 0<br />
But am still busy on permission of a user sde.
Thanks
Vuyiswa Maseko,
Sorrow is Better than Laughter, it may Sadden your Face, but It sharpens your Understanding
VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
vuyiswam@tshwane.gov.za
|
|
|
|
|
i need open file xls from query?
i used this command
select *
FROM
OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=C:\a.xls; Extended properties = Excel 8.0')...[sheet1$] a
but make error
Server: Msg 7314, Level 16, State 1, Line 15
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' does not contain table 'sheet1$'. The table either does not exist or the current user does not have permissions on that table.
OLE DB error trace [Non-interface error: OLE DB provider does not contain the table: ProviderName='Microsoft.Jet.OLEDB.4.0', TableName='sheet1$'].
--------------
i'm sure from sheet1$ is exist in file a.xls
123
|
|
|
|
|
hi 123,
excle file should have sheet with name "sheet1" i.e. excluding $ sign.
Regards
KP
|
|
|
|
|
thanks , i need open file excell from network 82.99.99.1
123
|
|
|
|
|
Hi,
Has anyone had any problems runnin the aspnet_regsql.exe tool with a SQL Server that is on a different server as your development PC?
When the tools ask me to enter the Server, I type in abc-def,1234. The 1234 is the name of the port. The when I click on the database drop down I get the following error:
Failed to query a list of database names from the SQL Server. Invalid object name 'sysdatabases'.
Why does it do this? I can open the database in SQL Server Eneterprise Manager for the 2000 database.
Regards
.NET Enthusiast
|
|
|
|
|
in stored procedure sql server 2000 , i am using the goto keyword to send the user to execute the particular line , Is this the right way to do so ?
I am saying this because , in general , we are told to stop using the goto keyword , as far as the memory consumption is concerned.
Yesterday is a canceled check. Tomorrow is a promissory note. Today is the ready cash. USE IT.
|
|
|
|
|
Sonia Gupta wrote: we are told to stop using the goto keyword , as far as the memory consumption is concerned.
I'm not sure why memory consumption should be an issue. The traditional arguments against using GOTO are outlined in this article[^]. What are you using GOTO for?
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
its better not to use goto statement. try to use some alternate to perform the functionality.
Do good and have good.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
