Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Wanet Telecoms Ltd on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Recent content by quatermass

  1. quatermass

    OPENROWSET

    Hi, As the SQL statement is a string for execution, just pasting in a working query won't work if there is a quote anywhere in it. SQL will take a single quote as being the end of what is to be executed and will attempt to run that and the remenants of the string. It will try to run select...
  2. quatermass

    datetime conversion

    Hi TJ Try select convert(datetime, replace('May-18-09 14:00', '-', ' '), 109) The format 109 wants the format to be mon dd yyyy hh:mi:ss:mmmAM (or PM) Using the replace function creates that format. Hope it helps Kevin ************************************************************** Rock...
  3. quatermass

    Create a folder using Date

    Hi, I generally use the following (VBA) code to generate a string to create a data-based folder cstr(year(now)) + rightpad(cstr(month(now))) + rightpad(cstr(day(now))) In order to generate the correct format (YYYYMMDD) ie 20080624, I use the following function function rightpad(val_in)...
  4. quatermass

    Pivotong rows to columns

    Hi Bruce, I haven't done anything with the number of columns you have, I have only had to do 7/12 for days/months, but.... What I do is to create a table with a single column and populate that column with 1-7 or 1-12 as required. Next, I run a cross-join query against the original table and...
  5. quatermass

    BCP Newbie needs help

    Hi, If you are trying to get a complete extract of the table you will need to make your first line set @as_select = 'bcp "DATABASENAME..ct_rl2cpcs"' where DATABASENAME is the database which holds the ct_r12cpcs table. Hope it helps...
  6. quatermass

    appending a file in DTS package

    Hi, I think the problem may be in the fact that the final "True" parameter indicates that the file will be created if it doesn't exist. Given that the file is for reading only, this doesn't make sense. Try removing the final "True" Hope it helps Kevin...
  7. quatermass

    Cognos 8...Can't connect Contect Store to MSQL 2005 Express databse

    Olivia Apparantly, the port for 2005 Express is randomly assigned and can be viewed through the SQL Server Config Manager. Under Network Config and on the IP addresses tab, is TCP Dynamic Ports, this value is the one which you will need to connect the Content Store on. I think the port can...
  8. quatermass

    Update the date to match the first record changed in a group

    Hi, You can either use the code to generate a new table, or use the sub-select statement as an update statement as follows: update table set DateAppended = subselect.min_DateAppended from table, (select PO, min(DateAppended) as min_DateAppended, Status from table...
  9. quatermass

    Update the date to match the first record changed in a group

    Hi Tamrak, If I've got the idea: select t1.PO, t2.min_DateAppend, t1.Status, t1.ShippedDate, t1.ReceivedDate from table t1, (select PO, min(DateAppended) as min_DateAppended, Status from table group by PO, Status) t2 where t1.PO = t2.PO and t1.Status = t2.Status...
  10. quatermass

    Line Chart - Suppressing Display of 0's and Nulls

    Hi Wayne, I am assuming that the report is in Impromptu. If this is the case, you can use the summary filter and prefilter options. I think this is right, I seem to recall doing a YTD/period to date Impromptu report like this. summary filter actuals = 0 This will have the same effect as...
  11. quatermass

    DTS import, Add 2 columns Date and Source

    Hi, There are a couple of ways to do this a) assuming that your target table has a datetime field for the import date, set the default value of this field to getdate() and the default value of the source field to be 'PICK' for example b) in the dts package on the source for the transform...
  12. quatermass

    Transforming columns to rows

    Hi, It has been a while since I have done this with DTS, so please forgive any coding errors, but the general formula is as follows Create a single column table with records of 1-7 (representing the days) Import the Excel data into a SQL table As the source for the transform, use both the...
  13. quatermass

    VBA Sql Syntax

    Hi, You will need to modify the statement to have the variable name outside the quoted sql command string DoCmd.RunSQL "INSERT INTO tblTest2 (f1) VALUES (" & myVar & ")" You may have issues with data formatting. In which case, either change the variable data type, or put...
  14. quatermass

    SQL Query. Duplicate Records in Data

    Laura, You can set the sql statement to pick up the latest date, by using maximum and group Example using Northwind table, Orders: SELECT EmployeeID, MAX(ShippedDate) AS Expr1 FROM Orders GROUP BY EmployeeID This will return the employee id with the latest shipped date for that...
  15. quatermass

    Skip Row in CSV import in a DTS

    Hi, You could try using the SkipInsert result. This is a basic example --start code dim x if DTSSource("col001") = "PRICE CHANGE RECS" then x = DTSTransformation_SkipInsert else DTSDestination("ITEM_NUMBER") = DTSSource("col001") x =...

Part and Inventory Search

Back
Top