Using Qualified Names Of Databases
I have databases a1 , a2 , a3 , a4 ... an.
Each has a table t1 .
I am writing a stored proc which compares the t1 table between any given two databases.
I want to pass the database name as a variable ..
so the SPROC looks like
exec sp_comparetable 'a3' , 'a7'
My question is , inside the SPROC ,how do I refer to the tables in the a3 and a7 databases.
Thanks,
Alok.
View Complete Forum Thread with Replies
Related Forum Messages:
Qualified Table Names
I have a quick question on how to qualify table names as it relates to"dbo" vs. user names. Suppose that I am a user named "dwuser1", andthat I need to create a table named "dw_stage_1". Do I use dbo as in"dbo.dw_stage_1" or do I use "dwuser1.dw_stage_1" for the qualifiedtable name? Are both OK? If so, what would be the implications ofeach?
View Replies !
When To Use Fully Qualified Names ([database].[schema].object)
I wihsh to discuss whether to use fully qualified names:[database].[schema].objectof objects to operate (create, query..) on is good or not?If someone change order of sql code blocks in my script - this may causelose of it's context (like: use master / use <mydb>..). I wish to have mysript independed on changes like this and always produce correct result.Does using full name make use of 'use <db>' statement unnecessary?
View Replies !
Fully Qualified Query Accross Databases
Are there any perfmonace or query optimization limitations or issues that arise when issueing a fully qualified query across multiple databases on the same Instance of SQL Server. In other words are all features of query optimization fully supported in queries that span databases on the same Instance.
View Replies !
How To Get The List Of Names Of The Databases Of A Server Using OleDbConnection In C#.......?
I want to get the names of the databases of the server using OleDbConnection in c# which i have to add in to a combobox.....the same task i achieved using SqlConnection where in there is a Class SqlClientCollectionNames...the code is as followsusing (SqlConnection connection =new SqlConnection("Data Source=server;User ID=username;Password=passwd")) { connection.Open(); DataTable myData = connection.GetSchema(SqlClientMetaDataCollectionNames.Databases); foreach (DataRow row in myData.Rows) comboBox1.Items.Add(row[0]); connection.Close();} but the same i did for OleDbConnection it is not working using (OleDbConnection connection =new OleDbConnection("Provider=SQLOLEDB;Data Source=server;User ID=username;Password=passwd")) { connection.Open(); DataTable myData = connection.GetSchema(SqlClientMetaDataCollectionNames.Databases);//no such method Databases present in OleDb namespace foreach (DataRow row in myData.Rows) comboBox1.Items.Add(row[0]); connection.Close(); } is there any other way to do this using OleDbConnection........??Please help
View Replies !
Not Fully Qualified Files
Hi all, I need to create flat file connection managers. The connection Manager editor forces me to use fully qualified file names. Is there a possibility to use unqualified file names, because I need to reference to files in different directories with the identical file strcture. Any Ideas? Guido
View Replies !
Full Qualified Assemblies
Hi all, I need to dynamically create a ADO.NET connection manger so, my question is if it is safe to create it with the string that is generated (ConnectionManagerType) when creating the connection manager manually on my PC. I understood that this string is the full qualified name of the assembly, so will it be different when I create it on another P.C or it is fixed on all P.Cs ? Thanks ahead Eran
View Replies !
Qualified Table Name Syntax
Im trying to write a generic data access layer that supports SQL CE and Im wondering if any type of schema qualifier can be placed in front of a table name when executing a sql statement. I've tried soemthing like this select * from dbo.Account I get this error, The table name is not valid. [ Token line number (if known) = 1,Token line offset (if known) = 19,Table name = account ] It doesnt make really make sense to include a qualifier for sql ce but I just wanted to make sure that there wasnt some other syntax that I wasnt aware of. Thanks.
View Replies !
Using Qualified Stored Procedure Name With SqlDataSource
I am trying to populate a GridView from a stored procedure. The stored procedure's schema name is not the same as the user id logged into the database. In the SqlDataSource wizard, I am able to select the stored procedure name (unqualified), but Test SQL fails: "Could not find stored procedure 'devSelLineOverview'." Running the page also fails with the same error.Well, of course, because the procedure name must be qualified as line16l2.devSelLineOverview. However, the wizard doesn't pick up the schema name and I'm unable to change it there. So I changed it in the page source, but then I get this error: "Invalid object name 'line16l2.devSelLineOverview'." I tried changing the command type to custom SQL statement (not a stored procedure) but that deleted all the select parameters. Here is the data source, with the qualified name. I've also tried [line16l2.devSelLineOverview] (Could not find stored procedure) and [line16l2].[devSelLineOverview] (Invalid object name).<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:PlantMetricsConnectionString %>"SelectCommand="line16l2.devSelLineOverview" SelectCommandType="StoredProcedure"><SelectParameters><asp:ControlParameter ControlID="date" Name="date" PropertyName="Text" Type="DateTime" /><asp:ControlParameter ControlID="shift" Name="shift" PropertyName="SelectedValue"Type="Byte" /><asp:ControlParameter ControlID="SKU" Name="sku" PropertyName="Text" Type="String" /><asp:ControlParameter ControlID="Lot" Name="lot" PropertyName="Text" Type="String" /></SelectParameters></asp:SqlDataSource>Thanks,Stephanie Giovannini
View Replies !
Dbo Qualified Name Problem W/Access 2003
I am the owner of a database with other users. I have given all usersdb_owner role. My server is MSDE 2000.I use the following code to import a spreadsheet:docmd.transferspreadsheet acImport,acSpreadsheetTypeExcel97,"dbo.DL_Funding_Data","theFilename.xls",TrueWhen I use an adp from Access 2000, this code executes successfully forall users. When I use an adp from access 2003, this code gets error3078 and will only work if I remove the "dbo." from the name of thetable.Any ideas on how to correct this. I don't want multiple copies of thecode depending on what version of Access I use as the front-end.Thanks,Jerry
View Replies !
SELECT Of A Table - WITHOUT SCHEMA QUALIFIED
Posted - 09/12/2005 : 15:16:05 -------------------------------------------------------------------------------- Hi I have a schema XXX. This schema owns a set of tables say XXX.A, XXX.B and XXX.C. I have a login XXX mapped to the user XXX. When I connect to the SQL Server using login XXX and execute the query. Select * from A, it throws be an error saying that"Invalid Object Name A". Now when I modify the select statement specifying the schema it works. i.e. Select * from XXX.A In the former case, is it not that SQL Server tries to search for the table in the current user's account and then if it not available it look's in the dbo's account. HOw can I make a select without specifying the schema ? I am using SQL Server 2005. Thanks & Regards Imtiaz
View Replies !
How To Extract The Filename From A FULLY QUALIFIED NAME?
hi folks, i am using foreach loop to load a whole load of files. but these files are placed into various folders and with distinctive names. each file is name is certain conventions that imply what sort of data is held in it. like marketing_extract_20071204.csv customers_extract_20071224.csv but within the foreach loop i have a variable with the FULLY QUALIFIED NAME (FQN)as in: Y:data_filesmarketing_extract_20071204.csv thus i would like to find out how i can extract the filename from the FQN? could i use a derived column expression or a script task? many thanks for your assistance, Nicolas
View Replies !
Schema, Owner And Fully Qualified Name
I have a database that has been upgraded from SQL 200 to 2005. The database was owned by 'Joe' and all objects were also owned by 'Joe'. In SQL 2000 when Joe logged in he could simply issue 'select * from table' and would get results. The upgraded db now has a user Joe and a schema Joe. All objects now belong to the schema Joe. Joe the user has his default schema set to Joe and he is the owner of the schema. When Joe logs in and tries to 'select * from table' he gets an invalid object. He can 'select * from joe.table'. How can I set it up so that the objects are still owned by the schema Joe and a simple 'select * from table' works when user Joe logs in? I was convinced that is he owned the schema and this was his default schema it should work. Thanks scott
View Replies !
SQL ERROR Not Recognizing The Fully Qualified Table Name
Why am im i getting this error I am trying to join four table from different databases.select INBTable.InBound_Calls, OUTTable.OutBound_Calls, INBTable.Line_Number as INLine_Number, OUTTable.Line_Number as OUTLine_Number, INBTable.Hold_Time as INHold_Time,OUTTable.Hold_Time as OUTHold_Time, INBAbandonInfo.INAbandon_Calls as INBAbandon_Calls, OUTAbandonInfo.OutAbandon_Calls as OUTAbandon_Callsfrom sql2.XMWin_Test.dbo.temp_Report20070807IN as INBTable JOIN sql2.Juniper_I.dbo.temp_Report20070807OUT as OUTTableon INBTable.Line_Number = OUTTable.Line_Number sql2.XMWin_Test.dbo.temp_GraphIN_INAbandon as INBAbandonInfoJOIN sql2.Juniper_I.dbo.temp_GraphIN_OutAbandon.Line_Number as OUTAbandonInfo on INBAbandonInfo.Line_Number = OUTAbandonInfo.Line_Number My Error Message: Msg 170, Level 15, State 1, Line 8 Line 8: Incorrect syntax near 'sql2'.
View Replies !
Fully Qualified Table Name Error In A Transaction Staement
Hi, I am getting the following error if I execute the below SQL in Query Analyzer or Stored Procedure begin TRAN select * from <ServerName>.<database name>.<user name>.<table name> COMMIT TRAN I am not able to use the fully qualified name local table name in a Transaction statement. Error is: The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction. OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a]. If I acess a remote server table name(liked server) it is working fine but for local table it is giving an error. If I remove the fully quanlified name and include only table name it is working fine. Can any one hlep me in resolving this issue? Thanks in advance. Regards Vijay
View Replies !
Static Analysis Of Scripts Not Containing A SQL Object Fully Qualified Name?
Hello everyone... Our team has a DBA governing body that refuses to accept any T-SQL script that our dev group writes where fully qualified names of the objects aren't used. These non fully named objects in scripts just aren't accepted in their world. So, all the scripts the developers now have to write must have fully qualified names in them. Of course, the Query analyser in Managment Studio could care less about object names as long as ambiguity is not found. So, I need some sort of Static Code analysis of these scripts that my team creates in order to hand them over to our DBA's to run. The DBA's intend to make this inspection a manual process (they have time to kill I guess)... but my team doesn't have that luxury. It is not easy to ensure all scripts have this qualification in them with some many of them being written. Does anyone know of any algorithim, tool, or options that could help me detect the presence of non-fully qualified names in T-SQL scripts? Thanks for any ideas... Ron D.
View Replies !
Are Duplicate Instance Names A Problem If Physical Server Names Are Different?
If I restore the master database from ServerA to ServerB and start the SQL Server services on ServerA and ServerB, the master databases will be identical and running sp_helpserver and @@ServerName will indicate the SQL Server instance for both instances is ServerA. Is this ok considering the physical server names are different? If this is ok, how does SQL Server advertise on the network who it is? Does it append the physical server name to the SQL Server instance name during network communications? Thanks, Dave
View Replies !
SQL Best Practices Analyzer Rule: Use Of Schema Qualified Tables/Views
SQL BPA says the following:"One or more objects are referencing tables/views withoutspecifying a schema! Performance and predictability of theapplication may be improved by specifying schema names.""When SQL Server looks up a table/view without a schemaqualification, it first searches the default schema and then the'dbo' schema. The default schema corresponds to the currentuser for ad-hoc batches, and corresponds to the schema of astored procedure when inside one. In either case, SQL Serverincurs an additional runtime cost to verify schema binding ofunqualified objects. Applications are more maintainable andmay observe a slight performance improvement if objectreferences are schema qualified."How important is to specify the schame (dbo. in my case) instored procedures? Will it really improve performance if I goand fix each object that is missing "dbo."?The problem is I have thousands and thousands of themwith no schemas. Before I invest a lot of time fixing themI am trying to determine if it's really worth it or not?Thank you
View Replies !
Sqlcmd With Trusted Connection And IP Address Or Fully Qualified Hostname
The command sqlcmd seems to fail when using trusted connection and an IP address or a fully qualified hostname. For example: sqlcmd -E -S nnn.nnn.nnn.nnn where nnn.nnn.nnn.nnn is the real IP address of the machine, or sqlcmd -E -S hostname.domain.com where hostname.domain.com is the fully qualified hostname of the machine, gives the error: Msg 18452, Level 14, State 1, Server 380GX280B05, Line 1 Login failed for user ''. The user is not associated with a trusted SQL Server c onnection. On the other hand, sqlcmd -E -S 127.0.0.1 works, and so does sqlcmd -E -S hostname, or sqlcmd -E -S tcp:hostname,1433. This is on a clean machine, with SQL Server 2005 freshly installed as Administrator with mixed authentication, and the test runned also by Administrator. Is it normal or is it a bug? Thanks. Georges
View Replies !
Sqlcmd With Trusted Connection And IP Address Or Fully Qualified Hostname
The command sqlcmd seems to fail when using trusted connection and an IP address or a fully qualified hostname. For example: sqlcmd -E -S nnn.nnn.nnn.nnn where nnn.nnn.nnn.nnn is the real IP address of the machine, or sqlcmd -E -S hostname.domain.com where hostname.domain.com is the fully qualified hostname of the machine, gives the error: Msg 18452, Level 14, State 1, Server 380GX280B05, Line 1 Login failed for user ''. The user is not associated with a trusted SQL Server c onnection. On the other hand, sqlcmd -E -S 127.0.0.1 works, and so does sqlcmd -E -S hostname, or sqlcmd -E -S tcp:hostname,1433. This is on a clean machine, with SQL Server 2005 freshly installed as Administrator with mixed authentication, and the test runned also by Administrator. Is it normal or is it a bug? Thanks. Georges
View Replies !
Error With Text Qualifier In Qualified Field During Flat File Import
We have a flat file import proces which imports data from a series of unicode flat files. The files have text qualifiers and are being imported to a table with the following format: CREATE TABLE [dsa].[OBS]( [Kundenummer] [nvarchar](10) NULL, [Navn] [nvarchar](60) NULL, [Adresse] [nvarchar](50) NULL, [PostnrBynavn] [nvarchar](50) NULL, [Kursusdato] [datetime] NULL, [Varighed] [decimal](18, 2) NULL, [Kursustype] [nvarchar](100) NULL, [Risikokoder] [nvarchar](50) NULL ) ON [PRIMARY] In one of our files we have two rows that looks like this: "19298529";"THIS IS ROW 1";"ADDRESS 9 -13";"4200 SLAGELSE";"02-05-2006";8.00;"Kombikursus Førstehjælp - Brand 8 lek.";"37" "19448242";"THIS IS ROW 2";"ADDRESS 50";"4140 BORUP";"04-05-2006";4.00;""Fra vil selv - til kan selv". Om børn 1½ - 3 Ã¥r";"22" Both rows are OK according to the format, but the second row actually contains the text qualifier in one of the qualified fields (""Fra vil selv - til kan selv". Om børn 1½ - 3 Ã¥r"). It's the title of a course with a comment. The proces fails on this file, and wont even redirect the row, as it does on other erroneous rows in other files we import. We believe this is a valid text, but apparently SSIS doesn't Is this a bug or is this record not allowed? Is there a work around, and why wont SSIS redirect the row? We believe the reason is that the field before is not text quaified (which is of course specified in the connection manager). Thanks in advance, Lasse
View Replies !
One Or More Of The Server Network Addresses Lacks A Fully Qualified Domain Name (FQDN).
Hello Guys, I had been trying to solve this error with no success : One or more of the server network addresses lacks a fully qualified domain name (FQDN). Specify the FQDN for each server, and click Start Mirroring again. The syntax for a fully-qualified TCP address is: TCP://<computer_name>.<domain_segment>[.<domain_segment>]:<port> I had installed three instances on my local machine to test Data base mirroring : Principal : running SQL Developer Instance Mirror : running SQL Developer Instance . Witness : Running SQL Express. Database mirroring already enabled using startup flag : -T1400 i even tried to configure it with out a witness but still have the same error . I used the follwoing server name in the mirroring wizard(not localhost) : Principal : TCP://Ali-laptop:5022 Mirrored : TCP://Ali-laptop:5023 Witness : TCP://Ali-laptop:5044 whats the problem guys?!
View Replies !
DB Mirroring: Different Server Names With Same Instance Names
I'm going to be setting up DB mirroring between two SQL Server 2005 boxes. Since these are on two different servers, can the instance names be the same? Is there any reason NOT to do so if the mirror server is going to be used exclusively for DB mirroring? For example: if the my primary DB is located on SERVER1INSTANCE1, can the mirror be SERVER2INSTANCE1 or do the instance names have to be different even though they're on different boxes. Thanks!
View Replies !
Table Names And Field Names
I'm trying to do an update query that looks like this: UPDATE PAEMPLOYEE SET PAEMPLOYEE.LOCAT_CODE = EMPLOYEE.PROCESS_LEVEL FROM PAEMPLOYEE A JOIN EMPLOYEE B ON A.EMPLOYEE = B.EMPLOYEE It's erroring out on the Employee prefix B.EMPLOYEE saying: ..."does not match with a table name or alias name used in the query" Is it wrong or will it cause problems to have a field name the same as the table name?
View Replies !
Server Names Or Instance Names
Hello there. I'm trying to populate a drop down box with a list of all available instances of SQL server (express or not) available on a network. To list all instances I'm using EnumAvailableServers() in SmoApplication. The issue is that the Instance column in the returned data table is always empty, and both the Name and the Server columns contain the same string -- the name of the computer the SQL server is installed. Locally I have SSE installed, the instance name is <computer_name>SQLEXPRESS, however, using Smo I can't seem to get to the either full instance name or just the instance name. I can't assume SQLEXPRESS, since there may be more than one installed. Alternately I used EnumRegisteredServers() in SmoApplication.SqlServerRegistrations and that, while returning instance information (can be retrieved from the RegisteredServer in the returned collection), doesn't always return the remote SQL servers in the list. Any idea how can this be done? Maybe I'm going at it the wrong way, maybe there's another class / method I need to use to retrieve all instances? Thank you
View Replies !
Service Broker Not Working For Restored Databases (SQL 2000 Databases Restored On 2005)
I just restored my SQL server 2000 database on the SQL server 2005. after this i ran the Service broker sample ("Hello World") on this database by changing the AdventureWorks name to the new database name. The "setup.sql" runs fine. When i run the "SendMessage.sql" i was not getting any rows in the output (The message was not getting inserted into the queue). I checked the Service broker is enabled on this databased using the query "select is_broker_enabled from sys.databases where name = 'newdbname' " It was 1. I even tried the ALTER DATABASE SET ENABLE_BROKER. but it didnt work. When i tried the sample on a newly created database it worked fine. Is there any solution to make the restored database to work for service broker. Thanks Prashanth
View Replies !
Copying Databases Between Databases
hi from France !!! i would like how to duplicate a database to another server with all datas, constraints, keys, indexes... should i use sp_attach_db, dts, backup/restore, sql scripts... ??? thanks to all, nico
View Replies !
Get Column Names
Hi,How do I display the column names from my Sql server table?In asp3 the recordset allowed thisforeach key in rs ColumnName = key.name ColumnValue = key.valuenextHow do I do this in .Net?I want to use a DataReader so I can read through each record and only display the ones I want.TABLE_ONEColumn_OneColumn_TwoColumn_Threethanks,
View Replies !
Getting The Column Names
Hello, Im trying to get the column names from a database and display them in textboxes. someone has already helped me by tellnig me that i need to use the FillSchema command. Which works just fine and I can see only the colum names in a datagrid when i bind it to that. The problem is that I do not know how to extract the name of a column and put it in to a textbox ? does anybody know how I can fo this ? Thanks a million Rob
View Replies !
Weekday Names
Sql server 2000 -sql query analyzerselect datename(dw,'01-01-2006')returns sundayNow I do:set language danishselect datename(dw,'01-01-2006')returns sonday. But it should be søndag. Same goes forsaturday/lordag/lørdagTried substring'ing and ascii'ing/unicode'ing and it is AFAIK an o insteadof the special danish character ø.Is this a "feature" or am I doing something wrong?I "solved" it by doingselect case DATENAME(dw,'01-01-2006') WHEN 'lordag' THEN 'lørdag' WHEN'sondag' THEN 'søndag' ELSE DATENAME(dw,'01-01-2006') ENDbut it sure aint pretty./jim
View Replies !
Backup Names
Okay, here is a real mellon scratcher. I've got a server I've gotsemi-automated backups on. A typical backup looks like:BACKUP DATABASE MasterTO DISK = 'c:ackupsMaster20070418.bak'WITH FORMATThen I'll do a log backup if applicable (Not for Master). I then getsome error about the device being offline. But if I run:BACKUP DATABASE MasterTO DISK = 'c:ackupsMaster20070418.log'WITH FORMATthen it works fine. The ONLY thing I've changed is the extension onthe backup file. But that is what I wanted to name the log. As aworkaround I have the backups saved as:Master20070418-bak.log and then name the log files the way they shouldbe. That works, but I don't like it.Security is full access to Everyone, so I don't think it is an access/permissions issue.Any ideas?Thanks,-Utah
View Replies !
SP And Transaction Names
Is it possible for MS SQL 2k to get confused when we have two stored proces that has the same name for their transactions and both stored procs are exec at the same time? Example: Create Procedure SP1 as Begin Transaction TranName1 Commit Transaction TranName1 Begin Transaction TranName2 Commit Transaction TranName2 Create Procedure SP2 as Begin Transaction TranName2 Commit Transaction TranName2 Begin Transaction TranName3 Commit Transaction TranName3 We are seeing some very weird data corruption but luckily its been way under 1%(something like 500 bad rows out of a few million). The only thing we can think of since it is such a very low number is maybe when blocking occurs SQL has trouble handing two transactions with the same name in its queue. Beyond that we have no idea what could be causing this issue.
View Replies !
NT User Names
Hi Is it possible to obtain an NT username (with T-SQL) from a user connecting to SQL Server with a SQL log in via ODBC? Thanks in advance
View Replies !
Different Server Names
Hello, I am using ADO controls for MSSQL Database. The server name on my machine is different from the server name on the machine where i am running my application. When i make changes to my application, i have to rebuild the connection on my ADO Control. Is there a way to create a server name on my developement machine to match the server name where i run my application? Would appreciate any help.
View Replies !
Alternate Names
I need to implement support for alternate names on the database I’m working on. When a user looks up: “Antony Bigglesworth” the query would return both: Antony Bigglesworth as well as Tony Bigglesworth, would both exist. Anyone has a best practice on this or has done this before?
View Replies !
.mdf And .LDF File Names
Hi All, I am using query analyzer to execute my sqls . I want to take out .mdf and .LDF files and put it in another machine . But there is no .mdf or .LDF file with prefix as my database name . I think the file name might be something else . Please help me in finding out the .mdf and .LDF of a particular database . Thanks , Sushi
View Replies !
Bad Index Names
I have inherited a database which had no clustered indexes at all, only non-clustered. WHen I started experimenting with creating some I got error messages back complaining that the name already existed in sysobjects. I have come to find out they tool they use to create the indexes, names the index the same as exisiting table(isn't this a great idea). Other than my current problem, does anyone see other issues might run into. My thought is to drop all the indexes with the table names and recreate them with a better standard. Has anyone else ever run into or done this? THanks
View Replies !
Matching Names
My company is going to start a Cancel web site so customers can cancel their future orders by simply filling out a form. I was wondering what would be the best way to compare the cancel db to db that is used to store customer information and order information. The cancel website will only hold the name, address, and Credit Card Number used. There will not be a customer/order number. The simple Credit Card to Credit Card search will be easy but I was more worried about comparing the rest of the information in case the Credit Card search fail. I was told the "standard" look up was to take the first 3 letters of the first name and the last 3 letters of the last name. If that comes up with more then one record, take that set and see if the zip code matches up and/or part of the street address matches up (counting we do not have very reliable people inputting the information into the system is not going to help this search). Anyway, any suggestions on comparing the data would be great. Thanks ahead of time
View Replies !
Long Names
I have created a database which has a table whose name is 128 characters long; this table has a column whose name is 128 characters long. When I issue the command sp_helpconstraint table_name, SQL Server 7 returns the message "string or binary data would be truncated." Any suggestions? Thanks!
View Replies !
Proper Names
Does anyone have a simple procedure to correct all caps or all lower case to proper case e.g. BOB to Bob?
View Replies !
|
TRACKER
