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!

Register Log in
  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Search results for query: *

  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 =...
  16. quatermass

    Change Database in Catalog

    Hi, Have you renamed the database in the Catalog Tables section on the right hand side of the Menu>>Catalog>>Tables screen? The 3 database icons represent (from the top): The database definition (from the database drop down) The SQL Server database name (where data is actually stored) The user...
  17. quatermass

    DTS packages

    Hi, If you are using the Execute Process Task, it is very simple: Write error.bat to accept parameters, eg echo %1 where 1 represents the first parameter in a command line run and % is a reserved symbol for input parameters. In the Execute Process Task, as the parameter put the error message...
  18. quatermass

    DTS: Conditional Load

    Hi, An alternative to Scott's suggestion would be to have an activex script within the dts which checks the number of records in the table, and will only continue if there are records in the table. The script would be : Function Main() dim cn 'connection object dim rs 'recordset object dim...
  19. quatermass

    catalog block

    Martine, The first user to open the catalog, regardless of user class, will gain exclusive (read-write) access. If you want to change the catalog, you can take a copy of the catalog, make the changes in the copy and then overwrite the main catalog. Regards, Kevin...
  20. quatermass

    Problem in COGNOS

    Hi, You can use a calculated field in impromptu to get the result you are after: maximum(ACCESS_DATE) for customer number This will exactly what it says on the box...and will show the last ACCESS_DATE for each customer number (assuming that customer number is a unique identifier). There...

Part and Inventory Search

Back
Top