Re: SQL Server question re table names - still stuck
3) When I do a CFQuery, it won't work unless I write the table name as canadianofficespacecom.city, instead of just city. Is the database specified in the DSN? Sounds as if one isn't specified. Ricardo. ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:329122 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
re: SQL Server question re table names - still stuck
I wrote: A client asked me to see if I could get a copy of his old web site up and running. I did a restore of the backed up database onto my PC. The problem is that all the table names have the name of the database prepended to them. When I view the tables in SQL Server Manager, the table, city, is named abc.city, where oldabc is the name of the database from which the data was backed up from. For the sake of this example, newabc is the database name I used on my own machine, to which the backup data was restored to. brad wrote: I can't say I've ever seen that one before, but you could just do a loop over the contents of INFORMATION_SCHEMA.TABLES and build/exec an ALTER statement for each one. http://technet.microsoft.com/en-us/library/ms190273.aspx How many tables are there? If there are under 75, you might be just as fast renaming them them by hand. Well, I tried renaming them by hand but it won't work. Using the SQL Server Manger, I right-clicked on the table and then clicked on RENAME. The table name, when I right-clicked on it, said something like: abc.city. But when the RENAME edit box came up, it contained only city. So the table isn't actually named abc.city. It is only presenting the table that way, and when I do a query in CF, it expects me to also refer to the table with that database name prefix. Hmm...ok, here's the exact scenario: 1) I'm imported the data from a backup file and named the database canadianofficespace. 2) When I look at the tables in SQL Server Manager, it prefixes every table name with canadianofficespacecom, which is not the name of the database. I presume it's the name of the database that it originally came from. 3) When I do a CFQuery, it won't work unless I write the table name as canadianofficespacecom.city, instead of just city. Can anyone help me out here? This is well beyond my SQL Server knowledge. ...lars ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:329078 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: SQL Server question re table names - still stuck
I didn't see a mention of which version of SQL Server you're running, but it sounds to me like canadianofficespacecom is either an object owner or a schema: http://www.sqlteam.com/article/understanding-the-difference-between-owners-and-schemas-in-sql-server I'm assuming that it's most likely SQL Server 2005 or 2008, in which case the simplest fix would probably be to change the default schema for the user that you specified in your CF datasource. To do this, access the properties for the user in question and you will see a default schema field -- change the value to canadianofficespacecom and you should then be able to query the tables without the prefix. HTH, -- Ezra Parker On Fri, Dec 11, 2009 at 1:07 AM, Larry Soo l...@bc4x4.com wrote: I wrote: A client asked me to see if I could get a copy of his old web site up and running. I did a restore of the backed up database onto my PC. The problem is that all the table names have the name of the database prepended to them. When I view the tables in SQL Server Manager, the table, city, is named abc.city, where oldabc is the name of the database from which the data was backed up from. For the sake of this example, newabc is the database name I used on my own machine, to which the backup data was restored to. brad wrote: I can't say I've ever seen that one before, but you could just do a loop over the contents of INFORMATION_SCHEMA.TABLES and build/exec an ALTER statement for each one. http://technet.microsoft.com/en-us/library/ms190273.aspx How many tables are there? If there are under 75, you might be just as fast renaming them them by hand. Well, I tried renaming them by hand but it won't work. Using the SQL Server Manger, I right-clicked on the table and then clicked on RENAME. The table name, when I right-clicked on it, said something like: abc.city. But when the RENAME edit box came up, it contained only city. So the table isn't actually named abc.city. It is only presenting the table that way, and when I do a query in CF, it expects me to also refer to the table with that database name prefix. Hmm...ok, here's the exact scenario: 1) I'm imported the data from a backup file and named the database canadianofficespace. 2) When I look at the tables in SQL Server Manager, it prefixes every table name with canadianofficespacecom, which is not the name of the database. I presume it's the name of the database that it originally came from. 3) When I do a CFQuery, it won't work unless I write the table name as canadianofficespacecom.city, instead of just city. Can anyone help me out here? This is well beyond my SQL Server knowledge. ...lars ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:329080 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: SQL Server question re table names - still stuck
but it sounds to me like canadianofficespacecom is either an object owner or a schema: +1 That would make more sense than being part of the actual table name. ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:329081 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: SQL Server question re table names - still stuck
Hmm, what's the chance the extra part isn't really the database name, but it is the owner name. Kind of like the default owner is dbo and your full path to a table is database.dbo.tablename. Perhaps it is databasename.somethingyoudontpect.tablename. Perhaps you need to look at altering the owner or schema of the table instead of the actual name. What version of SQL server are you on again? I'm not sure if I've ever had one table name show up, but when I go to rename it, another one appears. Also, what happens when you try and script a rename? http://technet.microsoft.com/en-us/library/aa275462%28SQL.80%29.aspx ~Brad Original Message Subject: re: SQL Server question re table names - still stuck From: Larry Soo l...@bc4x4.com Date: Fri, December 11, 2009 3:07 am To: cf-talk cf-talk@houseoffusion.com How many tables are there? If there are under 75, you might be just as fast renaming them them by hand. Well, I tried renaming them by hand but it won't work. Using the SQL Server Manger, I right-clicked on the table and then clicked on RENAME. The table name, when I right-clicked on it, said something like: abc.city. But when the RENAME edit box came up, it contained only city. So the table isn't actually named abc.city. It is only presenting the table that way, and when I do a query in CF, it expects me to also refer to the table with that database name prefix. Hmm...ok, here's the exact scenario: 1) I'm imported the data from a backup file and named the database canadianofficespace. 2) When I look at the tables in SQL Server Manager, it prefixes every table name with canadianofficespacecom, which is not the name of the database. I presume it's the name of the database that it originally came from. 3) When I do a CFQuery, it won't work unless I write the table name as canadianofficespacecom.city, instead of just city. Can anyone help me out here? This is well beyond my SQL Server knowledge. ...lars ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:329093 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
re: SQL Server question re table names - still stuck
Recap of problem: I imported an old, backed up SQL Server file. Now, when I view the tables or try to access them in CF, it requires me to prefix all table names with canadianofficespacecom.. Ezra Parker wrote: I didn't see a mention of which version of SQL Server you're running, but it sounds to me like canadianofficespacecom is either an object owner or a schema: I'm using SQL Server 2008...and the database is a few years old...possibly pre-dating 2005 or even older. I'm assuming that it's most likely SQL Server 2005 or 2008, in which case the simplest fix would probably be to change the default schema for the user that you specified in your CF datasource. To do this, access the properties for the user in question and you will see a default schema field -- change the value to canadianofficespacecom and you should then be able to query the tables without the prefix. I tried that. The username I was using is SA. The default schema was master so I changed it to canadianofficespacecom. The weird thing is that when I click on the OK button and the dialog closes, when I re-open it, it still shows the default schema as being master. It doesn't report an error. brad wrote: What version of SQL server are you on again? I'm not sure if I've ever had one table name show up, but when I go to rename it, another one appears. Also, what happens when you try and script a rename? I haven't tried to script a rename because the manual renaming didn't work. FYI I'm on SQL Server 2008. Here's a screen shot of the users and schemas: http://www.bc4x4.com/temp/cos-screenshot.jpg BTW, when I look at user dbo, I see that its Database Role Membership lists it as having db_owner status. So would I be correct in assuming that the problem I'm having is that the default schema for dbo is master instead of canadianofficespacecom? (Thank you to everyone for your help so far.) ...lars ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:329113 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: SQL Server question re table names - still stuck
On Fri, Dec 11, 2009 at 2:53 PM, Larry Soo l...@bc4x4.com wrote: So would I be correct in assuming that the problem I'm having is that the default schema for dbo is master instead of canadianofficespacecom? I believe so, yes. In order to resolve this from the user side of things you're going to need to connect as a different user, as you will not be able to change the default schema of the sa user, as noted here: http://msdn.microsoft.com/en-us/library/ms176060.aspx The value of DEFAULT_SCHEMA is ignored if the user is a member of the sysadmin fixed server role. All members of the sysadmin fixed server role have a default schema of dbo. So I would suggest creating a canadianofficespacecom login to correspond to the canadianofficespacecom user in your DB, and then executing the following T-SQL script: USE canadianofficespace; ALTER USER canadianofficespacecom WITH LOGIN = canadianofficespacecom, DEFAULT_SCHEMA = canadianofficespacecom; GO (As a side note, I had thought you could change the default schema in the GUI interface, but after trying it out it appears I was mistaken about this -- sorry for the misinformation there.) If this runs successfully, then change the DSN to connect with the new user name and password and you should be good to go. The alternative option would be to change the schema for the tables to dbo, but if it were me I would prefer changing users anyway in order to avoid connecting as sa. Please post back if you run into any problems. -- Ezra Parker ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:329121 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: SQL Server question re table names
I can't say I've ever seen that one before, but you could just do a loop over the contents of INFORMATION_SCHEMA.TABLES and build/exec an ALTER statement for each one. http://technet.microsoft.com/en-us/library/ms190273.aspx How many tables are there? If there are under 75, you might be just as fast renaming them them by hand. ~Brad Original Message Subject: SQL Server question re table names From: Larry Soo l...@bc4x4.com Date: Tue, December 08, 2009 1:45 pm To: cf-talk cf-talk@houseoffusion.com A client asked me to see if I could get a copy of his old web site up and running. I did a restore of the backed up database onto my PC. The problem is that all the table names have the name of the database prepended to them. ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:328974 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: SQL Server question re table names
I have only seen that with exports from MS Access. Yes, +1 about using the information INFORMATION_SCHEMA views. Tapping into the system views is my favorite way to generate mass statements on-the-fly ;-) DECLARE @DBPrefix VARCHAR(50) SET @DBPrefix = 'oldabc.' SELECT 'exec sp_rename @objname=''['+ TABLE_NAME +']'', @newname = '''+ SUBSTRING(TABLE_NAME , LEN(@DBPrefix)+1, LEN(TABLE_NAME)) + FROMINFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE @DBPrefix +'_%' -Leigh ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:328978 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
