I'm reporting from a Microsoft SQL database (poorly documented unfortunately) and would like to find a 3rd party application to assist me in rapidly making Select queries. The ability to browse data in a field from the interface would be a plus.
What are the best alternatives for rapidly creating these queries from some sort of builder or wizard?
TIA.
Hello.
I would like to analyze how a particular 3rd party business application interacts with SQL server.
Specifically, I want to capture the "exact" SQL commands (transact-sql statements) that this application issues whenever it completes an operation for the user of this application.
In other words, suppose the application issues the SQL command "INSERT INTO table (a,b,c) values (1,2,3)" to add a new entry to the database. Then, my understanding is that if I instead issue the exact same command with a tool like "SQL query analyzer", the database will be updated in the exact same manner. Hence, I have effectively accomplished the same job that the 3rd party application does without even using it (by idependantly issuing the same command to the database it uses).
Is this possible? Can I obtain all the information I need just by running SQL Profiler?
I have a 13 GB MYSQL table with the following definition:
Code:
CREATE TABLE `WikiParagraphs` (
`ID` int(10) unsigned NOT NULL auto_increment,
`Paragraph` text NOT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=73035281 ;
I query it like this:
Code:
select Paragraph from WikiParagraphs where ID in (1,2,3,4)
the 1,2,3,4 bit comes from the Sphinx FullText engine that gives me the IDs I need that match my query within about 500 milliseconds.
But retrieving the data itself takes approximately 3-6 seconds.
Obviously, I'd like to speed this query up.
Any ideas?
G-Man
HelloI need this really faster in mS SQL 2000Usernumber (int)reportid (FK)reportreportid (PK)Category (int)SELECT A, B, C, D INTO UserCopy FROM UserWHERE User.reportid IN (SELECT MAX(report.reportID) AS maxReport FROM Report GROUP BY report.Category) AND user.number NOT IN (120,144,206,345,221,789,548,666,1204,4875,22,135, 777,444)can return a more than 1000 rows (an the table = 10.000 rows): SELECT MAX(report.reportID) AS maxReport FROM Report GROUP BY report.Categoryand the table user has a few millions rowsReport.ReportId is a Primary key for User.reportid (FK) for the moment it takes up to 3 minutes, i need to do that in 30 seconds maximumthank you for helping
View 14 Replies View RelatedLet's say I have a table of users.
Let's imagine there's two fields:
username (PK),
password
Now I need to authenticate a user against this table. What is the recommended approach? Is it better / faster to
(1) SELECT * FROM [User]
WHERE username = 'whatever' AND password='whatever'
or
(2) SELECT * FROM [User]
WHERE username = 'whatever'
and then in my code check that the record returned matched the password?
Hello,
Lets say I have a SP that return 1000 records,
do I get any better speed if doing it on a SP instead of just SELECT without an SP ?
if I have many users on a web-site that will execute this SP - will they get any better
speed because it is a SP ? - can SP cache itself - if so - for how long ?
(Why should I use SP if not passing any parameters ?)
It seems when I run the query with the set staticts IO on then statistic reports back with the 'work table', and the query takes 30+ sec. if the worktable is ommited(whatever the reason?) the query take less 1 sec.
Here is my take, I believe work table is created in tempdb...and if not then whole query is using the cached page, am I right?
if I am right then the theory is, if I increase the (via sp_configure) server min memory setting and min query memory, the query ought use the cached page and return in less 1 sec. (specially there is absolutely no one but me on the server), so far I can't make it go faster...what setting am I missing to make it run faster?
Another question is if the query can not avoid but use the tempdb, is it going to always be 30 sec+ time? why is tempdb involvement make it go so much slower?
Thanks in for you help in advance
John writes "Problem with building SQL to select columns from three joined tables, all of which can have an outer join to a fourth table.
Environment is SQL Server 2000.
Here is a simplified version of schema:
EveTable:
EveTableId (key)
Title
OrgTableId
LocTableId
ImageId (can be null)
EveTable Joins to:
OrgTable:
OrgTableId (key)
Title
ImageId (can be null)
EveTable also Joins to:
LocTable:
LocTableId (key)
Title
ImageId (can be null)
All three tables join to:
ImgTable
Title
ImageId
Title
Problem: I wish to: Select EveTable.Title, LocTable.Title, OrgTable.Title, ImgTable.Title [all] where EveTableID=n
I am currently stuck at building even the basic SQL for this!
Best Regards,"
I have a query that has the following structure
Select *
From Table
Where Condition And ... (some 'Exists' conditions)
When I run the query using field names the query gets much slower, and I cannot understand Why!
Select MyField
From Table
Where Condition And ... (some 'Exists' conditions)
I'm talking about three times slower using the Select MyField sintax.
Any ideas???
I'm having problems executing TOP n queries on a database that was migrated from 6.5 to 7. I can get it to work on the Authors table in pubs, not in my other dbs. Here is an example:
CREATE TABLE dbo.tblsapParentCust (
Parent char (10) NOT NULL ,
Name varchar (40) NULL,
IsSoldTo bit NOT NULL DEFAULT 0,
CONSTRAINT PK_tblsapParentCust PRIMARY KEY CLUSTERED
(Parent)
)
GO
<load in some data>
SELECT TOP 10 * FROM tblsapParentCust
The select statement results in a syntax error:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '10'.
I can switch over to pubs and change the query to reference the Authors table, and it runs fine.
If anyone can explain this behavior to me, I would appreciate it.
Thanks,
Buddy
Hi,
In my database I have a field with the type decimal. In the select query I want to return true if this field is smaller than 1 and false if this field is 1. How can I do this?
I need something like that:
Select id, name, (mydecimalField < 1) from mytable
KaaN
I have 4 tables which I am extracting 2 distinct values;
Select Distinct UniId, PID from dbo.Event1
Select Distinct UniId, PID from dbo.Event2
Select Distinct UniId, PID from dbo.Event3
Select Distinct UniId, PID from dbo.Event4
Then, I want to select Distinct between these 4 tables (Event1, Event2, Event3 and Event4)
Then insert the distinct records of the 4 tables to the final table - dbo.EventLookup .
I need to create a temporary table using dynamic queries and then i have to use the temporary table for data manipulatuion.
Can someone help me out on this.
EG
sp_executesql N'Select top 1 * into #tmp from table1'
select * from #tmp
Hi,
I wanted to know a query which will create a final result table from a combination of select queries.
The select query is like :
1. select col1 , col2 , null from table1
2. select null , col2 , null from table2
3. select null , null , col3 from table 3.
null are inserted as i wanted a single select query which will merge all the columns from all the tables and finally create a result table.
Thanks in advance.
I wrote a simple select query that counts the number of records I have in certain zip codes. How can I get a total of the "count" column at the bottom of the results? For example, my results may look like this:
ZIP | (no column name for "count")
_____________________________________
89502 | 10
89509 | 15
89521 | 25
What statement would I use to get the total of '50' displayed in the resluts? Thank you in advance
-Lance
Hi
Will somebody please explain how to combine asp.net dropdown lists to write
a SQL database select query. I am using VWdeveloper and C Sharp.
For example, say I have 3 dropdownlists on my webpage as below,
List 1, Cities, London, Rome, Barcelona etc
List 2, Restaurants by Type, Italian, chinese, Indian etc
List 3, Number of tables/ seats 10-20, 20- 40, 50 -100
I want someone to be able to search for a restaurant by selecting an item from each dropdownlist
such as, "Barcelona" "Italian" "50-100"
This search query would return all the Italian restaurants in Barcelona with 50-100 tables/seats.
I would also like the select query to work even if one of the dropdownlists items is not selected.
Hope somebody can clear this up?
Also would sql injection attacks be a threat by doing it this way?
Thanks all
I'm trying to code a query in Access that finds rows w/ duplicate "ContactKeys" then finds duplicate "AddressLine1s" out of the list of duplicate "ContactKeys." (I tried subqueries but it was really slow)
I am trying to create a new table with only duplicate ContactKey rows, and then I wanted to use that table to pick out the duplicate AddressLine1 rows.
Code:
SELECT *
INTO dupContactKeys
FROM Contacts
WHERE ContactKey IN (
SELECT ContactKey
FROM Contacts
GROUP BY ContactKey
HAVING COUNT(*) > 1)
SELECT *
FROM dupContactKeys
WHERE ContactKey IN (
SELECT AddressLine1, Zip
FROM Contacts
GROUP BY AddressLine1, Zip
HAVING COUNT(*) > 1)
ORDER BY ContactKey, TypeKey;
drop table dupContactKeys
This of course doesn't work. Please help, as I am going slightly mad!
I am trying to select the max for each Schedule_Number when ProcessDescription = 'Exit Cold Rinse'. In the following table, 2:00 and4:00 should be returned for 12345_001 and 12345_002 respectively. Ihave tried to join the two queries and would like to use the currentSchedule_Number as one of the criteria when determining the max.Below is some code that I've used thus far? Does anyone havesuggestions?*Schedule_Number * Process_Description * TMDT*12345_001 * Exit Cold Rinse * 1/07/08 01:00:00 PM*12345_001 * Enter Cold Rinse * 1/07/08 01:30:00 PM*12345_001 * Exit Cold Rinse * 1/07/08 02:00:00 PM*12345_002 * Enter Cold Rinse * 1/07/08 02:30:00 PM*12345_002 * Exit Cold Rinse * 1/07/08 03:00:00 PM*12345_002 * Enter Cold Rinse * 1/07/08 03:30:00 PM*12345_002 * Exit Cold Rinse * 1/07/08 04:00:00 PMSelect *From(Select distinct Schedule_NumberFrom dbo.Process_DataWHERE left(Schedule_Number,5) = '12345') as Query1left join(Select *From dbo.Process_DataWhere TMDT =(SELECT Max(TMDT)FROM dbo.Process_DataWHERE Process_Description = 'Exit Cold Rinse' andQuery1.Schedule_Number = Query2.Schedule_Number)) as Query2on Query1.Schedule_Number=Query2.Schedule_Number
View 1 Replies View RelatedI am using a sql server 2000 database to log the results from a monitorthat I have running - essentially every minuite, the table describedbelow has a insert and delete statements similar to the ones below runagaint it.Everything is fine for a few weeks, and then without fail, all accessesto the table start slowing down, to the point where even trying toselect all rows starts timing out.At that point, the only way to make things right that I have found, isto delete the table and recreate it.Am I doing something specific that sql server really doesn't like? Isthere a better solution then deleting and recreating the table?CREATE TABLE [www2] ([ID] [int] IDENTITY (1, 1) NOT NULL ,[stamp] [datetime] NULL CONSTRAINT [DF_www2_stamp] DEFAULT (getdate()),[success] [bit] NULL ,[report] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[level] [int] NULL ,[iistrace] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]GOINSERT INTO [www2] ([Report],[Success],[Level],[iistrace],[Stamp])VALUES ('Error on: <ahref="http://www2.klickit.com/include/asp/system_test.asp">http://www2.klickit.com/include/asp/system_test.asp</a><br><br>The operation timedout<br><br>(Test Activated From: Lynx/2.8.2rel.1libwww-FM/2.14)',0,1,'',getDate())DELETE FROM [www2] WHERE (Stamp<getDate()-3) AND (Success=1) AND (ReportNot Like 'ResetThanks in advance,Simon Withers*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 1 Replies View RelatedBackground
SQL Server 2005 Standard 9.0.1399 64bit
Windows 2003 64-bit
8gb RAM
RAID-1 70gb HD 15K SCSI (Log Files, OS)
RAID-10 1.08TB HD 10K SCSI (Data Files)
Runs aproximately _Total 800 Transaction/Second
We deliver aproximately 70-80 million ad views / day
8 Clustered Windows 2003 32-bit OS IIS Servers running Asp.net 2.0 websites
All 8 servers talking to the one SQL server via a private network (server backbone).
In SQL Server Profiler, I see the following SQL statements with durations of 2000 - 7000:
select top 1 keywordID, keyword, hits, photo, feed from dbo.XXXX where hits > 0 order by hits
and
UPDATE XXXX SET hits=1906342 WHERE keywordID = 7;
Where the hits number is incremented by one each time that is selcted for that keyword ID.
Sometimes these happen so frequently the server stops accepting new connectinos, and I have to restart the SQL server or reboot.
Any ideas on why this is happening?
Regards,
Joe
Hi AllStrange request I know, but could somebody give me pointers on how I can put3 queries into 1 'thing' and then get only the unique entries from this'thing'.To explain, I'm using Excel/VBA/ODBC to query an SQL DB. The 3 queriesthemselves aren't that complex and all return the same 2 fieldsets of stockcode and stock desc. Because these separate queries might bring back thesame stock code/description I need to amalgamate the data and then queryagain to bring out only distinct stock values, eg:Query 1 brings back:stock code stock descIVP Invoice PaperSTP Statement PaperKGC Keyboard Coveretc... etc...Query 2 brings back:stock code stock descIVP Invoice PaperBOB Back PackKGC Keyboard Coveretc... etc...Query 3 brings back:stock code stock descKGC Keyboard Cover3.5"D 3.5" Disksetc... etc...I need to produce 1 resultset that shows:stock code stock descIVP Invoice PaperBOB Back Pack3.5"D 3.5" DisksKGC Keyboard CoverSTP Statement Paperetc... etc...(all unique entries)I'm currently just bringing back the 3 query results in Excel, but I'd liketo be able to do the above.In light of I'm using Excel/VBA/ODBC on a PC, is it possible to do?ThanksRobbie
View 1 Replies View RelatedI have 4 sets of select queries under 1 stored proc, now on the report calling the stored proc via dataset. when i run the dataset the only first set of the select query related fields appearing under the dataset.
But on the back end sql server, if i execute the same stored proc, i get 4 resultsets in one single executioln.
i am not seeing the remaingin 3 resultsets, on the reports dataset.
Is it possible on the reports or not.
In the asp.net project i was able to use that kind of stored procedures whcih has multiple select statements in 1 procedure., i use to refer 0,1,2,3 tables under a dataset.
Thank you all very much for the information.
I want to get output of below query in single row.
Select 'Name'
Select 'Surname'
Expected output is Name,Surname
Parameter Information cannot be derived from SQL statements with sub-select queries. Set Parameter information before preparing command.
Here's the query:
update GCDE_SEQ
set LAST_NO = (select max(FLD_NO)
from PONL_FLD)
,UPDT_USER = ?
,UPDT_DT = getdate()
where SEQ_NM = 'FLD_NO'
Why can't Execute SQL Task handle this simple query? I figure i can use 2 SQL Execute SQL Task, one to get the max into a var, and the other to do the updating. However, this is alot of trouble since i'm having this almost exact query in alot of places. Any way around this?
I found out how to select the top 10 used stored procedures. But how do you select the top 10 select statements that are used on the system or database? I had a DBA show me this one time but forgot it after that job. Thanks
View 1 Replies View RelatedI have an intermittent issue where some remote PC's occasionally fail to execute select queries that have a join or return multiple result sets, however simple one table select queries continue to work okay. When it does happen the PC's needs to be rebooted to get to work again. This may only happen some PC's while others continue to work away okay.
I am using a VB6 application and ADO to connect to the database and the error message I get is a General Network Error, Server Not Found when it fails to execute the query. I have ran SQL Profiler on the server and while simple select queries continue to run away okay, a query a join does not even seem to show up in the profiler. The program has been working fine for 15 years with 1000's of users and has only now become an issue on one site for a number of users. Have tried moving the database to a different server and swapping network cards on the local PC's but can't seem to find the cause. The processor and the memory don't seem to be under load, but I am not sure if there is something else in SQL that is causing it to hang under certain conditions.
There have been network analysts experts in to run scans on the network, but I have not had the results of this back yet. Other applications do not seem to be affected so if this analysis does not show up anything.
Hi All,
I have to load data from from a text file ( approx 3-5 million rows) into a table .
I can use straight bcp with the format file , but the loading of data has to follow some business logic
Let me know if there are any third party tool to load the data instead of regular bcp
I have SqlServer 7.0 on Windows NT 4.0
Thanks in advance
Does anyone know any software to backup single tables ? i recall one software that extracts the data into a text file called SQLinsert or something but wondering if they are others around ?
View 1 Replies View Relatedhello,
what are some good db managements tools that are available? I installed Toad from Quest and the Red Gate's toolbelt but I haven't play with them yet. I would like to know what is the most useful tool to invest time in.
Thanks.
Hey guys I really need your help.
We've outsourced our software last year, and it really a big mess for us since that company has been clashed. Then it so happened that the system failed to function well. Our batch transactions was working about 56 mins.. that is 24 records. Then it happened that this time 28 records was finished for about an hour. Our business hour is 8 hours only. That's why we have a back log now. We also failed to validate transaction since it takes a lot of effort before we update the database. It's working before but as our database becomes larger there are many errors occur in our application. Majority is runtime error. Then it happened that now we cannot move on anymore. We are using SQL server 2000 with VB6.0 application. our server memory is 1GB. Do you think adding additional memory will solve our problem?
Thanks!!
I prefer to write SQL code, without graphical tools.Have third party Query Analyzer that has filter and sort abilities?Also I looking for SQL editor with Completion ability (like we know forprogramming lang),for assist me write more quickly.
View 1 Replies View RelatedI am working on a SSIS package to move data between SQL2005 and DB2. I currently have the DB2 ADO .Net connector from DataDirect which works fine for connecting to the AS400. My question is that I am working on a script transformation and I want to be able to use the Connection already created in the package their.
I can set it up just fine using the properties for the script, but there appears to be no way to create SQLCommands and connections unless I reference the DataDirector DLL in the script, which requires adding it to the GAC and the Framework folder.
I am just wondering if this is the correct way to go about this or if there are any other recommendations. Please let me know if I need to supply more information.
Thanks,
Chris
Currently we are using SQL Servers Backup utility to backup our databases to disk and then the files to backup tape during a nightly backup.
The time it takes for the backups to disk to complete for the databases is becoming prohibitively long and performance problems on the DB server are manifesting.
I am interested in other solutions. However, the propaganda listed at the various companies sites are less than insightful.
I was wondering if anyone used a third party Agent like Brightstor Enterprise SQL Server backup agent, or Veritas NetBackup for SQL Server, that would be willing to give some recommendations. If you use something that isn't listed here and it works well, I would be interested in hearing about it.
Do these backup agents significantly slow down the database performance? Do they create any locking issues? Some insight into your backup hardware configuration would be helpful as well. Thanks in advance for your help.