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

    I just can't believe the Group by Function!

    I am the only SQL DBA in a house full of Foxpro'ers - GROUP BY was a big change for them as well. I believe they just made use of max() and min() whenever necessary. Good luck!
  2. redlam

    I just can't believe the Group by Function!

    In Foxpro, if the dept and jobdesc were different for the same job and subjob, which dept and jobdesc values would you expect to see for this grouping? If you don't group by those values or apply an aggregate function to them, SQL does not know what to return. Try using max(dept) and...
  3. redlam

    Creating database on the fly, need to change owner

    Hmm. I'm using SQL 2000 but this is what my BOL says about sp_changedbowner: Permissions Only members of the sysadmin fixed server role OR the owner of the current database can execute sp_changedbowner. I remember there being a service pack for BOL, perhaps I never applied it... Thanks for...
  4. redlam

    Creating database on the fly, need to change owner

    Hi there. I have an application that calls a stored procedure which creates new databases on the fly (don't ask, legacy code). My problem is that the application user becomes the database owner of these new databases. I need to be able to execute sp_changedbowner 'sa' in the same stored...
  5. redlam

    OpenRowSet maintains connection after query complete

    Hi. I am using OpenRowSet to query a Foxpro database from SQL. The query works fine, however, SQL maintains a connection to the file long after the query completes and the query window is closed. The only way to release the connection is to stop the SQL Service. This is a problem because...
  6. redlam

    DTS Export - bit field is exported as 'false'

    Thanks for the suggestion. Defining a new transformation to handle the bit fields seems to work.
  7. redlam

    DTS Export - bit field is exported as 'false'

    Has anyone else had any issues with this? I use a DTS package to export data to a text file. I need to import this data back into SQL but I'm running into trouble because the DTS package exports my bit fields as 'True' or 'False' instead of 0 or 1. When I attempt to reimport this, the import...
  8. redlam

    Identity column - good or bad

    Thanks for the info. Its helpful.
  9. redlam

    Identity column - good or bad

    Hi all. I work for a company that is developing a client/server application with a rather large and complicated database. I am told that we should not use identity columns as primary keys because "There are no advantages of using them with related tables" and "They are designed...
  10. redlam

    Update Query

    Try this... update mytable set myfield = left(myfield, len(myfield) -1) This assumes that the dash is always in the last position. Make a backup first, just in case :)
  11. redlam

    return tables that don't have indexes

    This seems to work... select name from sysobjects where type = 'u' and id not in (select id from sysindexes where indid <> 0)
  12. redlam

    DTS Import of Foxpro DB

    Hmmm... not sure what else could be the problem. My experience with ODBC and foxpro tables has been this: Any foxpro date field that does not allow nulls but is empty will be interpreted by ODBC as a default date of 12/30/1899 (this is by design - go figure). When you try to import this date...
  13. redlam

    DTS Import of Foxpro DB

    Hi Clive - I believe the problem here is that ODBC is interpreting the foxpro dates as smalldatetime - at least this is the case for me here. You'll need to change the datatype of the destination table on your SQL server to datetime. Hope that helps.
  14. redlam

    SQL Execution Plan - subtree cost vs. execution time

    Hi there. I am having trouble understanding different graphical execution plans for one of my SELECT queries. I have tested performance of this query using different joins and indexes and have noticed that the &quot;subtree cost&quot; could increase significantly even though the query execution...
  15. redlam

    Misleading info on TEXT data type in BOL?

    Hi there. I am researching a solution using a TEXT datatype for data that will exceed 8,000 characters. I know that there is a limit to what can be returned in a simple SELECT, UPDATE and DELETE statement but BOL seems to contradict itself on the exact limit. Maybe I'm misinterpreting...
  16. redlam

    SQL Query passing params to in() statement

    Hey Scott. Actually, I use the table variable method here - for some situations it works well, for others, a dynamic solution is better. To implement a table variable solution I created a user defined function to parse the variable string and load it into a table variable. I then call this...
  17. redlam

    logins

    When you create a role, you can assign it permissions to &quot;create or not create&quot; tables. Granting permission to create a table does not automatically grant permission to alter exisiting tables. You can fine tune your permissions on existing tables, views, sps, ect. Try this: Open...
  18. redlam

    Option (FAST n)

    Bummer. Thanks.
  19. redlam

    SQL Query passing params to in() statement

    Hi Special. I briefly scanned your code so pardon me if I missed the boat... I don't believe you can pass an array as described in your post. You will either have to parse the array into a temp table (table variable) and join it in your query or build your query dynamically. A simple example...
  20. redlam

    logins

    Hi there. Have you tried creating a new role? You can fine tune the permissions for this role and assign your users to it.

Part and Inventory Search

Back
Top