Showing posts with label names. Show all posts
Showing posts with label names. Show all posts

Wednesday, March 21, 2012

Identity sql servers on a web application - how

Hi,

What I want to do is simple to explain, hard to obtain (i guess):

I want to fill a dropdownlist with the names of the sqlservers detected on the system. I′ve installed a sw recently that prompts for that, and when you select that server, then it prompts you to insert username and password, and then appears a dropdownlist again with the names of the databases of that sqlserver.

Is this possible to do, at least fill dropdownlists with sqlservers and database names?!?I don't see how, thanks!

I don't know about getting a list of servers, but to get a list of the databases you can just use the sql server profiler to "spy" on that application to get the query it used to find all the databases in that instance...|||

Hi dvd,

try this

DataTable servers = ((DbDataSourceEnumerator)SqlClientFactory.Instance.CreateDataSourceEnumerator()).GetDataSources();

servers.Columns.Add(newDataColumn("FullServerName",typeof(String),"ServerName + ISNULL(IIF(InstanceName <> \'\', (\'\\\' + InstanceName), InstanceName), \'\') "));

this.ddlServer.ValueMember ="FullServerName";

this.ddlServer.DisplayMember ="FullServerName";

the server table will contains the server and instance installed on each server, after you can bind to whatever you need.

hope this help
|||

kpeguero@.hotmail.com:

Hi dvd,

try this

DataTable servers = ((DbDataSourceEnumerator)SqlClientFactory.Instance.CreateDataSourceEnumerator()).GetDataSources();

servers.Columns.Add(newDataColumn("FullServerName",typeof(String),"ServerName + ISNULL(IIF(InstanceName <> \'\', (\'\\\' + InstanceName), InstanceName), \'\') "));

this.ddlServer.ValueMember ="FullServerName";

this.ddlServer.DisplayMember ="FullServerName";

the server table will contains the server and instance installed on each server, after you can bind to whatever you need.

hope this help

Ok so I've done this:

When you click a button, it fills a dropdownlist with the instances of the sqlservers available:

protected void Button1_Click(object sender, EventArgs e)
{

DbProviderFactory dbpf = SqlClientFactory.Instance;

DbDataSourceEnumerator instancia = dbpf.CreateDataSourceEnumerator();
DataTable dt = instancia.GetDataSources();

if (dt.Rows.Count < 1)
ddlServer.Items.Add("No items");
else{
foreach (DataRow dr in dt.Rows)
{
ddlServer.Items.Add(dr["ServerName"].ToString());
}
}

}

However it gives always the "No items" issue, which means no sql server found...am I missing something?!?Thanks!

|||

I dont know how you can get the server name but if you have the server name you can get the rest of the info.

SELECT * from <servername>.master.dbo.sysdatabases.

|||

I've been trying hard on this, however, no success. Using the last code I've posted, the only thing I get is "No items". Am I missing something? Cde on my web.config, saying which is my provider, factory or something? Appreciate a little help, thanks a lot!

|||

No one?Really stuck on this!

Wednesday, March 7, 2012

Identity columns

Is there any simple query that returns identity column names in a database?
This will do it:
select * from syscolumns where columnproperty(id, name, 'IsIdentity') = 1
"John" <anonymous@.discussions.microsoft.com> wrote in message
news:993EA3EB-2BEB-4BEE-B6F4-C36BF550011C@.microsoft.com...
> Is there any simple query that returns identity column names in a
database?
|||John,
SELECT column_name AS 'Identity Column',table_name
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMNPROPERTY(OBJECT_ID(table_name),column_name,' IsIdentity') = 1
Dinesh
SQL Server MVP
--
SQL Server FAQ at
http://www.tkdinesh.com
"John" <anonymous@.discussions.microsoft.com> wrote in message
news:993EA3EB-2BEB-4BEE-B6F4-C36BF550011C@.microsoft.com...
> Is there any simple query that returns identity column names in a
database?

Identity columns

Is there any simple query that returns identity column names in a database?This will do it:
select * from syscolumns where columnproperty(id, name, 'IsIdentity') = 1
"John" <anonymous@.discussions.microsoft.com> wrote in message
news:993EA3EB-2BEB-4BEE-B6F4-C36BF550011C@.microsoft.com...
> Is there any simple query that returns identity column names in a
database?|||John,
SELECT column_name AS 'Identity Column',table_name
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMNPROPERTY(OBJECT_ID(table_name),col
umn_name,'IsIdentity') = 1
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"John" <anonymous@.discussions.microsoft.com> wrote in message
news:993EA3EB-2BEB-4BEE-B6F4-C36BF550011C@.microsoft.com...
> Is there any simple query that returns identity column names in a
database?

Identity columns

Is there any simple query that returns identity column names in a database?This will do it:
select * from syscolumns where columnproperty(id, name, 'IsIdentity') = 1
"John" <anonymous@.discussions.microsoft.com> wrote in message
news:993EA3EB-2BEB-4BEE-B6F4-C36BF550011C@.microsoft.com...
> Is there any simple query that returns identity column names in a
database?|||John,
SELECT column_name AS 'Identity Column',table_name
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMNPROPERTY(OBJECT_ID(table_name),column_name,'IsIdentity') = 1
--
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"John" <anonymous@.discussions.microsoft.com> wrote in message
news:993EA3EB-2BEB-4BEE-B6F4-C36BF550011C@.microsoft.com...
> Is there any simple query that returns identity column names in a
database?