Answer Questions

• Dan Crowell Identifier! [problem in passing a lengthy string to an SP]

  exec sp_SearchProductAdvanced "(name LIKE '%a%' Or name LIKE '%b%' Or name LIKE '%c%' Or name LIKE '%d%' Or name LIKE '%e%' Or name LIKE '%f%')and (salesprice between 3 and 10) " ,null after executing i got this error: The identifier that starts with '(name LIKE '%a%' Or name LIKE '%b%' Or name LIKE '%c%' Or name LIKE '%d%' Or name LIKE '%e%' Or name LIKE '%f%')and (salesprice ' is too long. Maximum length is 128. how can i solve this problem. so that i can use more values . my lenght exceeds 128. is there any way to get rid of this. in my SP i have declared the parameter variable as @criteria nvarchar ( 1000 ) Hi Chitrsen Aujikar , Thank ...Show All

• Akinja-Earl Report builder multi language

  Hi! I’m designing a Report Model to be used by the Report Builder. Now some reports should be viewed in more than one language. Is there a way to do this without creating a new model and copying the reports Fields names (eg column headers) should be translated and for some fields the model should choose another table column (eg ProductdescriptionFrench – ProductdescriptionEnglish). Thanks ...Show All

• Dirk Haest How to form a list ?

  Hi,   I have a table like this:   CatalogID  CatalogName  RootID --------------------------------------------------   1                 Microsoft              0   2                 Macromedia          0   3                 Office                   1   4     ...Show All

• jwraith Calculated Members and Aggregation

  Hi, I am still in AS 2000 and would like to know if it is possible to have calculations performed at lowest granular level of the cube before aggregation. Perhaps I am missing a basic understanding on this but what I am hoping to achieve is to hold parameters at the lowest level in a separate measure ‘Param’ (separate column in the Fact table) and then reference to those parameters in a calculated member with the resulting values held in the default measure of the cube before being rolled up in the aggregations to higher levels. Currently in the higher levels input parameters are being aggregated before determination of the calculated member value. Will much appreciate your suggestions on whether it is possible ...Show All

• KhRo Receive all messages on queue

  Hi i am trying to create a batch process then commit for all messages on the queue. The problem i am having is when i run my query (As below) I only receive the first message and the corresponding end dialog for the message although i have 2000 records sitting in the queue. It is my understanding that receive without any criteria i.e top(1) or where clause should select everything of the queue. I tried receive top(100) expecting 100 records but still only got 2 back. any help appreciated. WAITFOR ( RECEIVE queuing_order , conversation_handle , message_type_name , message_body FROM [RMIS_COMMS_Queue] INTO @tableMessages ), TIMEOUT 2000 ; Each RECEIVE returns ...Show All

• hbilan How to check is SSAS really clear the cache

  Hi,Does anyone know how to check if SSAS 2005 really clear the cache, after service restart or I run a ClearCache MDX scripts Thanks.J Lim Well, after service restart cache is obviously cleared, since the entire process was gone - so was all of its memory. If you are in doubt whether ClearCache really does it job, you can run simple MDX query to the cube - i.e. SELECT Measures.DefaultMember ON 0 FROM Cube, and watch profiler events - you will see that the data is coming from the disk. You can find some information about what events to watch and how to interpret them here: http://www.sqljunkies.com/WebLog/mosha/archive/2006/01/05/cache_prefetching.aspx HTH, Mosha ( http://www.mosha.com/msolap ) ...Show All

• Doffen Optimistic vs. pessimistic locking

  I am confused, I'll admit that outright. A lot of entries in these forums recommend optimistic locking for most cases. Well, I have a very simple case (like everyone else, I bet :-)) and it seems to me pessimistic locking is the right answer: User A loads an item on screen for maintenance. When user B wants to load the same item on screen for maintenance, he/she should be presented with a message "Item in use by someone else". That way they know they can't perform maintenance on the item (as yet). This can be achieved with pessimistic locking, by locking the row that is being loaded for maintenance and only unlocking it after an update or after another item is loaded in maintenance. The lock would be in update mode. This w ...Show All

• legojedi Limit number of rows returned

  I am developing a top 20 report. How do I limit the report to only return up to 20 rows (detail lines, no groups) Thanks Hello, Use Dyamic Queries like ="Select top " & Parameters!t.value & " pub_id,pub_name from dbo.publishers where city=ISnull(@City,City)" & order by & Parameters!col.Value To populate the Col Parameter where the user is going enter the sorting Column.Use the Query like this Select a.name from syscolumns a join sysobjects b on a.id=b.id and b.xtype='U' and b.name='WBHierarchy' --tablename I did not try ,Hope it will work Here i have given the top rows selection also as parameter Pls reply,if it is working. Thank you, R ...Show All

• Frances83 Transactional Repln,how to modify the width of a column of a table which is replicated

  Hi All, Is there a way by which we can modify the width of a column of a table which is being replicated without touching the ongoing transactional replication This is for MSSQL2000 Transactional Replication. I know (and successfully tried) that we can add a column to a table and that gets propaged to the replicate database and indeed the added column gets reflected there. How to add a column sp_repaddcolumn or Right Click on the Publication-Properties and it shows a button to Add a Column. This is what I have tried for modifying the width of a column of a table participating in Transactional Replication from varchar(10) to varchar(100) MH (source) -> MH1 (Replicate) The column “col1” had width of varchar(10) and this was altered to var ...Show All

• Admann SQL Reporting Services

  I have an excel report, with 10 columns on it. And each column has auto filters on it in Excel. When I develop this report, in SQL Reporting services 2000, how do i get the auto filters on the report. Basically, my question is, is there a way to GET AUTO FILTERS on a report in SQL RS 2000. Pl advise. Thanks. Adding auto-filters to Excel worksheets is not currently supported by the Excel renderer. You'll have to add the auto-filters after the workbook is exported from RS. -Chris Hi Chirs, Thanks very much for the reply. But the dilemma is that the users dont want the report rendered in Excel anymore. I am still going to render the report as a web link. Are there any options to add auto ...Show All

• Anurag Srivastava Exam 70–443 (MCITP)

  Hi, Somebody can speak (tips) about exam Exam 70–443: PRO: Designing a Database Server Infrastructure by Using Microsoft SQL Server 2005 Tips Amount Questions Time :-) you know we can only recommend official stuff here. http://www.microsoft.com/learning/exams/70-443.asp ... also the book and the practice tests will give you a better idea of what to expect in the exam which is worth gold. all i can say is buy the 70-443 study guide book from amazon and signup for the practice test on www.measureup.com best way i think, the book will give you a voucher for 15% off when you sit the exam and if you signup to the newsletter from www.measureup.com they'll send you a voucher for 25% off their tests (runs january 2007) ...Show All

• The_magament The configuration of the AdminConnection\TCP protocol in the SQL instance SQLEXPRESS is not valid

  Hello, I am getting the following warning in the Application Event Log: The configuration of the AdminConnection\TCP protocol in the SQL instance SQLEXPRESS is not valid. The warning is logged by the SQLBrowser service. This is happening on Windows 2003 server with SQL Server Express 2005. On the same machine SQL Server 2000 is installed. I am currently unable to connect from remote clients to the instance of SQL Express and I believe it has to do with this warning (I've tried all the usual remote connection troubleshooting without success - enabled protocols, SQL Browser Service running, there is no firewall on the server, I disabled firewall on the client when trying to connect). Does anyone know how to resolve this iss ...Show All

• vkv Remote update having a linked server takes forever to execute

  UPDATE CD SET col1 = SR . col1 , col2 = SR . col2 , col3 = SR . col3 , col4 = SR . col4 , col5 = SR . col5 , col6 = SR . col6 , col7 = SR . col7 , col8 = SR . col8 , col9 = SR . col9 , col10 = SR . col10 FROM LNKSQL1 . db1 . DBO . Table1 CD join Table2 USRI on USRI . col00 = CD . col00 join table3 SR on USRI . col00 = SR . col00 Here, I'm trying to tun this from an instance and do a remote update. col00 is a primary key and there is a clustered index that exists on this column. When I run this query, it does a 'select * from tabl1' on the remote server and that table has about 60 million rows. I don't understand why it would do a select *... Also, we migrated to SQL 2005 a week or so back but before that ...Show All

• Álvaro Peñarrubia Problem with creating a primary key in SMO

  The following is the code I am using to create a primary key on a table. When I run this code I get this error. " Cannot create Index '[PKI_BatchIngredientKey]' if parent is not yet created. " Public Sub AddPrimaryKey() 'Connect to the local, default instance of SQL Server. Dim srv As Server Try srv = New Server( "acer-1\sqlexpress" ) 'Reference the AdventureWorks database. Dim db As Database db = srv.Databases( "PS_Test" ) 'Declare a Table object and reference the HumanResources table. Dim tb As Table tb = New Table(db, "BatchIngredients" ) Dim idx As Index idx = New Index(tb, "PKI_BatchIngredientKey" ) 'Add inde ...Show All

• Ivan Selchenkov OLEDB Destination Error in SSIS Package not returning error column/desc

  I have a SSIS package that reads data from a dump table, runs a custom script that takes date data and converts it to the correct format or nulls and formats amt fields to currency, then inserts it to a new table. The new table redirects insert errors. This process worked fine until about 3 weeks ago. I am processing just under 6 million rows, with 460,000 or so insert errors that did give error column and code. Now, I am getting 1.5 million errors. and nothing has changed, to my knowledge. I receive the following information. Error Code -1071607685 Error Column 0 Error Desc No status is available. The only thing I can find for the above error code is DTS_E_OLEDBDESTINATIONADAPTERSTATIC_UNAVAILABLE To add to the confus ...Show All