Answer Questions

• gabegabe Cannot connect to AdventureWorks db

  All, I am running SQL05 Dev with AdventureWorks on my desktop with local admin rights. I also have it configured to connect locally since it's a DEV version of SQL. When I use either of the connection strings below I get the error message at the end of this post. Thanks, >Scott Here are the strings: Data source=(local); initial catalog=AdventureWorks or Data source=(SQLDEV05); initial catalog=AdventureWorks Here is the error message: TITLE: Microsoft Report Designer ------------------------------ A connection cannot be made to the database. Set and test the connection string. ------------------------------ ADDITIONAL INFORMATION: An error has occurred while establishing a connection to the server. When c ...Show All

• Jon Langdon - MSFT Problems while converting string to date.

  Hi Guys, Source Column Data type: Varchar Destination Data Type: smalldatetime We have 2 columns effective_Date and Expiration_Date: the format of data in these columns is not consistent. Sample values: 1/23/2006 Tuesday, June 21, 2005 1/0/1900 8-Mar-06, NULL Now, i want to convert to a standard date format like "2006-03-08 00:00:00". please let me know the steps to do.. Thanks in Advance. Sri Thanks Rafael...atleast i have something to start.. Where can we implement case statement .. can we do that in derived column transformation let me try this.. Thanks Sri That looks challenging....may be you can use a script component with Regular expressions to parse t ...Show All

• re infecta Converting a HEX string to its binary representation

  I am trying to take a hexadecimal representation of a binary number and convert to the true binary representation so that I can compare it against a binary field in one of my tables. After reading the documentation it seems I should be able to do this with the CAST or CONVERT function. However it does not appear to be working correctly. Can you tell me why this T-SQL code produces the wrong binary value: DECLARE @value binary(16) SELECT @value = CONVERT(binary, '0764DE49749F274EB924E1552FFE09EC') PRINT @value This prints out: 0x30373634444534393734394632373445 That is not correct it should be: 0x0764DE49749F274EB924E1552FFE09EC Thanks Chris: If you are really only dealing w ...Show All

• Cyberjunkie Credentials used to run this report are not stored

  Hi all, When I create a new report subscription in report manager, I got this error Credentials used to run this report are not stored any idea Thanks, Jone Thanks, Sharmila. I use user name = 'sa' and password = 'access' to access my datasource, and I tried to use them in 'Credentials stored securely in the report server', and checked both or first ckeckbox under this selection, and I got Logon failed. (rsLogonFailed) Logon failure: unknown user name or bad password. (Exception from HRESULT: 0x8007052E) what did I miss Thank you! Jone You have you have the credentials stored in order to create a ...Show All

• Steve Hempen How to connect via ODBC

  Hi, I would like to connect in SqlServer 2005 via ODBC SQLServer, how are the steps 1 - How can I connect ODBC 1.1 - I need SqlServer 2005 name - ok 1.2 - Which option should I take in order to login Windows NT authentication or Sql Server authentication Which one is the best option Cheers, Alessandro 1.2 - Which option should I take in order to login Windows NT authentication or Sql Server authentication That depends - Windows authentication is almost always better but if you cannot use it then SQL Server authentication is fine as long as you have it enabled on the server and you have created a login on the server. With Windows authentication you can easily use existing Windows domains, groups and u ...Show All

• lookup Parameter problem with OLAP and spaces

  Hello all, I have an issue with parameters. Let me explain..... I have report1 which uses analysis services as it's data source. The report displays a list of our companies sites along with some costs etc. Site names can include spaces and numbers but they are stored in the relational database as varchar[50]. On the relevant text box of report1 I have defined a data detsination using report2 and passing it the site name field.value. On report2 I have added the site name field as a filter dimension and set it as a parameter. On the report parameters I have set the parameter as being non queried. When I run report1 and click a value to "drill down" it only works for values that have no spaces and no numbers. If If conve ...Show All

• Salvatore Di Fazio Join 2 table with our relation

  is there any way to join to table with out relation I mean conect tow table row by row Phil is right about that. I think that is also what Dinakar is getting at. If there was something like rank# in tables may we could joine them !!! Mugambo wrote: Phil is right about that. I think that is also what Dinakar is getting at. As it is now, the data is "dumb" in that there is zero intelligence built into the results and that those results don't mean a thing. There are no real questions that can be answered with the way the results are assembled when there aren't any data relationships. What happens when 11 is removed fro ...Show All

• Thomas2054 Analysis Services 2005: "OLE DB error: OLE DB or ODBC error : Access denied" using an ORACLE source

  Hi, The source DB is in Oracle 9.i . I use the native pilot from Oracle. The connection test is OK. I can also explore the data in the Data Source View whithout any problem. But when I try to process the dimension, I get the error message "OLE DB error: OLE DB or ODBC error : Access denied.." . I tried with the Native pilot from MS for Oracle but got the same result. Could anyone help me Rg. Hi Edwards, I changed the service account in the "SQL server configuration manager", for Analysis services and now it works fine :D Thanks for your help, Sebastien During deployment of dimensions ( and cubes as well ) Analysis Server tries ...Show All

• Chaman Zinga Replication Error 7601

  I am getting the following error when i try to replicate from a sql server 2005 box to another sql server 2005 box: "Cannot use a CONTAINS or FREETEXT predicate on table or indexed view 'UserData' because it is not full-text indexed. (Source: MSSQLServer, Error number: 7601) Get help: http://help/7601" Now ive spent 3 days trying to fix this. Ive googled it many times and came across some suppossed workarounds like running sp_changepublication @publication = 'yyy' , @property = 'sync_method' , @value = 'native' , @force_invalidate_snapshot = 1 OR sp_changepublication '<yourpublicationname>','sync_method','native' SQL tells me that they apply succesfully but yet when i try to replicate, alas the same error. Anybody h ...Show All

• kkennedy1008 OpenQuery With Large String?

  Hi, I declare a variable @MdxSyntax as NVARCHAR(4000) to store MDX OpenQuery syntax on Store Procedure. SET @mdxSyntax = ' SELECT * INTO ##BU01505100 FROM OPENQUERY (MOJOLAP, '' WITH '') ' EXEC sp_executesql @mdxSyntax But maybe the syntax too long, system response syntax unclosed! So, I change @MdxSyntax as NVARCHAR(MAX), but it still response syntax unclosed. Why It's the limit of OpenQuery or MDX Thanks for help! Note: OPENQUERY does not accept variables for its arguments. You have to use the query as String values on OPENQUERY. ManiD, Thanks for your reply! But my point is no matter what I declare @mdxSyntax as NVARCHAR(4000) or NVARCHAR(MAX) , the query result always respons ...Show All

• r3n Cannot connect to the SQL 2005 Express

  I created test project and want to use SQL 2005 database inside. 1. I created Test database with DEFAULT rights 2. I go into server explorer, press Add Connection. Shows message box for connecting to the server: <PCNAME> using windows authntification. But NO database allowed to view :(. Which setting should be set for SQL server database to i could connect and work with it I do not use connection string - I use Visual Studio User Interface for connecting. I could try to connect as blind but I want to get list of the databases and connect on click. Could you please give me advice how I could to do that The list is for server, a server can host multiple databases. In order to see your local server in ...Show All

• Jon Stalnaker trigger question

  I have the need to use triggers in my database, but am a little foggy on something that reading the official msdn help did not quite clarify. If for example there is a trigger which is triggered when a certain record is updated (DDL ) Is it possible to see what the values were before and after the update I need to create a trail of updates such that the information can be rolled back if need be. Any help with this would be greatly appreciated. Thank you :) PS. If anyone notices that this is a re-post from another forum .... I put the other one in the wrong place accidentally :( ...Show All

• TheresaKad detach 6.5 compatible database in studio - HOW?

  I've got two databases showing in the studio tree, but they are marked 6.5 compatible and cannot be expanded. Worse, there is no way (apparently) in the menu of either database to remove, delete or detach them. How can I get these out of the system Thanks in advance. randyv Try to run this transact sql script against your database: USE databaseName GO EXECUTE sp_dbcmptlevel databaseName , 90 GO EXEC sp_updatestats GO What about using the TSQL statement for dropping / detaching the databases (DROP DATABASE / sp_detach) HTH, Jens K. Suessmeyer. --- http://www.sqlserver2005.de --- No, that doesn't work b ...Show All

• shafiqm deploying reports database to multiple servers

  Hi, i have some 35 customers, all of whom need a copy of the reports i have designed how do i quickly and simply roll out my reports. the databases are generally , but not always given the same name, and the user credentials will be different but the reports need to be the same. is this possible Regards, Russ Cheers John, im wondering how easy it is to do option 1. is there an example available that you know of Regards, Russ You have two basic options: 1.) Script out creating the reports in the database and provide the scripts to your users. 2.) Detach your database, and give it to your users. They can then attach the d ...Show All

• Hemant Kumar Is there a way to display messages? To see more details why it is not running?

  Hi can not execute a package! If I run the package from Visual Studio, it works; If I deploy it and execute it, it works; If I log in the server an execute it, it works; If I execute it from a Task using the SQL Server Agent it works fine until I have a DataReader Source in the Package! Is there a way to display messages To see more details why it is not running Thanks! Well, ensure that the account running the SQL Server service has permissions to the DataReader source. I doubt that it does based on the scenario that you've described. Log into the server and open up the services control panel. Locate the SQL Server service, and open it up. Note the us ...Show All