Database

5-Mar-08 18:59

Hi all

How i can remove identity property of a column.

I will be graetful to you

Thanks

Sebastian

Vimalsoft(Pty) Ltd5-Mar-08 19:17

Vimalsoft(Pty) Ltd

5-Mar-08 19:17

If you are Using SQL 2005,

Go to your Table (Object Explorer) and select you table, Right Click and Click Design,after that, Go to the Column that has Identity and Click once or select it, Below you will see its Properties and look for a property name "Identity Specification" user "Table Designer" , Click the "+" to Expand and Under it you will see the property named "(Is Identity" and is selected "Yes", change it and set it to "NO" And Close the Designer and if it ask you to save the changes say "YES" Then your Identity will be gone.

Hope this Helps

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

Sebastian T Xavier5-Mar-08 19:34

5-Mar-08 19:34

Thanks for your quick reply, but i want to do this in run time, so should i get any query snippet for that

once again thanks

sebastian

Vimalsoft(Pty) Ltd5-Mar-08 19:37

Vimalsoft(Pty) Ltd

5-Mar-08 19:37

ok Try this

http://manuals.sybase.com/onlinebooks/group-as/asg1250e/sqlug/@Generic__BookTextView/23551;pt=23695[^]

Hope it Helps

Sebastian T Xavier5-Mar-08 19:40

5-Mar-08 19:40

ok,i am looking into it, thanks once again

sebastian

Khawar Abbas15-Mar-08 19:58

Khawar Abbas1

5-Mar-08 19:58

Mention database type(access/sql server2000 / 2005).

Do good and have good.

Sebastian T Xavier5-Mar-08 21:25

5-Mar-08 21:25

its sql server 2005

Thanks for your response

sebastian

Conditions

AlexeiXX35-Mar-08 18:55

AlexeiXX3

5-Mar-08 18:55

Hi all, thanks for looking

I need a storedprocedure that retrieves rows based on parameters
Example 1:
SELECT * FROM Users WHERE Name LIKE '%' + @str + '%'
This will return all rows that have the word @str in Name column
If i pass '' or '%' it will return all the rows

I need the same but for an int column
Example:
SELECT * FROM Users WHERE UserID = @UserID
If i pass a number, it will return a row of the user with UserID = @UserID
But if no parameter is passed it wont work
My question is
Is there any way i can make this last query to return all the rows if no @UsersID is passed

Ill be calling this SPs from ASP.NET

Im giving this examples, i know they might seem very easy, and you may say, make 2 SPs
But in fact, Ill have more than 5 diferent filters, all applied to the same table, and any combination of this parameters is valid (param 1 and param2, param 1 only, param 5 only, param 1 2 and 4, and so on), so if i implement this in separated SPs ill end up having more than 20 almost equal SPs

Any advice will be appeciated
Thanks in advance

Alexei Rodriguez

pmarfleet5-Mar-08 19:38

5-Mar-08 19:38

Google[^] is your friend...

Paul Marfleet

"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush

AlexeiXX36-Mar-08 8:16

AlexeiXX3

6-Mar-08 8:16

Hey, thanks for the link
I solved part of my problem with this:
select * from table where col1 = isnull(@param1,col1)
works great

But i have a little different scenario here
One of the parameters is for a function that returns a table, it accepts a string and splits it returning the values as one column rows, like:
select * from fnSplitString ('1,2,4,5,6') Will return:
Value
1
2
3
4
5
6

select * from table where col1 = isnull(@param1,col1)
and col2 in (select * from fnSplitString (@param2))

This works when i pass param2, but if param2 is null i need all the rows
Can this be done with a single query or do i have to make N queryes

I was gonna use for the function something like this:
select * from fnSplitString (isnull('1,2,4,5,6,','String with all posible values but seems inefficient and values can change'))

Any way to omit the second condition when @param2 is null (with single query and without SQl concatenation)?

Thanks in advance

Alexei Rodriguez

Khawar Abbas16-Mar-08 1:43

Khawar Abbas1

6-Mar-08 1:43

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.

Creating a Help Form... [modified]

AlexeiXX36-Mar-08 6:05

AlexeiXX3

6-Mar-08 6:05

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

Query Scenario

RadioButton5-Mar-08 9:31

RadioButton

5-Mar-08 9:31

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.

Re: Query Scenario

Michael Potter5-Mar-08 10:03

Michael Potter

5-Mar-08 10:03

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)

Re: Query Scenario

Ashfield5-Mar-08 21:31

Ashfield

5-Mar-08 21:31

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

new_phoenix5-Mar-08 9:17

new_phoenix

5-Mar-08 9:17

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

Wrong forum

pmarfleet5-Mar-08 10:11

Using ADO.Net to retrieve data from Excel 2007 - Cannot import more than 255 columns

5-Mar-08 10:11

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

Mehawitchi5-Mar-08 4:33

Mehawitchi

5-Mar-08 4:33

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()

Re: Using ADO.Net to retrieve data from Excel 2007 - Cannot import more than 255 columns

pmarfleet5-Mar-08 8:37

Re: Using ADO.Net to retrieve data from Excel 2007 - Cannot import more than 255 columns

5-Mar-08 8:37

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

Mehawitchi5-Mar-08 10:11

Mehawitchi

5-Mar-08 10:11

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

Re: Using ADO.Net to retrieve data from Excel 2007 - Cannot import more than 255 columns

pmarfleet5-Mar-08 21:38

Re: Using ADO.Net to retrieve data from Excel 2007 - Cannot import more than 255 columns

5-Mar-08 21:38

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... Smile | :)

Paul Marfleet

"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush

Mehawitchi7-Mar-08 9:49

Mehawitchi

7-Mar-08 9:49

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 Big Grin | :-D

, that I enforce the dataset to add the rows of the second upload into those of the first upload

Greatly appreciate your help

Re: Using ADO.Net to retrieve data from Excel 2007 - Cannot import more than 255 columns

jvandertol122-Jun-09 9:49

jvandertol1

22-Jun-09 9:49

Perhaps this might work better:

SELECT * from [MyNamedRange]

or

SELECT * from [Sheet1$A1:B10]

How to Execute a DTS Package within a Procedure

Vimalsoft(Pty) Ltd4-Mar-08 23:06

Vimalsoft(Pty) Ltd

4-Mar-08 23:06

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

Re: How to Execute a DTS Package within a Procedure

pmarfleet5-Mar-08 1:14