In MS-ACCESS we can write and store queries and then deploy them in our applications. How can we do the same in SQL-SERVER 2000
Queries written in MS-ACCESS were converted into tables while I migrated the database from ACCESS to SQL SERVER.
Hi There,
I need to pull data using input from one table in sql server 2005. I have to query against the sql server 2000 database and pull data into sql server 2005. I have a list of ids that I have to pass to a query to get the desired data. What is the best practice for this. Can I use SSIS or do I need to build an app in C#? Can somebody please reply back?
Thanks a lot!!
I am trying to get the same results in SQL server with a query as it works in access
There are two tables:
TITLES
Fields in query: TITLES.TitleID, TITLES.CustLName, TITLES.CustFName, TITLES.TitleState, TITLES.VehYR, TITLES.VehMake, TITLES.VehModel
Fields in Table:
[TitleID]
,[TitleNum]
,[TitleState]
,[VehYR]
,[VehMake]
,[VehModel]
,[NewORUsed]
[code]...
How this can be ONE SQL Query?In query designer on access theleft table is TITLES_IN_OFFICES_qry and the table on the right isTITLOC.I have never used a query as a table before in a select .
I'm new to the world of SQL and queries. There was an existing SQL server when I took the job and have been learning what I can as it comes up. My problem is that installed a new SQL server and moved everything to it. Everything seems to be working with the exception of the existing queries. I have changed the server in the ODBC Administrator but I still get the following error:
Connection Failed:
SQLState: 01000
SQL SErer Error: 10060 & 10061
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]ConnectionOpen (Connect()).
Connection Failed:
SQLState: 08001
SQL SErer Error: 17
[Microsoft][ODBC SQL Server Driver][SQL Server does not exist or access denied.
I have also checked to make sure that the server is listening for incoming sockets connections. (Port 1433) It is.
I don't know if there are files (DSN) that need too be moved to the new server or if there is another place to change the server the queries are pointed to. I hope someone can help me.
Thanks,
Jo
Is there any log that catches all querys in SQL Server 2000???Full problem (in slanish): http://foro.hackhispano.com/showthread.php?t=28980Thanks
View 2 Replies View Related1) Given the name of an index how to find the columns associated with the particular index in SQL Server?
2) Given the name of a constraint, how to find whether it is enabled or disabled?
3) Is there any equivalent of the Oracle 'valid' state in SQL Server? e.g. In Oracle 'valid' state is defined for views, indexes. Are there equivalent concepts in SQL Server and if there are, how to find if a particular index/view is 'valid' or not?
In one of our requirement, I want all the query details for the SQL query batch that got executed for the day. I know, we can get sql query from dm_ exec_ query_stats. But I want all sql query along with their session details ie. ExecutedDateTime, SessionId, UserID etc. I have tried using sys.dm_ exec_ sessions. But it contains only last executed query details for all the sessions. how to obtain all the session details for all the query executed for the day in the server.
View 7 Replies View RelatedI have a remote server with SQL server 2014 instance on it. There is nothing else running on the SQL Server box(dedicated SQL box). There is only one instance of SQL 2014 on the server. No other versions of SQL server are on the server.
Issue:
1. When I execute a query connecting to the SQL server instance through my local SSMS, the query executes in 30 secs.
2. When i connect to remote server through windows RDP session and execute the same query in the SSMS(on server), then query executes in 1 minute.
Hi there,
I am trying to link one sql server to other sql server(version 7.0). I was able to link server1 to server2 by creating an odbc source and am able to see the tablenames when i click on the linked server tables.
My problem is..when i am trying to query on these tables its giving me error saying "OLE DB provider 'MSDASQL' does not contain table xxxxxx"
i am using select * from servername.tablename.dbo.tablename.
Any help on this will be appreciated.
Ravi
Does anyone out there know of any utility that can Migrate Queries to SQL Server 7. I have over 400 of them and Dread doing them all by Hand.
Thanks,
-Ray
I have a query in Access that has an iif statement in the select system like so:
SELECT [01_qryCommonCost_01].*, tblPercent_Afe.AfeDescription,
tblPercent_Vendor.[Vendor%], tblPercent_Afe.[Afe%], IIf([manual%] Is Not
Null,[Manual%],IIf([PropType]="NonMkt",0,IIf([Vendor%] Is Not
Null,[Vendor%],IIf([Afe%] Is Not Null And [GlType]<>"OpExp",[Afe%],[Gl%]))))
AS [%], 0+round2([Amount]*[%]) AS Allocated,
0+round2([Allocated]*[Salvage%]) AS Salvage
FROM (01_qryCommonCost_01 LEFT JOIN tblPercent_Vendor ON
([01_qryCommonCost_01].GlType = tblPercent_Vendor.GlTyp) AND
([01_qryCommonCost_01].VendorName = tblPercent_Vendor.VendorName)) LEFT JOIN
tblPercent_Afe ON ([01_qryCommonCost_01].AfeNo = tblPercent_Afe.AfeNo) AND
([01_qryCommonCost_01].Group = tblPercent_Afe.Group)
But I can't figure out how to nest the iif statement in T-SQL. Any hints?
Can anyone help me how to extract data from two different SQL servers? Both the servers are alredy registered on my local machine.I need to select data from one database on Server1 and insert the same in a table in another database of Server2.Names of the servers are abc-efg-hij and 143.89.55.67.
Thanks for any help!
Dear fellows,
Can anybody tell me how can i apply recusive/Tree query using select
statement.
For example I've a table structure for an Organization as follows:
TableName: Employee_tbl
Fields: emp_id, emp_name, supervisor_id
emp_id emp_name supervisor_id
---------- --------------- -------------------
101 ZAFIAN
102 BRUNNER 101
108 CALLAHAN 102
105 RUSSO 102
110 SIM 102
103 DUELL 101
and so on
1. How can I get the above records in Hirarchical format starting from top
or from anywhere else in the hierarchy?
In Oracle it can be done as follows:
SELECT emp_id,emp_name,supervisor_id
FROM employee_tbl
CONNECT BY supervisor_id = PRIOR emp_id
START WITH supervisor_id is null;
Please reply me at the following address if possible:
faisal@visualsoft-inc.com
Hi,Does anyone know if MS SQL Server 2000, will run stability on a WindowsXP based O/S, using one of the new Dual Core chips, or HT chips?Especially given that when you install it you get compatabilitywarnings?ThanksDavid
View 2 Replies View RelatedHello,When I try to run the following query on two different SQL Servers,I get error on one of the server (both has same set of data). I wastrying to get rows for ProductCode='XYZ_Q1'.SELECT ProductName, ProductType, ProductDesc FROM Product WHEREDepartmentID=12 AND ProductType > 2000 AND CAST(SUBSTRING(ProductCode,CHARINDEX('_', ProductCode)+2, 1) AS int)=1Example dataProductCode|ProductName|ProductType|DepartmentID|P roductDescXYZ_T_1|Test1|1000|12|TestXYZ_T_2|Test2|1000|12|TestABC_T_1|Test3|1000|11|TestABC_T_2|Test4|1000||11|TestXYZ_Q1|Test5|1000|12|TestABC_Q1|Test6|1000|11|TestIt's trying to cast all values under 'ProductCode' column instead ofapplying to subset with condition 'DeparmentID=12 AND ProductType >2000 'I solved the problem by equating it to whole string rather thantrying to extract the integer part of it. But I wanted find-out reasonas to why this is happenning.Is there any SQL Server setting that's causing this?Thank you very muchManchaiah
View 1 Replies View RelatedI have a situation where I need to do some reporting for simular asset records but the data is listed on different database from the same server.
I was curious as to the way to write the queries that I will need.
Lets say I have 3 databases and I am looking for a asset of some type and the ID is a serial number.
The serial will be on the main table from each database. Is there a way I can set a relation from a view between these records?
I.E.:
1: Database A -DSNA_tblMaiin - Serialnumber
2: Database B -DSNB_tblMaiin - Serialnumber
3: Database C -DSNC_tblMaiin - Serialnumber
I believe that this is a dot notation question.
Thanks in advance,
Gene
Ok, this is a little convoluted so bear with me.
I have a SQL 2000 SP4 box on Windows 2000 Server with the Oracle 11g Client installed.
I've created a linked server to an Oracle database that lives offsite (sites connected with a fairly high speed VPN link).
There's a view on the Oracle database I'm trying to access through the linked server using the four-part naming convention*.
If I execute a fairly simple query against that view, the query runs until it eventually times out two hours later. And by simple, I mean something like this:
select * from CGN_LMS..CGNLMS.V_PRODUCTION_SUM where FISCAL_PERIOD = 200803
That happens if I use the OLE DB provider from Oracle or the one from MS.
Now, if I run the same type of query against that Oracle DB from Excel or Access (using the ODBC driver), the query will complete in seconds, returning about 800 rows of data.
For fun, I created a blank Access database with one linked table that points to that V_PRODUCTION_SUM view on the Oracle database, and one Query within that Access database that SELECTs * FROM the linked table WHERE FISCAL_PERIOD=200803.
I then created a linked server on my SQL 2000 server that points to the Access database. Now, if I hit that saved Access query through the SQL 2000 linked server, it also returns the results in about 9 seconds. If I query the linked table in the Access database and specify WHERE FISCAL_PERIOD = 200803, it completes in about 6-8 minutes.
So I guess my question is... WTF?
How is it that this:
SQL 2000 ->OLE DB provider-> Oracle
Times out after 2 hours, while this:
SQL 2000 -> Jet driver-> Access -> ODBC driver -> Oracle
returns all my results in seconds/minutes?
Couple other side notes...
A lot of the queries that I test with using this linked server work just fine. In fact, with this particular query, if I were to just pull SELECT TOP 800 * with no WHERE clause, the query would run rather quickly (like in 4 seconds).
Also, if I use OpenQuery instead of the 4-part linked server name, the queries return results as quickly as I'd hope. That might work as a last resort, but we have existing applications that use the 4-part names already and we'd rather not have to suss out and change all those if we don't have to.
Any help or insight would definitely be appreciated.
Justin
Hi Folks,
I am looking for some help on how to do a calculation for each row returned in a query.
For example, I have the following query:
Select Name, Supplier, Colour, Cost, [Cost Inc Vat] from products;
The idea is that Cost and a variable, @vat included in part of the SQL statement (I assume), will be be used to calculate the value of the column [Cost Inc Vat] for each row, but I dont know how to do this.
Is is possible in SQL Server Compact and how can it be done?
Many thanks,
Morrislgn
I'm looking for the minimum date of an entry into a history table. The table contains multiple entries for the customer and the item with an activation and deactivation date for each entry.
I could use the following:
select customerId, item, min(activationDate) from history group by customerId, item
or a sub query
select customerId, item, activationDate
from history h1
where activationDate=(select min(activationDate) from history h2 where h2.customerId=h1.customerId and h2.item=h1.item)
How are these two queries parsed differently by SQL.
They return a different number of results.
Thanks,
karen
Hi All,
I hope I am at the right place to post this question:
How can I move the tables with its data from Access to SQL Server 7.0 using SQL queries. Later I might put everything in the store procedure and have a third party running the store-procedure to do it by itself.
Please advice what should I do first? Do I create new tables in the s SQL Server 7 or can I move the data and create the tables at the same time?..Thank you.
Is there a way to find out total number of queries ran i.e by day/hour or since the database was brought up in SQL Server 7?
Thanks in advance,
How do I find out top expensive queries from SQL Server 2008 – Standard edition ?
View 9 Replies View RelatedI am trying to perform a distributed query however have a situation I haven't dealt with before the linked server I add to do the query is a named instance (DVD_NASDOMINO). How would I specify this in a query as in the FROM part in a sql statement. I tried the obvious DVD_NASDOMINO.qlsdat.dbo.stmenqry and DVD_NAS.DOMINO.qlsdat.dbo.stmenqry.
Both returned errors in the query.
Thanks for the help.
May i have a list of Which queries or codes couldn't use in a Transactions in Sql Server ?
We are migrating a database that makes use of distributed queries. In other words, it queries data from other databases on the same server, as well as from databases on linked servers.
The use of linked servers as well as linked databases (for lack of a better term) presents a challenge since we will need to register various new linked servers in the target environment. Ideally, we would like the database to be portable such that we will not have to worry about registering linked servers regardless of where it is hosted.
Is there a way to write distributed queries so that do not rely on linked servers/databases? For example:
SELECT * FROM [ServerName].[Database].[Owner].[TableName]
If there is such a method, it would make our database much more portable in terms of server migration.
TIA.
El Salsero
I've setup a parameterised query in PHP correctly and achieved the results I wanted from a basic view.
I then developed the application to use a Table-valued Function to be able to simplify the PHP code.
SELECT * FROM [CDBF].[dbo].[webOrganisation] ('w%',5,11)
This query works fine within the code.
/* Define the query. */
$tsql = "SELECT * FROM webOrganisation ('w%',5,11)";
/* Execute the query. */
$stmt = sqlsrv_query( $connection, $tsql);
if ( ! $stmt )
{
echo "Error in statement 2 execution in display_search().";
die( print_r( sqlsrv_errors(), true));
}
/* Iterate through the resultset printing a row of data upon each iteration.*/
while( $row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_ASSOC))
{
// Display results (this bit works fine!)
}
/* Free statement and connection resources. */
sqlsrv_free_stmt( $stmt);
However when I try to parameterise it, I get errors.
Error in statement 2 execution in display_search(). Array ( [0] => Array ( [0] => 07009 [SQLSTATE] => 07009 [1] => 0 [code] => 0 [2] => [Microsoft][SQL Native Client]Invalid Descriptor Index [message] => [Microsoft][SQL Native Client]Invalid Descriptor Index ) [1] => Array ( [0] => 07009 [SQLSTATE] => 07009 [1] => 0 [code] => 0 [2] => [Microsoft][SQL Native Client]Invalid parameter number [message] => [Microsoft][SQL Native Client]Invalid parameter number ) )
/* Define the query. */
$tsql = "SELECT * FROM webOrganisation ( ? , ? , ? )";
/* Execute the query. */
$start = 5;
$finish = 11;
$params = array($search.'%',$start,$finish);
$stmt = sqlsrv_query( $connection, $tsql, $params);
if ( ! $stmt )
All other parts of the code remain the same.
Where am I going wrong
If applicable, what's the limit on concurrent queries supported by SQL Server 2005 Express? Also, if there's a limit, does next connection after the max errors out or merely queues up?
Thanks,
Phillip
Hi, I have a Table that contains SQL queries in one of its columns.I need to execute those query and finally want to retrive the result in another table i cannot use Cursors,Its working extremely slow, near about 1 min only for 2000 rows.Please tell me how can i minimize my time.Or any solution (without cursor) for such problem.Please help its very urgent.e.g Say TableContainQuery(PKID,QueryField) Now above table have 10000 of records, I need to retrieve data from by executing queries contain in above table. I am using SQL Server.
Regards,Dheeraj
I am using Server 2012 and very new to SQL. I have a request from a physician for a list of his patients that meet a criteria. This is stored in a temp table names #cohort.
Using this cohort he wants each row to be one patient with a list of labs, vitals, etc. Three items are the most recent lab value and date. I could query each lab individually and place it into a temp table and then join all temp tables at the end, but I am trying to move past that and have all labs in one temp table. All temp tables are joined with PatientSID.
I tried to do something for just 2 labs, but it is not working. There could be nulls values when joined with the #cohort table.
Individually the SELECT statements pull in the most recent lab value and date, but I cannot get them into a temp table with one row of PatientSID and then the lab value and date if they exist.
IF OBJECT_ID ('TEMPDB..#lab') IS NOT NULL DROP TABLE #lab
SELECT
cohort.PatientSID
,SubQuery1.LabChemResultNumericValueAS 'A1c%'
,SubQuery1.LabChemCompleteDateTimeAS 'A1c% Date'
,SubQuery2.LabChemResultNumericValueAS 'LDL'
[Code] .....
I have the following:
(a) One Dynamic SQL Query that takes 37 ms when run as a single query or in an SP.
(b) Three SQL Indexed View queries that take 0 ms when run together.
When i add (a) + (b) in the same SP, i should get 37 ms + 0 ms = 37ms, but NO it takes 400 ms.
What is causing the extra 363 ms of latency.
How can we get most frequent queries that are running against to a table in our database?
View 3 Replies View RelatedCurrently i have set of queries which i run for data extraction and result pasted in Excel table so that my pivot table and Chart gets populated along with the summary in Excel file.
I would like to automate this thing and want to know if i can achieve excel output for dashboard on a click of button.
Our developers have gotten this idea lately that instead of having many small stored procedures that do one thing and have small parameter lists that SQL can optimize query plans for, its better to put like 8-10 different queries in the same stored procedure.
They tend to look like this:
create procedure UberProc (@QueryId varchar(50))
as
if @QueryId = 'First Horrible Idea'
begin
select stuff from something
end
if @queryid = 'Second really bad idea'
begin
select otherstuff from somethingelse
end
I see the following problems with this practice:
1) SQL can't cache the query plan appropriately
2) They are harder to debug
3) They use these same sorts of things for not just gets, but also updates, with lots of optional NULLable parameters that are not properly handled to avoid parameter sniffing.