Search results for query: *

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...

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...

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)...

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...

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...

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...

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...

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...

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...

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...

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...

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...

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...

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...

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

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...

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...

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...

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...

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...