Search results for query: *

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!

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

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

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

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

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

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

Thanks for the info. Its helpful.

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

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

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

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

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.

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

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

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

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

Bummer. Thanks.

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

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