Answer Questions

• Davids Learning DDL Extraction

  Does anyone know how to pull DDL create statements out of a SQL Server 2005 EE database for existing objects I'm mainly concerned with indexes and constraints. If possible I can create the statements myself if I can get all of the information out of the databse. Thanks in advance You can look in the system views and get all the information you need. INFORMATION_SCHEMA prefixed views are a more denormalized version of the sys.XXX views and will contain most everything you would need to script create statements. Thanks I'll check it out. I was looking for something that is not using .NET code and is just SQL. Hi, you can either ...Show All

• GraemeE LDAP query returning erroneous values

  I'm trying to query an LDAP server from a stored procedure written for the CLR but not getting the expected results. The code is as follows: <Microsoft.SqlServer.Server.SqlProcedure()> _ Public Shared Sub LDAP_UserExists(<Out()> ByRef exists As Boolean , ByVal username As SqlString) Dim adspath As New StringBuilder() adspath.Append( LDAP://[.......]/ou=Members/cn= ) adspath.Append(username) If username.ToString().Length > 0 Then Dim uobject As New DirectoryEntry(adspath.ToString(), "" , "" , System.DirectoryServices.AuthenticationTypes.Anonymous) If Not (uobject Is Nothing ) Then exists = True Else exists = Fal ...Show All

• TWild Cannot insert duplicate key row in object 'MSmerge_genhistory' with unique index 'unc1MSmerge_genhistory'

  I have 1 client who keeps running into the following error on the subscriber and merge agents > “Cannot insert duplicate key row in object 'MSmerge_genhistory' with unique index 'unc1MSmerge_genhistory'.” Last time we got this error I ran a reindex on table MSmerge_genhistory on the publisher database, I then successfully generated a new snapshot and the subscribers started to synchronize again. This time around I keep getting the error even after I follow these steps (I also ran all the jobs to clean up replication). The last time I ran into this error I created a job to reindex msmerge_genhistory on a nightly bases in an effort to avoid this problem. Can somebody please provide me with a workaround and also the rea ...Show All

• Gregory English Service Pack

  How would one determine the SP for Reporting Services. Is it different from the SP of SQL Server itselves. This is for 2005 It is still the same for the new service packs. You install a service pack on an instance not on the entire pc. Greetz, Geert Geert Verhoeven Consultant @ Ausy Belgium My Personal Blog Does this mean the we can have instances where the SP levels are different for SQL Server and Reporting services I thought when a SP is applied on a server it is applied to all the services There might be an easier way to do this, but here's one way to do it. In SQL Server Management Studio's object explorer, connect to your Database ...Show All

• humble.apprentice Totals on last page only - How?

  Just started (today) using RS2005. Me and my Brian Larson MS RS2005 book. Can't figure out how to get my Grand totals to ONLY print on the last page of my report. They are calculating correctly, but printing on every page. I guess I wouldn't mind if the totals were page specific, but it's not even doing that... Thanks!! It tells me I can only use the globals you mentioned in the page header and footer. I try that and then it tells me I can't sum fields in the page footer.... Any ideas. Thanks visibiily-> hidden property of the cell. Thanks!! I would have never thought of that...... you can hide them by using the expression iif(Globals!PageNumber <> Globals!TotalPages, true, false) Hi ...Show All

• DanBog Restart of SQL Server Agent needed - every day

  Hi, I'm having a very strange problem here. I've got a few SSIS-packages here which runs as Server Agent Jobs. They are scheduled to run every hour. This is what happens: on the first run, all packackes are running and completing all steps successfully. After that, at the second start of the jobs, activity monitor shows "executing step 1", but nothin happens anymore. To get the jobs running again, I need to restart SQL-Server Agent Service. I really have no clue whats wrong with my agent. Any suggestions/hints Regards, Jan Hi, I was wondering if you've found a solution to this problem. I haven't released my project yet but I am concern about this message in my logs. My logs ...Show All

• rockworld Additive or non-additive dimension depending on the measure group

  Hi, I have a dimension called [Year of Account]. For most of my measure groups all values are additive over this dimension - no problem there. Unfortunately I have one measure group that is NOT additive over this dimension. I wanted to deal with this by removing the [Year of Account].[All] member from the dimension but I only want to remove it for this one measure group. As far as I know that's not possible. (Or am I wrong ) Any other ideas about how I should deal with this Any suggestions welcome. Thanks Jamie I think you are probably right, one possible work around would be to use an MDX script assignment to override the value in the relevant subcube so that the users did not see misleading i ...Show All

• Nekodar how to generate as "enter" function in SQLSERVER?

  For example: select 'sp_refreshview ''' + [name] +''' go ' from Sysobjects where xtype='v' order by [name] I want the result is follow: sp_refreshview 'lt.trxdetai' go sp_refreshview 'ps_account' go thanks You can do below and use the text mode output. There is no need to put CR and LF. select 'exec sp_refreshview ''' + [name] +''';' from sysobjects where xtype='v' order by [name] If you need to generate the call as different batches then simply add a CR & LF as part of the string itself like: select 'exec sp_refreshview ''' + [name] +''' go ' from sysobjects where xtype='v' order by [name] Modify your statement to add char(13) as ...Show All

• Terry A. King Can Fact table link to more than 16 dimensions?

  Hi, I am new to building cubes. I am trying to build a cube which includes: 7 database dimensions 22 cube dimensions (19 cube dimensions are liked to 4 table dimensions) The problem is when I try to make the PK of the fact table to include the 22 fields I receive an error that the PK cannot be more than 16 fields. What should I do Thanks in advance, Aref  It is a bit strange design. You should not have that many cube dimensions in you only have 7 database dimensions. Make sure you take a look at the AdventureWorks sample database for example of how to build your cube. Going through tutorial is also a good idea. Edward. -- This posting is provided "AS IS" with no warranties, and confers ...Show All

• lezi Restoring using smo

  I have built a VB.NET app that uses SMO to restore a database from a backup device to a database. This works great if I restore the backup into the original database. If, however, i try to restore the backup into ANOTHER database, it fails. My code looks like this (only important parts are included): db1 = New Management.Smo.Database db1.Name = Me .lstDatabase.SelectedItem restore = New Management.Smo.Restore restore.Action = Management.Smo.RestoreActionType.Database restore.Database = db1.Name backDeviceItem = New Management.Smo.BackupDeviceItem( Me .lstBackups.Text, Management.Smo.DeviceType.LogicalDevice) restore.Devices.Add(backDeviceItem) restore.ReplaceDatabase = True restore.SqlRestore(SqlServe ...Show All

• Zajda Setting time to zero in a datetime object

  Anybody know of an easier way to set the timepart to zero in a datetime object than: declare @day as datetime select @day = getdate () select @day = dateadd ( hh ,- datepart ( hh , @day ), @day ) select @day = dateadd ( mi ,- datepart ( mi , @day ), @day ) select @day = dateadd ( ss ,- datepart ( ss , @day ), @day ) select @day = dateadd ( ms ,- datepart ( ms , @day ), @day ) Thanks for picking me up, Mani; I had forgotten about this snag. Dave Here's another way to do it. It's faster than the previously mentioned approach. Select @Day = DateAdd(Day, DateDiff(Day, 0, @Day), 0) To explain how this works... Fir ...Show All

• DaveSussman Parameter area in RS2000

  Hi, Is there any way to have some kind of control over the controls in the reporting services htmlviewer parameter area. What i really wan't to do is set a minimum width to the combobox's in that area, or a fixed width,it would be good too. Thanks. To keep it short and simple, no. HTH, jens Suessmeyer. --- http://www.sqlserver2005.de --- Oh sure yes there is a css file, I was not aware that you can influence the display behaviour of the controls by these files. --- http://www.sqlserver2005.de --- At least RS2005 we can. I think that in RS2000 things are a bit diferent. I can’t seem to find the file htmlviewer.css (this is the file we can tweak in RS2005 t ...Show All

• AlwaysTrying Can't store chinese in SQL Database

  update Food set FoodName = ' 杏仁' where ID = 100 in database as Hi SuperM, You should use Encoding support from your .NET Application. To Encoding: Encoding target = Encoding.GetEncoding( "GB18030" ); Byte[] buffer = target.GetBytes( text ); return Convert.ToBase64String( buffer ); From Encoding: Encoding target = Encoding.GetEncoding( "GB18030" ); Byte[] buffer = Convert.FromBase64String( text ); return target.GetString( buffer ); You could use GB18030 to simplified or Big5 to traditional. Later, value converted you send to store procedure. Good Coding! Javier Luna http://guydotnetxmlwebservices.blogspot.com/ thanks, the field n ...Show All

• Malacki Always two queues?

  I'm just getting around to understanding notification services in sql 2005 and I have been working through some examples.  I'm curious as to why there are always two queues and two corresponding services being set up even when both queues/services exist in the same database.  Here is my ultimate goal.  I want to have triggers put messages on the queue for various stored procedures to handle asynchronously - i.e. table xyz is updated, and I need a stored proc to take the updated values and possibly generate or update rows in another table.  When I tried to set up one queue and one service, nothing ever seems to get put on the queue: Create Message Type TestMessageType Validation = Well_Formed_XML; Create Contract T ...Show All

• Chuff TSQL Code Checker

  Is there a tool similar to FXCop that will check for TSQL ( or just SQL ) errors , ommissions etc and suggest code improvements Also, along the same lines, are there client-side JavaScript and VBScript tools similar to FXCop Thanks Short answer: no. Long answer: not really... For SQL Server 2000, there is a SQL Server Best Practices Analyzer that does some basic code-checking of T-SQL on objects in your database. http://www.microsoft.com/downloads/details.aspx FamilyID=B352EB1F-D3CA-44EE-893E-9E07339C1F22&displaylang=en For SQL Server 2005, there is nothing available at the moment. We are making a new version of SQL Server BPA, but it will not have the T-SQL checking capabilities. My team's blog talks about the next ...Show All