Database

OleDb different DataBases

21-Jul-06 23:14

You will have to rewrite just about every stored proc as there is specific TSQL features that are not available or implimented different in Orcale.

As someone once said "This could take some time"

Look where you want to go not where you don't want to crash.

Bikers Bible

hamidreza_buddy21-Jul-06 6:56

hamidreza_buddy

21-Jul-06 6:56

Hi as you know with OleDB we can open many kind of DataBases.
i want to know is there any difference between SQL statements we send to darabases or all of them support support a single tyoe of SQL statements.
I wanted to Get Information of tables of Databases(something like "Server Explorer" of VS.NET which opens every Type of Databases and shows their "Tables" and "Queries" and "procedures" and "Functions". abd also Field Types)
can i do it with SQL statement? and if yes are they different for different databases?

Paul Conrad21-Jul-06 7:12

Paul Conrad

21-Jul-06 7:12

hamidreza_buddy wrote:
is there any difference between SQL statements we send to darabases or all of them support support a single tyoe of SQL statements

Yes and no. Most Structure Query Language statements are the same, but each database (MS SQL, MySQL, Oracle, Firebird, etc) have their own minor differences.

Frank Kerrigan21-Jul-06 12:44

21-Jul-06 12:44

Most support a industial standard called Sql-92 which will in theory work with any compliant SQL-92 database. But sadly in pratice it doesn't work like that and small quirks make problems porting sql code.

Look where you want to go not where you don't want to crash.

Bikers Bible

Colin Angus Mackay22-Jul-06 1:05

22-Jul-06 1:05

Frank Kerrigan wrote:
Most support a industial standard called Sql-92

And some, like SQL Server 2005, now support SQL-99

Scottish Developers events:
* .NET debugging, tracing and instrumentation by Duncan Edwards Jones and Code Coverage in .NET by Craig Murphy
* Developer Day Scotland: are you interested in speaking or attending?
My: Website | Blog

Frank Kerrigan22-Jul-06 7:33

22-Jul-06 7:33

I stand corrected Laugh | :laugh:

Look where you want to go not where you don't want to crash.

Bikers Bible

Colin Angus Mackay22-Jul-06 7:45

22-Jul-06 7:45

You were correct in the fist place. You said "most" use SQL-92. I just thought I'd add that "some" now use SQL-99. If I remember correctly, SQL-92 is a subset of SQL-99, so it could be said that everything that is based on SQL-99 is, by default, also based on SQL-92.

Frank Kerrigan22-Jul-06 8:29

max rows in a table of SQL 2000

22-Jul-06 8:29

I think SQL-92 relates to 1992 and SQL-99 relates to 1999. But I'm guessing

Look where you want to go not where you don't want to crash.

Bikers Bible

cdotpal21-Jul-06 5:54

cdotpal

21-Jul-06 5:54

can anyone tell me what is the maximum number of rows one can store in a table of MS SQL SERVER 2000.

Regards

KK Adams21-Jul-06 6:21

KK Adams

21-Jul-06 6:21

Per Microsoft's SQL 2000 Books OnLine (BOL) the number of rows are limited by available storage.

To find this in the reference, select index tab, type in rows, size option. Towards the bottom of the first table is this answer.

Regards,

KK Adams
Rock On!

Colin Angus Mackay21-Jul-06 12:13

21-Jul-06 12:13

In addition to what KK Adams said, you also have to take into account the primary key. Since the primary key must be unique you will always end up giving yourself a limitation based on that. For example, the most common primary key is an int. There are just over 4 billion possible values that can be stored in an int therefore a table with an int for a primary key will be restricted to just over 4 billion rows.

If you were to use something else, such as a tinyint, you would be restricted to the range of that type. In the case of a tinyint it would be 256.

cdotpal22-Jul-06 6:17

cdotpal

22-Jul-06 6:17

Colin , Adam thanks a lot for the important information.
BTW the primary key in my table is of integer type.. so I expect it would be over 11 billion or so... more than enough Smile | :)

Colin Angus Mackay22-Jul-06 6:50

22-Jul-06 6:50

cdotpal wrote:
BTW the primary key in my table is of integer type.. so I expect it would be over 11 billion or so

No, it would be ~4 billion

Frank Kerrigan22-Jul-06 7:57

22-Jul-06 7:57

From SQL Books On Line

integer or int
Has a length of 4 bytes, and stores numbers from -2,147,483,648 through 2,147,483,647.

Correct me if I'm wrong; for an integer indentity column you'd only get 2 Billion rows as it'll only use the positive side of the Integer datatype number as its from -2 Billion to +2 Billion

I was going to get an int table and fill it with more than 2 billion rows, but that seemed rather sad just to prove my point.

Look where you want to go not where you don't want to crash.

Bikers Bible

Colin Angus Mackay22-Jul-06 8:59

22-Jul-06 8:59

Frank Kerrigan wrote:
for an integer indentity column you'd only get 2 Billion rows as it'll only use the positive side of the Integer

By default yes, you are quite right. When the identity column reaches 2147483647 it will be unable to insert any new rows. If you attempt any more it will generate an error message: "Arithmetic overflow error converting IDENTITY to data type int"

However, there is nothing to stop you setting the identity seed to -2147483648 when the table is created. This will allow you to use the full ~4 billion range of an int.

Frank Kerrigan wrote:
I was going to get an int table and fill it with more than 2 billion rows, but that seemed rather sad just to prove my point

Yes, very sad indeed. Big Grin | :-D

Frank Kerrigan22-Jul-06 11:43

Trasfer file via DTS Package from FTP server to SFTP server

22-Jul-06 11:43

Colin Angus Mackay wrote:
Yes, very sad indeed.

lol......

Look where you want to go not where you don't want to crash.

Bikers Bible

SQL INNER JOIN Query

Brendan Vogt21-Jul-06 1:33

Brendan Vogt

21-Jul-06 1:33

Hi,

I am having difficulty with my INNER JOINS. Can someone please help me to sort this out?

I have a shipping table, a shipping in shipping type table, and a shipping type table.

Shipping (1) -- (M) ShippingInShippingType (M) -- (1) ShippingType

(1) -- (M) is one to many.

I am trying to get the shipping name from the Shipping table, together with the shipping type information. My SQL Statement looks like this:

SELECT
ShippingTypeID_PK,
ShippingTypeName,
ShippingTypeDescription
FROM
ShippingType
INNER JOIN ShippingInShippingTypes AS "ShippingInShippingTypes1"
ON ShippingType.ShippingTypeID_PK = ShippingInShippingTypes1.ShippingTypeID_FK
INNER JOIN ShippingInShippingTypes AS "ShippingInShippingTypes2"
ON Shipping.ShippingID_PK = ShippingInShippingTypes2.ShippingID_FK;

It complains:
Msg 4104, Level 16, State 1, Procedure sp_ShippingTypes_GetAllShippingTypes, Line 19
The multi-part identifier "Shipping.ShippingID_PK" could not be bound.

I don't understand what it means and how to fix it.

Please can someone help??

Regards,
ma se

Re: SQL INNER JOIN Query

Mairaaj Khan21-Jul-06 3:22

Mairaaj Khan

21-Jul-06 3:22

ma se wrote:
Shipping (1) -- (M) ShippingInShippingType (M) -- (1) ShippingType

(1) -- (M) is one to many.

I am trying to get the shipping name from the Shipping table, together with the shipping type information.

Try this From caluse with your query:

FROM
  ShippingType
    INNER JOIN ShippingInShippingTypes 
      ON ShippingType.ShippingTypeID_PK = ShippingInShippingTypes.ShippingTypeID_FK
    INNER JOIN Shipping 
      ON ShippingInShippingTypes.ShippingID_FK = Shipping.ShippingID_PK;

What i understand from your query is that ShippingInShippingType is a relationship between Shipping and ShippingType tables, and you want to retrieve information from the Shipping table.
If this is so then in my openinion the FROM clause should like that:

FROM Shipping INNER JOIN
 ShippingInShippingType ON Shipping.field = ShippingInShippingType.field INNER JOIN
ShippingType ON ShippingInShippingType.field = ShippingType.field

Regards

_____________________________
Success is not something to wait for, its something to work for.

nmandaliya21-Jul-06 0:09

nmandaliya

21-Jul-06 0:09

Hi To all,
I have application, in which one DTS package is copy one file from one server (FTP) to other server (FTP). Now on destination side, they are changing it to SFTP.
So my question in,
1)does sql server 2000 supports tranfer from FTP to SFTP ?
2) does sql server 2005 supports tranfer from FTP tp SFTP ?
3) What do we need to chage DTS so that we can transfer the file?
4) if SQL server not capable, do i need third party tool ?

Note. We have user name and password to access the SFTP server.

Bye and thanks in advance.

Re: Trasfer file via DTS Package from FTP server to SFTP server [modified]

mr_lasseter21-Jul-06 11:21

mr_lasseter

21-Jul-06 11:21

1)does sql server 2000 supports tranfer from FTP to SFTP ?
Not natively
2) does sql server 2005 supports tranfer from FTP tp SFTP ?
Not natively
3) What do we need to chage DTS so that we can transfer the file?
You could create an activeX control and use it in dts. Putty has a command line sftp client, but there are several sftp programs for windows. Try searching on google.

Mike Lasseter

-- modified at 17:21 Friday 21st July, 2006

Re: Trasfer file via DTS Package from FTP server to SFTP server

Frank Kerrigan21-Jul-06 13:04

Using LIKE with variable [modified]

21-Jul-06 13:04

Try and find a SFTP that supports command prompts so you can use batch files in DTS i.e. ftp.bat which can be run by DTS.

Look where you want to go not where you don't want to crash.

Bikers Bible

cyber-will20-Jul-06 22:09

cyber-will

20-Jul-06 22:09

Hi,

I am trying to perform a search with a search term sent to a stored procedure. However when I use this

<br />
WHERE <br />
    Column LIKE '%@SearchQueryVariable%' <br />

it doesn't work. This doesn't work either

<br />
WHERE <br />
    Column LIKE @SearchQueryVariable <br />

Any ideas on how this is done?

-- modified at 4:23 Friday 21st July, 2006

Solved :

<br />
WHERE <br />
    Column LIKE '%' + @Search + '%'<br />

Re: Using LIKE with variable

Frank Kerrigan21-Jul-06 2:22