I have several databases on the same server that have the same table structures within them. Call them
Server.A
Server.B
Server.C
...etc.
I have designed a report that works in one database, say "A". I would like to add a parameter box to the report giving the user the ability to select which database to run the report in. I'm not sure if this is possible.
There may be more complex ways to handle this, say, with dynamic SQL or by building a large UNION of select statements across the various databases. But I figured I might ask for a simple solution first. :-)
I have a dataset listing distinct values for items (like 1, D10, M4, etc.) The WHERE statement in my query refers to unit IN(@Unit). I then have 2 report parameters to select 1) a date (datetime); and 2) a multivalue parameter to select one or all of the "units". I would like the second parameter to default to "Select All". Can someone tell me how to do this? I'm sure this is a fairly simple thing but I am really struggling. The report parameter is set as multivalue; My "available values" is set to "from query" and refers to my "unit" dataset and the value and label fields are set to "unit" (only field I bring into this particular dataset). The "Default Values" section is set to "from query" , the dataset is set to "unit" and the value is set to "unit". I can preview the report and select a date but the list of units comes up with all boxes unchecked, including "Select All". Any help will be much appreciated. Thanks.
I have this semi-complex query that is selecting items from numerous tables residing on 2 different databases. So far the query works perfectly. Here is the problem: The user is given the option of selecting items based on whether a course is Completed (C) Incomplete (I) Passed (P) Failed (F) or (ALL). I am not really sure how to do the select all, the others I can do depending on the value...
Any thoughts??
Query:
sql Code:
Original - sql Code
ALTER PROCEDURE [dbo].[Sel_CourseActivityPerUser] ( @status varchar(25), @course varchar(100), @datesmalldatetime ) AS
I am using RS 2000. I have a multi select parameter where I can select multiple states by separating with a comma. I am trying to figure out how to incorporate an "All" parameter.
Query:
Select [name], city, state, zipcode From Golf inner join charlist_to_table(@State,Default)f on State = f.str
Function:
CREATE FUNCTION charlist_to_table (@list ntext, @delimiter nchar(1) = N',') RETURNS @tbl Table (listpos int IDENTITY(1, 1) NOT NULL, str varchar(4000), nstr nvarchar(2000)) AS BEGIN DECLARE @pos int, @textpos int, @chunklen smallint, @tmpstr nvarchar(4000), @leftover nvarchar(4000), @tmpval nvarchar(4000) SET @textpos = 1 SET @leftover = '' WHILE @textpos <= datalength(@list) / 2 BEGIN SET @chunklen = 4000 - datalength(@leftover) / 2 SET @tmpstr = @leftover + substring(@list, @textpos, @chunklen) SET @textpos = @textpos + @chunklen SET @pos = charindex(@delimiter, @tmpstr) WHILE @pos > 0 BEGIN SET @tmpval = ltrim(rtrim(left(@tmpstr, @pos - 1))) INSERT @tbl (str, nstr) VALUES(@tmpval, @tmpval) SET @tmpstr = substring(@tmpstr, @pos + 1, len(@tmpstr)) SET @pos = charindex(@delimiter, @tmpstr) END SET @leftover = @tmpstr END INSERT @tbl(str, nstr) VALUES (ltrim(rtrim(@leftover)), ltrim(rtrim(@leftover))) RETURN END GO
When passing the prameter values to a linked report, I want to set one of the parameter at the linked report to "Select All", this was added automatically to the parameter list when multivalue report parameter is used . How to set this "Select All" as a parameter or can it be done? Thanks
My environment:XP Home, VWD, SQLEXPRESS.A purely local setting, no network, no remote servers. I try to do a JOIN query between tables in the membership ASPNETDB.mdf and one table in a self created 3L_Daten.mdf. After dragging the tables into the Query Design window and connecting them VWD creates this query (here I added the control declaration): <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionStringAspNetDB %>" SelectCommand="SELECT aspnet_Users.UserName, aspnet_Membership.Password, aspnet_Membership.Email, aspnet_Membership.PasswordQuestion, aspnet_Membership.PasswordAnswer, aspnet_Membership.CreateDate, aspnet_Membership.LastLoginDate, aspnet_Roles.RoleName, [D:VISUAL STUDIO 2005WEBSITES3L_V1APP_DATA3L_DATEN.MDF].dbo.Personendaten.Age, [D:VISUAL STUDIO 2005WEBSITES3L_V1APP_DATA3L_DATEN.MDF].dbo.Personendaten.Sex, [D:VISUAL STUDIO 2005WEBSITES3L_V1APP_DATA3L_DATEN.MDF].dbo.Personendaten.Area FROM [D:VISUAL STUDIO 2005WEBSITES3L_V1APP_DATA3L_DATEN.MDF].dbo.Personendaten INNER JOIN aspnet_Users ON [D:VISUAL STUDIO 2005WEBSITES3L_V1APP_DATA3L_DATEN.MDF].dbo.Personendaten.User_ID = aspnet_Users.UserId LEFT OUTER JOIN aspnet_Roles INNER JOIN aspnet_UsersInRoles ON aspnet_Roles.RoleId = aspnet_UsersInRoles.RoleId ON aspnet_Users.UserId = aspnet_UsersInRoles.UserId LEFT OUTER JOIN aspnet_Membership ON aspnet_Users.UserId = aspnet_Membership.UserId"></asp:SqlDataSource> THIS WORKS, BUT: As you can see the database 3L_Daten.mdf is inserted with its full path, which is not feasible for deployment reasons. My question: How can I address both databases purely by their database names ? Both have been created within VWD and lie under App_Data. (I tried almost everything, I practiced with the SQL Server 2005 Management Studio Express Edition, I tried linked servers, all without success). Thank you for your consideration.
i need to create a rowset containing database names where a particualr table name exists. ie. in Oracle i would select owner from sys.all_tables where table_name='MYTABLE'
I know master.dbo.sysdatabases contains my owners, and in each database sysobjects would identify MYTABLE .. sp_tables only lists those in the current database, but i want to list all databases that contain MYTABLE
Hi I'm trying to select data from different databases, Actually databases have the same structure and tables but different data, so I want to mix data from specific tables of all databases. Any help would be great
I'm quite new to MS SQL and am having a few problems I hope someone here can help me with.
I've created a small web application that enables users to backup and restore databases easily.
There is one table that holds information about the backups that have been taken and one table that holds details about each database (name, location etc)
What I want to do is have a web interface that allows a user to link a new database (one that is already attached to SQL but) to the system by inputting it into the database details table.
Ideally this will be a drop down menu that shows the databases attached to the server that arnt: 1. Already in the backup system table 2. Not the system databases.
This will allow the user to easily select the database and give it a title (usually different from the local name of the database) for identification.
The database local name, and title would then be input into the database details table.
I'm thinking that I need to use a SELECT statement, but I'm not quite sure how I can restrict the results to databases that arnt already attached or already in the system.
Just wondering, in DTS, can I run a SELECT script that selects from 2 different databases (both on the same server)?
I can run this in Sql Query Analyzer, but in a DTS, it doesnt accept my database name prefixes:
SELECT a.something FROM DB_ONE.dbo.product a, DB_ONE.dbo.mp_brand b, DB_TWO.dbo.lk_pcat_cutover c WHERE a.PCat <> c.Pcat
(where DB_ONE and DB_TWO are the 2 different db names)
I have one connection to DB_ONE... does this mean I cant access DB_TWO when using this connection? I want to try and avoid using a temporay table for storing DB_TWO's data in DB_ONE... Is this possible?
I need assigning a group of users read, write, update (not delete) permission on all user databases. is there any way to do so instead of creating role on each database separately.
Hi,We are discussing possible implementation for sql2005 database(s). This database will serve one web portal. Part of data will get into it by hand, and part will be replicated from internal system.Some of us are for creating two separate databases, since there are two separate datasources. One, automatic, will change very little over time and requires almost no maintenance. Other datasource will be manual input. Tables and procedures related to this part will change over time.Some of us are for creating single database, since it will serve one web site. More important this group is concerned about performance issues since almost every select will require join between tables that would be stored in two separate databases. Do these issues exist? Can you share some insights, comments, links about this?
How do I write an sql statement to link tables in two databases and query them? For example: SELECT db1.table1.field1, db2.table1.field1 FROM Table1 INNER JOIN db1.table1.field1 ON db2.table1.field1 = db1.table1.field1
I have a basic sql statement, where I have a usersID, and I want to joing that usersID to another table in another database to get the users first and last names. How do I join across databases... each with a different connection string? Here's what I want.. Select usersID from tableA in databaseA, and usersFirstName, usersLastName from table B in database B where the usersID from tableA = the usersID in tableb.
I am trying to find out what records on in one DB and not the other. This SQL statement works find if the DBs are on the same Server. If I replace on the DBs with a linked DB (on another server) I get the following error.
SELECT * FROM [LinkedServer].[DBonLinkedServer].[dbo].[Sales Price] WHERE NOT EXISTS (SELECT [LocalDB].[dbo].[Sales Price].* FROM [LocalDB].[dbo].[Sales Price] WHERE ([LocalDB].[dbo].[Sales Price].[Item No_] collate Latin1_General_CS_AS = [LinkedServer].[DBonLinkedServer].[dbo].[Sales Price].[Item No_]) AND ([LocalDB].[dbo].[Sales Price].[Unit of Measure Code] collate Latin1_General_CS_AS = [LinkedServer].[DBonLinkedServer].[dbo].[Sales Price].[Unit of Measure Code]) )
Msg 4104, Level 16, State 1, Line 3 The multi-part identifier "LinkedServer.DBonLinkedServer.dbo.Sales Price.Item No_" could not be bound.
Msg 4104, Level 16, State 1, Line 4 The multi-part identifier "LinkedServer.DBonLinkedServer.dbo.Sales Price.Unit of Measure Code" could not be bound.
Hi I will like to bind the listbox control only with the rows where the colums text = categoryX Where do I declare the variable @Category ? If Not Page.IsPostBack Then Dim conPubs As SqlConnection Dim cmdSelect As SqlCommand Dim dtrAuthors As SqlDataReader conPubs = New SqlConnection(ConfigurationSettings.AppSettings("connectionstring")) conPubs.Open() cmdSelect = New SqlCommand("Select * From Slideshows WHERE ([Slideshows].[Category] = @Category)", conPubs) dtrAuthors = cmdSelect.ExecuteReader() ListBox1.DataSource = dtrAuthors ListBox1.DataBind() dtrAuthors.Close() conPubs.Close() End If Best Regards
I want to customize database maintenance to run only on selected databases based on their status in sys.databases, without having to statically select them in the GUI.
I'm trying to write an SSIS package that will do the following:
1. Query for a list of databases that are not read-only or recovering, etc. 2. Perform database maintenance tasks (e.g., rebuild indexes) on those databases.
I'm doing this because the database maintenance tasks do not provide a way to select databases based on criteria or a SQL query filter.
My package includes an Execute SQL task to get the list of databases, a Foreach loop container to loop through the databases, and then...this is where I get stuck. I can't use the existing maintenance tasks (Rebuild Index, Update Statistics, etc.) because I don't know how to provide an input variable for the list of databases. I'd rather not write dynamic SQL to generate commands for each database object.
I'm sure that is really simple, but how do I pass a parameter with multiple value to a SQLdatasource? ex: SELECT field1 from tblTableA where idTableA IN ( @Param1) Let's say I want to pass 1,2,3,4 as Param1 (SELECT field1 from tblTableA where idTableA IN ( 1,2,3,4)) How I am supposed tu use the .SelectParameters.Add() to pass a list of integers instead of a single value?? Thanks in advance.
Is it possible to use a WHERE-IN statement with a SqlDataSource control. For instance: <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:myConnectionString %>" SelectCommand="SELECT [Id], [Name], [Phone] FROM [Table1] WHERE ([Id] IN @Id)"> <SelectParameters> <asp:Parameter DefaultValue="( 1, 3, 5, 7, 11 )" Name="Id" Type="Int32" /> </SelectParameters></asp:SqlDataSource> I'm hoping the gridview would then display a table with rows for records 1,3,5,7,11. Thanks for any help...
Hi, I am trying to get the login name of a user, trim off some characters, which works fine, convert this to a string variable, which works fine and then use this variable in an sql select parameter. I've tried countless things and am not sure why my variable @NewString is not working in the Select command. If I add a default value I know is present, it works, but it can't seem to pick up the variable value of NewString. It prints out as expected in the response.write statement, but I really need it to connect to the corresponding value in the database. Any ideas are greatly appreciated-code below. Thanks, Liz
<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server"> <SCRIPT LANGUAGE="vb" runat="server"> Public Function LoggedOnUser() As StringReturn (Request.ServerVariables("LOGON_USER")) End Function </SCRIPT> <%Dim MyString As String = LoggedOnUser() Dim MyChar As Char() = {"O"c, "N"c, "E"c, ""c}Dim NewString As String = MyString.TrimStart(MyChar)Response.Write("Hello ") Response.Write(NewString) %> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:pboConnectionString %>" SelectCommand="SELECT [LastName], [FirstName], [logon] FROM [Phonebook] WHERE ([logon] = @NewString)"> <SelectParameters> <asp:Parameter DefaultValue="" Name="NewString" Type="String" /> </SelectParameters> </asp:SqlDataSource> <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataSourceID="SqlDataSource1"> <Columns> <asp:BoundField DataField="LastName" HeaderText="LastName" SortExpression="LastName" /> <asp:BoundField DataField="FirstName" HeaderText="FirstName" SortExpression="FirstName" /> <asp:BoundField DataField="logon" HeaderText="logon" SortExpression="logon" /> </Columns></asp:GridView> </asp:Content>
Please can someone provide an overview of how to select all records in the parameter list. I am trying to get an understanding of how this works.
Having a union join with all value is fine, to retrieve the value of 'all'. However, given the data-set there is no 'all' value. From reading the ms course notes, the query syntax is: select from where = (field = @x) or @x = 'all'. How does this syntax work? Does x = all, so all= all = true then returns all records?
I have a problem with SP when passing in Parameters. Basically something like this:
-- Pass 1, 2 or 3 as parameter EXEC SP_mySP 1
-- The SP will do the following SQL Statement SELECT * FROM myTable WHERE ( (If @Parameter1 = 1 then myColumn = 'A' or myColumn = 'B') (If @Parameter1 = 2 then myColumn = 'C') (If @Parameter1 = 3 then myColumn is not null) )
Is it possible and how do I do use a select like query with an adocommand's parameter object?For Example:Instead of this:command.CommandText = "SELECT * FROM Table WHERE field LIKE'%searchString%'This:command.CommandText = "SELECT * FROM Table WHERE field LIKE '%?%'command.Parameters.Append(command.CreateParameter ....Thanks
Which is more efficientWhere NonindexedColumn=x and IndexedColumn=yorWhere IndexedColumn=y and NonindexedColumn=xor does matter? Will the optimiser work it out?(I'm building the SQL string on the fly in the fron-end)
Hy , I'he made a stored procedure with 1 parameter @cTableName and I want to return data from that table: select * from @cTableName , that is all. But it does not work. When I execute the stored procedurere I want to send the name of the table : execute MySP "MyTable". Is it possible? Thank you!
I'm not sure if parameters is the correct solution, but it's all I can think of. I'm trying to use a "template" report with a graphic logo and some other standard layout to provide all my subreports with the same look. On the "default" subreport, I intend to have links to the other subreports, but I can't find a way to dynamically change the subreport shown by the Subreport item.
