Hi, while I'm trying to do Select statements, is there a way/setting whereby I don't have to constantly prefix the table names with a prefix?
For example, I'm working in multiple environments and they're differentiated by the prefix in their table names, uat.** vs prod.**
Any help would be appreciated.
Please email me at dickson.lau@rogers.com
Thanks again.
Dickson
Hi all,
I'm using SQL Server 2005 Express Edition, and I have restored a database that was using SQL Server 2000 previous. All of that went fine - sprocs and tables/data all made it.
However, when viewing the tables in the object explorer in the management studio, all the tables are prefixed with the schema name (testuser - so the table names are like: testuser.pn_user). Now, all of the code I have queries the tablename only without prefixing testuser to it - but this doesn't work anymore (invalid object name.)
I've tested, and if I prefix the table names in my code with "testuser.", it starts working again, but would rather avoid doing this. Is there something I can do within Sql Server? I've made the "testuser" account "mapped" to the database I'm using (not sure what that means - I'm more of a software developer than a database guy.) Also, when I do "Properties" for the "testuser," the default database is set to the one I'm trying to query w/o the schema.
Any ideas?
When I create a table in sql server database name appears in prefix of table.
My database name: Digitall
My table name: Digitall.News
How can I remove Digitall prefix from my tables?
For our ETL process, we maintain a TransformationList table that has the source view and the destination table. Data is copied from the view into the table (INSERT INTO). I am trying to find column names in the Views that are not column names in the associated Table.
In the below example, want to end up with three records:
1, View1, Column4
2, View2, Column4
2, View2, Column5
I have it almost working, except that there is a table, ChangeColPrefix table, that is used by the ETL process to change some of the view's column name prefixes. Some of the source views have column names with prefixes that do not match the destination table column names. Say view SouthBase has all the column names prefixed with SB - like SBAcct, SBName. And the Destination table of Area District has ADAcct, ADName. There would be a row in the ChangeColPrefix for SouthBase, SB, AD, 1, 2 that would be used by the ETL process to create the INSERT INTO Area District From SouthBase.
I need to use this ChangeColPreifx to find my unmatching columns between my source views and destination tables. With out that table SBAcct and SBName from SouthBase will not appear to match the columns of ADAcct and ADName, but they do match.
I want to end up with these three records as non-matching:
View1, Column4
View2, Column4
View2, Column5
View1 has Salumn2 and View2 has Salumn5, and they must be changed to Column2 and Column5 as per the ChangeColPrefix table before running the Select from INFORMATION_SCHEMA.COLUMNS EXCEPT Select from INFORMATION_SCHEMA.COLUMNS looking for unmatched columns.
/***** Set Up Test Data *****/
-- Create 2 test views
IF EXISTS(SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[View1]'))
DROP VIEW dbo.[View1]
GO
CREATE VIEW View1
AS SELECT '1' AS Column1 , '2' AS Salumn2 , '4' AS Column4;
[Code] ....
Is there a way we can prevent a object owner from dropping his tables. Example:
There is a user called 'tom' who is given create table permissions, 'tom' creates the table completes his dev and then the table is moved into production where 'tom' is still the owner. However, in production, 'tom' does not have the create table privs. Because 'tom' is the owner of the table, he is still able to drop the table in production. This is what I am trying to avoid. I would like to retain 'tom' as the owner and want to take away his create/drop privs.
Hi
I am using SQL server 7 database and ASP as front end. I run an application where a text file is loaded into database. After this is done procedures are run to create a set of tables that have snapshots of the data in the text file.. each time i load a new text file i want to create the snapshots.. i hve written a stored procedure to create tables and insert values into the tables.. however how do i delete the tables i created the previous time.. the number of snapshots and their names will depend on the size of the text file.. how do i refer to all the snapshots created and drop them all before creating new ones?
plese guide
regds
I used the set showplan_all on command to get some statistics; I can't use a server trace and I don't like the graphical thingummy.
I ran a sproc that contained three drop table commands; SQL Server refused to drop those tables when set showplan_all was on. I turned it off and it dropped them fine; why can't I drop tables with set showplan_all on?
Hi,
I have founde such a nice code to create statements for dropping all statistics in a database.
DECLARE @tblname sysname, @statname sysname, @sql nvarchar(2000)
DECLARE c CURSOR FOR
SELECT object_name(id), name FROM sysindexes WHERE INDEXPROPERTY(id, name, 'IsStatistics') = 1
OPEN c
FETCH NEXT FROM c INTO @tblname, @statname
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'DROP STATISTICS [' + @tblname + '].[' + @statname + ']'
PRINT @sql
FETCH NEXT FROM c INTO @tblname, @statname
END
CLOSE c
DEALLOCATE c
Please help me with changing this code to take care only on indexes from my own tables (no system ones).
Thanks for your help.
Przemo
Hi,
I want to drop a Global temp table within a stored procedure.
But first, I want to check it's existence.
So, what I would like to do is something like this
if exists
(select * from sysobjects where name like '##globaltemptable')
drop table ##globaltemptable
But I don't think the global temp tables get stored in sysobjects.
Any suggestions?
Thanks in advance
Hi,
I have created a bunch of tables in the Master db by mistake.I want to drop those tables.Please tell me away to drop those.
Thanks!!
This is my problem
Table A Columns:
OriginatingNumber,
TerminatingNumber
Both of these columns contain npanxx numbers which would be the first 6 digits of a phone number, example 217345 which is in illinois.
Table B Columns:
npanxx,
state
table B hold all npanxx numbers in the united states and what state it pertains to.
what i need to do is run a query to check and see what state the OriginatingNumber and the TerminatingNumber are in and decide if both numbers passed are in the same state or not.
so for example in Table A Row 1: OriginatingNumber is 217345 and the TerminatingNumber is 309454. I need to check both of these numbers against table B and it would return that both numbers are in IL. Basically I need to return a result of yes or no. yes they are in the same state or no they are not in the same state.
Any help would be great
Thanks for reading,
Nick
I have a database consisting of two main tables and 12 sub tables.
This was leading to increase in database size. So we thought of storing the sub tables data in the main tables in form of xml in a column of varchar(2000) type.
So we created a new database that only had 2 tables and we stored the data of the sub tables in the new column of the main table.
Surprisingly we saw that the database size increased rather than decreasing .
Hi all,
lets say i have a table as follows
id name1 red2 orange3 green
and this has s join to another table via the id
table2
id event 1 stop2 stop3 start
now if i make a view and drop these two tables the jkoin is there but i would liek to report on all names who have a event of "go"but it gives me no results , which is right . but how can i output all the names and in the evnet put a empty SO
name eventred emptyorange emptygreen empty
thanksrobby
Hello,I am quite new to ms-sql and I have a problem : I want to create an SQLrequest which would copy serveral records from a table in a given databaseto another table (with exactly the same structure) in another database(theses two tables and databases already exist).Could you please tell me how to do this ? I dont know how to access twodifferent databases in a single SQL request.Thank you for you help.
View 3 Replies View RelatedHi all,
How do I query two tables in different databases on the same SQL Server?
In short, I want to do:
Select A.*
from database 1. table 1 as A
inner join database 2. table 1 as B
on A.COL1 = B.COL1
Both database 1 and 2 are on the same SQL Server.
Please advise.
Thanks,
V
I get a 90-120 second blocking when send 15 or so simultaneous queriesto SQL Server 2000 that query a view made up of two joined tables.After each query is blocking for the same amount of time they allreturn. Further identical queries of this type work in 3-4 seconds(caching?) until hours later where it happens again. If I query thetables directly (without the view) I still get the same blocking. If Iremove the join (it is a simple inner join on two columns) I do not getthe blocking.Any ideas?
View 3 Replies View RelatedThis seems like a very simple question but i have never been able tofind an easy answer to it.I have a user table and i do a join with another table, we'll call theother table a results table.The results table has numerous rows with the userid foreign key.I want to make a query that will give me the number of rows in theresults table for each user where the result is some valueThe query is simple to make but will only show the users who have arecord in the results table the meet the where criteria, however i wantto display each user and show a record count of 0 when there are noresults in the results table that match the criteria.for example i have 2 tables.tblUsers_______________userid | username--------------------------1 | user12 | user2tblAnswers________________userid | answer----------------------------1 | 11 | 01 | 42 | 12 | 0if i run the query:select max(username), count(answer) from tblUsersleft outer join tblanswers on tblAnswers.userid = tblUSers.idwhere tblAnswers.answer = 4group by tblUsers.idi just getuser1 | 1i want to getuser1 | 1user2 | 0the only way ive found to do this is with a temp table and a curser tocreate all the users records and go back through an insert the answercount for each user. This approach seems very expensive and requires aquery that is 3 times larger than is needed for the same resultswithout including 0 count records. I know there must be a better way todo this.Any help is appreciated.
View 2 Replies View RelatedI am trying to query multiple tables that have the same column. They are a supposed to be a unique ID to the product, but not to the database. I have a primary key already set to each othe tables. The tables do not share anything between them, but i would like to search for these IDs in other tables before my insert command is executed. I am writing my code in VB. example of what i am trying to accomplish.
Table1 = TVs
ID/SKU_ID/Manufacturer
1 87654 Samsung
Table2 = DVD_Players
ID/SKU_ID/Manufacturer
2 765234 Kenwood
I want to search for SKU_ID in multiple tables( I think a total of 9 tables). I want to search between tables to catch for the same SKU_IDs. and if it finds it, raise error. I will be able to figure the vb.net portion. I just dont know how to go about the sql query portion. Thanks in advance and appreciate any help. Thanks!
What I am trying to do now is combine multiple complex queries into one table
and query it showing the results on an ASP.net page.
I am currently using SQL Server 2000 backend. Each one of these queries are
pretty complex so I created each query as a Stored Procedure. These queries are
dynamic by each user, so the results will never be the same globally.
What I have done so far was created a master stored procedure passing the
current user's username as a parameter. Within the master SP (Stored Procedure)
it creates a temporary table inserts and executes multiple stored procedures and
inserts into the temporary directory. Each of the sub stored procedures all
have the same columns. After the insert to the temp tables, I then query the
temp table and return it to the function it was executed in code and fill it as
a System.Data.DataTable. I then bind the DataTable to a
Repeater.DataSource.
Problem: When the page is rendered, it returns nothing. I
tested the master SP in the data environment and it works fine.
Suspect: ASP.net when the SP is executed, it sees that the
data is a disconnected datasource? Perhaps the session in the SQL Server when
the temp table is created isn't in SYSOBJECTS system table?
Here is an example of the code from the master SP:
CREATE PROCEDURE dbo.TM_getAlerts @Username
varchar(32)ASCREATE TABLE #MyAlerts ([DATE] datetime, [TEXT]
varchar(200), [LINK] varchar(200) )
INSERT INTO #MyAlertsEXEC
TM_getAlertsNewTasks @Username
INSERT INTO #MyAlertsEXEC
TM_getAlertsLateTasks @Username
SELECT [DATE] AS 'DATE', [TEXT]
AS 'TEXT', [LINK] AS LINK FROM #MyAlerts
GO
It is a fairly simple call... but why doesn't ASP.net doesn't see it when the
the DataTable is filled. I tried just executing one of the sub SP without
creating temporary tables and it works flawlessly. But the query in one of the
sub SP is a normal but complex select.
I think this should be a dead easy one.
SQL Server 200.
Can I query to find all the tables in a single schema? I can't for the life of me find this one out!
I'm currently using a dreaded cursor with an exists test to get the results I want but there is surely an easier way!
Cheers,
G
Hi;I am looking for suggestions about how to solve a problem using tsql.I have been asked to create a report concerning 4 tables.Each of the 4 tables is in its own database.The 4 tables are identical in name and structure.I would like to query all 4 of these tables and get the results as ifthey were one table.All suggestions welcome, thanks.Steve
View 2 Replies View RelatedHi, all:This is probably a simple problem, but, as an SQL newbie, I'm having alittle trouble understanding multi-joins and subqueries.I have the following tables and columns:MemberTable-----MemberID (primary key)MemberNameAddressCountryFoodsTable------FoodID (primary key)FoodNameMusicTable-----MusicID (primary key)MusicNameMoviesTable-----MoviesID (primary key)MoviesName....and their linking tables...Members2FoodsTable-----MemberID (foreign key)FoodsID (foreign key)Members2MoviesTable-----MemberID (foreign key)MoviesID (foreign key)....and so forth.Now what I'm trying to do is retrieve a specific MemberID, his address info(from the Members table), and get a listing of his favorite Movies, Foods,Music, etc. I know I probably need to JOIN tables, but where do I JOIN thetables? Do I have to JOIN the various Music/Foods/Movies tables or is itthe Music/Members2Music and Foods/Members2Foods, etc. tables? And I assumeI would probably need to perform a subquery somewhere?I realize I'll need to first filter the Members, Members2Music,Members2Foods, etc. tables by the MemberID, and afterwards, retrieve alisting of the actual Music/Foods/Movies names. I'm just confused how to dothat. (By the way, I have a total of 10 other tables or in addition toMusic, Foods, etc. so it's a lot of table JOINing.)If someone could please help me out with the actual SQL coding, I'd reallyappreciate it!Thanks for the help!J
View 6 Replies View RelatedPlease, could anyone tell me how to get information from which attribute of which table is the attribute from the view derived(it could also be a complex expression, not just attribute)through querying system tables or views(INFORMATION_SCHEMA, preferably if possible, because it's standard)
i'm using MSSQL2005.
Thank you!
I am learning T SQL and SQL queries and have limited VB knowledge, and have a some simple queries to run on a table with parameters, and would like verification of the proposed methodology and suggestions.
Simply put, I have a [Transactions] table with columns [Price], [Ticker], [TransDate], [TransType] and calculated columns for [Days] and [Profit].
There are two parameters, [@Dys] (to query a the table for transactions within a certain period[Days = 30] and [@TT] to query only the closed transactions ie... [TransType='C']
I have been studying Stored Procedures and will be writing a Stored Procedure, but need verification if the following will work... Getting the SUM and AVG calcluations for the fields above is not a problem but I need to display SUM and AVG information also for those transactions where [Profit >0] and [Profit <0], which is easy enough by creating a subquery. But the problem is:
1. If I use a SubQuery for [Profit <0] and for [Profit>0], can I create an alias for [Count(*)] (to get a row or transaction count for each, and then divide that into the Total [Count(*)] alias for the Transactions table to get a value for % profitable or Probability (% total Profitable trades versus % total Unprofitable trades)?
2. Or, do I need to create either temporary tables or views to have 3 distinct tables (1 table for Transactoins and 2 temp or Views for [Profit >0] and [Profit <0])?
Any suggestions and advice or examples on how to do this would be appreciated.
Craig
My questions are:
I need help in writing a query.
The query should get top 10 items and their values from current year and the values for the same items from previous year table.
I was able to write the code for 1st part that gets values from 1st table but I don't know how to get the values from 2nd table.
The 2 tables does not have any primary/foreign key relations. Both tables have same structure and same columns.
I am attaching some images below to give more information.
Image of results from my query.
Image of how the final output should look like.
The Store Procedure code is:
ALTER Procedure [dbo].[free_customsHS4](
@TblName1 varchar(20),
@TblType varchar(20),
@District varchar(6),
@Month varchar(3)
)
AS
Begin
SET NOCOUNT ON;
Declare @SQuery nvarchar(3000)
set @TblName1 = '[' + @TblName1 + ']'
set @TblType = '[' + @TblType + ']'
SELECT @SQuery = 'select top 10 a.commodity1 as HS4, b.descrip_1 as Description,
sum(a.all_val_mo) as [Amount],
(sum(a.all_val_mo)/(select Sum(a.all_val_mo) FROM ' + @TblName1 + 'a
where a.stat_month <=' + @Month + ' and a.district=' + @District +'))*100 as [% Share]
FROM ' + @TblName1 + ' a left outer join ' + @TblType + ' b on a.commodity1=b.commodity1
where a.stat_month <=' + @Month + ' and a.district=' + @District +'
Group by a.commodity1, b.descrip_1
order by [Amount] desc'
EXEC sp_executesql @SQuery
END
Dear Readers,Is it possible, like in Access, to link to tables in other SQL databases that are on the same server? I have a query that I originally had in Access that queered from multiply databases. It did this by having those other tables in the other databases linked to the database that had the query.
I am trying to query the Topics in my discussion forum...The Topic contains a "last_poster_id" and a "author_id" I need the username and userid for both "last_poster_id" and "author_id" in the table "aspnet_Users"How do I do this?I would guess I need to use sub select statements. Can someone help me?
View 12 Replies View RelatedAm I right in saying that the MS Full-text search engine does not support a wildcard at the beginning of the word? Only at the end? E.g. "VB*" works but "*NET" doesn't.
View 2 Replies View RelatedHi, I have created a table with the following
docmd.runSQL "Create Table #Test(A int)"
in Access 2000 and cannot find the table on my table list, it's not on my enterprise manager either. My guess is that maybe it has something to do with the prefix #? So I started searching for "Create table #" on google and dbforums and cannot find anything. Can someone tell me what's special about having a #prefix on a table and where can I find more information about it?
Thanks
SHK
Good Morning Db forum.
I am working on a query that I need to pull all fields that contain 3 alpha characters. for example BCB001, MCR001, CHP001 and so forth.
Is there a SQL alpha wildcard that I could use to pull all records that have the three alpha chars?
What does the ap_ stand for as a prefix to stored procedures?
A DBA tried to teach me something about the prefixes etc.. but I can't recall what it meant.
In this case ap_ may represent "ONE" persons naming convention?
something procedure...
other common ones in our database are dt_ and zz_
SQL 2000
I am testing a query for use in Crystal Reports. It was copied from an existing query with the necessary adjustments. The first part of it works correctly;
SELECT NA.*
into #cl_temp
FROM OLT.dbo.NACBTR NA
WHERE NA.CourseCode in ('RGF00001','RGF00002','RGF00005','RGF00006', 'RGF00038','RGF00039','RGF00040','RGF00041','RGF00042','RGF00043') And
NA.completedDate >= '01/01/2006' and
NOT EXISTS (SELECT * FROM hrdw.dbo.E_View EV WHERE NA.ssn = EV.ssn)
but when I add the second line;
select #cl_temp.*,
ISNULL((select 1 from #cl_temp where #cl_temp.coursecode = 'RGF00001'),0) as fire_yes into #oshasafety_temp
I receive the error message:
The column prefix '#cl_temp' does not match with a table name or alias name used in the query.
Any ideas?
Thanks,
Tom
btw, sql newbie.
I have an Access database, that is in connection with sql server.
On my computer with access2007 i have no problems with viewing tables and stuff.
But on other computers with access2003 it gives an error when i use this query:
INSERT INTO [tbl_sap-staffel] ( ItemCode, CardCode, [Amount-0], [Price-0], [Amount-1], [Price-1], [Amount-2], [Price-2] )
SELECT [qry_sap-spp-0].ItemCode, [qry_sap-spp-0].CardCode, [qry_sap-spp-0].Amount, [qry_sap-spp-0].Price, [qry_sap-spp-1].Amount, [qry_sap-spp-1].Price, [qry_sap-spp-2].Amount, [qry_sap-spp-2].Price
FROM ([qry_sap-spp-0] LEFT JOIN [qry_sap-spp-1] ON ([qry_sap-spp-0].ItemCode = [qry_sap-spp-1].ItemCode) AND ([qry_sap-spp-0].CardCode = [qry_sap-spp-1].CardCode)) LEFT JOIN [qry_sap-spp-2] ON ([qry_sap-spp-1].ItemCode = [qry_sap-spp-2].ItemCode) AND ([qry_sap-spp-1].CardCode = [qry_sap-spp-2].CardCode);
It says:quote:
ODBC call failed
[Microsoft][ODBC SQL Server Driver][SQL Server]
The column prefix 'MS1' does not match with a table name or alias name used in the query.
The column prefix 'MS2' does not match with a table name or alias name used in the query.
And it will give this error 6 times.
I dont know what to do.
Can anybody help me?