Database

8-Jul-06 1:55

Further to Ennis Ray Lynch, Jr.'s answer. You can put several SQL statements together to get the effect that you want:

DECLARE @sql nvarchar(4000);
DECLARE EventCursor CURSOR
FOR
SELECT 'SELECT * FROM ['+TABLE_SCHEMA+'].['+TABLE_NAME+'] WHERE eventNum= @eventNum' 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE COLUMN_NAME = 'EventNum'
OPEN EventCursor
FETCH NEXT FROM EventCursor INTO @sql
WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC sp_executesql @stmt = @sql,
         @params = N'@eventNum int',
         @eventNum = 4 -- Replace this number with the desired event number
                       -- or a parameter that represents it.
    FETCH NEXT FROM EventCursor INTO @sql
END
CLOSE EventCursor
DEALLOCATE EventCursor

Yes

Ennis Ray Lynch, Jr.7-Jul-06 12:36

Ennis Ray Lynch, Jr.

7-Jul-06 12:36

Don't listen to the naysayers. Using T-SQL you can. I do not have access to SQL Server atm so I will describe it and leave it to you. Select from the system objects table where name = Event Num making sure you only get column definitions. Select all table names from the system tables (it might be objects to) that contain the given column. Then you can use a T-SQL query to return multiple result sets each one using exec "select * from " table name" WHERE eventNum=" + whatever.

Sorry I am not in front of SQL Server or I would have given you the code.

A man said to the universe:
"Sir I exist!"
"However," replied the Universe, "The fact has not created in me A sense of obligation."

-- Stephen Crane

Re: Yes

Colin Angus Mackay8-Jul-06 1:47

Ado.net ODBC problem connecting to Progress database

8-Jul-06 1:47

Ennis Ray Lynch, Jr. wrote:
Don't listen to the naysayers. Using T-SQL you can

You are still doing what I said and SELECTing the tables individually. You are just automating the process slightly by building a string with the SQL in in and then executing that dyanmic SQL. There is no single command to do what the OP requested.

DECLARE @sql nvarchar(4000);
DECLARE EventCursor CURSOR
FOR
SELECT 'SELECT * FROM ['+TABLE_SCHEMA+'].['+TABLE_NAME+'] WHERE eventNum= @eventNum' 
FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = 'EventNum'
OPEN EventCursor
FETCH NEXT FROM EventCursor INTO @sql
WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC sp_executesql @stmt = @sql, 
        @params = N'@eventNum int', 
        @eventNum = 4 -- Replace this number with the desired event number 
                      -- or a parameter that represents it.
    FETCH NEXT FROM EventCursor INTO @sql
END
CLOSE EventCursor
DEALLOCATE EventCursor

Thanks for the code

Ennis Ray Lynch, Jr.8-Jul-06 7:30

Ennis Ray Lynch, Jr.

8-Jul-06 7:30

Still no manual discovery is required which I think is what he wanted.

A man said to the universe:
"Sir I exist!"
"However," replied the Universe, "The fact has not created in me A sense of obligation."

-- Stephen Crane

Re: Thanks for the code

VK-Cadec10-Jul-06 7:27

VK-Cadec

10-Jul-06 7:27

Thank you for the code - that worked well.

Do you happen to know how we code using CTE, Common Table Expression in SQL2005?

I tried something like :

With event_numbers(table_name, column_name) AS
(
select table_name, column_name from information_schema.columns where column_name='EventKey'
)

I get the table names but how would I select out the complete information for each table?

Thanks!

tanya foster7-Jul-06 8:01

tanya foster

7-Jul-06 8:01

Hello,
I am developing an vb.net application and trying to connect to a 9.1B Progress database (I already have read how Progress is not an easy database to connect to.) I have the Merant driver installed on the client and server for connecting to Progress.

When I try to connect to Progress through the system admin using Merant ODBC and the system DSN, it connects fine. (Start->Settings->Control Panel->Administrative Tools->Data Sources(ODBC)->System DSN->Configure) (on the Advanced tab in Configure, the Security Required is NOT selected)

So far, all the forum postings(multiple websites) I have seen for the error message

ERROR HY000 MERANT ODBC Progress driver insufficient information to connect to the data source

recommend the same fix yet I have tried the recommended fix and still get the error.

My connection string is

DRIVER={MERANT 3.70 32-BIT PROGRESS}; DSN=OURDSN; UID=" "; PWD=" "; database=ourdatabase;

The forums say the error is from not having a UID and PWD parameter in the connection string. However, I DO have each of these. I read that the ASP-engine will(incorrectly) not pass on the UID/PWD parameters when the UID/PWD variable values are emtpy. Workaround: pass on a non-empty UID/PWD string filled with blanks.

So I changed my original connectionstring from UID=; to UID=" "; and from PWD=; to PWD=" ";

No improvement.

anybody know the connection string for Progress database in .net using Merant 3.70 driver or how I can further determine what the insufficent information is referring to?

Thanks

Re: Ado.net ODBC problem connecting to Progress database

Jesal Rana7-Jul-06 19:33

Jesal Rana

7-Jul-06 19:33

Youb have written correct string but if you have SQL server name (e.g.'sa' PWD='' or PWD='sa' ) whatever u have given at the inststallation time that must be matched. Then u can easily connect to ur database. I think there's no need to write 'DRIVER={MERANT 3.70 32-BIT PROGRESS};' But I m not sure about this so check it.

Without that it is working but don't give null string to the UID or PWD field but gie as below.

DRIVER={MERANT 3.70 32-BIT PROGRESS}; DSN=OURDSN; UID=; PWD=; database=ourdatabase;

Bye,

Jesal Rana

OK

Re: Ado.net ODBC problem connecting to Progress database

tanya foster8-Jul-06 2:13

tanya foster

8-Jul-06 2:13

Jesal,
thank you so much for a response. I have posted this question on four different websites, including "the" Progress database defacto website with no responses. It is saturday morning and I am at home. I will follow up on your recommendations on Monday and respond.
By the way, have you had this same experience? do you have much experience with Progress databases?
thanks!

Re: Ado.net ODBC problem connecting to Progress database

tanya foster10-Jul-06 3:50

tanya foster

10-Jul-06 3:50

Jesal,
I am trying to connect to a Progress database within a vb.net or asp.net (either one will work for me) application. There is no Microsoft SQL Server involved.

If I don't have Driver={MERANT 3.70 32-Bit Progress};
within the connection string, the error messages are:

Found ObcException type error - ERROR [HYC00][MERANT] [ODBC PROGRESS driver]Optional feature not implemented

ERROR [HY000][MERANT] [ODBC PROGRESS driver] msgOpen: Unable to open message file: PROMSGS

ERROR [IM006][MERANT] [ODBC PROGRESS driver] SQLSetConnectAttr failed.

So clearly, I will get less messages if the connection string contains Driver={MERANT 3.70 32-Bit Progress};

Error In Attach Database

md_refay7-Jul-06 7:33

md_refay

7-Jul-06 7:33

i have sql server2000 i create datbase on it when i try to attach that database again on sql server this error appear
"error 602 "could not find row in sysindexes for database id 12 ,object id 1,index id 1.run dbcc checktable on sysindexes"
* what is the soulation to attach that database
* how i run checktable on datbase that are not connected to sqlserver
* when i try to connect that database to sql server2005 it's work
p;z answer me quickly
it's emeganecy

ma_refay

Re: Error In Attach Database

Colin Angus Mackay7-Jul-06 9:41

Re: Error In Attach Database

7-Jul-06 9:41

Okay - You created a database in SQL Server 2000. You detached it. [Something possibly happend at this point but it is not mentioned in your post]. You try to attach it to a SQL Server 2000 but it does not work. You try to attach it to SQL Server 2005 and it does work. The error message when attaching it to SQL Server 2000 is error 602 "could not find row in sysindexes for database id 12 ,object id 1,index id 1.run dbcc checktable on sysindexes

Questions:
Did you try what it said to do in the error message?
Did you, at any time, upgrade the database to run on SQL Server 2005?

md_refay8-Jul-06 1:34

md_refay

8-Jul-06 1:34

how i check the sysindexs table of database doesn't connected to sqlserver
i need to work with that database on sqlserver2000 not sqlserver 2005

ma_refay

Re: Error In Attach Database

Colin Angus Mackay8-Jul-06 1:51

Add a new IDENTITY [modified]

8-Jul-06 1:51

ma_refay wrote:
how i check the sysindexs table of database doesn't connected to sqlserver

Okay - fair enough. But you didn't answer my second question. Did you at any time upgrade the database to run on SQL Server 2005? Or to put it another way: Did you at any time connect your database to SQL Server 2005?

If the answer is yes, then I suspect that SQL Server 2005 upgraded the database for you. In which case the easiest thing to do is to go back to the most recent backup from when it was a SQL Server 2000 database. I'm not sure that the database can be downgraded once upgraded.

TheEagle7-Jul-06 7:07

7-Jul-06 7:07

How could I add a new record to a table (Using a stored procedure) that has only one column(ID column) that has the Identity property enabled with Seed=1 and Increment=1?

For example if the table has two columns where the second column is CustomerName we should use INSERT INTO to add only a value to the second column because the first column (ID column) will have a new value automatically because of the Identity properties mentioned above.

"I am too late but i will never give up"

-- modified at 13:48 Friday 7th July, 2006

Elina Blank7-Jul-06 8:11

7-Jul-06 8:11

In your stored proecdure write the following:
SET IDENTITY_INSERT [TableName] ON
BEFORE inserting new record

Sincerely,
Elina

Life is great!!!
Enjoy every moment of it! Blush | :O

TheEagle7-Jul-06 9:29

7-Jul-06 9:29

That is exactly what I needed but in the following code:

--The Identity column name: FieldNO
SET IDENTITY_INSERT [ProjectField] ON
INSERT INTO ProjectField_Locale (FieldNO,CultureID,FieldName,FieldDescription)
VALUES (@@Identity,dbo.GetCultureID(@CultureName),@FieldName,@FieldDescription)

The ProjectField_Locale has another ID column (FieldCultureID) with the same Identity properties(I didnt write its name in the INSERT INTO statement because it will be added automatically too) so which Identity value I will get from @@Identity(the one from [ProjectField] table or the new one in ProjectField_Locale table?).

"I am too late but i will never give up"

Elina Blank7-Jul-06 10:34

7-Jul-06 10:34

I have this from the msdn Help:
"You can set the identity properties on only one column per table"

So, how can you have two columns with the same Identity property?

Sincerely,
Elina

Life is great!!!
Enjoy every moment of it! Blush | :O

TheEagle7-Jul-06 22:37

7-Jul-06 22:37

I didn't mean in the same table.I meant FieldNO(Primary Key) in the first table(ProjectField) and FieldCultureID(Primary Key) in the second table(ProjectField_Locale).

The FieldNO in the second table has the Identity Property Disabled and it is a Foreign Key.

Sorry if I could't explain my question because it is complex.

"I am too late but i will never give up"

Elina Blank10-Jul-06 4:04

10-Jul-06 4:04

I think, it will be the identity of the ProjectField_Locale, but i am not 100% sure.
You can always test it with print statement.
Sorry, for not being able to help too much Frown | :(

Sincerely,
Elina

Life is great!!!
Enjoy every moment of it! Blush | :O

TheEagle11-Jul-06 1:36

11-Jul-06 1:36

I am the one who should say Sorry...

I didnt understand the statement :

SET IDENTITY_INSERT [table name] ON

But after viewing several articles through the internet I understood it and then I found a solution for my problem and it worked perfectly:

DECLARE @AA INT
DECLARE @bb INT
SET @AA=(SELECT MAX(FieldNO) FROM ProjectField)+1
SET IDENTITY_INSERT [ProjectField] ON
INSERT INTO ProjectField (FieldNO)
VALUES (@AA)
SET @BB=@@Identity

INSERT INTO ProjectField_Locale (FieldNO,CultureID,FieldName,FieldDescription)
VALUES (@BB,dbo.GetCultureID(@CultureName),@FieldName,@FieldDescription)

INSERT INTO ProjectField_Locale (FieldNO,CultureID,FieldName,FieldDescription)
VALUES (@BB,dbo.GetCultureID(@CultureName2),@FieldName2,@FieldDescription2)

Ofcourse your first respond helped me to go in the right way..Thank you very much..I hope that we meet again in another exciting conversation Smile | :)

"I am too late but i will never give up"

Elina Blank11-Jul-06 2:32