|
Hi, take a look at ADO.NET 2.0. In .NET 2.0 it is much easier to get schemas.
'A programmer is just a tool which converts caffeine into code'
|
|
|
|
|
I am Using VS C# 2005 But unable to get all meta dadata info
/************************************************
try
{
ref_con.Open();
DataTable dt1 = ref_con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
_dataset.Tables.Add(dt1);
DataTable dt2 = ref_con.GetOleDbSchemaTable(OleDbSchemaGuid.Foreign_Keys, new object[] { });
_dataset.Tables.Add(dt2);
DataTable dt3 = ref_con.GetOleDbSchemaTable(OleDbSchemaGuid.Primary_Keys, new object[] { });
_dataset.Tables.Add(dt3);
DataTable dt4 = ref_con.GetOleDbSchemaTable(OleDbSchemaGuid.Check_Constraints, new object[] { });
_dataset.Tables.Add(dt4);
DataTable dt5 = ref_con.GetOleDbSchemaTable(OleDbSchemaGuid.Provider_Types, new object[] { });
_dataset.Tables.Add(dt5);
DataTable dt6 = ref_con.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new object[] { });
_dataset.Tables.Add(dt6);
DataTable dt7 = ref_con.GetOleDbSchemaTable(OleDbSchemaGuid.Constraint_Column_Usage, new object[] { });
_dataset.Tables.Add(dt7);
DataTable dt8 = ref_con.GetOleDbSchemaTable(OleDbSchemaGuid.Indexes, new object[] { });
_dataset.Tables.Add(dt8);
_dbtables.GetTableList(dt1, dt2,_dataset);
_dbcolumns.GetColumnList(dt1, dt5, dt6, _dataset);
results = true;
}
catch (Exception excp)
{
System.Windows.Forms.MessageBox.Show(excp.Message,"Failed To Get MetaData",
System.Windows.Forms.MessageBoxButtons.OK,System.Windows.Forms.MessageBoxIcon.Error);
}
finally
{
if (!ref_con.State.Equals(ConnectionState.Closed))
ref_con.Close();
}
but still unable to get all info
try this using a table containing columns of AutoNumber,long
and see the datatable value-----Provider_Types.
there same number value for some(binary) data types
************************************************/
Know it share it that is correct knowledge (...kittu)
-- modified at 21:34 Saturday 5th November, 2005
|
|
|
|
|
I am using MSDE on my computer. I have application that uses SQL. The application and MSDE are on the same computer. My connecting string follows:
this.cnKennel.ConnectionString = "Data Source=(local)\\NetSDK;packet size=4096;integrated security=SSPI" + ";persist security info=False;initial catalog=kennel";
Because I will be installing this application on many computers I would like not to specify the computer name/server name. Can someone tell me how I can do this.
|
|
|
|
|
I guess I don't clearly understand the question.
If the MSDE will always be on the same computer as the application, then your current connection string will always work (provided MSDE installed with the instance name NetSDK).If MSDE is installed without an instancename, then just DataSource = (local) will always work on the same box.
If the MSDE will be on only one of many computers, then you have no choice but to specify the name of the server (or its IP address if using TCPIP network library) and the instance name (if installed as an instance).
If there will be multiple clients, you likely should read the server name part of the connection string from a configuration file for your app, so it can be changed without recompling.
Absolute faith corrupts as absolutely as absolute power
Eric Hoffer
All that is necessary for the triumph of evil is that good men do nothing.
Edmund Burke
|
|
|
|
|
Thank you much, that was it!!!
|
|
|
|
|
Hello
I want to get data from MSSql sample database: pubs
I registered it in ODBC as sqlMY
(i use dialogWindow, without database support)
I do:
CDatabase *dat;
CRecordset *rec;
dat=new CDatabase;
dat->Open( "sqlMY", FALSE, FALSE, "ODBC;", TRUE );
new CRecordset(dat);
rec->Open(AFX_DB_USE_DEFAULT_TYPE, "select * from authors", CRecordset::none
);
And it shows error messege in rec->Open(...) , error is about SQLFetchScroll/SQLExtendedFetch
1. What i do wrong?
2. How can i access to my row data in CRecordSet (shouldi do CMyRecordSet() derived from CRecordSet and add there my data column as variables)?
thanks
Best Greetings
Michal
|
|
|
|
|
ok, i found, but....
<quote>virtual BOOL Open( UINT nOpenType = AFX_DB_USE_DEFAULT_TYPE, LPCTSTR lpszSQL = NULL, DWORD dwOptions = none );
(...)
lpszSQL
A string pointer containing one of the following:
A NULL pointer.
The name of a table.
An SQL SELECT statement (optionally with an SQL WHERE or ORDER BY clause).
A CALL statement specifying the name of a predefined query (stored procedure). Be careful that you do not insert whitespace between the curly brace and the CALL keyword.
even if i do
rec->Open(AFX_DB_USE_DEFAULT_TYPE, "authors",CRecordset::none );
it still have error
df
|
|
|
|
|
I have this provedure that it long so i have pasted the first and last part of the procedure.
Alter PROCEDURE outputexport
@Key varchar(20) = null
,@export int = 1
AS
BEGIN
CREATE TABLE test
/*Create the table for data*/
.
.
.
SELECT * FROM test
if @export = 1
begin
exec filetransferto 1, '"G:/export"', 'output', 'txt', 1, 0, @TableName='test'
exec filetransferto 1, '"G:/export"', 'output', 'txt', 1, 0, @TableName='test'
end
else select * from test
drop table #QRMDump
END
GO
I am trying to put this in such as that i can run from different server but the file will go to the same location that shown in('"G:/export"')
I am trying to declare @cmd that will do this
declare @cmd varchar(128)
set @cmd = (select @@ServerName) + '"G:/export"'
and calling the
exec filetransferto 1, @cmd, 'output', 'txt', 1, 0, @TableName='test'
but it give invalid file path error.
hope someone can give me answer!!
thanks
|
|
|
|
|
1. @@Servername is always going to give you the name of the server on which the SQL Server resides, regardless of which network client is used to execute the sp.
2. "G:/Export" always refers to a drive that is local to the SQL server (either a physical drive letter or a mapped drive). Prifixing it with the servername acomplishes nothing, since as the error messge states, that is an invalid way to specify a path.
3. You might be able to specify a UNC name: //SomeComputerName/somesharename/export,
but you would need to
a. share the destination drive (Say as Exports)
b. provide the sharing source comuter name either as a parametr value, or 'hard coded' into the SP. For example, if the destination computer is named "FileServer1",
and it shares the exports directory as Export_Share, then you would replace "G:\export" with "//FileServer1/Export_Share"
Absolute faith corrupts as absolutely as absolute power
Eric Hoffer
All that is necessary for the triumph of evil is that good men do nothing.
Edmund Burke
|
|
|
|
|
I have this provedure that it long so i have pasted the first and last part of the procedure.
Alter PROCEDURE outputexport
@Key varchar(20) = null
,@export int = 1
AS
BEGIN
CREATE TABLE test
/*Create the table for data*/
.
.
.
SELECT * FROM test
if @export = 1
begin
exec filetransferto 1, '"G:/export"', 'output', 'txt', 1, 0, @TableName='test'
exec filetransferto 1, '"G:/export"', 'output', 'txt', 1, 0, @TableName='test'
end
else select * from test
drop table #QRMDump
END
GO
I am trying to put this in such as that i can run from different server but the file will go to the same location that shown in('"G:/export"')
I am trying to declare @cmd that will do this
declare @cmd varchar(128)
set @cmd = (select @@ServerName) + '"G:/export"'
and calling the
exec filetransferto 1, @cmd, 'output', 'txt', 1, 0, @TableName='test'
but it give invalid file path error.
hope someone can give me answer!!
thanks
|
|
|
|
|
Hai,
I am having a table(Suppose Table1) with some data. After wards I have made some changes in the table so that the primary key changed and added some more columns(some references also). How I can write the script to modify the table with this change with out dropping the table. Because I need the data saved in the table. How I can write the upgradable script for that.
Please show me the right way. Thanks very much.
Thank You,
Rahul.
"Learn from Mistakes for suceess otherwise get buried with the Mistakes"
|
|
|
|
|
You could use ALTER TABLE[^]
My: Blog | Photos
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious
|
|
|
|
|
May be it's late now... but in Enterprise Manager, when you enter in 'design table', after you make the changes, there's a button in the tool bar that says Save Change Scripts, that will save a script that'll do exactly what you are asking for  . So you can run this script in another server for example. The script alters the table, it doesn't drop and remake it.
daniero
|
|
|
|
|
daniero wrote: The script alters the table, it doesn't drop and remake it.
Not necessarily. Depends on what you are doing. Sometimes this script will copy the data to a temp table, drop the existing table and recreate it, then rewrite the data back into it. Alot depends on what you are modifying, and whether or not default values can be used. Otherwise...great suggestion....letting sql server create the script is the best thing to do.
|
|
|
|
|
hellow to all ..
i have a tabels with data , i have field that have dates ...
how can i write an sql statment that pick the first date from all the dates that the tabel have ?
by first i mean the smaliest date ..
thxx
|
|
|
|
|
SELECT MIN(dateColumn) FROM MyTable
My: Blog | Photos
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious
|
|
|
|
|
|
Hello,
I have a datagrid which l want to save the contents to the database. I am not sure how to do this. Would l have to save one row at a time, or can l save everything and at once. I am using C# with SQL Server 2000. I normally write stored procedures for database operations. But with the datagrid I am not sure how this is done.
Some code examples would be very grateful.
Thanks in advance,
Steve
|
|
|
|
|
Hi all,
How can i make the change to user the @@servername variable so when I run the out of a different server I don’t have to change the code. I want to make change so I don't have to put the server name (such as @@servername = testserver) how can i define that?
CREATE FUNCTION fIsProduction ()
returns bit
AS
begin
declare @Prod bit
SELECT @Prod =case when @@servername = 'testServer'
then 1 else 0 end
return @Prod
end
|
|
|
|
|
Why not put it in a configuration file that you read at startup?
Absolute faith corrupts as absolutely as absolute power
Eric Hoffer
All that is necessary for the triumph of evil is that good men do nothing.
Edmund Burke
|
|
|
|
|
Does anyone have any horror stories (or success stories) about moving a number of SQL Server 2000 databases over to SQL Server 2005? I couldn't see any such articles on the MSDN site, but specifically I want to know what needs to be looked out for.
|
|
|
|
|
I would like to show the varchar2 size in a information display and am simply not finding where to get the value. I assume it is in the column properties. Any suggestions? I am using the OracleClient/OracleDataReader in ODP version 9i to read the DataTypeName and the display is in a C# app.
Thanks
"Simplicity is more complicated than you think. But it’s well worth it” (Ron Jeffries)
|
|
|
|
|
You can get the info from the user_tab_columns or the all_tab_columns view
the sql would look like:
select column_name, data_type, data_length
from user_tab_columns
where table_name = 'YOUR_TABLE_NAME'
or if it is not your schema but you have permissions to it....
select column_name, data_type, data_length
from all_tab_columns
where table_name = 'YOUR_TABLE_NAME'
and owner = 'SCHEMA_OWNER'
remember the SCHEMA_OWNER and YOUR_TABLE_NAME are case-sensitive searches and should be ALL CAPS.
hth
Al
|
|
|
|
|
Al Ortega wrote: from user_tab_columns
Thanks, Just what I was missing!
"Simplicity is more complicated than you think. But it’s well worth it” (Ron Jeffries)
|
|
|
|
|
Hello friends,
I want to create 1 stored procedure in Oracle. I have written it & when trying to execute, its giving some error message...i am using Ora 8i.
While creating SP, Oracle gave me error msg :- "Warning: Procedure created with compilation errors."
Will anybody please help me in solving the issue??? Here I am giving that SP...
Looking forword to see your reply..  Thanks in advance...
Regards,
Rohan
CREATE OR REPLACE PROCEDURE usp_Security_IsAuthorisedUser
(strUserName IN VARCHAR2(40),
strPassword IN VARCHAR2(16),
blnIsValidUser OUT BOOLEAN,
strInfo OUT VARCHAR(10))
AS
BEGIN
DECLARE intCount NUMBER;
intCount := 0;
SELECT COUNT(*) INTO intCount
FROM USERS
WHERE UserID = strUserName AND Password = strPassword;
--If count = 1 then only user information is correct
IF intCount = 1 THEN
BEGIN
blnIsValidUser := TRUE;
intCount := 0;
strInfo := 'SP';
SELECT COUNT(*) INTO intCount
FROM UnitRights
WHERE HoU = strUserName;
IF intCount = 1 THEN
strInfo := 'UH';
EXIT;
ELSE
SELECT COUNT(*) INTO intCount
FROM GENCODES
WHERE FLD_NAME = 'MARKET_REG' AND UDF_ST3 = strUserName;
--If count = 1 then this is Region Manager
IF intCount = 1 THEN
strInfo := 'RM';
EXIT;
END IF
END IF
END;
ELSE
BEGIN
blnIsValidUser := FALSE;
SELECT COUNT(*) INTO intCount
FROM USERS
WHERE CODE = strUserName;
IF intCount = 1 THEN
strInfo := 'PI';
ELSE
strInfo := 'UU';
END IF
END;
END IF
END;
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
