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", and
that 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 qualified
table name? Are both OK? If so, what would be the implications of
each?
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.
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 8 Replies View RelatedIm 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.
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
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'.
I am designing a package to export staging tables into a flat file.The names of the tables will be: TableAStaging_YYYYMM and TableBStaging_YYYYMM. As you can see the names of the tables will be changing each month.
The flat files will have similar naming: C:MyPathFlatFileTableAStaging__YYYYMM and C:MyPathFlatFileTableAStaging__YYYYMM.I want to run the package as an sql job in two steps, one for each table.I need to dynamically pass the table names and file names (together with the path) to the IS package.
Hi
I was wondering if anyone has an idea of how we could find the table names and column names of the tables in our Sql server database at runtime/dynamically given our connection string? Please let me know.
Thanks.
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?
Hello,
Maybe anyone have done that before?
I have table where i store SOURCE_TABLE_NAME and DESTINATION_TABLE_NAME, there is about 120+ tables.
i need make SSIS package which selects SOURCE_TABLE_NAME from source ole db, and loads it to DESTINATION_TABLE_NAME in destination ole db.
I made such SSIS package. set ole db source data access mode to table or view name variable.
set ole db destination data access mode to table or view name variable. set to variables defoult values (names of existing tables)
but when i loop table names is changed, it reports error, that can map columns, becouse in new tables is different columns.
how to solve that problem?
How do I use table names stored in variables in stored procedures?
Code Snippetif (select count(*) from @tablename) = 0 or (select count(*) from @tablename) = 1000000
I receive the error 'must declare table variable '@tablename''
I've looked into table variables and they are not what I would require to accomplish what is needed.
After browsing through the forums I believe I need to use dynamic sql particuarly involving sp_executesql. However, I am pretty new at sql and do not really understand how to use this and receive an output parameter from it(msdn kind of confuses me too). I am tryin got receive an integer count of the records from a certain table which can change to anything depending on what the user requires.
Code Snippet
if exists(Select * from sysobjects where name = @temptablename)
drop table @temptablename
It does not like the 'drop table @temptablename' part here. This probably wouldn't be an issue if I could get temporary tables to work, however when I use temporary tables i get invalid object '#temptable'.
Heres what the stored procedure does.
I duplicate a table that is going to be modified by using 'select into temptable'
I add the records required using 'Insert into temptable(Columns) Select(Columns)f rom TableA'
then I truncate the original table that is being modified and insert the temporary table into the original.
Heres the actual SQL query that produces the temporary table error.
Code Snippet
Select * into #temptableabcd from TableA
Insert into #temptableabcd(ColumnA, ColumnB,Field_01, Field_02)
SELECT ColumnA, ColumnB, Sum(ABC_01) as 'Field_01', Sum(ABC_02) as 'Field_02',
FROM TableB
where ColumnB = 003860
Group By ColumnA, ColumnB
TRUNCATE TABLE TableA
Insert into TableA(ColumnA, ColumnB,Field_01, Field_02)
Select ColumnA, ColumnB, Sum(Field_01) as 'Field_01', Sum('Field_02) as 'Field_02',
From #temptableabcd
Group by ColumnA, ColumnB
The above coding produces
Msg 208, Level 16, State 0, Line 1
Invalid object name '#temptableabcd'.
Why does this seem to work when I use an actual table? With an actual table the SQL runs smoothly, however that creates the table names as a variable problem from above. Is there certain limitation with temporary tables in stored procedures? How would I get the temporary table to work in this case if possible?
Thanks for the help.
I have a function that returns a table from a comma-delimited string.
I want to take this a step further and create a function that will return a set of tablenames in a table based on a 'group' parameter which is a simple integer...1->9, etc.Obviously, what I am doing is not working out.
CREATE FUNCTION dbo.fnReturnTablesForGroup
(
@whichgroup int
)
RETURNS @RETTAB TABLE (
TABLENAME VARCHAR(50)
[code]....
Hello,
It is possible to write stored procedures which take table names as parameters; is it also possible to do this with table valued functions?
For example, a simple stored procedure is this:
CREATE PROCEDURE SelectTop(@tableName sysname)
AS
BEGIN
Execute('Select top 10 * from ' + @tableName + ';')
END
I want to be able to do the analogous thing with a table valued function (so that I can query the result set, without having to create a temp table). How should I do this (i.e., pass a tablename as an argument to a table valued function)?
Hi there. I haven't been able to figure out how to join a table on column on multiple table names. Here's the situation:
I have a table "tblJob" with a key of jobID. Now for every jobID, the program creates a new table that keeps track of the stock before the jobId was processed and after it was processed to give accurate stock levels and show the difference in stock levels. So, a jobID of 355 would be related to the table: "tblPreStock_335" and "tblPostStock_335". These 2 tables have all the materials in stock and the quantity. Therefore they show how much material was used. I need to figure out the difference in the material in the stock before and after the processing.
That means that I have to get a stockID, get the associated pre and post tables, and then display the difference of ALL the materials in the pre and post tables.
Could someone help me get started on the right path? Even a link to similiar problem that I haven't found would be nice.
Thx
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
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
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 1 Replies View RelatedI 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
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
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 3 Replies View RelatedOracle provides for a table to have an alias name.
Does SQL Server 7 support alias names for tables?
Thanks!!
I like to print the table names that starts with 'table'.
I'm getting the same table name four times. Instead of four different table name.
create table table1(tableID int, tableName varchar (30))
insert table1
select 1, 'tableSam' union all
select 2, 'tableDaniel' union all
select 3, 'tableRon' union all
select 4, 'tableKen' union all
select 5, 'tableHonda'
create table table2(tableID int, tableName varchar (30))
insert table2
select 6, 'tableSam' union all
select 7, 'tableDaniel' union all
select 8, 'tableRon' union all
select 9, 'tableKen' union all
select 10, 'tableHonda'
create table table3(tableID int, tableName varchar (30))
insert table3
select 11, 'tableSam' union all
select 12, 'tableDaniel' union all
select 13, 'tableRon' union all
select 14, 'tableKen' union all
select 15, 'tableHonda'
create table table4 (tableID int, tableName varchar (30))
insert table4
select 16, 'tableSam' union all
select 17, 'tableDaniel' union all
select 18, 'tableRon' union all
select 19, 'tableKen' union all
select 10, 'tableHonda'
declare @string varchar(330)
declare @tableName varchar(30)
declare @count int
set @count = 0
while (@count < 5)
begin
select @tableName = table_name from information_schema.tables
where table_name like 'table%'
exec(@string)
print @tablename
set @count = @count + 1
end
In SQL Server Management Studio when browsing the tables they are all prefixed with dbo followed by the table name. Is there a way of turning this off.
Thanks
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.
Is there a way i can create a query that gives me a list of all the table names in the database?
View 2 Replies View RelatedHi all,
By using below query i can get no of tables having the give column name in a particular database,
SELECT COUNT(*) AS CounterFROM syscolumnsWHERE (name = 'empno')
but i want to know the table names too?
any one please suggest me how to find table names too......
In other words i know the particular column name and right now i want to know the table names in which this column name exists.
hi...
can someone pls tell me the sql statement to
1)Select all the tables in SQL server(only the ones created by me)
2)Select all the column names from a given table
tks a lot... :)
Please help,
I am inserting data into a newly created table with many rows similar to thoes below using a sp with a parameter @Extract
CREATE PROCEDURE mytest
@Extract int
AS
DECLARE @MyName varchar(25)--these keep changing for each total
DECLARE @Counter varchar(2)--this will increment from 2 to 40
SELECT @MyName ='tblG3>74Ex' +convert(varchar(3), @Extract)
SET @total1 =(SELECT COUNT(*)
FROM @MyName
WHERE (sSurgery_id ="S0" + @Counter) and (iExtract_nm = @Extract)
SELECT @MyName ='tblG335-74Ex' +convert(varchar(3), @Extract)
SET @total2 = (SELECT COUNT(*)
FROM @MyName
WHERE (sSurgery_id ="S0" + @Counter)and (iExtract_nm =@Extract)
insert into tblNew([35-74],[>74],etc,etc,Report4)
values (@total1,@total2,ect,ect,@report4)
it all works until I try to use the @MyName
Please put me out of my misery
Many thanks
David
While setting up a BCP in a DTS package, I've found a rather annoying 'feature' in DTS. When selecting the destination table, the display does not provide enough spaces to see the full table name. It is a drop down with a set width, and the database and owner are displayed with the table name. I hadn't noticed this problem in the past, but the database I'm working with now has several tables that start with the same letters, and when the drop down displays them, they all look the same.
Does anyone know of a work around for this so that I can see the full table names? Even being able to see them after the package is created would be nice, because then I could check to see if I guessed the correct table.
Thanks
Can I do something like the following with SQL Server?
DECLARE @Table_Name varchar(100)
SET @Table_Name = 'Accounts'
SELECT * FROM @Table_Name
Using VB5|6/ADO/MSSQL6.5. I want to get the names of tables in a query. I.e. if query is "Select Name, Title from Employee inner join job on employee.empid = job.empid" I want a way to get "Name, Title"
In VB 3 we used the VBSQL.VBX which had 2 functions for this: SQLTabCount and SQLTabName.
Thanks in advance
DP
Hi All,
I was wondering how would I get the column names from a table? Not the results just a listing of column names. What is the command to get this information? I am using MS Access, but I posted here because MS SQL is the closest thing on this forum and the syntax is usually similar.
Thanks a bunch.
Val
Hi, I need do get a columns names from a table? How to do this in pure SQL? I thought about creating a stored procedure or user function with a result of a string ( col1name,col2name ....) I do not know how to count the number of columns in a specyfied table? Any help would be appreciated. Thanks in advance. magicxxxx
View 2 Replies View Related