I'm working on a script to convert data from one software package
to another. Greatly simplified, it looks something like
create procedure import_widget as
begin
insert into our_widget (foo, bar)
select baz, quux from their_db.dbo.their_widget
end
go
The problem is that the name of the source database varies from
one system to another, so I want to pass the database name as a
parameter. I think I could do the following, but is there a
better way to go about it?
create procedure import_widget (@db_name sysname) as
begin
exec 'create view their_widget as select * from '
+ @db_name + '.dbo.their_widget'
insert into our_widget (foo, bar)
select baz, quux from their_widget
Hi, I need to write a report which will run based on the user's input for Server Group. Meaning, if the user wants the report to be run for one say, UAT group, the report (and therefore the stored procedure) should be executed against UAT server. Or, if the user wants it to be run on the Production machine, the stored procedure should be executed on the Production Server. Assume that both UAT and Production servers are in the same domain to rule out any authentication issue.
I think one way of doing it is to create a linked server on one server which we use to connect via the Report Manager. So, whatever Server Group is selected based on that the SP will be executed on that respective Server Group's Server, since in the query we will have given full names of the tables that we use.
But, I strongly suspect, that having linked servers will not be accepted since it requires that the server owners maintain that. So, I was thinking if there is any other way of achieving this. If anyone knows, please help me with this.
I've got a cube in SSAS 2205 with some financial measures that I want to appear conditionally in an MDX query. Here's what I mean...
The cube measures I'm interested in are actual revenue, forecast revenue and budget revenue. The dimension I'm reporting across is the [Time].[month] member. The report I'm trying to create with this query has each month across the top. But the headers show actual revenue if the current reporting month is on or before the month in question. For future months, we show the forcast revenue if there is any. For any other future revenue, we show the budget measures. So a sample report for April would have columns like...
Code SnippetJanuary February March April May June July August September Actual Actual Actual Actual Forecast Forecast Forecast Budget Budget
I need to conditionally bring back the right measure and have the measure name (e.g., column header) indicate if it's an actual, forecast or budget value.
Hi, I have to choose a particular column from a table whenever a condition met. That means i have to use the column in the select query whenever the condition met otherwise i have to create a dummy column with the same column name. I tried to use the following sample query. Eg: Create table temp (test_id int, test varchar(100))
Declare @var varchar(100) Set @Var='Hi' Select case when @Var='HI' then "None" else Test_new End as Test from Temp
But it throw error as "Invalid column 'Test_new' "
As per my situation the Test_new column will be available in temp table whenever the condition is not met.So when @var<>'HI' i have to get the details of the test_new column otherwise i have to create a dummy column with the same name.
Hey, I have a search form with a selectbox. This selectbox contains the columnnames.I want when I put a text in a textbox and select a value in the selectbox and click submit that it search database.The Columnnames I put in a session. If you see I have put in the querystring as columnname @sescolumn which I have initialised as asp:sessionparameter.But it gives no results. When I put @sescolumn between [] like normal columnnames are it doesn't work also. Can someon put my on the right path?
<asp:SqlDataSource ID="Database_ecars" runat="server" ConnectionString="<%$ ConnectionStrings:connectionstring %>" SelectCommand="SELECT [AutoID], [Merk], [Kleur], [Type], [Autotype], [prijs], [Zitplaatsen], [Afbeelding1], [Afbeelding2], [Afbeelding3], [Afbeelding4] FROM [Auto] where @sescolumn like @seskeyword and [AutoID] not in (select [AutoID] from [verhuring] where [StartVerhuur] >= @sesdatefrom and [Eindeverhuur] <= @sesdatetill)" > <SelectParameters> <asp:SessionParameter Name="sesdatefrom" SessionField="datefrom" Type="Decimal" /> <asp:SessionParameter Name="sesdatetill" SessionField="datetill" Type="Decimal" /> <asp:SessionParameter Name="seskeyword" SessionField="keyword" Type="string" /> <asp:SessionParameter Name="sescolumn" SessionField="columnname" Type="string" /> </SelectParameters> </asp:SqlDataSource>
I Hope someone can help me out with this problem.. its pretty NB.
I have a pair of multivalued parameters. So, parent and child... the child needs to get its selection based on the selection made by the user on the parent parameter ( the child parameter is hidden).
Fictional values: Parent Parameter: SelectAll, ALL , 1 , 2 Child Parameter: Home, Work, Play
If user selects "ALL" from the parent parameter I need Home and Play selected.
My idea was this: =iif(Parameters!Parent.Value(1) = "All","Home,Play") <-- in Available values.
However, the dataset that reads this value does not like the coma delimited string. This dataset is a cube created dataset, reading the child parameter.
Any help is greatly appreciated. Kind Regards, Neil
I am writing a package where the user uploads a flat file to a web folder. I need to automate this package to run everytime it sees a new file.
How can I implement this?
Can I make a call to a package or a sql server job to run from .net 2.0?
Do I need to use a service broker to look for a new file and run the package or a stored proc....I am looking for an async process where user doesnt have to wait for the package to run as it involves data validation of flat file and its huge...
Here is the issue I'm having. I am writing a stored procedure that takes a couple of parameters. Each one is the value within a specific column in one table (i.e., @part = 'o-ring' or @sub_assembly = 'hydraulic ram'). Needless to say, the columns form a hierarchy. What I am trying to achieve is to allow the user to specify one of the parameters and get a count for all records where the specified value is in the corresponding column. So, if the user puts in the parameter @part = 'o-ring', I want it to know that the where clause for the select statement should look for o-ring in the part column and not the sub_assembly column. Here is what I am trying to do, which isn't working.
IF(@sub_assembly = NULL) BEGIN IF(@part = NULL) BEGIN PRINT 'This is an error. You must have at least a part' END ELSE BEGIN SET @querycolumn = 'Part' SET @queryvalue = @part END END ELSE BEGIN SET @querycolumn = 'SubAssembly' SET @queryvalue = @sub_assembly END
SELECT SubAssembly, Part, COUNT(RecordID) FROM Table WHERE @querycolumn = @queryvalue GROUP BY SubAssembly, Part ORDER BY SubAssembly, Part
The problem is that I'm getting an error when I try to use @querycolumn to supply the column name to the WHERE clause. Any ideas or suggestions?
Does anyone know if there is a way to dynamically set the chart type (i.e. pie, line, column, etc.) for your report in SSRS? I want the customer to be able to chose which type of chart they'd like to see.
I've looked all over the Internet and can't find anything.
Any help would be appreciated. Alan
Clarification: I guess I should clarify a little. I am using an ASP.NET 2.0 page written in C# and using a ReportViewer control.
More Clarification: The ReportViewer displays an rdlc.
I have over 50 rows in this table and my bizIDNum is always getting set to 4. Please take a look at the code and tell me whats wrong. If I do the select statement in the database the return number is correct. here is the code
// db connection SqlConnection sqlConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["work4tipsConnectionString"].ConnectionString); sqlConnection.Open(); string sqlSelect = "SELECT MAX(bizID) FROM business"; SqlCommand sqlCommand = new SqlCommand(sqlSelect, sqlConnection); //sqlCommand.Parameters.Add("@Username", SqlDbType.VarChar).Value = username; string bizIdNum = (string)sqlCommand.ExecuteScalar(); sqlConnection.Close(); sqlConnection.Dispose();
I am using this function for my datasource; "="data source=" & Parameters!Server.Value & ";initial catalog=" & Parameters!Database.Value"
When I set my database parameter to be a multivalued, it does not work, it will only let me select one db at a time. I would like to grab a table named pm00200 from many databases.
Hi All, I have a web site where the user can select from a list of databases (they're actually all the same structure, but have data unique to each client). Each database is a SQL 2005 database held in the App_Data folder. This all works a treat. Each database is simply named after the client it is holding data for, and the connection string for each is stored in a database that is accessed However, what I need is a form where a database name can be entered, and I can then create a new database with the same structure as the others - including tables, indexes and stored-procedures. I know I can use the copy database wizard in the Management Studio, but that isn't an option to the users of this system (it's to be used on an intranet, and the page I'm trying to create will only be used by project managers - but these people won't have a clue when it comes to configuring databases themselves). I have scripted a database, but have no idea on how I can actually use this script from within Visual Studio and more importantly, don't know how to configue it to have the database name as a parameter. I've also seen some examples that suggest using SQL-DMO, but haven't been able to work out how to use it and according to Microsoft it shouldn't be used for new developments (http://msdn2.microsoft.com/en-us/library/ms132924.aspx). Anyway, I really hope someone is able to offer some assistance on this one as I've spent a whole day trawling forums and various web-sites trying to find a solution to this. Thanks & regards, Paul
I've got a GridView on my webform bound to a SQLDataSource (called sqlZKEWILL). Works fine as long as I hard-code the "dbo" database owner in the Select statement, as in: select EXIDV, Carton_Count, Carton_Total, dbo.ZINSPECTION where EXIDV = @CartonID The problem is that I need the database owner name to be dynamic. This will be a parameter that I read from another SQL table. For example, I'm doing this: Select USERID, DatabaseOwner from tblUsers where UserID = 'Kimm' I need to take the DatabaseOwner from this second Select statement, and use it as part of that first select statement. On the CodeBehind page, I tried replacing "dbo" with the Database Owner field (which I saved in a session variable) with the following code: Private Sub gvLineItems_DataBinding(ByVal sender As Object, ByVal e As System.EventArgs) Handles gvLineItems.DataBinding sqlZKEWILL.SelectCommand = sqlZKEWILL.SelectCommand.Replace("dbo", Session("DatabaseOwner").ToString.Trim) End Sub
But I am still getting the error message: "Invalid object name: dbo.ZINSPECTION". It doesn't seem to recognize that I've replaced "dbo" with a different value.
Any thoughts on getting this to work would be appreciated.
I have an application that is developed to support a customer per database. All the data is unique to that customer and is physically partitioned from other customers. Also, I have a database that has common tables to all customers. I use stored procedures to access all data. I would like to keep from duplicating all the stored procedures (since the meat of them stays the same) because of the database references.
Is there any way to use the "USE <database>" functionality in the stored procedures to switch context dynamically without having to reference the unique databases?
I have a trigger which retrieves database names from a table. I need to use this retrieved database name in another sql insert statement as a variable e.g. set @mydbname = Select .... from.. (to get the database name) then.. insert into @mydbname.dbo.emplTable
At the moment it reads @mydbname as the string "@mydbname" not the value the variable holds
I need the database name as a variable because i have to write to the correct database (there are 15) Any help appreciated
Dear SSIS experts, I was wondering if there's such a thing that we could assign the database connection on the fly dynamically? so that I save the connection string or the database name somewhere and SSIS reads it on load time or something.. Thank you
hi, in the front end i have two fields one is dropdown list another is checboxlist say dropdownlist consist of title name and checkbox list consists of sub title name.on selecting title name from dropdownlist i will be getting respective title name in checkboxlist. here when i select checkboxlist(suppose if there are 6 items in checkboxlist n if i select only 3)then my databse column should generate one dropdownlist selected item and 3 checkboxlist selected item.
Eg: if dropdownlist contains( title1,title2,title3) and checkboxlist contains(chk1,chk2,chk3,chk4,chk5,chk6). if i select title1 from dropdownlist i will be geeting 6 item in checkbox list) if i select only 3 items of checkbox list. then i should be able to create column name by title1,chk1,chk2,chk3
HiMy problem is as follows:I need to transmit data between two databases on the same server, but Ihave to use dynamic database names (they must be configurable). Forexample I need to achive sth like that:insert into [database1].[dbo].[table1](select columns from [table2])when database1 is not known at implementation stage.I know I can use EXEC @t_sql_code, but I wonder if there is any otherway? (OPENROWSET doesn't seem to suit my needs)Thanks in advanceAmfi
I am building a data warehouse for a customer who has systems located in two different countries.
I need to import that data from four seperate databases, which all share the same structure.
To do this i have created 20 packages to import that data from the source database. What i would like to do, is at run time set which database the SSIS package should get its data from.
In sql 2k this was easy with a global variable that was set, then use a dynamic properties task to set the data source.
How can i achieve the same result in SSIS? the data source is an ODBC connection, with the four ODBC connections having similar names, eg ABC_NZ, ABC_AU
I am doing an e-commerce project. This website will have a category of product. Each category will have sub-category. And sub-category may have another level of sub-category. This means the number of sub-category is not fixed. In the sub-category we will have products. Each product will have unpredicable number of properties (1, 2, 3 or many properties).
With the current requirment, I can know exactly the number of sub-category level and the number of properties. But the problem comes when later my boss add more category, sub-category ,... sub-category(more sub-category level), and product, as well as the products properties. At that time my database schema will not suitable for new category, products because the in can only design database with fixed number of sub-category level and fixed number of product properties(attributes or fiels in database).
Therefore, I want to ask all of you that - Is there anyway to solve this problem? - how to design database that meet extended requirements as I present above?
Hi *, is there a way to connect Excel to SQL Server so that Excel serves as a frontend to SQL Server? I heard rumors that this is possible with the 2007 release.
I'm looking for a product that helps me storing massive data outside an Excel file. Right now, I'm using Palo (open source multidimensional database).
I have 4 different flat files types each having different no. of column, order of columns etc. I want to upload all the 4 types into the same destination table in the SQL database. Before uploading I need to apply transformation to each column in the flat files. The transformations could be like
1) Multipying the source column by 100
2) Put an if condition for 2 source columns and then select one column to be copied into the destination.
I have the flat files schema with me and also all the transformations that are required.
Question:
Can SSIS provide me with a component that can read the flat file schema and the transformations from the database and apply them to the source data and then upload it to the constant destination table? Can derived column transformation be provided with the input columns list and the transformation to be done on each dynamically?
Why I want this way?
In future there can be an addition of extra flat file formats and we want to keep the changes to the SSIS packages to he mininum. Just entereing the addiional schema and transformation details in the database should run the package on the new flat file successfully.
I'm a newbie to SSIS so this question may cause people to point and laugh. I apologise if that's the case.
I have a configuration database that holds connection details to a remote database. I want to use these connection details to dynamically create a new connection to some remote database.
I usually work in with C# where this would be simple but I can't find how to do this using SSIS.
I have found people using configuration files but as I already have my connection data in a table I don't really want to strip it out. (Also, is it a security risk to have database connection strings stored in a config file?)
I have been able to implement a custom script object to build the connection string and assign it to a user variable within the package but can't find any way to then use that variable to connect to the remote database.
I have a question regarding the dynamic sql. I have a script which which deletes all the foreign keys and re- creates it. So, I have created a table instead of a temp table. Now I need to create that foreign keys table in such a way that in which ever database I run the script, this foreign keys table should be created in that particular database.
For example:
IF OBJECT_ID('tempdb..keys','U') IS NOT NULL DROP TABLE keys CREATE TABLE keys.[@sourceserver],[@database]. (RowId INT PRIMARY KEY IDENTITY(1, 1), ForeignKeyConstraintName NVARCHAR(200), ForeignKeyConstraintTableSchema NVARCHAR(200), ForeignKeyConstraintTableName NVARCHAR(200), ForeignKeyConstraintColumnName NVARCHAR(200)
I have a requirement to make all of my KPI goals configurable by the end user - I would like to add the data to my datawarehouse so that it is updated in the cube whenever the cube is processed. I'm thinking I will need a table for each KPI that includes two columns (I will be setting goal by year):
Year Value
In my cube I then create a measure group for each KPI (for some I will add a calculations to break down the yearly goal to lower levels of detail).I will add each measure to my Dimension Usage and relate to each of my time dimensions at the Year level.I currently have around a dozen KPIs - my above approach seems a bit messy i.e. requiring a table and measure group for each KPI (I guess I could at least put them all into one table in my relational db).
Sharepoint is a pretty darn dynamic service and that got me thinking of how databases are created. Just wondering out loud, surely someone has thought of creating databases in such a manner, but I don't know if it's a thought that has been struck down.
It would look something like this: Single Table ID uniqueidentifier PK ParentID uniqueidentifier FK to ID Name nvarchar(MAX)
Value nvarchar(MAX)
In this manner, you would create your database "columns" as needed in the data-layer.
If that is too strict, (every datatype would be encoded to base64), you could create a value option for basically every data type. EG. nvarchar(max), nvarbinary(max).... and add another field that describes the data-type to be used for that "column" ID uniqueidentifier PK ParentID uniqueidentifier FK to ID Name nvarchar(MAX) DataType nvarchar(50) //constrained to allowable datatypes MaxLen nvarchar(31) //ahh, what the hey, let's add this for sniggles. ValueNvarchar nvarchar(MAX) nullable
ValueNvarbin nvarbinary(MAX) nullable
....
Now, to allow the "values" for those "columns", you may be able to still use the single-table approach, but it may be better to have an extra table for that (probably even a different partition and drive).
Things to consider, indexing..... In development, the data-layer would handle the creation/reading of table columns. The business-layer, which could be many for different parts of a company, would make it's requests to the DL. It may need a username, and the DL would either just create it, or suggest an already existing username column. The path to that username may not be where a particular biz element wants it, so they will ask the DL create another under a diff path.
The business-layer is probably the most important reason for wanting a single dynamic table.
In the end, the relation structure could be like: Human //dir, no value, no parent (root) FirstName //value - text LastName //value - text Parent1 //value - Me Id Sibling1 //value - Me Id SiblingN //value - Me Id
School //dir, no val ParentId //value - Id (perhaps category would be Building) Name //value - text
The sibling N would be an example of how a table may need to be dynamic. A positive of the single-table approach is no limitation on number of "columns". Another is the ease to move a hierarchical structure if needed. School may want to be University instead of just "school" and be placed as a child of "school".
I'd like to modify the dates within this where clause to be dynamic, building the date depending on the current year, but everything I try doesn't seem to be syntactically correct.
SELECT * FROM Openquery(LS_CIS, 'select * from BI_WRKFLW_TASKS where (BI_EVENT_DT_TM>=''1/1/2011'' and (BI_NEEDED_DT_TM>=''1/1/2011''))OR (BI_EVENT_DT_TM>=''1/1/2011'' and BI_NEEDED_DT_TM is null)') AS derivedtbl_1 I'd like to replace ''1/1/2011'' in the where clause with something like: CAST(CAST(YEAR (GETDATE())-4 AS varchar) + '-' + CAST(01 AS varchar) + '-' + CAST(01 AS varchar) AS DATETIME)
I got your email address from your web cast. I really enjoyed the web cast and found it to be very informative.
Our company is planning to use SSIS (VS 2005 / SQL Server 2005). I have a quick question regarding the product. I have looked for the information on the web, but was not able to find relevant information.
We are getting Source data from two of our client in the form of Excel Sheet. These Excel sheets Are generated using reporting services. On examining the excel sheet, I found out that the name Of the columns contain data itself, so the names are not static such as Jan 2007 Sales, Feb 2007 Sales etc etc. And even the number of columns are not static. It depends upon the range of date selected by the user.
I wanted to know, if there is a way to import Excel sheet using Integration Services by defining the position Of column, instead of column name and I am not sure if there is a way for me to import excel with dynamic Number of columns.
Your help in this respect is highly appreciated!
Thanks,
Hi Anthony, I am glad the Web cast was helpful.
Kamal and I have both moved on to other teams in MSFT and I am a little rusty in that area, though in general dynamic numbers of columns in any format is always tricky. I am just assuming its not feasible for you to try and get the source for SSIS a little closer to home, e.g. rather than using Excel output from Reporting Services, use the same/some form of the query/data source that RS is using.
I suggest you post a question on the SSIS forum on MSDN and you should get some good answers. http://forums.microsoft.com/msdn/showforum.aspx?forumid=80&siteid=1 http://forums.microsoft.com/msdn/showforum.aspx?forumid=80&siteid=1