Querying Table In Arbitrary Database

May 1, 2006

Would appreciate some help with this one. I need to run a query on a known table an arbitrary database. The DB name will be selected at runtime.

What I've tried:

DECLARE @DBName varchar(10)
SET @DBName = 'WBTST'

SELECT TOP 100 PERCENT Col1, Col2
FROM [@DBName].dbo.Table1
GROUP BY Col1

Result:
Invalid object name '@DBName.dbo.UPR00100'.

I've also tried without the square brackets around @DBName in the FROM clause. This results in: Incorrect syntax near '.'.

Thanks for the help
Wes

View 2 Replies


ADVERTISEMENT

How To Tailor CREATE DATABASE For An Arbitrary Instance

Sep 20, 2006

Suppose the following:

As part of a product install (using InstallShield)...

I create a SQL Server Express instance (say "X") via a silent install
I supply a script to create a database in instance X.

The idea, of course, is to have a fully automated install. But there's one problem I can't quite see how to work around:

- the CREATE DATABASE statement needs the name of a file to contain the database, and that file needs to be in a folder that belongs to the instance (e.g. Microsoft SQL ServerMSSQL.1, Microsoft SQL ServerMSSQL.2, etc).

Is there a syntactic variant that allows me to avoid this problem?

Thanks
Josh

View 4 Replies View Related

Arbitrary Dates

Mar 30, 2006

How can I select all the "filler" dates.


CREATE TABLE tbDateList
( ItemDate datetime )

INSERT INTO tbDateList (ItemDate)
SELECT '01/01/2006' UNION ALL
SELECT '01/10/2006'

SELECT * FROM tbDateList
DROP TABLE tbDateList


In the select statement, I want to select all dates in tbDateList, but I also want to include dates that are not in the list.

Eg: the above select * will return the 2 dates, but I want it to return the 10 dates, 01/01/2006 - '01/10/2006'

Any suggestions?

Mike B

View 4 Replies View Related

Transact SQL :: Querying Data From Master Table Into Transaction Table?

Oct 13, 2015

I am stuck in the following scenario.

Tbl_Loan
Trans_ID
Emp_ID
Guarantor_1_ID
Guarantor_1_ID
TRN_01
EMP_01

[code]....

View 5 Replies View Related

Performance Tuning For Selecting Arbitrary Range

Mar 22, 2007

I have two tables:

Headers(id int, time datetime)

Data(id int, product_id int, property_id int, value float)

Data.id references Headers.id

Headers.id is a primary key,
Data has clustered index (id, product_id, property_id)

Headers has several thousand rows, Data several million. I want to return all rows from Data for a given product_id and a given property_id such that Header.id is in a given range.

Right now I am doing

SELECT id, time, value
FROM Headers H, Data D
WHERE
H.id = D.id AND
H.time >= @StartTime AND
H.time <= @EndTime AND
D.product_id = @ProductID AND
d.property_id = @PropertyID

This query can take 10+ seconds to run, though once I run it for a given product_id, queries for different values of property_id are much faster. Try a different product_id, and it takes longer. Given that there are millions of records in Data, is it reasonable for it to take this long? The index was suggested by Query Analyzer's Index Tuning Wizard, and I tried a couple variations on the query without any noticeable performance improvement. But, I'm no DBA...anyone have any tips? I googled a bit but couldn't figure out the right way to phrase my question to find any good info...thanks in advance

View 1 Replies View Related

Sql2k: Increment Numeric Part In Arbitrary String

Apr 11, 2003

CREATE FUNCTION fctisnumericex(@c varchar(1))
RETURNS int AS
BEGIN
RETURN CASE WHEN ASCII(@c)>=ASCII('0') AND ASCII(@c)<=ASCII('9') THEN 1 ELSE 0 END END

CREATE FUNCTION fctstringincrement (@string varchar(255),@maxlen int)
RETURNS varchar(255) AS
BEGIN
DECLARE @@posr int
DECLARE @@posl int
DECLARE @@c varchar(1)
DECLARE @@token1 varchar(255)
DECLARE @@token varchar(255)
DECLARE @@token3 varchar(255)
DECLARE @@i int
/* emulates parts of the behaviour of s_modformatting::substringincrement */
/* 1. find the place where the numeric token starts from the right */
/* if we didn't find any non-numeric part then it might well be that the rightmost digit is already numeric */
IF dbo.fctisnumericex(SUBSTRING(@string,DATALENGTH(@string),1))=1
BEGIN
SELECT @@posr=DATALENGTH(@string)
END ELSE BEGIN
SELECT @@i=DATALENGTH(@string)
SELECT @@c=SUBSTRING(@string,@@i,1)
WHILE dbo.fctisnumericex(@@c)!=1 BEGIN
SELECT @@i=@@i-1
IF @@i<1 BEGIN BREAK END
SELECT @@c=SUBSTRING(@string,@@i,1)
END
SELECT @@posr=@@i
END
/* so have we got any numeric part inside that string? */
IF @@posr>0 BEGIN
/* yep. see how long it lasts */
SELECT @@i=@@posr
SELECT @@c=SUBSTRING(@string,@@i,1)
WHILE dbo.fctisnumericex(@@c)=1 BEGIN
SELECT @@posl=@@i
SELECT @@i=@@i-1
IF @@i<1 BEGIN BREAK END
SELECT @@c=SUBSTRING(@string,@@i,1)
END
/* separate now the parts of the string */
IF @@posl>1 BEGIN SELECT @@token1=SUBSTRING(@string,1,@@posl-1) END ELSE BEGIN SELECT @@token1='' END
SELECT @@token=SUBSTRING(@string,@@posl,@@posr-@@posl+1)
IF @@posr<DATALENGTH(@string) BEGIN SELECT
@@token3=SUBSTRING(@string,@@posr+1,DATALENGTH(@string)-@@posr) END ELSE BEGIN SELECT @@token3='' END
/* increment the numeric part */
SELECT @@token=convert(varchar(255),convert(int,@@token)+1)
END ELSE BEGIN
/* no numeric part at all. start with 1 at the end */
SELECT @@token1=@string
SELECT @@token='1'
SELECT @@token3=''
END
/* recompose the string and trim to max length if necessary */
RETURN SUBSTRING(@@token1+@@token+@@token3,1,@maxlen)
END

View 4 Replies View Related

Querying Using Another Table

Apr 30, 2008

Hello all,

I have a table (tbl_a) with 2 columns: itemNames | Date .
ItemNames can have duplicates.
I have another table (tbl_b) with date ranges in 2 columns: startDate | endDate.

what I would like to do is: query tbl_a where date between startDate and endDate of tbl_b.

does anyone have an idea?
thanks in advance.

View 2 Replies View Related

Querying Table More Than Once

Jun 12, 2008

Hi All

Thx in advanced for any help anyone can offer me.

I have 2 tables Staff and Class which are as follows

Staff
ID (Primary Key)
FirstName
MiddleName
Surname
Etc. . .

Class
ID (Primary key)
LessonTemplateID
TeacherID
TermID
AssistantID

LessonTemplateID and TermID link to other tables which i don't need right now.

The Class.TeacherID and Class.AssistantID both link to the Staff.ID

I am trying to create a query where i can say i want to select a class.ID of lets say 12 and display both the Teachers name and the Assistants Name.

If i do just one join between Staff.ID and lets say Class.TeacherID all is fine and i can display the teacher.FirstName for a particular class.ID. However my problem is when im trying to display both teacher and assistant. Every time i try to create a query to do this the query comes back blank.

Im really stuck on this and just can't figure it out (is it even possible?).

Thx for any help Scott.

View 2 Replies View Related

Querying The Database

Jun 12, 2008

how to write a query to find the no of tables present in that database.

Any suggestions.

View 4 Replies View Related

Transact SQL :: Best Way To Make A Function For Summing Arbitrary Number Of Times Values

Jun 22, 2015

How is the best way to make a function for summing an arbitrary number of times values (table parm?)- I 've read it's necessary to convert to seconds, sum then convert back, but Im' wondering if there's an alternative.

Here's the example I want to sum:
00:02:01:30
00:01:28:10
00:01:01:50
00:06:50:30
00:00:01:50

View 8 Replies View Related

Querying Between Table Adapters ?

Sep 12, 2006

Hi all, I am currently moving from access queries to SQL and am using the query Builder In Visual web developerto help me with the SQL Code..This has been easy while just querying the one table adapter but Now I am stuck..I have a table adapter that links to an SQL Server database through a connection in web.config.I have another table adapter that links to DB2 through a different Connetion in web.config.My question is ..How do you query from one table adapter to another.Each table adpater can only query based on the 1 connection ? Thx,Ray..

View 1 Replies View Related

Log Users Querying Table

May 17, 2000

We have been asked to keep a log of users querying a particular table.
User's can query any odbc compliant tool, not just one application.

Can we do this w/ sql server 7?

View 1 Replies View Related

Querying Large Database

Mar 2, 2005

I need to query a database with about 3 million records using a 'like' statement on various varchar columns. I've have a non-clustered index on the columns included in my query, but the performance is still pretty slow. Can anyone suggest ways to increase the speed of my queries? Does full-text indexing improve performance or does it just facilitate more advanced searches (which I don't need)? Would char instead of varchar datatypes be faster?

thanks!

View 3 Replies View Related

Querying To Mysql Database

Sep 20, 2007

Hi, I have been using Microsoft Access for a couple of years and we have just switched to SQL Server. However, I'm having problems querying the Mysql databases our company has in place. I have managed to establish connections in the Visual Studio but can't seem to link tables from two different databases like I could in Access. Is this possible? Also, is it possible to connect to the mysql database via management studio? Apologies for my complete lack of knowledge!

View 4 Replies View Related

Querying Reportserver Database

Mar 24, 2008

Hi everybody,
Our SRS reporting security model had to be designed so that the access is by individual report basis and by user. I cannot give access to individual reports based on windows AD group. Because everybody in that group does not need to have access to a particular report. Two people from one AD group needs access to say Report1. Only one person need to have access to Report2 etc.... Creating lot of AD groups for the security is also not advisable.

Right now I can tell right away users having access to a particular report as the number of reports and the users is small. But I want to design a report that can give me a list of users who have access to a specific report.
How do I query the reportserver database to get this information. I mean is this information saved in any of the tables?
Can you please give any info that you know of such information?
Thank you in advance for all your help,



View 5 Replies View Related

Querying The Size Of Table Fields?

Jun 5, 2008

Hello I am writing a stored procedure and instead of "hard coding" the size of the procedure parameters I would like to set them to the size of fields in my database.
 for example instead of declaring a parameter like this:
@ProjectDescription varchar(500)
 
I would like to do something like this:
 
@ProjectDescription varchar(Select size of "ProjectDescription" From Projects);
 
Thanks for the help! 

View 4 Replies View Related

How To Keep Original Order When Querying A Table?

Sep 10, 2004

Hello, everyone:

I have a table like:

ColName
b
b
b
d
d
d
a
a
c
c
c

I use DISTINCT to filter duplicated row. I want to get the return by original order like:
b
d
a
c

However, SQL Server re-order it if using DISTINCT and return like:
a
b
c
d

Can any one have the idea to handle that? Thanks

ZYT

View 4 Replies View Related

Querying A Really Badly Designed Table

Mar 28, 2006

I am working on an application with a number of tables (which I can't
change) that have data of the form a1, b1, c1... a2, b2, c2...a3, b3,
c3... The tables contain 5 to 10 sets of data in each row.



I want to create a stored procedure to return a cursor with a row for
each set of data for a row of the table. I know I can use unions:



select a1 as a, b1 as b , c1 as c... from sometable where pk=@pk

union

select a2, b2, c2... from sometable where pk=@pk

union

select a3...



Is there a way that is more efficient (eliminates requerying the table and all the unions)?

View 4 Replies View Related

Advise On Querying A Database Daily

Jun 2, 2004

I am implementing an application that is looking into another System’s database (DBF). I need to query out new orders that have been put in daily. What is the best way to handle this situation? Should I write a service or have a trigger setup in MSDE? I just need to do a simple SELECT statement that grabs orders between a date range and update my SQL database (MSDE). I am not looking for specific code examples, just a solution.

What are my choices?

Thanks,

Jason

View 5 Replies View Related

Querying DB2 Database With Reporting Service

Jan 24, 2005

People

I'm in a trouble.
So, I need to query some data in DB2 with Reporting Service.
I don't know if there's a way to do it, without using DTS Package, because if I did it, I'd be replicating it, and it's not the case. 'Cause I just want to query the data in DB2 and not bring the data to SQL.

There's a provider that I can do it?
Suppose I use a Linked Server, and add a Net-Library based on their Operational System, it would resolve? If not, what would be the best way of resolving this trouble??

View 3 Replies View Related

Stored Procedure Querying Another Database

Feb 6, 2007

Hello,I need to come up with a stored procedure that will allow me to readdata from another database. The database I need to read the data fromis a UniData residing on a Unix server. The stored procedure needs toreside on my sql 2005 server. The task is very simple in Access as wehave ODBC connections set up to the UniData via Informix (or IBM)UniData ODBC drivers. I can easily combine my UniData and Sql Servertables from within access. However, I can't seem to find a way toreplicate the same behavior in MS SQL Stored Procedure without the useof Access. Is that even possible?Thanks,Marek

View 7 Replies View Related

SQL Query Question- Querying The Middle Table In A M To M

May 17, 2000

I need help with a query, and I haven't seen anything like this in a SQL book.

I will use the Northwind database for this example. The tables I am using are Products, Order_Detail, and Orders. There is a many to many relationship between Products and Orders, that is resolved by the Order_Detail table. Suppose I want to find out which products are related to each order. How would I query the tables?

Thanks everyone,
Nathan

View 1 Replies View Related

Querying Single Table For Multiple Summaries - How?

Jul 20, 2005

Folks,While I still have some hair left, can someone help me with thisquery?I have a table "TestRunInfo". Amongst other fields there are"TestRunIndex" (Pri Key), "TesterID", "Duration", and "Status".The Status field links to a Status table, which links the index valueto a more meaningful label "Pass", "Fail" etc...As you may have guessed, there is a record for each test that anindividual tester runs, and with that record is a duration, and status(1,2,3 etc).What Im trying to do, is create a datasheet view, with a single rowfor each testerID, summarising that Testers work as follows:TesterID, Total Duration, Count of passed tests, Count of failed testsSo far I have:Select TesterID, sum(Duration), count(Status) FROM TestRunInfo GROUPBY TesterIDBut this of course purely gives the total number of tests run by thatengineer as the count. I need to break it down. Help? Someone?Please?!?!?TIASteve

View 2 Replies View Related

Transact SQL :: Is Table Will Be Extracted When Querying To DB Views

Sep 15, 2015

I wonder how the query will be executed when querying to views.Is database engine will be extract the tables that are used to create the view and query for the result ?

View 5 Replies View Related

Data Warehousing :: Querying In Fact Table

May 2, 2015

I have a Fact Table with a ID column as Primary key and clustered index is created. And also I have 4 dimensions FK's of data type INTEGER. And finally, I have one aggregation measure in the Fact Table.

Now, my situation is How can I improve the speed of querying the fact table by creating any of the below indexes?

1. XML
2. Spatial
3. Clustered
4. Non-Clustered

View 2 Replies View Related

Querying A Linked Server With Local Database...

Mar 25, 2008

I would like to query a linked server and join that database to my local database. I finally was able to get it to work and I got this query to work:

select * from openquery([hcda-storagesrvlaserfiche], 'select * from toc where parentid=358')

I would like to join one of my tables from my local database to the query. How can I do this? Thanks!

View 5 Replies View Related

Querying SQL Express Database From Excel Macro

Apr 28, 2006

How do I query an SQL Express database from within an Excel 2002 VBA macro?

For example, how could I accomplish this in an Excel macro.

SELECT myID FROM MyTable WHERE Compound = string in cell A1

And then put the resulting ID number in cell A2. The relationship between Compound and myID is always one to one.

Thanks to all.

Dan

View 1 Replies View Related

Determining Database Role Of Querying User

Dec 7, 2007

I know that I can use:

IS_MEMBER('MyRoleName') = 1

to determine if a user is a member of a predetermined role, but how do I ask what the role name of the querying user is instead? Basically I want to do something like:

SELECT * FROM CityTable WHERE City IN (SELECT City FROM CitySecurity WHERE SelectGroup = ROLE_NAME)

I'm doing this because the "CitySecurity" table contains security information from another system that has been imported into SQL Server, where the 'SelectGroup', as part of process, will be named the same as the SQL Server role.

Thanks,
Kayda

View 4 Replies View Related

SQL Server 2012 :: Querying Table With Several Date Type Columns

Oct 30, 2014

I have a table (we will cal DateTable) with several (20) columns, each being a date type. Another table's (Project) PK is referenced in the DateTable.

I am trying to write a query that will pull all dates for a specific project from the DateTable if they meet certain criteria(i.e. if the date is <= 7 days from now.

I started with a normal select statement selecting each column with a join to the project and then a where clause using

(DateTable.ColumnName BETWEEN GETDATE() AND DATEADD(day, 7, GETDATE()) OR (DateTable.ColumnName BETWEEN GETDATE() AND DATEADD(day, 7, GETDATE())) ...

The rest of the columns(all with OR between them).

The problem with this is that because I am using OR once one of the dates meets the criteria it selects all the dates that are associated with the project. I ONLY want the dates that meet the criteria and don't care about the rest.

Obviously because I have all the columns in the select statement... So I need something like

Select ALL Columns
from DateTable d
Join Project p
where p.ProjectID = d.ProjectID AND only dates BETWEEN GETDATE() AND DATEADD(day, 7, GETDATE()))

View 2 Replies View Related

Throwing Error When Querying The Image Of Database In SQL Server 2000.

Oct 3, 2007



I created an Image for the Database in sqlserver 2000. When I am querying directly the database as "Select * from employee", it is returning the result set.

But, when I am querying Database image with same query. It is giving error

Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'Employee'

It is expecting the Database Owner. If I do query like "Select * from [Owner].employee", it is working fine.

My requirement is not use owner in queries, it should be simple query ("Select * from employee").


Can any one, help me out.
Thanks in advance.
Bhupesh

View 1 Replies View Related

SQL Reporting Services 2005: Querying A SQL Server 2000 Database

Feb 5, 2008



Is there any way to deploy reports to a Report Manager in SQL Server Reporting Services 2005 if the reports query SQL Server 2000 databases? Or are only 2005 databases able to deploy to the web?

Also, if I have a report that queries both, can I deploy it to the web on the 2005 Report Manager with a linked server to the 2000 server?

View 4 Replies View Related

Query A SQL Server Compact Table While Querying A SQL 2005 Server Table

May 21, 2008

Hi there,

I'm trying to run a query on a SQL Server 2005 table which has a WHERE clause that requires a query from my SQL Compact table.



SELECT * from RemoteDB.TESTDB.dbo.Objects

WHERE Last_Updated > '2008-05-21 10:51:00'

AND Object_PARENT IN (select Object_CODE from LocalDB.PDADB.dbo.Objects)


Basicallly on a linked system, this query would find all new objects in my main database where the same objects exist in my local database. This would work just perfectly, no problems.

Now, the local database is actually on a PDA running SQL Server Compact Edition. There is currently no support for creating a linked environment. I have the option of pulling the table off the local db and pushing it to the remote db and then running the above query from within the single db and then retrieving the list of new entries and pulling them down to the local db but that is a HUGE amount of bandwidth, even if I just used the single primary key column.

Would anyone maybe have a little advice for me on how I could possibly achieve the above result on SQL Server Compact please?


Thanks in advance

View 10 Replies View Related

SQL Server 2012 :: Querying A Supersession Two Column Table With Multiple Supersessions In Both Columns

Jan 29, 2014

I'm fairly new to SQL and am just setting up a Windows 8 app using an Azure SQL server. The issue I have is looking up a part number supersession and getting the latest number. One part number can have multiple supersessions (ie RTC5756 > STC8572 > STC3765 > STC9150 > STC9191 > SFP500160 ).The data I am supplied monthly has both the superseeded items and the supersession information in both columns and is not easy to decipher - for example:

Supersessions Table
----------------------

RTC5756 | STC9191
SFP500160 | STC9191
STC9191 | STC2951
STC3765 | STC9191
STC8572 | STC9191
STC9150 | STC9191

[code]...

The newest part number is kept in a separate table - called "source" - which in this instance is SFP500160. I need access to the latest part number but also to the part's previous numbers, due to the fact that some people may still be stocking them as an old part number and for them to search by. Is there an easy and efficient way of doing both a lookup for the supersessions and a join on the two tables to minimize the queries on the database?

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved