I am joining 2 tables in SQL 2005
Table A has 100 attributes
Table B has 100 attributes
if I write a select stmt that has 2 columns from table A and 2 columns from table B
does the query at the database have 4 columns in the query or 200 columns in the query
of course I will be returned 4 columns but what is the record complex at the DB ?
how many columns at the DB are in the query 4 or 200 ?
please advise
Good evening:
We're porting an old app written in ASP.NET 1.1433 and SQL 2000 to ASP.NET 2.0 and SQL 2005. In the old app we have a few data grids that are populated from a dataset pulled from the database. We use a SQL query that we build based on more than 10 different user inputs, the result of which is an enormously complicated SQL string. We'd like to move this processing into a SPROC in the 2005 database.
Rather than writing stored procedures to create the SQL SELECT statement, is it possible to pass an entire select state ment to a SPROC and have it executed within? We're trying to capitalize on paging in 2005 using ...ROW_NUMBER() OVER (ORDER BY PM ASC)... and building the string using IF ELSE statements is mind numbling complex and tedious.
And suggestions woudl be great.
Thanks,
Brad
Hi everybody!
I have made a simple application with VB6 and SQL 2005 Express.
And after testing it in a network simulated with Virtual-Machine Workstation, the "real world" application was first getting slower and slower, and finally giving timeout errors.
It is even difficult to connect. The application times out always at 30 secs although the ado connection timeout was established longer (45)
I have checked on the machine where the server is installed, just in case the slow down was due to network problems, but it fail locally too.
Any help is wellcome
I have upsized some Access 2002 tables to Sql Server 2005 express. I put together a query and when I try to execute it, it tells me the MS Access is an earlier version prior to SQL server 2005 and it will not save the queries. When I try to run them anyway, I get the repeated message and the query never executes. I'm taking a T-SQL class and this is how the instructor. Is they a fix for SQL Server or Access to get around this or do I have to revert back to MDSE 2000?? Can MDSE 2000 and SQL Server be loaded onto the same machine (laptop)? Help!!!!! I am very new at this...
View 5 Replies View RelatedI'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'm just looking into installing a second instance of SQL 2005 with SP2 on a W2K3 R2 machine that already contains a default instance, if i do so is the order of installation
1) Install named instance
2) Apply SP2 to the newly installed named instance
If i do this, will either of these require a system reboot ?
The default instance should remain operational if no reboot required as the service pack is for a specific instance in this case ?
Thanks in advance
P
We are trying to use source control to store our database objects andqueries in Perforce. The general idea is to produce scripts whichcreate all objects in the DB and then store the SQL in source control.Has anyone done this before and can suggest some advice? Alternativelyif anyone has other methods for using source control on a database,could you please let me know?Also, is there any automated way of extracting individual scripts forobject creation from the database?
View 2 Replies View RelatedHi,
We are running SQL Server 2005 Ent Edition with SP2 on a Windows 2003 Ent. Server SP2 with Intel E6600 Dual core CPU and 4GB of RAM. We have an C# application which perform a large number of calculation that run in a loop. The application first load transactions that needs to be updated and then goes to each one of the rows, query another table get some values and update the transaction.
I have set a limit of 2GB of RAM for SQL server and when I run the application, it performs 5 records update (the process described above) per second. After roughly 10,000 records, the application slows down to about 1 record per second. I have tried to examine the activity monitor however I can't find anything that might indicate what's causing this.
I have read that there are some known issues with Hyper-Threaded CPUs however since my CPU is Dual-core, I do not know if the issue applies to those CPUs too and I have no one to disable one core in the bios.
The only thing that I have noticed is that if I change the Max Degree of Parallelism when the server slows down (I.e. From 0 to 1 and then back to 0), the server speeds up for another 10,000 records update and then slows down. Does anyone has an idea of what's causing it? What does the property change do that make the server speed up again?
If there is no solution for this problem, does anyone know if there is a stored procedure or anything else than can be used programmatically to speed up the server when it slows down? (This is not the optimal solution however I will use it as a workaround)
Any advice will be greatly appreciated.
Thanks,
Joe
Hi there,
I am trying to create an un-attended SQL 2005 Express install. I have successfully made my own template.ini file to install SQL the way I like but I cannot find a way to make it install with the "Ad hox remote queries" option enabled.
Is there anyway to do this? I can't seem to find any switches in the INI file to allow this.
Thanks
Joseph Beckett
I am not very good in queries. Could you please suggest me some web site/Tutorial/Artical where i can get Study Material for complex and real life queries. I know the syntexes, I just need to practice queries to enhance my skills
View 1 Replies View RelatedI have been trying to export an sql2000 database to sql2005 with no luck. I continuosly get the error
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
I have looked upon all support material and have installed the latest sql2005 service pack, but this has not fixed the problem. a simple query as:
select * from [transaction] ta,transactionentry tr
where tr.transactionnumber=ta.transactionnumber and
glacctid=6 and ta.[time]> '3/01/2006'
would produce this fatal error.
the current version I am using is:
Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) Oct 14 2005 00:33:37 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
This problem is happening on some of the most important tables in the database.
the error log contains the following:
2006-06-06 11:43:32.59 spid54 ***Stack Dump being sent to C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLLOGSQLDump0001.txt
2006-06-06 11:43:32.59 spid54 SqlDumpExceptionHandler: Process 54 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
2006-06-06 11:43:32.59 spid54 * *******************************************************************************
2006-06-06 11:43:32.59 spid54 *
2006-06-06 11:43:32.59 spid54 * BEGIN STACK DUMP:
2006-06-06 11:43:32.59 spid54 * 06/06/06 11:43:32 spid 54
2006-06-06 11:43:32.59 spid54 *
2006-06-06 11:43:32.59 spid54 *
2006-06-06 11:43:32.59 spid54 * Exception Address = 0136B4F0 Module(sqlservr+0036B4F0)
2006-06-06 11:43:32.59 spid54 * Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
2006-06-06 11:43:32.59 spid54 * Access Violation occurred writing address FFFFFFFC
2006-06-06 11:43:32.59 spid54 * Input Buffer 260 bytes -
2006-06-06 11:43:32.59 spid54 * select * from [transaction] tr,transactionentry te where tr.
2006-06-06 11:43:32.59 spid54 * transactionnumber = te.transactionnumber and glacctid = 6
2006-06-06 11:43:32.59 spid54 *
2006-06-06 11:43:32.59 spid54 *
2006-06-06 11:43:32.59 spid54 * MODULE BASE END SIZE
2006-06-06 11:43:32.59 spid54 * sqlservr 01000000 02BA7FFF 01ba8000
2006-06-06 11:43:32.59 spid54 * ntdll 7C800000 7C8BFFFF 000c0000
2006-06-06 11:43:32.59 spid54 * kernel32 77E40000 77F41FFF 00102000
2006-06-06 11:43:32.59 spid54 * MSVCR80 78130000 781CAFFF 0009b000
2006-06-06 11:43:32.59 spid54 * msvcrt 77BA0000 77BF9FFF 0005a000
I have followed instructions on some support issues posted but none have helped. If anyone has any solution to this problem PLEASE HELP!!!!.
Thanks.
I have a co-worker whose sql server 2005 is exhibiting strange behavior. We have already re-installed sql server 2005 and service packed it to SP2 to try and see if the behavior stops but it has not.
Every so often during the day sql server 2005 will start to slow down to the point that my co-worker's queries begin to time out. He turned on profiler to look at what was going on behind the scenes.
We see where sa is releasing and acquring locks to the tune of 180,000 rows in a fifteen minute span when this behavior starts so does his time outs. He has reporting options and analysis services installed but not configured. His only connection is to his local database. Occasionally, you see a number like - (03000d8f0ecc) appear in the Text Data column in profiler for sa. I read something about reporting options using sa for clean up but I don't think that is what is happening here.
Does someone have a clue as to what is happening and a way we can prevent the behavior? It is affecting his ability to work on his application.
Thx
i need to get the result of two different queries into 2 different sheets of the same excel file while exporting a report to excel using reporting services..
somebody please help
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!!
HelloWhen I use a PreparedStatement (in jdbc) with the following query:SELECT store_groups_idFROM store_groupsWHERE store_groups_id IS NOT NULLAND type = ?ORDER BY group_nameIt takes a significantly longer time to run (the time it takes forexecuteQuery() to return ) than if I useSELECT store_groups_idFROM store_groupsWHERE store_groups_id IS NOT NULLAND type = 'M'ORDER BY group_nameAfter tracing the problem down, it appears that this is not preciselya java issue, but rather has to do with the underlying cost of runningparameterized queries.When I open up MS Enterprise Manager and type the same query in - italso takes far longer for the parameterized query to run when I usethe version of the query with bind (?) parameters.This only happens when the table in question is large - I am seeingthis behaviour for a table with > 1,000,000 records. It doesn't makesense to me why a parameterized query would run SLOWER than acompletely ad-hoc query when it is supposed to be more efficient.Furthermore, if one were to say that the reason for this behaviour isthat the query is first getting compliled and then the parameters aregetting sent over - thus resulting in a longer percieved executiontime - I would respond that if this were the case then A) it shouldn'tbe any different if it were run against a large or small table B) thisperformance hit should only be experienced the first time that thequery is run C) the performance hit should only be 2x the time for thenon-parameterized query takes to run - the difference in response timeis more like 4-10 times the time it takes for the non parameterizedversion to run!!!Is this a sql-server specific problem or something that would pertainto other databases as well? I there something about the coorect use ofbind parameters that I overall don't understand?If I can provide some hints in Java then this would be great..otherwise, do I need to turn/off certain settings on the databaseitself?If nothing else works, I will have to either find or write a wrapperaround the Statement object that acts like a prepared statement but inreality sends regular Statement objects to the JDBC driver. I wouldthen put some inteligence in the database layer for deciding whetherto use this special -hack- object or a regular prepared statementdepending on the expected overhead. (Obviously this logic would onlybe written in once place.. etc.. IoC.. ) HOWEVER, I would desperatelywant to avoid doing this.Please help :)
View 1 Replies View RelatedHi,
I am using visual web developer2005 express edition and finding hard time to get my query run in this i am making my own login page as i have few more things to ask to user before they get logged in so i am not using the login control.
i want to write my own query without help of sqlDataSource control from start something like
sqldatasource con=new sqldatasource;
con.connection String=""
then what all things will come........ ???
and please give me some poitners to some articles which help one to do the requested.
Regards,
I have an update query which either inserts a row or increases quantity, depending if row exists or not. It works, better than my explanation probably.After that query could be a good time to count total of all calculated sub sums.Something like this. previous queryEND goSELECT SUM(SubTotal)FROM dbo.t_Shoppings I have tried this on the tool which has a long name, but I think my way didn't work. (Microsoft sql server management studio express)Is this possible or do I have make and call another stored procedure.I can send my sp if someone wants.
View 4 Replies View Related how can i execute for example
2 queries together, in a single stored procedure Select top 20 * from Product where Active=1select Count( *) from product if i execute such one how can i get the 2 results in vb/c# ?
hi which is bettere to use a quesry from the code or to use a Stored Procedure and call the SP from the code
View 3 Replies View RelatedHi All,
Not sure if I've got the correct place for this question. But, I'm trying to create and sql query to list the lates 10 items in a database. So far I haven't had any luck finding this.
All I have is a normal query (below). Can anyone help me please?
SELECT * FROM pages WHERE show = 'yes' ORDER BY id Desc;
Regards,
Rich
Hi,I have 5 tables in sql database, naming Book, Category, Subject, UserDownload, User.In Book table, BookID, BookTitle, CategoryIDIn Category table, CategoryID, CategoryNameIn Subject table, SubjectID, SubjectName, CategoryIDIn UserDownload table, UserID, BookIDIn User table, UserID, UserNameI used Book to store information of books. Those books has many categories. In those categories, there is also some subjects.When user downloads book, I update UserDownload table.The result I want to get is, Top Ten Download Subject. How can I get? Please help me.
View 1 Replies View RelatedCould anyone help me?I need all the commands like select,from etc.Basically I need to learn writing queries etc.Please
View 1 Replies View RelatedI just upsized my Access2K db to SQL. I am using Front Page 2000 for my website. When I had the database as Access, I was able to use one of my Access queries as my record source for my data base. I was able to choose between my queries AND my tables as the source for my records. Now that I've upsized, I am no longer given that choice. My only choices are the tables. Unfortunately, my database is designed to pull records from a query, not just a table. So my question is, in FP2000, how do I use a QUERY from my newly upsized SQL db as my record source?
Thank you all very much in advance.
Hi!
Where to write MDX Queries and how to call the query in Analysis Services.
Please help me on this.
I'd like to know how (if it's possible) to write a TRIGGER in SQL that will, when a limit has been reached, remove an entry from a table...
Any ideas? Thanks.
Hi and thanks for reading.
I have a query that requires multiple joins and has several one-to-many relationships with other tables. The basic query I have is this:
Code:
SELECT TOP 50 eventID,eventTitle,eventAbstract,eventInsertDate,eventUpdateDate,eventAuthID_fk,
eo.eventOccurrenceID,eo.eoDescription,eo.eoApprovedDate,
l.LocationID,l.LocationShortName,c.categoryID,c.categoryName,a.ageID,a.ageDescription
FROM enm_eventOccurrence eo INNER JOIN enm_events e ON e.eventID=eo.eoEventID_fk
LEFT JOIN (enm_categories_occurrence co LEFT JOIN enm_categories c ON c.categoryID=co.co_categoryID_fk) ON eo.eventOccurrenceID=co.co_eventOccurrenceID_fk
LEFT JOIN (enm_location_occurrence lo LEFT JOIN location l ON lo.lo_locationID_fk=l.LocationID) ON lo.lo_eventOccurrenceID_fk=eo.eventOccurrenceID
LEFT JOIN (enm_ages_occurrence ao LEFT JOIN enm_ages a ON ao.ao_ageID_fk=a.ageID) ON ao.ao_eventOccurrenceID_fk=eo.eventOccurrenceID
This works, but it will pull out multiple rows for the same eventOccurrenceID (the PK for eventOccurrence) because of the several one-to-many relationships. For instance, there could be multiple categories, locations and ages for one event occurrence, so this will produce multiple rows.
But, since I need to get only a limited of rows, the TOP will fail since there are multiple rows for each, and there's no way to tell how many rows exactly will come out (so just multiplying the TOP value is out of the question).
Using DISTINCT or GROUP BY also seems to be out of the question, since I need to have the fields from the one-to-many tables in the SELECT list but cannot GROUP BY them.
My proposed solution is to run a query selecting only the eventOccurrenceID and using the WHERE information that the user is requesting, then running another query (with the full select list) with a "WHERE eventOccurrenceID IN (".
But, I hate using two queries and just wonder if there's a better way around this.
Appreciate the help, and thanks for reading.
-colin
I frequently need to run ad hoc queries against a database, capturing the output in a text file. In SQL Server 2000 I used Query Analyzer with no problems. In 2005 I'm trying to use the query function in Management Studio-but the output is (so far) unusable due to a Null character (hex 00) that follows every 'real' character. I assume this is due to Unicode but haven't found any way (yet) to tell it to just output in plain everyday text. (ASCII or ANSI-either will work.)
Is there some other tool I can use for SQL queries?
Thanks.
It is given the following schema of relations:
Donators(Id <<PK>>, Name, Surname, IdCity <<FK(City)>>, Group, Sex, Age);
Files(IdFile <<PK>>, Donator <<FK(Donators)>>, Date, Type);
City(IdCity <<PK>>, Name, State);
I need to find a query for each of the following:
1. name, surname of the plasma donators(type='plasma'), with Sex='F' and younger than 45 years old.
My solution:
SELECT d.Name, d.Surname
FROM Donators d, Filles f
WHERE d.Id=d.IdFile AND f.type='plasma' AND d.sex='F' AND d.age<45
2. average age of the plasma donators
SELECT AVG(d.age)
FROM Donators d, Files f
WHERE f.type='plasma'
3. the states which have more donators
I have no idea how to do it
4. the id's of the plasma donators from Chicago
SELECT d.id
FROM Donators d, Files f, City c
WHERE d.id=f.idfile AND d.idcity=c.idcity AND f.type='plasma' AND c.name='Chicago'
5. the id's of the cities without plasma donators
SELECT c.idcity
FROM City c
WHERE NOT IN (SELECT c.idcity
FROM City c, Donators d, Files f
WHERE f.idfile=d.id AND f.type='plasma' AND d.idcity=c.idcity)
6. remove the donators older than 80, after having removed the corresponding files
No idea how to do it
In conclusion, could you tell me if my queries are correct and help me with the others?
Thanks
Table 'dbo.tblonenet_sdt_proj_hold' Schema as follows:
cust_id int
dateon DATETIME
dateoff DATETIME
There are two possibilites
1) Its no longer on hold and so a dateon and dateoff value will be in the table.
2) Its still on hold and so dateoff will be NULL.
I want to add the total time its been on hold between the two dates and there could be multiple entires for cust_id 802
SELECT Hon,Hoff FROM
(SELECT sum(dbo.GetBusinessdays(dbo.tblonenet_sdt_proj_hol d.dateon,getdate()))
FROM dbo.tblonenet_sdt_proj_hold
WHERE cust_id='802' AND dateoff IS NULL) AS HON
[code]...
The resulting error in SQL Management Studio 2005 is 'No column was specified for column 1 of 'HON'.'
I'm confused.
I am creating a interaction application for alton towers available through a website (using asp.net). This website is connected to a database(sql server 2000) containing infomation on different shops, rides, restaurants and facilities at the park.
I have inputted data to my database. The user should be able to do searches such as:
rides (and shops or all) available in a certain area.
rides in a certain area above a certain height rescrictions.
etc
I'm confused. I have used SQL statements before and I know it is possible to retrieve this information from the database, but how should I go about this and how should I be storing this information so that I am able to call it from the front teir.
I have read up on views, stored procedures and triggers and I'm a bit lost. Should I be creating all the possible queries and then store them as a view (or stored procedure) I thought I would just have to write a sql statement but it seems a lot more confusing...
and what about triggers and user defined statements??
PLEASE help, I have a deadline in a week : (
THANK YOU
how do i make a querie that finds relations to a "part number" for models, and assemblies, but when it finds a relation of part number with a Product, it gives additional attributes for information?
this code that i have so far doesn't do waht i want,, showing
part_number
2 2 1 1 Kilimanjaro
2 2 2 2 Dolomiti
2 2 11 11 Mud Zinger 1
and jumbling up information
select part_inventory.part_number, assemble.assemble_number, model.model_number, product.serial_number, model.model_name
from part_inventory inner join model_configuration on part_inventory.part_number = model_configuration.part_number
inner join model on model.model_number = model_configuration.model_number
inner join assembly_configuration on part_inventory.part_number = assembly_configuration.part_number
inner join assemble on assemble.assemble_number = assembly_configuration.assembly_number
inner join product on product.serial_number = model.serial_number
where part_inventory.part_number = 2;
Label(LabelID,Label) labelid=pk
-------------------------------
Shelf(ShelfID,Shelf) shelfid=pk
-------------------------------
Country(CountryID,Country) countryid=pk
---------------------------------------
Track(TrackID,Track) trackid=pk
-------------------------------
Artist(ArtistID,Artist,ArtistPhoto,CountryID) artistid=pk,countryid=fk country(countryid)
-----------------------------------------------------------------------------------------
CD(CDID,CDTitle,CDDate,CDCover,LabelID,ShelfID) cdid=pk, labelid=fk label(labelid),shelfid=fk shelf(shelfid)
------------------------------------------------------------------------------------------------------------
CDTrack(TrackID,CDID) trackid,cdid=pk, trackid=fk track(trackid), cdid=fk CD(cdid)
----------------------------------------------------------------------------------
ArtistTrack(ArtistID,TrackID) artistid,trackid=pk, artistid=fk artist(artistid),trackid=fk track(trackid)
---------------------------------------------------------------------------------------------------------
I've already created these tables but i need some aid writing these queries coz i am still a beginner in sql
Search Queries
------------------------------------------------------------
Input Artist
OutputCDTitle,CDDate,CDCover,Label,CDSection,Shelf 1
Group ByCDSection
------------------------------------------------------------
InputCDTitle
OutputCDTitle,CDDate,CDCover,Label,CDSection,Shelf 2
Group ByCDSection
------------------------------------------------------------
InputTrack
OutputCDTitle,CDDate,CDCover,Label,CDSection,Shelf 3
Group ByCDSection
------------------------------------------------------------
InputLabel
OutputCDTitle,CDDate,CDCover,Label,CDSection,Shelf 4
Group ByCDSection
------------------------------------------------------------