Answer Questions

• Elder Morales File System Task in SSIS

  Wanting to use File System Task in SSIS to move files from one location to another for archiving. I can't seem to figure out how to use a wild card for the file name. It seems that I must specify the actual file name which is a problem because only the first 4 letters in the file name remain a constant. Does anyone know how to use a wild card or a way to work this in Ok, thank you. I will give that a try. Darrin Turner wrote: Wanting to use File System Task in SSIS to move files from one location to another for archiving. I can't seem to figure out how to use a wild card for the file name. It seems that I must specify the actual file name which is a problem because only the first 4 letters in the ...Show All

• hrubesh SYSAdmin users do not have permission to see records in a view?

  We have a third party application and wish to create a report based upon a view. The strange thing is logged in to the server as a SQL SYSAdmin account, we cannot view the data via the view. SQL Admin accounts are setup correctly and there is nothing different on this particular server. No errors are returned just a blank view with no records. Could this be a permissions problem or orphaned schemas in that particular database I thought SYSAdmin could view and do just about anything and the people who use this particular database would not have the know how on denying permission to the SYSAdmin role. Thanks N Hi, As far as I can tell there is nothing unusual with this view. Admin users in ...Show All

• johnvms String Lookup Error

  I'm creating my first SSIS package. The data source is a csv file and the destination is a SQL server table. I have several lookups. The date and time lookups are working fine. The string lookups are failing. I can see the records when I open the table in SQL Server. Do I need special handling for strings Truncating Here are the error messages. I'd appreciate any input and help. Thanks. [Lookup Commodity [2560]] Error: Row yielded no match during lookup. [Lookup Commodity [2560]] Error: The "component "Lookup Commodity" (2560)" failed because error code 0xC020901E occurred, and the error row disposition on "output "Lookup Output" (2562)" specifies failure on error. An error occurred on the ...Show All

• Montana Jones Native SOAP and Transactions

  If I expose a series of stored procs as Web Services using Create Endpoint, can calls to these be included inside a XA compliant transaction Any good documentation on this because I can't find any at the moment. Thanks in advance Ben Any Microsoft people out there want to help on this ...Show All

• charlestek SQL Server 2005 SP1 Failure - 'dm_exec_query_resource_semaphores'

  Hi Sam, In my case, the components in sp1 were installed successfully except the database engine. And the following messages were captured from the setup log. Please advise me. Thank you in advances, Kasemchai ---------------------------------------------- Action start 15:24:23: CommitSqlUpgrade.D20239D7_E87C_40C9_9837_E70B8D4882C2. <Func Name='LaunchFunction'> MSI (s) (5C!10) [15:24:23:494]: Transforming table _sqlAction. MSI (s) (5C!10) [15:24:23:494]: Note: 1: 2262 2: _sqlAction 3: -2147287038 MSI (s) (5C!10) [15:24:23:494]: Transforming table _sqlAction. MSI (s) (5C!10) [15:24:23:494]: Transforming table _sqlAction. MSI (s) (5C!10) [15:24:23:494]: Note: 1: 2262 2: _sqlAction 3: -2147287038 MSI (s) (5C!1 ...Show All

• Squiddy How to concate 2 ore more text fields into one field?

  Hello, I hope someone has already done this, but I have a table with a text column- example ColA, now i want to run a query to select the ColA in this table and combine the results of ColA into a ColB in another table. Something like - Note: the codes below doesn't work!! DECLARE @ResultID as int DECLARE @AccID int DECLARE _rows CURSOR FOR SELECT AccID FROM tableA FETCH NEXT FROM _rows INTO @AccID WHILE (@@fetch_status <> -1) BEGIN UPDATE TableB SET Report = Report + (SELECT txtField FROM tableA WHERE AccID = @AccID) WHERE AccID = @AccID FETCH NEXT FROM _rows INTO @AccID END Thanks in advance Thanks CetinBasoz, but the the problem I am dealing with is the text field and it's a different ...Show All

• thorlax402 SQL Recovery Mode

  Hi, How to make all MSSQL DBs run in Simple log form by default other than FULL so when we srhink DBs the logs will go bybye. :-) Thanks SET NOCOUNT OFF DECLARE @strSQL nvarchar(50) DECLARE @databaseName nvarchar (255) DECLARE MyCursor CURSOR FOR --used for cursor allocation Select name from sysdatabases Open MyCursor Fetch Next From MyCursor Into @databaseName While @@Fetch_Status = 0 BEGIN SET @strSQL = 'ALTER DATABASE '+ @databaseName + ' SET RECOVERY SIMPLE' EXEC sp_executesql @strSQL Fetch Next From MyCursor Into @databaseName END Close MyCursor Deallocate MyCursor Print 'Recovery Model changed to SIMPLE for all databases' Hope this helps. Database settings come from the model database. ...Show All

• Ljhopkins Problem using Monthname function

  I am trying to have the below formula populate a portion of a textbox The right(Parameters!ToTimeDimensionCloseYYYYMM. label , 2 ) is because I am dealing with " YYYYMM " and I only want the "MM" portion. =iif(right(Parameters!ToTimeDimensionCloseYYYYMM. label , 2 )=01,Monthname(12),monthname(right(Parameters!ToTimeDimensionCloseYYYYMM. label , 2 )) ) When I use this formula it will work for December, however for the rest of the months if a user chose 200603, it will read "March" when I really want it to read "February". =iif(right(Parameters!ToTimeDimensionCloseYYYYMM. label,2 )=01,Monthname(12),monthname(right(Parameters!ToTimeDimensionCloseYYYYMM. label ,2)-1))) However, w ...Show All

• CherieAnn "Keys lock row not found" error

  Jens I also am having trouble with the reporting services not being able to the report server databases. I get the error "Keys lock row not found". I can't find any citation to help me figure out what this means nor how to fix it. Any help you can give me would be greatly appreciated. Ron What version of Reporting Services are you using How did you create your report server database Did you make any changes to it manually using OSQL or Enterprise Manager or Management Studio -Lukasz ...Show All

• Docpro777 Copying encrypted objects ?

  Hi, I would like to copy a function from one sql 2005 database to another, but the function is encrypted so cannot use the script to window commands etc... Is there a way of copying encrypted objects from one sql 2005 db to another I don't really care to know the contents of the function. Any help appreciated. James. Yes if it is encrypted then the DTS would fail, only way is to decrypt and then recreate on the destination server. I would recommend that you create a DTS Package to transfer the object. There was a 'Copy SQL Server Object Task' that was made exactly for this purpose. I thought this would be the case. thank you for your help :) ...Show All

• jaackov Oracle to SQL Server Migration using Server Migration Assistant for Oracle V2.0 - Help Needed

  Hi all, I am basically a front end guy and we need to do a dB migration from Oracle 9i to SQL Server (sitting in a shared environment). For the start we wanna try out this tool. This tool provided by the MSN comes along with an extension pack that needs to be installed along with the tool. http://www.microsoft.com/downloads/details.aspx FamilyId=E35CEE88-C919-463F-B020-81468CD231DA&displaylang=en It’s mentioned there that Server component installation: Locate a SQL Server machine which you want to migrate to. Run SSMAExtPack.exe on that machine locally.It will install a sysdb database with procedure and functions that emulate some of the Oracle system packages. You don't need to d ...Show All

• mshvw Where to put DISTINCT in a CTE?

  CTE's are surprisingly useful for paging data, but I'm running into a problem where I have to add a distinct clause. The idea is to pull topic records for a user's forum posts, but only one topic record even if there are multiple posts in the topic. Here's what I have so far: CREATE PROCEDURE pf_PagedTopicsByUser( @StartRow int, @PageSize int, @UserID int ) AS DECLARE @Counter int SET @Counter = (@StartRow + @PageSize - 1) SET ROWCOUNT @Counter; WITH Entries AS ( SELECT ROW_NUMBER() OVER (ORDER BY IsPinned DESC, LastPostTime DESC) AS Row, pf_Topic.TopicID, pf_Topic.ForumID, pf_Topic.Title, pf_Topic.ReplyCount, pf_Topic.ViewCount, pf_Topic.StartedByUserID, pf_Topic.StartedByName, pf_Topic.LastPostUserID, pf_Topic.LastPostName, ...Show All

• Alex-MyRpg Dynamic feed of table name to Transfer SQL Server Object Task

  Hi I would like to be able to feed the List of tables to the Transfer SQL Server Object Task dynamically. I have got a foreachloop container which it feeds the table names into a variable @table_name (string). Transfer SQL Server Object Task is with in foreachloop container I did add an expression into the property of Transfer SQL Server Object Task and assign the tablelist property to @table_name I would be grateful if you can give me any hint. Thanks S The logic looks right to me...are you seeing any error I have a blog post that explains how to iterate through a SQL result set (in your case to get the list of tables) using a foreach loop conatiner. I hope that hepls you ...Show All

• mattdawg Comments in SQL

  Hi, I am new to MS SQL Server 2005. I used MySQL. Now my doubt is how to put a comment in SQL statement, where i am using query editor in SQL Management Studio. Rgds., Aazad Hi, Use -- for single line comments and /* For multiline comments */ HTH, Jens K. Suessmeyer. --- http://www.sqlserver2005.de --- ...Show All

• Mongsreturn RMO: Replication between SQL Server 2005 amd SQL Server 2005 Express

  Hi! On an existing MergePullSubscription (Subscriber: SQL Express) I call the SynchronizeWithJob() method. But each time, the following exception occures: ExecutionFailureException: The specified pull subscription is not configured with a synchronization agent job. But how do I configure synchronization agent jobs in SQL Server 2005 Express Thanks for your reply. This is a run time error on the MergeSynchronizationAgent obtained with the SynchronizationAgent property. As it is not a compile time error, I think the dlls are already registred. If not, what is the register process We also try to use the asynchronous method SynchronizeWithJob but we are waiting for the 'status'... But i ...Show All