Answer Questions

• lagu2653 SQL varchar conversion

  Ok i've been asked to right a sp that converts a date. Now this is usually quite easy, but unfortunatly it was a poorly designed DB, and the date has been stored as a varchar(50) in the for dd/MM/yyyy, so doing a stright CONVERT(dateTime, Field, 101) doesn't work. So my question is, is there a way to do a varchar to varchar conver such as this: CONVERT(varchar(50), <field>, 'MM/dd/yyyy') where <fields> is varchar(50), and data stored within it is in the format dd/MM/yyyy. Thanks for any help you can give. Here is a complete list of all the codes you can use CONVERT(CHAR(12), sqldateval, 101 ) AS [The Date] Where the yellowed number can be any of these numbers ...Show All

• bluedvd cross server update problem

  The system has worked for two years. SQL Server 2000 runs on Windows 2000 Server “A”. Another instance of SQL Server 2000 was moved from Windows 2000 Server “B” old to Windows 2003 Server “B” new, by restoring a backed up copy from old to new. The system has cross server updates, where Server “A” is updating records in very large tables on Server “B”. After the move from Server “B” old to Server “B” new, suddenly the cross server updates fail, but only when they are conducted within a Begin and Commit Transaction batch. The system works when I remove the Begin and Commit Transaction, and no longer have them run in a batch. Also, I had a cross server update, having “A” update records in “B” ...Show All

• G Holden What kind permission required to access sys.sysreferences on SQL 2005?

  In SQL 2000, a guest user can access any system tables in a database, but it looks not the same in SQL 2005. What even worse is the SQL security handles it in a confused way. When I sign on as sa and run: SELECT * FROM sys.sysreferences Of course I get what I want. While, if I sign on as a user only has guest privilege, I get nothing. Based on my understanding, if one does not have permission to SELECT, he should get rejucted error message. Otherwise, he should get return data. Now, SQL 2005 does not give any. More interesting thing is when select * from sys.sysobjects, SQL 2005 return data to both sa user and user with guest privilege. Did MS applied different security on different sys.sys.. table When a select return nothing, ho ...Show All

• Old Mortality AMO. Aggregation designs can't be deleted? Is this a bug?

  Why this code public static void ClearAggregations( MeasureGroup pMg) { if (pMg.IsLinked) return ; foreach ( Partition lPart in pMg.Partitions) { lPart.AggregationDesignID = null ; lPart.Update(); } pMg.AggregationDesigns.Clear(); pMg.Update(); } doesn't work What are needed and suffisient calls for deleting af all aggregation designs in a MeasureGroup     >For example, in cube editor, in online mode in Visual Studio: >when somebody deletes a MeasureGroup from the cube (by calling as usual cube.MeasureGroups.Remove), >we don't want that delete to happen on the server until the user explicitly saves. :-) But If the ...Show All

• ThiagoPadilha Error: 18461, Severity: 14, State: 1

  I'm moving the master & resource databases as described on MSDN... http://msdn2.microsoft.com/en-us/library/ms345408.aspx I've completed step 7... "Start the instance of SQL Server in master-only recovery mode...." and am up to step 8 which requires me to login to SQL Management Studio to run a query, but I receive the following error... Login failed for user 'sa'. Reason: Server is in single user mode. Only one administrator can connect at this time. (Microsoft SQL Server, Error: 18461) Further details produces... "Error: 18461, Severity: 14, State: 1" I have no other open connection that I can think of. Can anyone help In case anyone comes upon this posting, h ...Show All

• Harish1 How to get only date component from a datetime column

  Hi all, I have a table containing a datetime column and it stores date with time ( I use GETDATE() to store the value ). For reporting purpose I need only the date component from this column without the time component. I do not want to use the datepart(yyyy, orderdt), datepart(mm, orderdt) and datepart(dd, orderdt). Will following help select CONVERT ( VARCHAR (10) , GetDATE(), 121 ) ...Show All

• dmee SQL Express user permissions?

  I'm trying to build a web application using SQL2005 Express and Visual Studio Express.  It all works OK on the local machine, but gives an error "Login failed for user xxxx\ASPNET" when used remotely.  I can't find where I can set login permissions for SQLServer 2005 Expresss - can anyone help, please Thanks John I think I have made some progress, probably narrowing down the problem, but not complete. I have built a new app with two databases in App_Data.  One is ASPNETDB, the other is a test database.  I have made xxx\ASPNET the owner of both databases, and mapped the xxx\ASPNET login to both databases, with "user" and "default schema" both "dbo" for both databases. I have two  ...Show All

• nephsbirth2021 Issues With SQL 2005 Encryption

  Are there any pitfalls i should look out for when using the encryption in SQL 2005 The database I'm doing all the testing on a test database and it is a copy of the real database. i wanted to test the encryption on real data without using the main database. thank you for your help. Jeff Welch Can you help us to reproduce the problem I would need to know what client you are using (i.e. sqlcmd, MS SQL Server Management Studio (query), etc.) and if possible the script you used. Any additional information you think is relevant will also be greatly appreciated. Thanks a lot, -Raul Garcia SDE/T SQL Server Engine Yes, but the feilds are bla ...Show All

• Philip Borchert Checking to see if a record exists and if so update else insert

  I've decided to post this as a sticky given the frequency this question is asked. For those of you wishing to build a package that determines if a source row exists in the destination and if so update it else insert it, this link is for you. http://blogs.conchango.com/jamiethomson/archive/2006/09/12/SSIS_3A00_-Checking-if-a-row-exists-and-if-it-does_2C00_-has-it-changed.aspx Thanks Jamie! If you want to do a similar concept to Jamie's blog post above, but with the Konesan's Checksum Transformation to quickly compare MANY fields, you can visit here: http://www.ssistalk.com/2007/03/09/ssis-using-a-checksum-to-determine-if-a-row-has-changed/ Phil Can somebody point me to a simple example of this. I have a flat file and I want to che ...Show All

• Loopsludge How to catch messages on Event Handlers

  hello everyone I'd like to know if there is a way to catch the error messages when a tasks fails, that's because i's like to store every message on a user variable, so i could log all of them later, I was thinking that it may be possible with the event handlers, could it be regards Thanks Jammie, i'll try to do so. best regards Yes. When an executable errors the error message is in the @[System::ErrorDescription] variable scoped to the OnError eventhandler. -Jamie ...Show All

• SterlingH How to avoid Leaf-Level calculations

  From Spofford's book: "...the developer can create measures instead of using an MDX script to perform leaf-level calculations. Then the leaf-level calculation is precalculated during cube processing time via an SQL Query, and the expensive on-the-fly calculation is avoided. For the Amount = Price * Volume type of calculation, a simple calculated column in DSV ... " Now how do you achieve similar performance gain when you have a division instead of a multiplication For instance average price, calculated as TotalPrice / Quantity. According to this book, using a MDX Script (under the Calculations tab of Cube Design in BIDS) will imply a heavy performance penalty. Are there any alternatives ...Show All

• Aing_d Secure Replication from Cluster to Workgroup

  I am trying to set up replication (transactional with updates) from a 2-node Active/Passive SQL Server 2005 Cluster to a SQL Server 2005 server in a Workgroup. When configuring the Snapshot Agent, Log Reader Agent and Queue Reader Agent from the Publisher (Cluster) we are advised not to use the SQL Server Agent Service Account for security reasons. When I specify a Domain Account I get an "unknown security error" staring the snapshot agent. When I select the option to run under the service account all works well. Strange thing is when I specify the service account I still get the "unknown security error" message. Anyone got any ideas Peng, here is the error when the snapshot agent ...Show All

• BLueDeViL Allow Blank in parm not working

  I am using RS2005. I have a data driven parm that I have checked "allow blank" as a value. I cant allow null so that isnt checked. I have even entered a space in the area where you can provide a default value. When I preview the report ... this parameter has <Select a Value> in the combo box. All other parms are filled in with defaults so the report should run. I hit "View Report" and get an error message "Please select a value for the parameter 'ParameterX' " which is the parameter I have said could be blank. Of course if I drop down the list there isnt a blank value to select - and I dont see why I would want to add one and force a selection of spaces ... seems like something is wrong here. Please help ...Show All

• billb59 Cannot generate SSPI context

  I have a SQL Server 2000 in a specific W2K server, that belongs to a domain that is administered by a W2003 server. There is a client that has a new computer with WXP in the same domain. It used to have an old machine with W2K pro. and with the same user it worked OK. When accesing SQL from the client with an Office2003 application via ODBC with windows authentication, after several minutes working OK, suddenly it gets this message: Conection error. SQL state: S1000 Cannot generate SSPI context. In the client I have executed cliconfg.exe and created a piped name protocol, but still it won't work. This is great, finally over several sleepless nites thinking about why does this problem all o ...Show All

• Crane101 Deploying multiple files through a package

  Hi I am working on on SQL Server Integration Services and facing few problems in it. Actually am supposed to create a package that would automatically pick excel spreadsheets with a specific format and import it onto the SQL server. (Lets say , there is a company named AB and they have got various products named CD,EF and GH and each product has its own spreadsheet in which its monthly sales(JAN, FEB,...NOV, DEC) is given. So i have to build a generic package for each product (lets say CD) so that i don't have to import every spreadsheet seperately for each month. To summarize i just have to build a package where i can deploy the various spreadshhets again and agian instead of building a package for each and every month spreadsheet s ...Show All