We are trying to compile a difinitive procedure for our customers moving from SSAS 2000 (where we use DSO) to 2005 (where we use AMO). However, we are running into problems getting even the 2000 environment set up according to the specs.
The requirement is a domain environment where the web server and the DB server are 2 different machines. We are replicating this environment locally using 3 Virtual Machines in VMWare Player. All have Windows Server 2003 Enterprise with SP2 installed. One is our domain controller, one is the DB server (SQL Server 2000 and SSAS 2000 SP4 with the hotfix rollup installed) where almost none of our application components are installed, one is the web server with IIS and our ASP.NET application and COM components, which bundles DSO and dependencies, but no database client tools installed. We set up ASP.NET to run as a domain user, a member of Administrators group on the domain and both server machines, and also OLAP Admins on the DB server (we can limit access later once everything works).
I can successfully create the database, cube, dimensions, and partitions from the ASP.NET code calligng COM components written in VB6 which use DSO. However, creating aggregations results in a clipboard error. Here is the code:
1380
Set dsoPartition = m_dsoServer.MDStores(mvarDBName).MDStores(cubeName).MDStores(1)
dsoPartition.OlapMode = olapmodeMolapIndex
1385
' get the partition analyzer
Dim dsoPartitionAnalyzer As DSO.PartitionAnalyzer
Set dsoPartitionAnalyzer = dsoPartition.Analyzer
' initialize the analyzer
dsoPartitionAnalyzer.InitializeDesign
1390
There is more, but the error occurs on one of the three statements between 1385 and 1390
The error is: "Can't open Clipboard" number 521 Source "DSO"
Creating the aggregations via the Storage Design wizard on the DB server directly seems to succeed.
Does anyone know how to automate the copying of a sql server 2005 database diagram to the clipboard so that this could for instance be accomplished from a word template. Does SQL Server 2005 expose an interface so that you can do this?
I am trying to automate the copying of a database diagram into a word doc - can be done manually by right clicking on the diagram and sending to clipboard, pasting it into the doc. The clipboard is easily accessed from within word.
Within a report matrix, I want to be able to have different aggregations for totals. One column would be a Sum of the data in that column, the next column would be an Average of the data. Is this possible, or is this not supported in SSR2005?
hi, I'm making a cube using SSAS and the data in the cube is updated daily. however, each day I take the data with the date of migration because I want to keep the data as history snapshots. the problem is when browsing the data I want to aggregate only the data of a desired date not all the data together. can I define a custom aggregator rather than those found in the properties for the measures? Thanks a lot. Mina
I am trying to add aggregations to the partitions of my cube.
For that, I am using the Aggregation Designer, In which I choose MOLAP partitions with "Performance gain reaches 80%". Next I run the calculation. But the designer found only 78 aggregations and stop at 20% (496 Kb) !
My configuration is SSAS 2005 SP2, x64. Except this problem, everything is allright. Dimension attributes are all aggregatable.
Have you any idea of what can cause such a problem ? Thanks in advance.
Fact table gets update every day with thousands of records, I been increase the aggregations manually. Is there a way I can increase aggregations (design storage) programmatically . My cub gets process ones in day with VB program. I want to increase aggregations before processing cub through VB.
Hello, Not sure if data minimg can help me solving this type of inquiries. Thanks in advance for any pointers you could provide me. Problem: We have many dimensions with many levels. Sales directors will sometimes note a drop or a spike when looking at one of the dimensions members, most of the time at a high level. Example, For Q4 07, the aggregation of Q4 Billing plus remaining Q4 Backlog dropped by $10M in America for the Automotive business from week 4 to week 5 while it was forecasted to remain stable. The immediate question that will be sent to the guys in charge of this business is "What happened?". Obviously evyone struggle to answer these questions eveytine there is a drop anywhere. Using classic tools, they will try to spot a single anomaly or determine if this is accross the board and what are the concurent factors like lower than expected shipments combined with some orders cancellations. The drop-off can lie in a single product or customer location, in which case it is fairly easy while time consuming to spot the anomaly using classic drill-down approach. The drop-off can also be more wide spread or caused by a combination of factors like unable to ship in time, orders cancellations and sales credits in which case it is more difficult to spot where is the anomaly and explain it. So I wonder if there is any way that I could use data mining to make the entire process of detecting anomalies accross dimensions levels, members and measures more straight-forward for end-users. What do you think? Thanks, Philippe
I had just installed SQL 2005 dev on my laptop and got an error message when I tried to create a package using the BI IDE. I received the same error using VS2005 IDE. But the project was created regardless without any packages. When I tried to create a new package in the project, I received the same error again, but with an option to view the error details.
Following is the text of the error details:
TITLE: Microsoft Visual Studio ------------------------------
I am trying to create a CLR UDF in SQL 2005 and cosnistently run into the following error. What am I doing wrong?. Please correct me and show me the right way of doing this.
Msg 6551, Level 16, State 2, Procedure EmailSplitter, Line 3
CREATE FUNCTION for "EmailSplitter" failed because T-SQL and CLR types for return value do not match.
Here is what I am trying to achieve. Split a Email field in the database. For that I am trying to return an array using C# and then trying to call the UDF for the C#.
--1).CLR Code. (EmailSpitter.cs)
using System; using System.Collections.Generic; using System.Text;
namespace SQLTools { public class EmailSplitter { public static string[] Parse(string data) { string[] columnData; string[] separators = new string[1]; separators[0] = " ";
This has got to be simple... Every time I try to create this sproc I get the following error: Msg 156, Level 15, State 1, Procedure sp_AddTradeItemsToSelections, Line 5 Incorrect syntax near the keyword 'VIEW'. Its driving me nuts because I can't see anything wrong with line 5 where the view is being created. CREATE PROCEDURE [dbo].[sp_XXXXX] @UserdataIDInt, @BuilderIDInt AS CREATE VIEW TradeItem_Append AS SELECT TradeItemID, UserDataID, BuilderID FROM Selections WHERE (UserDataID = @UserdataID) SELECT TradeItem_Append.UserDataID, TradeItems.BuilderID, TradeItems.TradeID, TradeItems.TradeItem, TradeItems.Price, TradeItems.DDLRefNo, TradeItems.TradeItemID, TradeItems.Details, TradeItems.ProductType, TradeItems.Room, TradeItems.Notes FROM TradeItems LEFT OUTER JOIN TradeItem_Append ON TradeItem_Append.TradeItemID = TradeItems.TradeItemID WHERE (TradeItem_Append.TradeItemID IS NULL)AND(TradeItems.BuilderID = @BuilderID) DROP VIEW TradeItem_Append
Hello All im developing web application in VS2005(C#) while Creating SQLDATASource for Gridview im getting Following error cannot get web application service Please help ME Regards Balagangadharan.R
im creating a ecommerce website, and i need to create a database, im using visual studio .net and when i try to create a new database using VS.net i choose "use sql server authentication" and no matter what i type in for the login id, i get the following ado error
"ADO Error : ' Login failed for user "blank". Reason. Not associated with a trusted SQL server connection.'
Hi, When I try to create a CLR function in SQL 2005 (June CTP) I get the following error: Msg 6505, Level 16, State 1, Procedure Extenso, Line 1Could not find Type 'Extenso' in assembly 'ExtensoNET'. The assembly registers successfully, with no errors. I use the following command to create the function:CREATE FUNCTION Extenso (@Valor float)RETURNS VARCHAR(255)AS EXTERNAL NAME ExtensoNET.Extenso.EscreveExtensoGO The function's code is the following: using System;using System.Collections.Generic;using System.Text;using Microsoft.SqlServer.Server;using System.Data.SqlClient; namespace ExtensoNET{ public class Extenso { [SqlFunction(DataAccess = DataAccessKind.Read)] public static string EscreveExtenso(double? nValor) { //Valida Argumento if (nValor==null || nValor <= 0 || nValor > 999999999.99) return ""; //Variáveis int nTamanho; string cValor, cParte, cFinal; string[] aGrupo = { "", "", "", "", "" }; string[] aTexto = { "", "", "", "", "" }; . . . } return cFinal; } }} Thanks in advance, Anderson
Hi I have a Win2K SP1 server running SQL 7. Whenever I try to create a DTS package, I get a box headed "DTS CoCreateInstance" with "Error in the DLL" as the box text. I have tried removing and re-installing SQL 7 and have also applied SQL SP 1,2 & 3. There are no errors in the event log, just the popup error outlined above.
I don't see what's wrong with this command. >> Create View BCPOutBatchWeightTaiwan AS Select * from batchWeight a JOIN Geography b ON a.GeographyPtr = b.GeographyPtr where b.countrycode = 'tw' << I get the following error if I try to create it. >> Server: Msg 4506, Level 16, State 1, Procedure BCPOutBatchWeightTaiwan, Line 2 Column names in each view must be unique. Column name 'GeographyPTR' in view 'BCPOutBatchWeightTaiwan' is specified more than once. <<
But it runs fine if I run only th 'Select' part of the command without 'CREATE' line.
I am trying to create an assembly on a sql server 2005 machine but it gives me following error:
Msg 33009, Level 16, State 2, Line 2 The database owner SID recorded in the master database differs from the database owner SID recorded in database 'XYZ'. You should correct this situation by resetting the owner of database 'XYZ' using the ALTER AUTHORIZATION statement.
I tried using the alter authorization statement to change the owner. It did not work.
I am able to create same assembly on another test database but can not create it on this database.
i want to create table using another table but i am getting the following error.
Server: Msg 156, Level 15, State 1, Line 2 Incorrect syntax near the keyword 'AS'.
my query is:
CREATE TABLE errorlog AS SELECT * FROM log where 1=0 becos i a trying to copy the structure of the table log to table errorlog.please rectify my problem,please
We upgraded from SQL Server 2000 to 2005 recently. I'm attempting to create a credential for the first time and I keep getting the error shown below. Can anyone tell me what to do to resolve it? Thank You
TITLE: Microsoft SQL Server Management Studio ------------------------------
Create failed for Credential 'schjob'. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+Credential&LinkId=20476
I am trying to create an assembly on a sql server 2005 machine but it gives me following error:
Msg 33009, Level 16, State 2, Line 2 The database owner SID recorded in the master database differs from the database owner SID recorded in database 'XYZ'. You should correct this situation by resetting the owner of database 'XYZ' using the ALTER AUTHORIZATION statement.
I tried using the alter authorization statement to change the owner. It did not work.
The same assembly is created on another test database but can not create it on this database.
I just wondering where can I find the table of my database SuplliersDatabase. I need to know if it is already exists or not? I tried to look around but then still I can't find it.
(1) create view qcostcentre as select * from dbo.costcentre.dtblcostcentre
>ERR: Msg 208, Level 16, State 1, Procedure qcostcentre, Line 4 Invalid object name 'dbo.costcentre.dtblcostcentre'.
(2) create view qcostcentre as select * from costcentre.pcusers.dbo.dtblcostcentre
>ERR: Msg 7202, Level 11, State 2, Procedure qcostcentre, Line 4 Could not find server 'costcentre' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.
note: pcusers is a user of 2 database (costcentre & datamaster) with dbo_datareader owned & role schema .
When ever i tried to creat a diagram for the database i have i got this error:
Database diagram support objects cannot be installed because this database does not have a valid owner. To continue, first use the Files page of the Database Properties dialog box or the ALTER AUTHORIZATION statement to set the database owner to a valid login, then add the database diagram support objects.
the version of the database i am working in was backed up from another machine
I have a client who is getting an error when they try to create a report subscription. The error is as follows:
An unexpected error occurred: A subscription delivery error has occurred. ---> A subscription delivery error has occurred. ---> One of the extension parameters is not valid for the following reason: The account you are using does not have administrator priviledges. A subscription cannot be created for [email address].
The thing I find strange is that the client was able to create a shared schedule with no problems, so I don't see how they don't have administrator privileges. The shared schedules and subscriptions are created programmatically through a web application if that is of any importance.
I encountered something strange, deploying the following assemblies
CREATE ASSEMBLY [Sentry]
AUTHORIZATION [dbo]
FROM 'c:clrSentry.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS;
GO
CREATE ASSEMBLY [Sentry.XmlSerializers]
AUTHORIZATION [dbo]
FROM 'C:clrSentry.XmlSerializers.dll'
WITH PERMISSION_SET = SAFE;
GO
Sentry.XmlSerializers errored out with: Msg 6218, Level 16, State 2, Line 2 CREATE ASSEMBLY for assembly 'Sentry.XmlSerializers' failed because assembly 'Sentry.XmlSerializers' failed verification. Check if the referenced assemblies are up-to-date and trusted (for external_access or unsafe) to execute in the database. CLR Verifier error messages if any will follow this message [ : Microsoft.Xml.Serialization.GeneratedAssembly.XmlSerializerContract::CanSerialize][mdToken=0x600006e][offset 0x00000001][token 0x01000019]System.TypeLoadException: Could not load type 'Sentry.SentryDataService.SentryDataService' from assembly 'Sentry, Version=1.0.2764.18017, Culture=neutral, PublicKeyToken=null'. Type load failed.
The interesting thing is that when I deploy this locally on my machine, there are no problems whatsoever. Using sp_configure the servers appear to be set up the same way. The database also has trustworthy set on for both.
Hi all, when I create publication with New Publication Wizard. I get the following errors. Did someone meet this before? Can you help me? Thanks!
===================================
SQL Server could not create publication 'JK'. (New Publication Wizard)
===================================
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------ Program Location:
at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType) at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand) at Microsoft.SqlServer.Replication.ReplicationObject.ExecCommand(String commandIn) at Microsoft.SqlServer.Replication.ReplicationObject.CommonCreate() at Microsoft.SqlServer.Replication.Publication.Create() at Microsoft.SqlServer.Management.UI.CreatePublicationWizard.CreatePublicationThreadMethod()
===================================
Cannot promote the transaction to a distributed transaction because there is an active save point in this transaction. Changed database context to 'Test'. (.Net SqlClient Data Provider)
------------------------------ For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.2047&EvtSrc=MSSQLServer&EvtID=3933&LinkId=20476
------------------------------ Server Name: nxstudio-davidsql2k5_dc Error Number: 3933 Severity: 16 State: 1 Procedure: sp_addmergepublication Line Number: 1034
------------------------------ Program Location:
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)
The error occurs on the CREATE CERTIFICATE statement:
Msg 15208, Level 16, State 1, Line 1
The certificate, asymmetric key, or private key file does not exist or has invalid format.
I am logged on as a local admin when running the script. The SQL Server service account is also a local admin, and the account I am logged on as is sysadmin in SQL Server. This script works fine in my Dev environment and a separate Pre-Prod environment.
DataSourceView rdsv = new DataSourceView(); rdsv=dbNew.DataSourceViews.Add("datasourceviewname", Utils.GetSyntacticallyValidID( "datasourceviewname", typeof(DataSourceView))); rdsv.DataSourceID = dsNew.ID; OleDbConnection cn = new OleDbConnection(dsNew.ConnectionString); OleDbCommand cmd = new OleDbCommand( "SELECT * FROM tablename WHERE 0=1", cn); OleDbDataAdapter ad = new OleDbDataAdapter(cmd);
I want to create a datasourceview, thus use upper code .but my dataosurceview don€™t create and when I run that, in this line: (rdsv.Schema.Tables.Add(dss.Tables["tablename"].Clone());) appears one error that say€? Object reference not set to an instance of an object€?.
//************************// When I use another code like this for createting datasourceview: DataSet dset = new DataSet(); SqlConnection cn = new SqlConnection("Data Source=localhost;" + "Initial Catalog=DataSourcename;Integrated Security=true"); SqlDataAdapter da = new SqlDataAdapter("select * from tablename",cn); da.FillSchema(dset, SchemaType.Mapped, "tablename"); DataSourceView dsv = new DataSourceView("DataSourceviewname", "DataSourceviewname"); dsv.DataSourceID = "DataSourcename"; dsv.Schema = dset.Clone(); dbNew.DataSourceViews.Add(dsv); dbNew.Update(UpdateOptions.ExpandFull);
I compile that and any error don€™t exsit but after running I notice that €śdsv€? is empty and just the name of culomns exist and when I process the model in microsoft sql server mangement studio ,this error appears: Error (Data mining): In the 'structurename' structure, the 'columnname' column does not contain valid bindings to data and cannot be processed. Please help me . this is necessary for me because i need that very immediate. please if you can answer.
We are developing OLE DB provider for our database engine. Current version works with SSAS.
I tried to create an SSIS project. There I could successfully create 'Data Sources', 'Data Source Views' and I could even explore data on a selected table. That is, Analysis Services components are working fine.
When I create an OLE DB Source (input) with our provider. I could select a table from a list, but when I click 'Preview' or 'OK' I get following error.
=================================== Error at Package [Connection manager "localhost.SYSTEM.admin"]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E22. An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040E22 Description: "Non-NULL controlling IUnknown was specified, and either the requested interface was not IUnknown, or the provider does not support COM aggregation.". Error at Data Flow Task [OLE DB Source [1]]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "localhost.SYSTEM.admin" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed. (Microsoft Visual Studio) =================================== Exception from HRESULT: 0xC020801C (Microsoft.SqlServer.DTSPipelineWrap) ------------------------------ Program Location: at Microsoft.SqlServer.Dts.Pipeline.Wrapper.CManagedComponentWrapperClass.AcquireConnections(Object pTransaction) at Microsoft.DataTransformationServices.Design.PipelineUtils.AcquireConnections(IDTSComponentMetaData90 componentMetadata, Connections connections, IServiceProvider serviceProvider) at Microsoft.DataTransformationServices.DataFlowUI.DataFlowComponentUI.ReinitializeMetadata() at Microsoft.DataTransformationServices.DataFlowUI.DataFlowAdapterUI.connectionPage_SaveConnectionAttributes(Object sender, ConnectionAttributesEventArgs args)
Additional Info:-
OLEDB_SERVICES registry entry has value -4, i.e. DBPROPVAL_OS_ENABLEALL & ~DBPROPVAL_OS_TXNENLISTMENT & ~DBPROPVAL_OS_RESOURCEPOOLING. Same is returned for DBPROP_INIT_OLEDBSERVICES property. ThreadingModel registry entry has value 'Apartment'. Same is returned for DBPROP_DSOTHREADMODEL property. We also tried all combinations for ThreadingModel=Free|Apartment|Single and OLEDB_SERVICES=DBPROPVAL_OS_ENABLEALL/DBPROPVAL_OS_DISABLEALL/(DBPROPVAL_OS_ENABLEALL & ~DBPROPVAL_OS_TXNENLISTMENT & ~DBPROPVAL_OS_RESOURCEPOOLING) We are confident that we support COM aggregation on all the objects for which we return interface pointers. Note that we have used NO ATL in the whole provider code (otherwise, we might not be in so much struggle, probably). Entire code is pure C++ (compilable on almost any platform). After some googling, we found that this could happen if there is any memory corruption as well. We do not see any traces of this in our debug logs. I have no clues on how to resolve this. Can somebody help me? I am stuck for long time.