|
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
|
|
|
|
|
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
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
|
|
|
|
|
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
|
|
|
|
|
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!
|
|
|
|
|
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
|
|
|
|
|
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
|
|
|
|
|
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!
|
|
|
|
|
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};
|
|
|
|
|
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
|
|
|
|
|
|
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
|
|
|
|
|
|
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
|
|
|
|
|
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! 
|
|
|
|
|
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"
|
|
|
|
|
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!
|
|
|
|
|
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"
|
|
|
|
|
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 
Sincerely,
Elina
Life is great!!!
Enjoy every moment of it!
|
|
|
|
|
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
"I am too late but i will never give up"
|
|
|
|
|
|
I have a stored proc which populates a drop-down box in an ASP.NET datagrid. The user enters a part number and the datagrid populates based on that part number. The datagrid is already in edit mode using template columns.
What I would like to do on the SQL side if possible is the following:
a) If the part number does not exist, populate the drop-down box with all part types, and order by description. (Okay, this is no problem.)
b) If the part number DOES exist, populate the drop-down box with all part types, but list the saved description first, then list the rest by description.
This is what I have thus far:
CREATE PROCEDURE [dbo].[GetPartDescriptions]<br />
<br />
@PartNumber varchar (60)<br />
<br />
AS<br />
if NOT EXISTS (Select * from Parts where PartNumber = @PartNumber)<br />
BEGIN<br />
SELECT ID as ptID, Description from PartTypes pt ORDER by Description<br />
END<br />
<br />
GO
|
|
|
|
|
Sorry for not answering your question, but I have to ask you this question: Why would you have parts with the same part number? I would think that would be unique for each part type. No?
“Profanity is the attempt of a lazy and feeble mind to express itself forcefully”
|
|
|
|
|
Jerry Hammond wrote: Why would you have parts with the same part number
Parts is the table leckey is using and partnumber is the field.
Yes, the partnumber field ought to be unique for each part type.
|
|
|
|
|
Maybe I'm the confused one. Part b of your requirements seem to imply multiple parts (or part types) with the same part number.
“Profanity is the attempt of a lazy and feeble mind to express itself forcefully”
|
|
|
|
|
Sorry for wording my question poorly. The part number is indeed unique. What I am looking for is if the user enters a part number that already exists in the database, the dropdown list populates with the part type first, then the remaining part type choices alphabetically. If the part does not exist, just order alphabetically. I had a friend help me last week and got this:
CREATE PROCEDURE [dbo].[GetPartDescriptions2]<br />
<br />
@PartType varchar (60)<br />
<br />
AS<br />
<br />
SELECT PartTypes.ID, PartTypes.description,<br />
CASE when PartTypes.ID = @PartType then 1<br />
else PartTypes.ID<br />
end as order_by<br />
from PartTypes INNER JOIN parts p on p.PartTypeID = PartTypes.ID<br />
<br />
ORDER BY order_by<br />
<br />
But I was getting confused and the bolded parts are not right. I am passing in the part number so it should be something like this:<br />
<br />
<code>CREATE PROCEDURE [dbo].[GetPartDescriptions3] <br />
@PartNumber varchar (60)<br />
<br />
AS<br />
<br />
SELECT PartTypes.ID, PartTypes.description, Parts.PartNumber, Parts.PartTypeID<br />
CASE when PartTypes.ID = Parts.PartTypeID then 1<br />
else PartTypes.ID<br />
end as order_by<br />
from PartTypes INNER JOIN parts p on p.PartTypeID = PartTypes.ID<br />
<br />
ORDER BY order_by<br />
GO
But enterprise manager doesn't like that. Asked my friend and she sent me this:
You could get the Part Type by
Declare @PartTypeId int
select @PartTypeId = PartTypeId
from PartTypes where Partnumber = @PartNumber
I'm now having trouble figuring out where to stick this new select statement in the entire code.
GO
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
