Return All Databases Names Using Query?
Hi, I am trying to get all the databases from MS SQL into a dropdownlist. However, I am not sure how to do that in query ... is there a way to retrieve all databases name using a query in MS SQL? Thanks.
View Complete Forum Thread with Replies
Related Forum Messages:
Return Field Names
Howdy all,I'm wishing to write a stored proc to return only the field names from a table. What I've tried gets the field names but also returns all of the data in each row. I only want the field names. Is this possible?Thanks!JP
View Replies !
Return Table Names - How?
In a stored procedure returning several tables how can I assign a name to each table. I wish to select the table in my app using DataSet.Tables("MyTable") rather than DataSet.Tables(0) Create Procedure myProc As Select * from table1 Select * from table2 Select * from table3 Go
View Replies !
Return Tables That Have Certain Column Names
Hi all, From the INFORMATION_SCHEMA.TABLES view I want to return the TABLE_NAME of tables that have columns say, named Email and EmailStatusId. Is it possible to do this with a single select statement or would I have to use two selects for this? Please advise. Thanks in advance.
View Replies !
Using Qualified Names Of Databases
I have databases a1 , a2 , a3 , a4 ... an. Each has a table t1 . I am writing a stored proc which compares the t1 table between any given two databases. I want to pass the database name as a variable .. so the SPROC looks like exec sp_comparetable 'a3' , 'a7' My question is , inside the SPROC ,how do I refer to the tables in the a3 and a7 databases. Thanks, Alok.
View Replies !
Return Column Names Where Values Were Not Found [stored Procedure]
I need to check whether procedure found any matches or not. If not it has to return the column name where matching value was not found. For example, if there was no record found in the table "Addresses" column "customer" with the value @username, it should return "street". If id with value @prod_id was not found in the table "Products", the "productname" must be returned as well. CREATE PROC sp_test @id INT, @username VARCHAR(50), @prod_id INT AS SELECT name FROM Customers WHERE id=@id SELECT street FROM Addresses WHERE customer=@username SELECT productname FROM Products WHERE id=@prod_id It is kind of check, which has to find out if users have inserted all the necessary values or not. Thanks for any advice.
View Replies !
How To Get The List Of Names Of The Databases Of A Server Using OleDbConnection In C#.......?
I want to get the names of the databases of the server using OleDbConnection in c# which i have to add in to a combobox.....the same task i achieved using SqlConnection where in there is a Class SqlClientCollectionNames...the code is as followsusing (SqlConnection connection =new SqlConnection("Data Source=server;User ID=username;Password=passwd")) { connection.Open(); DataTable myData = connection.GetSchema(SqlClientMetaDataCollectionNames.Databases); foreach (DataRow row in myData.Rows) comboBox1.Items.Add(row[0]); connection.Close();} but the same i did for OleDbConnection it is not working using (OleDbConnection connection =new OleDbConnection("Provider=SQLOLEDB;Data Source=server;User ID=username;Password=passwd")) { connection.Open(); DataTable myData = connection.GetSchema(SqlClientMetaDataCollectionNames.Databases);//no such method Databases present in OleDb namespace foreach (DataRow row in myData.Rows) comboBox1.Items.Add(row[0]); connection.Close(); } is there any other way to do this using OleDbConnection........??Please help
View Replies !
SQL Query Help With Names
hi i have one field in employee table where the field name is employeename where firstname + ' ' + lastname is stored in that field how can i break them in 2 pieces so that i can store in 2 different fields and some reacords has got firstname+ ' ' + middlename+ ' '+ lastname and i want to store them in temp tables how can i do that.
View Replies !
Get Table Names From Query
Using VB5|6/ADO/MSSQL6.5. I want to get the names of tables in a query. I.e. if query is "Select Name, Title from Employee inner join job on employee.empid = job.empid" I want a way to get "Name, Title" In VB 3 we used the VBSQL.VBX which had 2 functions for this: SQLTabCount and SQLTabName. Thanks in advance DP
View Replies !
Retrieving User Names Through SQL Query
I am trying to retrieve the user names of all users within a database. The problem is, that I have only created a login within SQL 7.0 for the NT group that the users belong to. As such, when I try to query the syslogins table I only get the NT group. Is there any way of retrieving the users that belong to this NT group. Any help or suggestions on this would be greatly appreciated. Thanks, Karl
View Replies !
Return Result From One Query As A Column In Other Query
I'm having a bit of a trouble explaining what I'm trying to do here. I have 3 "source" tables and a "connecting" table that I'm going to use tblContacts - with contactID, ContactName etc tblGroups - with GroupID, GroupName tblSubGroups - with SubGroupID, GroupID and SubGroupName (groupID is the ID for the parent Group from tblGroups) They are related in a table called tblContactsGroupConnection - with ContactID, GroupID and SubGroupID One contact can be related to many subgroups. What I want is a list of all contacts, with their IDs, names and what groups they are related to: ContactID, ContactName, [SubGroupName1, SubGroupName2, SubGroupName3] ContactID, ContactName, [SubGroupName1, SubGroupName3] ContactID, ContactName, [SubGroupName3] I'm sure there's a simple solution to this, but I can't find it. Any help appreciated. :) Kirikiri
View Replies !
Two Different Tables, Each With Identical Column Names... Query Help
I have two different tables... one for all Staff, and another for all Temp Staff. I need both to output to a datagrid, and so I need to grab both tables from a SQL query to output to my datagrid, but I can't seem to get the logic right for it to work. Can someone give me some suggestions on why my results are blank when I'm running this query? I thought a simple join would allow both sets of identical column names to coexist in peace...SELECT TOP 100 PERCENT dbo.StaffDirectory.UserName, dbo.StaffDirectory.LastName, dbo.StaffDirectory.FirstName, dbo.StaffDirectory.Dept, dbo.StaffDirectory.Title, dbo.StaffDirectory.EMail, dbo.StaffDirectory.LocationFROM dbo.StaffDirectory INNER JOIN dbo.TempStaff ON dbo.StaffDirectory.Location = dbo.TempStaff.Location AND dbo.StaffDirectory.EMail = dbo.TempStaff.Email AND dbo.StaffDirectory.Title = dbo.TempStaff.Title AND dbo.StaffDirectory.Dept = dbo.TempStaff.Dept AND dbo.StaffDirectory.FirstName = dbo.TempStaff.FName AND dbo.StaffDirectory.LastName = dbo.TempStaff.LName AND dbo.StaffDirectory.UserName = dbo.TempStaff.UName AND dbo.StaffDirectory.MDNo = dbo.TempStaff.MDNoIs something wrong here? It just doesn't work =(Any suggestions would be really appreciated.Thank you
View Replies !
A Strange Problem With SQL Query Fro Getting Field Names
Hello All, I have been trying to get this code work, but I could not. Every thing seems going well. However, The result of running the sql query is strange. It shows the field names twice. Eg:) if you have a table called "newtable" that has two fields[Custnumber, Custname], you will get somthing like this [Custnumber, Custname Custnumber, Custname]. I have tried many times, but I couldn't fix it. Sub Page_Load(sender As Object, e As EventArgs) handles Mybase.Load if not page.Ispostback then try Sqlconnection = New Sqlconnection (connectionString) querystring = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNs WHERE TABLE_NAME = 'Newtable'" SqlCommand = New SqlCommand(queryString, Sqlconnection) SqlConnection.Open dataReader = SqlCommand.ExecuteReader(CommandBehavior.CloseConnection) while dataReader.Read() Tablefields_txt.text += dataReader.Getstring(0) & ", " End while catch ex as Exception msgbox("An error has occured: " + ex.Message,0, "Error Message") finally SqlConnection.Close() End try End if Any help , please
View Replies !
Query Returning Duplicate Field Names
I have a .NET program that can connect to either an Access 97 database or anSQL Server 7 database. In the database I have two tables which have a fieldcalled ID. When I run a query like "SELECT A.*, B.* FROM A, B", the queryreturns those fields as "A.ID" and "B.ID" when connected to Access 97, butas "ID" and "ID" in SQL Server 7. Is there anyway to get SQL Server toprepend the table name to the field name in a case like this and not returnduplicate field names like that without having to specify aliases for thefields?- Don
View Replies !
Are Duplicate Instance Names A Problem If Physical Server Names Are Different?
If I restore the master database from ServerA to ServerB and start the SQL Server services on ServerA and ServerB, the master databases will be identical and running sp_helpserver and @@ServerName will indicate the SQL Server instance for both instances is ServerA. Is this ok considering the physical server names are different? If this is ok, how does SQL Server advertise on the network who it is? Does it append the physical server name to the SQL Server instance name during network communications? Thanks, Dave
View Replies !
Regarding The Query To Save The Ids Of The Edited Names From Different Tables Into A Single Table.
Here with the below query iam binding my gridview with industry name,company name,Plant Name,Group Name related to the IDs in Audit table.Select Aud.Ad_ID_PK,Aud.Audit_Name,Ind.Industry_Name,Cmp.Company_Name,Pla.Plant_Name,Gr.Groups_Name,Aud.Audit_Started_On,Aud.Audit_Scheduledto,Aud.Audit_Created_On from Industry Ind, Company Cmp, Plant Pla, Groups Gr, Audits Audwhere Ind.Ind_Id_PK =Aud.Audit_Industry and Cmp.Cmp_ID_PK =Aud.Audit_Company and Pla.Pl_ID_PK =Aud.Audit_Plant and Gr.G_ID_PK =Aud.Audit_Group and Ad_ID_PK in (select Ad_ID_PK from Audits) Now i want to edit these names. when i click on edit in gridview these names will be filled into textboxes and when i change the names it should compare the name with particular tables and should get the Id of that and store in Audits table. For example: i have this data in my audits table: Commercial83312 2 2 2 1 Here Commercial83312 is ID of that Audit and 2,2,2,1 are the Industry,Company,Plant and group Ids for that particular audit.In the front end i can see the names of this particular IDs. when i edit the industry name in the UI it must check the name with industry table and get the ID of the changed name and store it in audit table. so the data may be changed in audits table as : Commercial83312 4 2 2 1 so here the industry ID is changed I need the stored procedure for this. please help me,its very urgent...
View Replies !
How To Retrieve Query Results With Now Field Names, Dotted Line Or Footer
Hi folks,I'm trying to write a simple SELECT statement that will execute inquery analyser but will just have the data with no column names, or thedotted line between them and the data. I also want to avoid thestatement at the end which says nnn rows affected. any ideas? I want todo this because I intend to write the results to a flat file.Thanks for your helpDanny....
View Replies !
How To Make The SSMSE To Return Whole Records Without Any Close Query Form And Re-create Query Form Operation?
Hi, I got a problem. I installed Microsoft SQL Server Management Studio Express 2005 version. And I created a Compact database. I created an connection in SSMSE to connect the database and opened a query form. then, i run the following sql: Select * from Table1 It returned 3 records to me. After that, I used program to insert record into this table. Then i ran this sql again, it still show me 3 records. I closed the query form, and re-created a new query form, then run the sql, it returned 4 records to me. Why? It's very strange and difficult to operate, right? Is there anyone know how to make the SSMSE to return whole records without any close query form and re-create query form operation? Thanks a lot! And Merry X'max!!!
View Replies !
DB Mirroring: Different Server Names With Same Instance Names
I'm going to be setting up DB mirroring between two SQL Server 2005 boxes. Since these are on two different servers, can the instance names be the same? Is there any reason NOT to do so if the mirror server is going to be used exclusively for DB mirroring? For example: if the my primary DB is located on SERVER1INSTANCE1, can the mirror be SERVER2INSTANCE1 or do the instance names have to be different even though they're on different boxes. Thanks!
View Replies !
Return From A Query
Hi How can I know whether a query or a stored procedure is successfully executed? I mean like in delete case? here is an example sql_delete = "DELETE FROM UserData where U_ID='" & tempID & "'" rstIDChk.Open sql_delete, cnn, adOpenStatic, adLockOptimistic How can I make sure that the record is deleted so that I can proceed with other jobs? How can I catch it in the program like from VB? Is there any return like true or false in SQL? :( Tks alot..
View Replies !
Query Doesn't Return 0
Hi I have written a query for viewing the results of an on-line survey. I have three tables involved in this query: answers, answerpossibilities and users. So I use a few joins and made this query: ALTER PROCEDURE dbo.GeefAntwoordenMeerkeuze ( @question_id int ) AS SET NOCOUNT ON; SELECT answerpossibilities.answerpossibility_content AS[Answerpossiblity], COUNT(answers.answers_id) AS [Times chosen] FROM answers right OUTER JOIN answerpossibilities ON answers.answerpossibility_id = answerpossibilities.answerpossibility_id left join users on answers.user_id = users.user_id WHERE ((answerpossibilities.question_id = @question_id AND nswerpossibilities.answerpossibility_content!='-- choose answer --')) GROUP BY nswerpossibilities.answerpossibility_content ORDER BY [Times chosen] desc The above query works fine. The data returned by this query is shown in a gridview. When an answerpossibilty was never chosen it shows 0 as times chosen. So that's fine. But the problem is, only answers of users who completed the survey should be shown. In the users table there's a field user_completed. So the query should check whether this field is 1 (true). ALTER PROCEDURE dbo.GeefAntwoordenMeerkeuze ( @question_id int ) AS SET NOCOUNT ON; SELECT answerpossibilities.answerpossibility_content AS[Answerpossiblity], COUNT(answers.answers_id) AS [Times chosen] FROM answers right OUTER JOIN answerpossibilities ON answers.answerpossibility_id = answerpossibilities.answerpossibility_id left join users on answers.user_id = users.user_id WHERE ((answerpossibilities.question_id = @question_id AND nswerpossibilities.answerpossibility_content!='-- choose answer --') and users.user_completed = 1) GROUP BY nswerpossibilities.answerpossibility_content ORDER BY [Times chosen] desc Using this query only answers of users who completed the survey are shown but answer possibilities that were never chosen are no longer shown with 0 as times chosen. The gridview simply doesn't show them anymore. Thanks for helping me! Something went wrong by posting this message I guess, all blank lines were gone.. maybe because I used Safari on my iMac
View Replies !
Need Help Getting A Return Value In SQL Query Analyzer
This has got to be a simple one, but I could not find out how I have this simple proc for test purposes only... ALTER PROCEDURE dbo.RotoTest( @strSSN VARCHAR(11), @blnUseACHDate BIT = 0, @intInvestorId int = 0)ASreturn 5 Thats right it does nothing but return 5 and thats because I removed all the real code to simplify my question. When I hit this proc in SQL Query Analyzer.. exec RotoTest '123-45-6789', 0, 1 No return value is displayed in Query Analyzer. When I try something like this exec RotoTest '123-45-6789', 0, 1, intRetValue Output (in this situation I get wrong number of arguments) How can I get a return value displayed on my screen in SQL QUERY ANALYZER with out modifying the proc? I do not want to modify the proc in anyway (because I can't), I just want to display the return value
View Replies !
How To Return Value From Dynamic Query Or Set The Value
Hii I am Varun i have a problem with the dynamic stored procedure This is my stored procedureALTER PROCEDURE dbo.sp_TimeTableAdjustment1 (@TeacherID_OnLeave numeric(9), @DateFrom datetime ,@DateTo datetime , @UserID numeric(9) ) AS declare @flag as numeric(9) declare @year as varchar(4) set @year=(select batch from batchmaster where iscurrent=1 and isdeleted=0) if( @year=null or len(@year)=0) set @year = year(getdate())exec ('if not exists(select * from timetableadjustments_'+@year+' where datefrom='''+@datefrom+''' and dateto='''+@dateto+''' and teacherid_onleave='+@teacherid_onleave+') begin insert into TimeTableAdjustments_'+@year+' (TeacherID_OnLeave,DateFrom,DateTo,UserID) values ('+@TeacherID_OnLeave+','''+@DateFrom+''','''+@DateTo+''','+@UserID+'); end') else set @flag=(select timetableadjustmentid from timetableadjustments_2007 where datefrom=@datefrom and dateto=@dateto and teacherid_onleave=@teacherid_onleave) return @flag --exec('select timetableadjustmentid from timetableadjustments_'+@year+' where datefrom='''+@DateFrom+''' and dateto='''+@DateTo+''' and teacherid_onleave='+@TeacherID_OnLeave+' --') --return @flag --exec('@flag=select timetableadjustmentid from timetableadjustments_'+@year+' where datefrom='''+@DateFrom+''' and dateto='''+@DateTo+''' and teacherid_onleave='+@TeacherID_OnLeave+'')
View Replies !
Return A Value After Insert The Query
Hi! create table testReturn(id int identity(100,1),name varchar(10)) How can I return the value of identity column after inserting the value. Dim objConn As SqlConnection Dim SQLCmd As SqlClient.SqlCommand Dim ds As New DataSet Dim strsql As String Try objConn = New SqlConnection objConn.ConnectionString = _ "Network Library=DBMSSOCN;" & _ "Data Source=localhost;" & _ "Initial Catalog=mydb;" & _ "User ID=userid;" & _ "Password=pass" objConn.Open() strsql = "insert into testReturn values ('a')" SQLCmd = New SqlClient.SqlCommand(strsql, objConn) Dim rowsAffected As Integer = 0 rowsAffected = SQLCmd.ExecuteNonQuery Dim rv As String rv = SQLCmd.Parameters(0).Value.ToString() Response.Write(rv) Catch ex As Exception Response.Write(ex.ToString) End Try
View Replies !
Return Percentages In The Query.
Hello,I'm trying to something that just works in Oracle, but does not in SqlServer.How can I get the percentage of two counts to be returned on each rowof the query?select count(sid), /* all not the not null */count(*),(count(sid) /count(*) ) as percent_not_null,4 as four,(3/4) as three_over_fourfrom dbo.sysusers7082040Incredibly, it changes even 3/4 into a zero!For efficiency, I want the percentage returned in the query.And to not use variables and coding. Efficiency,both of the server, and of my time.Note: I am using dbo.sysusers as an EXAMPLE only. My realquery will be on user defined application tables.What is the solution please?
View Replies !
Query Help: Need To Return 2nd From Top Record
i need to retrieve the most recent timestamped records with uniquenames (see working query below)what i'm having trouble with is returning the next-most-recent records(records w/ id 1 and 3 in this example)i also need to return the 3rd most recent, 4th, 5th and 6th most recent- i figure if i can get the 2nd working, 3rd, 4th, etc will be cakethanks,brett-- create and populate tabledrop table atestcreate table atest(id int not null, name char(10), value char(10),timestamp datetime)insert into atest values (1,'a','2','1/1/2003')insert into atest values (2,'a','1','1/1/2004')insert into atest values (3,'b','2','1/1/2003')insert into atest values (4,'b','3','1/1/2002')insert into atest values (5,'b','1','1/1/2004')-- select most recent records with distinct "name"sselect a.* from atest as awhere a.id = (select top 1 b.id from atest as bwhere b.name = a.nameorder by timestamp desc )/*query results for above query (works like a charm)2a 1 2004-01-01 00:00:00.0005b 1 2004-01-01 00:00:00.000*/
View Replies !
MS Query To Return SQL Records
What I'm trying seems simple, although I'm fairly new to this. I want to return data from a SQL db(6.5) to an Excel spreadsheet via a Query thats going to have date parameters (originating from the spreadsheet). Basically, the User types in a Date range, run a Macro (I assume), which returns db records that are within that Date range. I can create the Query in MS Query, but how do I pass it parameters (Dates) and run it from Excel? Any help (esp. example) would be greatly appreciated.
View Replies !
How Many Rows Will A Query Return?
Does sql server have a mechanism (aside from count()) that for any given SELECT query will tell you only how many rows it will return without actually returning the data? The reason for this is that we have a generic lookup form in an application that is used on almost every screen (we have a lot of screens, so it gets a lot of different, sometimes complicted, queries passed to it to use for the lookup, and having to manually edit the query to use count over all the select clauses doesn't seem like the best way to handle this. If we could do a kind of 'trial run' against the server just to get the number of rows and use that to help set up the form, that would be ideal.
View Replies !
How Make MDX Query Return Something Rather Than Nothing?
Hi I have encountered some problems creating MDX query. There are two input parameters on report, both dropdown list and from query. The first parameter will check "State", and the second parameter "Store Name" depends on previous' result. If there's no "State" found, I manage to show a "No Data" in the "State" droplist. But with "No Data" in first parameter, the second parameter simply is not enabled. I want the second parameter shows " No Data" as well but not succeed. Here's code ... SELECT {[Measures].[A1], [Measures].[B1], [Measures].[C1]} ON COLUMNS , [Store].[Store Name].ALLMEMBERS ON ROWS FROM ( SELECT ( STRTOSET(@State) ) ON COLUMNS FROM [Cube]) ... The query need to look at dataset that contains [store name] on rows, and some measures on columns. It also restricted by parameter @State. Store Name that satisfies @State will be displayed. But if nothing from SELECT ( STRTOSET(@State) ) ON COLUMNS FROM [Cube], there's nothing in result. I'd rather like show something rather than nothing. I try codes like ... MEMBER [Store].[Store].[NA] AS '"N/A"' IIF(<Parameter empty>, (SELECT ... ON COLUMNS, [Store].[Store].[NA] ON ROWS FROM ...), (SELECT ...<Original statement >) ) ... It shows "Subselect support only Column axis". Any one has an idea? Please help me out Many thanks! Mr. L
View Replies !
Query That Return Nulls
This is my problem, i have 2 tables: one table that hold data, having id as identity, say table (id, content), the other table have 2 columns: (id, number). The second table number column refer to the id in the first table. i want to build a query that get the data from the first table that correspond to a specific id in the second table, not only this i want to get the previous, the current and the next item. For example: if table_data, table_info is first and second tables, something that can do it is : DECLARE @i int SELECT * FROM table_data WHERE id in (@i -1, @i +1, @i) The problem here if @i, @i+1 or @i-1 doesn't exist the column will not be returned, i want to get a result similar to id, content 25 null 26 null 27 #some content# or 34 #content1# 35 #content2# 36 #content3# so my problem is that nulls doesn't appear, i thought about using OUTER JOIN, but the problem is that outer join take tables, not (@i -1, @i +1, @i), so if only i can make somehow the outer join use these values, i think it works. Any help please, and thanks in advance
View Replies !
Using A Sub Query To Return Relational Data
Hi i am trying to use this query to pull all the publications stored in the database and all the authors contributing to that publication (1 to many relationship). I am trying to use a sub query so that i can display the results on one row of a gridview (including a consecutive list of all the authors). However i am recieving this error: Incorrect keyword near the word SET. ? Maybe i need to add a temp column in the sub query to pull all the related authors for a single publication - but i dont know the sql for this? can anyone help? Thanks SELECT ISNULL(Publication.month, '')+ ' ' + ISNULL(convert(nvarchar, Publication.year), '') as SingleColumn, Publication.publicationID, Publication.title FROM Publication WHERE Publication.publicationID IN (SELECT (convert(nvarchar, Authors.authorName)) FROM Authors INNER JOIN PublicationAuthors ON Authors.authorID = PublicationAuthors.authorID) AND Publication.typeID IN (SELECT PublicationType.typeName FROM PublicationType INNER JOIN PublicationType ON Publication.typeID = PublicationType.typeID
View Replies !
How Can I Force The Query To Return Values?
I want the following query to return a row even when table 'X' is empty. How would I do this? SELECT TOP 1 @Var1, @Var2, @Var3 from X The parameters @Var1, @Var2 and @Var3 are passed to the stored procedure in which the above query is included. When table is empty, it reurn nothing. It only return a row when table is not empty.
View Replies !
|
TRACKER
