1. inline SQL with a StringBuilder
2. managed sql
3. SQL text processing (@SQL as varchar(5000); SET @SQL = 'SELECT ' + @var...)
4. a regular sproc that has the columns and table name hard coded
1,2, and 4 always end up at about the same time given the averages.
3 is always at last 1.5 times slower, and usually closed to 2 times.
1 and 2 both use StringBuilders, the code is a direct copy, and 3 is a copy as well.
My managed SQL is:
[Microsoft.SqlServer.Server.SqlProcedure]
public static void usp_Items_Select_Managed(SqlString table, SqlString name,
SqlString value)
{
// sql
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.AppendFormat(
"SELECT {0}.* FROM {0} WHERE {0}.{1} = {2}",
table,
col,
value
);
SqlConnection sqlConnection = new SqlConnection("context connection=true");
SqlCommand sqlCommand = new SqlCommand(stringBuilder.ToString(), sqlConnection);
I am having a Data flow task in For each loop which will gets 100 sourcetable names and 100 target table names...
am having a simpleData flow task which trasferes from OLEDBSource to OLEDBDestination. I am repeating the Dataflow task which transfers from sourcetablename extracted from for loop to a destination table var.
The problem am gettting is for the first table it is able to transfer correcly because I did mapping for those tables at design time...but for the next coming sourcetable-desttable (which r having different no of cols,datatypes) its giving Validation failed...and...needs to refresh metadata....
is there any way to refresh the metadata of Data flow task (I set the property of OLEDBSource validate external meta to false then also same error is coming)
I have, a SSAS 2012 tabular instance with SP2, there is a database on the instance with a read role with everyone assigned permissions. When configuring the Power BI analysis services connector, at the point where you enter Friendly Name, Description and Friendly error message, when you click next I receive the error "The remote server returned an error (403)." I've tested connecting to the database from Excel on a desktop and connect fine.I don't use a "onmicrosoft" account so don't have that problem to deal with.
We use Power BI Pro with our Office 365. As far as I can tell that part is working ok as I pass that stage of the configuration with a message saying connected to Power BI.The connector is installed on the same server as tabular services, its a Win2012 Standard server. The tabular instance is running a domain account that is the admin account for the instance (this is a dev environment) that account is what I've used in the connector configuration. It's also a local admin account. There is no gateway installed on the server.
I have a cube that we are processing nightly via an Analysis Service Processing Task in SSIS. In order to increase the performance of the processing time, we elected to use a lot of rigid dimension attributes, and do a full process of everything in the SSIS task. The issue that I am having is that after that task completes, I need to go into Visual Studio to deploy the cube becuase we are unable to browse or use the cube. This issue seemed to start once we changed the SSIS Analysis Service Processing Task to do a full process on the dimensions, rather than an incremental.
I would expect that once development is done, and it is processed and deployed, that is it. My thinking is that the SSIS task should just update the already deployed cube,
How to right choose key column in"Mining Structure" for Microsoft Analysis Services?
I have table:
"Incoming goods"
Create table Income ( ID int not null identity(1, 1) [Date] datetime not null, GoodID int not null, PriceDeliver decimal(18, 2) not null, PriceSalse decimal(18, 2) not null, CONSTRAINT PK_ Income PRIMARY KEY CLUSTERED (ID), CONSTRAINT FK_IncomeGood foreign key (GoodID) references dbo.Goods ( ID ) )
I'm trying to build a relationship(regression) between “Price Sale” from Good and “Price Deliver”.But I do not know what column better choose as “key column”: ID or GoodID ?
I am connecting to SSAS cube from Excel and I have date dimension with 4 fields (I have others but I don't use it for this case). I created 4 fields in order to test all possible scenarios that I could think of:
DateKey: - Type: System.Integer - Value: yyyyMMdd Date: - Type: System.DateTime DateStr0: - Type: System.String - Value: dd/MM/yyyy (note: I am not using US culture) - Example: 01/11/2015 DateStr1: - Type: System.String - Value: %d/%M/yyyy (note: I am not using US culture) - Example: 1/11/2015
Filtering on date is working fine:
Initially, in excel, filtering on date was not working. But after changing dimensional type to time, and setting DataType to Date, as mentioned in [URL] filter is working fine as you can see in the picture.Grouping on date is not working:
I have hierarchy in my Date dimension and I can group based on hierarchy, no problem. But user is used to pre-build grouping function of excel, and he wants to use that. Pre-build functions of Excel, Group and ungroup seems to be available as you can see in following picture:
But when user clicks 'Group', excel groups it as if it is a string, and that is the problem. User wants to group using pre-build grouping function available in Pivot table. I also find out that Power Pivot Table does not support this excel grouping functionality. And if I understood well, this pre-build grouping functionality of excel, needs to do calculation at run time, and that is not viable solution if you have millions of rows. So Power pivot table does not support pre-build grouping functionality of excel and hence we need to use dimension hierarchy to do the grouping. But I am not using Power Pivot table, I am using simple Pivot Table. So I expect grouping functionality to be working fine. Then I tried to do simple test. I created a simple data source in excel itself. And use it as source of my Pivot table. Then grouping is working fine. The only difference that I can see is (When double click the Measure value in Excel),For date values of my simple test, excel consider them as 'Date'.
For date values of my data coming from cube, excel consider them as 'General'
2.1. But value here is same as it was in simple test.
2.2. 'Date Filter' works just fine.
2.3. If I just select this cell and unselect it, then excel change type to 'Date' though for that cell.
2.4. I have created 4 different types of fields in my date dimension thinking that values of attribute of my dimension might be the problem, but excel consider 'General' for all of them.
2.5 This value (that can be seen when double clicking on measure) comes from 'Name Column' of the attribute. And the DataType defined is WChar. And I thought that might be the reason of issue. And I changed it to 'Date'. But SSAS does not allow it to change to 'Date' giving error : The 'Date' data type is not allowed for the 'NameColumn' property; 'WChar' should be used.
So, I don't know, what is the puzzle piece that I am missing.
1. Date filter works, group does not work
2. Excel consider it as 'General' string.
3. SSAS does not allow to change 'NameColumn' to Date.
I would like to know the best practice for running analysis service in terms of port usage. Is it better to run on a specific port or have dynamic ports ? We have clustered servers that run default on 2383 but not sure with non clustered what's the best way to get performance.
I have some questions about SQL Servers 2000 and 2005 compatibility. In my configuration I have to use both servers. The cubes are stocked in 2005 server. May I transfer from 2005 to 2000 Analysis Services the cubes?
If yes, what is the procedure? The result of migration is the same in the two different versions?
Which calls for me to import: Microsoft.SqlServer.Dts.Runtime However, I don't have this DST Runtime DLL installed on my machine. I have installed "everything" in SQL Server Developer Edition and Visual Studio. Any idea how I can get this?
I'm running a package on SQL Server 7, SP2 created in the same environment. When running a package from DTS Designer, I get an execution status of "Error Occurred" in the Package Execution Status dialog on one of the data pump tasks I'm running along with the number of rows inserted. When I double-click on the task, I get the following message: " The Connection is currently being used by a task. The connection cannot be closed or reused".
Both the exception log for the task and the package, specified for error output, indicate the task ran successfully and the clients confirm that the appropriate number of rows have been inserted in the table. Has anyone encountered this error that can explain the cause/cure?
When I'm using installshield to install sql runtime, I get the message "The Workstation service has not been started" during Configure SQL Server Agent. Does someone know what it means and how to solve it?
I've an DTS package, which has an Execute SQL Task. I'm doing the operations based on a parameter. I need to pass that in runtime. Is there any way to do that in Execute SQL Task? BOL says that the runtime parameter can be set by giving a question mark(?). But when I tried this, it says, value not passed.
I am Using Sql Server 2005 enterprise edition to develop an accounting system. now i want to distribute this software to other cleints. I have created a setup which includes all the components required for the run time of the software but i dont know how to include the sql server database in it. as i know that i have created access based software i used to include the database file in the setup and it would work but in the case of sql server i dont know how to do it . is there any runtime of sql server which needs to be downloaded or any other way out.
set @t = 'Test' set @chg22 = 'USE <%1> GO' set @chg22 = REPLACE(@chg22,'<%1>',@t)
exec(@chg22) SELECT * FROM Table_Name
but i am not getting it..in above example i am just took one line select * from table_name but actually in .sql file more that 300 lines are there so i m not able to concatenate it..
We run a regular query through Excel that is linked to a Microsoft SQL Server data source.
We have run this query routinely for the past few years. Suddenly this query has started returning an error message - “[Microsoft][ODBC SQL Server Driver] [SQL Server] Difference of two datetime columns caused overflow at runtime?. It seems to be a common error (as per Google search) however I don’t want to go messing around with something I don’t fully understand. Can anyone point me in the direction of what to look out for?
Currently im using SQL queries in combination with ASP.NET..
I have currently only one table('Requests'), which has all the user info of name,id,studyrequested etc.. A person can request for any number of studies.
Now the prob is that from now on whenever a user requests for a new study not only should his details be stored in the Request table but also in a new Users table. In the Users table that particular users details must only be loaded once.. i.e. if the same user enrolls for another study his details should only be updated in the requests table and not in the Users table, since his details have already been loaded once in the Users Table..
While this works fine for new users, but what bout the old users already stored in the Requests table before the Users table existed.
Is there any way i could run a sql script only once at runtime, so that all the old legacy users are loaded once in to the Users Table.
Hi!Can anybody tell me what I'm missing here?I'm trying to insert a new record into a sql-database from code atruntime (not stored procedure),but get the following error message:"The 'strUn' is not permitted in this context.Only constants, expressions or variables allowed here.Column names are not permitted."//Create sql connectionSqlConnection con = new SqlConnection("server=LocalHost;database=Users;uid=geir;pwd=geir");//Open database connectioncon.Open();//Create variables to hold values from textboxesstring strUn = txtUsername.Text;string strPw = txtPassword.Text;//Create a sqlCommand to insert textbox values into sql-databaseSqlCommand sqlcmd = new SqlCommand();sqlcmd.CommandText = "INSERT INTO tblUsers(Username,Password)VALUES(strUn, strPw)";sqlcmd.Connection = con;try{sqlcmd.ExecuteNonQuery();}catch(SqlException ex){lblInfo.Text = "ExecuteNonQuery failed because: " +"" +ex.Message;}finally{con.Close();}
My boss claims that there is a free SQL server runtime. Is thistrue? I was only aware of MSDE. But I believe the connections arecapped at 5 in MSDE. If there is a free SQL server runtime, are thereany limitations to using it (performance, connection count)? Thanks.
I want to combine a bunch of reports (which are stand alone reports on there own) as subreports in a parent or master report. We have done this in Crystal Reprots in order to get a group of reports out in one file.
The issue with Reporting Services is the header and footers of the subreports are not rendered, so I have to repeat the logo information for each subreport in the body of the parent report. I can do this in a table or list or rectangle, but I have to do this for each subreport. Is there any way that I can specify the name of the subreport at runtime? That way I can put everything into a List and drive it from the database. I noticed that the dropdown to select the report name does not allow you to create an expression for this field.
Does anyone have an idea on how to handle runtime parameters for a package? Ideally I'd like to have the package prompt the user for a date range and then store the 2 dates in package variables for use later in the package.
I am trying to create a report with unknown number of report items. (as I dont know how many columns the stored proc is going to return).Is there a way to create these type of reports using SSRS other than creating the rdl programatically using vb.net or c#?
I'm trying to create a custom data flow transformation, I need to set the value of a user variable which will be used by a downstream component(for example: a data flow destination) at runtime, is this possible? If possible, in which method should I do that? Thanks.
Our company is currently looking at a package which uses sql server 2000 as backend and power builder as front end. The vendor said that we need to have SQL client runtime install in the user pc. Can anyone tells me what is SQL client rutime and the purpose of it?
Hi! My question is exactly the subject. My Web Form has only a GridView and a DetailsView, there is no SqlDataSource at project time, i create the SqlDataSource at runtime using code like this in the Page_Load event: (I NEED IT TO BE CREATED DYNAMICALLY)1 Dim SQLDS As SqlDataSource = New SqlDataSource() 2 3 SQLDS.ID = "CustomerDataSource" 4 SQLDS.ConnectionString = ConfigurationManager.ConnectionStrings("connectionstring").ConnectionString 5 SQLDS.SelectCommand = "select customerid,companyname,contactname,country from customers" 6 SQLDS.InsertCommand = "insert into customers(customerid,companyname,contactname,country) values(@customerid,@companyname,@contactname,@country)" 7 SQLDS.UpdateCommand = "update customers set companyname=@companyname,contactname=@contactname,country=@country where customerid=@customerid" 8 SQLDS.DeleteCommand = "delete from customers where customerid=@customerid" 9 10 SQLDS.UpdateParameters.Add(New Parameter("companyname")) 11 SQLDS.UpdateParameters.Add(New Parameter("contactname")) 12 SQLDS.UpdateParameters.Add(New Parameter("country")) 13 SQLDS.UpdateParameters.Add(New Parameter("customerid")) 14 15 Page.Controls.Add(SQLDS) 16 17 If Not Page.IsPostBack Then 18 GridView1.DataKeyNames = New String() {"customerid"} 19 GridView1.DataSourceID = SQLDS.ID 20 21 ' ... and so on The DetailsView1 uses the same SqlDataSource to show data, but i could not find a way to synchronize the DetailsView1 with the GridView1 when a record is selected in the GridView1.How can I synchronize the DetailsView?I played with the ControlParameter but i can't find either how to add a ControlParameter in code, is there a way? Every place talking about ControlParameter shows something like this: 1 <asp:SqlDataSource ID="SqlDataSource1" runat="server" 2 ConnectionString="<%$ ConnectionStrings:Pubs %>" 3 SelectCommand="SELECT [au_id], [au_lname], [au_fname], [state] FROM [authors] WHERE [state] = @state"> 4 <SelectParameters> 5 <asp:ControlParameter Name="state" ControlID="DropDownList1" PropertyName="SelectedValue" /> 6 </SelectParameters> 7 </asp:SqlDataSource> 8 Ok. OK. But my SqlDataSource is created dynamically. Any ideas on how to solve this problem?Thanks!
Hi, I am trying to create a DTS package dynamically. I have taken all these steps i.e., 1. SN.EXE -K c:DTS.KEY2.tlbimp.exe "C:program filesmicrosoft SQL Sever80ToolsBindtspkg.dll" /out:c:Microsoft.SQLServer.DTSPkg80.dll /Keyfile:c:DTS.KEY3.gacutil.exe -i C:Microsoft.SQLServer.DTSPkg80.dll. Now when i compile the code, i didnt get any compilation error. But when execute the code it gives me a runtime error which goes as given below : An unhandled exception of type 'System.InvalidCastException' occurred in DTSFactory.exe Additional information: QueryInterface for interface Microsoft.SQLServer.DTSPkg80.CustomTask failed. I am getting this error near the line : oCustTask = (DTS.DataPumpTask2)oTask.CustomTask; The following is the code. using System; using System.Drawing; using System.Collections; using System.ComponentModel; using System.Windows.Forms; using System.Data; using DTS = Microsoft.SQLServer.DTSPkg80; namespace DTSFactory { /// <summary> /// Summary description for Form1. ///This is assuming that all steps have been taken in the following document: ///http://SQLDEV.NET/DTS/DotNetCookBook.htm ///SN.EXE -K c:DTS.KEY ///tlbimp.exe "C:program filesmicrosoft SQL Sever80ToolsBindtspkg.dll" /out:c:Microsoft.SQLServer.DTSPkg80.dll ///Keyfile:c:DTS.KEY ///gacutil.exe -i C:Microsoft.SQLServer.DTSPkg80.dll ///These steps are needed for interop with dtspkg.dll /// </summary> public class Form3 : System.Windows.Forms.Form { /// <summary> /// Required designer variable. /// </summary> private System.ComponentModel.Container components = null; private System.Windows.Forms.Button button1; public DTS.Package2Class pkg = new DTS.Package2Class(); public Form3() { // // Required for Windows Form Designer support // InitializeComponent(); // // TODO: Add any constructor code after InitializeComponent call // } private void Form3_Load(object sender, System.EventArgs e) {