Answer Questions

• Lorry Craig How to change identity to an existing column?

  Hi can anybody tell , Is it possible to alter existing column to add IDENTITY Thanks in advance Thank u akbar Yes It is possible to add Identity Column to a table already have some data. BUT there is no such a command to alter a column to be an IDENTITY Column. You have to go through the following steps. 1. Create a same table with the IDENTITY COLUMN. but with diffrent Name 2. Move all data to the New Table, with IDENTITY INSERT ON option 3. Drop All constraints from the OLD table 4. Drop the OLD table 5. Rename the new table as the OLD table 6. Attach all constraints to the new table ... Hope that it will help ...Show All

• duck thing Identify queue that activated sp ?

  Hi there I have multiple queues with the same activated stored procedure (for various reasons we are trying this scenario). My biggest obsticle is i cannot figure out a way to determine with the activated sp which queue caused it to activate. Basically i need to make the sp dynamic, so that no matter which queue activated the sp the sp can determine the queue name and use that dynamically to do the receive command from the right queue. I am sure it is possible since sys.dm_broker_activated_tasks shows how many sp's are activated by each queue, however the sp name is the same for all queues so that does not help me. How do i determine within an activated sp which queue caused it to activate Thanx ...Show All

• Khurram Javaid How to check null or '' in date field in expression and ...............

  I have a column say 'ActivationDate' which is a (database timestamp [DT_DBTIMESTAMP]) which I want to replace with an expression in derived columns The condition is if 'ActivationDate' field is null or '' then 'Null' else 'ActivationDate' I am struggling to write this condition. Without condition i.e. at present it saves the value in this database '1753-01-01 00:00:00.000'. In the preview the 'ActivationDate'field does not show any thing so I recon it is either null or '' If you put a data viewer on the output path of the derived column, before inserting the result of the expression, what do you see for the activation date column The expression you have now doesn't really do anything.. ...Show All

• Declan_ Script task: Dts.Logging.Log

  I am trying to understand the use-case of Dts.Logging.Log. Following BOL, I see that I can use Dts.Log to send a custom log message to my log provider. The following example code produces an event called "User:ScriptTaskLogEntry", with the message "My custom message here". Dim dataBytes(0) As Byte Dts.Log( "My custom message here" , 0, dataBytes) I would like to be able to produce my own custom event (lets call it "MyCustomEvent"), as well as a custom log message. I thought it would be possible via Dts.Logging.Log, which requires that I specify an event name, in addition to a log entry. However, when I place the following code in the script task, I do not get any logging whatso ...Show All

• caligula View 'order by' not working

  Hi there, I have a view created using the following code. The view works perfectly but does not order by the name column as I've asked it to do. In the view designer if I click on execute then the order is applied but if I save the view and run it externally (i.e. in an ASP page or within the management terminal) it does not order correctly and seems to order by the Id column. Any help would be much appreciated. Here's the code: SELECT TOP (100) PERCENT dbo.Members.DivisionID, COUNT(*) AS Members, dbo.Country.Name FROM dbo.Members INNER JOIN dbo.Country ON dbo.Members.DivisionID = dbo.Country.CountryID AND dbo.Members.CountryID = dbo.Country.CountryID GROUP BY dbo.Members.DivisionID, dbo.Country.Name ORDER BY dbo. ...Show All

• 5letters Confusing timeout; bug in deleting cubes and dimensions?

  Hi, I got this error when I deploy a mining model. -- Error 1 OLE DB error: OLE DB or ODBC error: The query has been canceled because the estimated cost of this query (628) exceeds the configured threshold of 300. Contact the system administrator.; 42000. 0 0 Error 2 Errors in the OLAP storage engine: An error occurred while the dimension, with the ID of 'Revenue Labeled Prod ~MC-LPROD', Name of 'Revenue Labeled Prod ~MC-LPROD' was being processed. 0 0 -- I don't understand why this happens. Previously I've created one cube and 4 dimensions. I got the same error before. I think the cube was the culprit so I removed the cube and dimensions. After removing them, I build the project. Successful. But fail again when processing ...Show All

• zyx100 Connection String Encryption for SQL Server Mobile

  I'm seeking suggestions on how to go about encrypting a database connection string in the compact framework. It's something that I thought would be relatively straight forward as I've accomplished it without much difficulty in the full framework, but I'm stuggling to find a solution. I've been trying to implement a solution using public/private key asymmetric encryption using the RSA provider. I've essentially been trying to encrypt the connection string once on my development machine so that I can include both the encrypted connection string value and public key in either code or an external file. I'd then keep the private key out of harms reach. The problem I've encountered in doing this is that that RSA provider seemingly only allows ...Show All

• MicahN Combine multiple results of subquery

  Table users: userid, name, added... Table groups groupid, groupname... Table groupadmins: userid, groupid The users to groups relationship is many-to-many, which is why I created the intermediate table. I would like to return a recordset like: userid, name, groupids 12344, 'Bob', '123,234,345' If I try to just select groupid from groupadmins: select userid, name, (select groupid from groupadmins where groupadmins.userid = users.userid) as groupids from users then I'll get an error that a subquery is returning multiple results. Some users are not group admins and those that are may have a single or multiple groups. The only thing I can think of is to select the groupids seperately and use a cursor to loop through the results ...Show All

• gfhyang Login failed for user ''. The user is not associated with a trusted SQL Server connection.

  Hi all, Can someone explain it to me why I am getting the following error when I try to connect SQL server express with .NET 2.0 Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Login failed for user ''. The user is not associated with a trusted SQL Server connection. Here is my code and i am using windows authentication: <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.SqlClient" %> <% Dim connAkaki As SqlConnection Dim cmdSelectAuthers As SqlCommand D ...Show All

• Dirk Haest Disable a task in the current package programmatically?

  Hi, I have a package comprising a number of Data Flow Task steps, to import various tables of data from some Access databases into SQL Server. The name of some of these Access databases will change depending on the date, e.g. last year's data is currently in a database called "2005data.mdb". At year end this will be superseded by "2006data.mdb". The Access databases are within a 3rd party system so I have no control over the file names. I have a Script Task that checks the current date, and changes the name of an Access database in the connection string to reflect last year's date. But to complicate matters, last year's file might not exist. So the Script Task checks whether the file name exists. If not, I woul ...Show All

• Cameron D Error loading http://localhost/ReportServer

  Hi! I've installed the Report Server but when I try to go to http://localhost/ReportServer I get the following error: Reporting Services Error An internal error occurred on the report server. See the error log for more details. (rsInternalError) And the log in the c:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\LogFiles\SQLDUMPER_ERRORLOG.log file is this: 12/01/06 12:51:40, ERROR , SQLDUMPER_UNKNOWN_APP.EXE, AdjustTokenPrivileges () failed (00000514) 12/01/06 12:51:40, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Input parameters: 4 supplied 12/01/06 12:51:40, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ProcessID = 5344 12/01/06 12:51:40, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ThreadId = 0 12/01/06 12:51:40, ACTION, SQLDUM ...Show All

• Hiral Auto hide not working properly

  When I start Management studio for the first time on boot the Auto hide (Pin icon) does not work. I have to pin it first and then unpin it to make it work. Initially I thought it happens only on my machine but same thing I tried on couple of other machines. Happens only with Object Explorer not with Properties pan. All these machines have good primary 1GB RAM and processing power Intel dual core CPU 1.60 Gz. It is very minor issue but little annoying! Anyone else experienced similar behavior Using MS SQL Server 2005 EE Thanks for taking the time to enter the issue. When you enter an issue through Connect it automatically creates a bug in our tracking database. We can then communicate with ...Show All

• thisiswhere DTS Classes Not Showing in Visual Studio

  I am trying to use VB.NET to run SSIS packages. However I don't have the various dts namespaces available. When I attempt to import them I only have Microsoft.SqlServer.Server in intellisense. I am running on XP sp2, VS 2005 (full install) and even went so far as to install sql 2005 sp1 full install on my local machine. What gives with only having Microsoft.SqlServer.Server available thanks, Scott Michael, Thank you for your reply. That's my problem...I can't reference the assemblies. The imports statement only show Microsoft.SqlServer.Server. If I add a reference to web.config like: < add assembly = " Microsoft.SqlServer.Dts.Runtime, Version=9.0.242.0, Culture=neutral, Pu ...Show All

• Kursat Konak SQL Authentication after removing SQL 2000 for SQL 2005

  I just removed (via Add and remove programs) SQL 2000 Server and now only have SQL 2005 left on my machine. The 2005 Server (SQLExpress ) is set up for mixed mode and I've verified that the registry setting is 2 for LoginMode, yet I'm still unable to login. This is the following error TITLE: Connect to Database Engine ------------------------------ Cannot connect to CJL-SERVER\SQLEXPRESS. ------------------------------ ADDITIONAL INFORMATION: Login failed for user 'drafttrackuser'. The user is not associated with a trusted SQL Server connection. (Microsoft SQL Server, Error: 18452) For help, click: http://go.microsoft.com/fwlink ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=18452&LinkId=20476 -------- ...Show All

• Shiby John Help translating Access SQL to T-SQL

  Can someone tell me what this Where clause (from MS Access) should be T-SQL Basically, if the value in the [reason] field contains 'DIST' then the row should return as long as the value in [movement] is greater than or equal to 1. Where IIf ( [reason] LIKE '%DIST%' , Val ( [movement] ),1 )>= 1 Thanks! You can do below:   where case when reason like '%DIST%' then sign(Movement) end = 1   -- or   where reason like '%DIST%' and sign(Movement) = 1   -- or below which will use index on Movement if available   where reason like '%DIST%' and Movement >= 1 Thanks anomolous! That was the right answer. I just had to ...Show All