How To Build Gui Or Forms To Wrap SQL Queries That Are Normally Run In Query Analyzer
Jul 23, 2005
Hi all,
I'm a newbie to SQL and I need help with investigating what ways are
possible to build an interface of some sort that wraps around a SQL
query script.
I have a simple SQL query which we normally run inside Query Analyzer
to update certain fields in the SQL DB based on FQDN provide by the
user. The user normally opens up the query, edit the script to replay
the xxxxx in the line "set @FQDN="xxxxx" with the node name and then
fires it off.
I would like to know what are the ways to wrap a simple user interface
around it so the user does not have to open and edit the script
directly. A simple GUI or Excel type form interface that has a free
form text field for input and then a button to run the script.
Obviously, I would like to see the output in some sort of output pane
as well.
Is this something that can be done in Query Analyzer ?
Thanks in advance,
Michael.
View 1 Replies
ADVERTISEMENT
Oct 12, 2005
I've been using MS-SQL Server for many years but never come across this problem before.
When I try and run a very simple query from Query Analyzer it takes a LONG time. Even when there are no tables involved!
Even:-
select 1
go
takes 28 seconds to return '1' when running against the local server. i.e. both QA and the Server are running on the same machine.
Can anyone help explain how to get my performance back! Thanks.
View 1 Replies
View Related
Sep 20, 2005
Is there any free and good tool to build queries visually with sql server express?
SQL Express Manager is rather rudimentary.
Thank you for any advice!
Regards,
Fabian
View 2 Replies
View Related
Jul 20, 2005
I've got some SQL that works as far as returning a recordset from a series ofUNION statements.viz:SELECT whateverUNION thisUNION thatUNION otherNow I want to group and sum on it's results.Started out tying:SELECT * FROM(union stuff)....but couldn't even get past the syntax check.Where I'm headed is a sort of pivot table presentation of some hours dataassociated with various projects with a column for each of six date ranges.Bottom line: can somebody give me a pointer to the syntax needed to wrap thoseUNION statements and then select/group/sum their results?--PeteCresswell
View 9 Replies
View Related
Jun 26, 2007
Afternoon
I'm getting the below error message:
Autogrow of file 'FORMS' in database 'FORMS' cancelled or timed out after 30547 ms. Use ALTER DATABASE to set a smaller FILEGROWTH or to set a new size.
FORMS.LDF file is 7613952 KBand the growth is 512MB .
By how much should I set the filegrowth? The users are complaining that the application is freezing on them.
This is sqlserver 2000.
View 6 Replies
View Related
Feb 12, 2008
When I run the following query from Query Analyzer in SQL Serer 2005, I get a message back that says.
Command(s) completed successfully.
What I really need it to do is to display the results of the query. Does anyone know how to do this?
declare @SniierId as uniqueidentifierset @SniierId = '85555560-AD5D-430C-9B97-FB0AC3C7DA1F'declare @SniierAlias as nvarchar(50)declare @AlwaysShowEditButton as bitdeclare @SniierName as nvarchar (128)/* Check access for Sniier */SELECT TOP 1 @SniierName = Sniiers.SniierName, @SniierAlias = Sniiers.SniierAlias, @AlwaysShowEditButton = Sniiers.AlwaysShowEditButtonFROM SniiersWHERE Sniiers.SniierId=@SniierId
View 3 Replies
View Related
Aug 29, 2001
THis is so annoying. I have 3 ADO executes in my program. THe first one creates a view, the second one performs an outer join on that view and returns a result set, the third execute drops the aforementioned view. THe program that is using this is installed on about 200 computers scattered across Germany and Italy, all querying the same MSsql server 7.0. THe queries run quite quick when few users are actively using the program (after hours for example). however in the heat of the day performance goes up and down dramatically with identical queries taking from 1 to 20 seconds to return their result set. Now I initially thought 'bandwidth issue out of our server'. However I noticed that if I take those three queries and run them from the sql server enterprise manager( running on the same computer as the aforementioned program) then the queries run instantly and the data is in my result pane in less than 2 seconds ALWAYS....even when the program is dogging it with 20 second delays before the result set returns. I know it is hanging on the return of the result set as I put a stop after before each ADO execute in order to check which one was eating up my time. Why is there this dichotomy between running the queries from my enterprise manager versus running them from an ADO object. Both are using TCP/IP (no named pipes involved). I havent monkied with the attributes of the ADO result set so they are all set to default.
I have used the sql server profiler to trace these queries and they always run in less than 33 milliseconds. THe duration is also never more than 33 milliseconds. THis stinks of a network resource issue but what always leads me somewhere else is how consistent the performance of the enterprise manager is when it runs the exact same three queries.
Here is my slightly edited connection string
Public Const connection_string = "Provider=SQLOLEDB;Server=000.000.000.000;" & _
"User ID=johndoe;Password=janedoe;Network=dbmssocn;" & _
"database=fidojoe"
Here are the 3 ADO executes:
conn.Execute (sqlstr_create_view)
Set resultset1 = conn.Execute(sqlstr_get_providers_by_DMISID)
conn.Execute (sqlstr_drop_view)
View 3 Replies
View Related
Feb 25, 2012
When I run query in excel it gives result with different column sequence. The same query gives result with different column sequence when used in query analyzer or VBA Macro. E.g., Select * from ABC.
result in Excel 2003 SQL OLE DB query
col-A col-B col-C
values...
Result with Query Analyzer and VBA Macro
col-c col-B col-A
values...
View 3 Replies
View Related
Apr 9, 2006
I hope I am not asking about something that has been done before, but Ihave searched and cannot find an answer. What I am trying to do is torun a query, and then perform some logic on the rowcount and thenpossibly display the result of the query. I know it can be done withADO, but I need to do it in Query Analyzer. The query looks like this:select Varfrom DBwhere SomeCriteriaif @@Rowcount = 0select 'n/a'else if @@Rowcount = 1select -- this is the part where I need to redisplay the resultfrom the above queryelse if @@Rowcount > 1-- do something elseThe reason that I want to do it without re-running the query is that Iwant to minimize impact on the DB, and the reason that I can't useanother program is that I do not have a develpment environment where Ineed to run the queries. I would select the data into a temp table, butagain, I am concerned about impacting the DB. Any suggestions would begreatly appreciated. I am really hoping there is something as simple as@@resultset, or something to that effect.
View 6 Replies
View Related
Mar 22, 2006
Hello,
we have automated build on every night. In our solution is SSIS project, where each package is encrypted per password. We call build process per command line like this..
C:ProgrammeMicrosoft Visual Studio 8Common7IDEdevenv.exe (c:DevelopmentX3\X3.sln /build Release)' in 'c:DevelopmentProjectsDailyBuild
Through build process we get a error:
External Program Failed: C:ProgrammeMicrosoft Visual Studio 8Common7IDEdevenv.exe (return code was 1):
We think a reason is, that on build of SSIS project must be entered a password. You can wonder for what we need that SSIS packages are part of our build. We hope that on build process is also created Deployment Utility, if so set in dtproject.user. Is it so? Is there any way to create Deployment utility on automated build process? Can be a password provided pre command line?
with best regards
Anton Kalcik
View 5 Replies
View Related
Jan 16, 2007
Glad to do it. Sorry.
View 1 Replies
View Related
Jun 10, 2008
Hi,
I have two tables called Actcodes and a another table called FundBalances...
The Act codes have the following format..
PlanId int,
ClientId int,
StartDate datetime,
EndDate datetime,
ClientActCode char(2)
CodeDescription char(15)
so based on what the user has selected i am getting the names of each codes
and they have the following Rows.
PlanId ClientId Startdate EndDate ClientActCode CodeDescription
111 1 1/1/2008 3/31/2008 01 Begininng balance
111 1 1/1/2008 3/31/2008 02 Contributions
111 1 1/1/2008 3/31/2008 03 something
111 1 1/1/2008 3/31/2008 04 sdkfjdkf
111 1 1/1/2008 3/31/2008 05 dfdfd
111 1 1/1/2008 3/31/2008 06 dfddfs
111 1 1/1/2008 3/31/2008 09 dfdf
111 1 1/1/2008 3/31/2008 15 dfdkfdlfk
and my fund balance i have the following rows..
PlanId int
Participantid int
StartDAte datetime,
end date datetime,
FundId int
Loans
Act1 char(2)
TotAct1 money
.
Act20 char(2)
TotAct20 money
and the data is as follows
PlanId ParticipantId StartDate EndDate fundId Loans Act1 TotAct1 Act2 totact2 ----- Act20 TotAct20
111 1212 1/1/2008 3/31/2008 15 NULL 01 15.15 02 15.48 20 12.4561
111 1212 1/1/2008 3/31/2008 45 0 01 45.12 02 453.123 20 54.00
so on and so so forth
tht Act1 matches the ClientActCode in the Act..
So is there a way i can get those column in my select which have actCodes present in the Act code table. for in... in my act code table for this plan i have 01,02,03,04,05,06, 09, 15 clientAct codes
so can i get the its respective Acts and Totacts column as Act1, Act, Act3, Act Act5, Act6 based on the ClientAct codes.
Any help will be appreciated..
Thanks
Karen
View 23 Replies
View Related
Mar 8, 2004
Hi,
Please Help me to build this query.
I have got a "User" Table
---------------------
UserID UserName
---------------------
1 Tuffy
Another Table "Groups" Table
---------------------
GroupID GroupName
---------------------
1 Manager
2 Employee
3 Sales
I have got a "UserGroup" Table HOLDING ID'S as Foreign key.
The data in the TABLE is like this
---------------------
UserID GroupID
---------------------
1 1(Manager from "Group" Table
1 2(Employee)
1 3(Sales)
2 2(Employee)
2 3(Sales)
---------------------
Now when a user logged in The Groups have to be returned as a string that contains pipe separated Group names
for example "Manager|Employee|Sales|"
So if User 1 log in I need something like that
UserID (1)-->"Manager|Employee|Sales|"
Please help me how to write this query.
Regards
View 5 Replies
View Related
Apr 24, 2007
I have a table with 2 columns:
Col1 Col2
stat1,stat2 AV,AD
stat3 TD
I need to build a query like this:
SELECT *
FROM table3
WHERE stat1=AV and stat2=AD and stat3=TD
How do I do this?
View 6 Replies
View Related
Apr 10, 2007
Here is my problem.
I need to build a new SQL query and im almost new with all the sql language. Im searching a lot and I have 2 book but I cant found what I'm looking for.
I need to update the Site and Department in the table 1 with the site/department in the table 3 using the table 2 as a join. The table one is an item owned by the person in the table 3 and I need to set the Site and Department as the same.
Table 1
Asset Name | InstanceID | Site | Department
Table 2
InstanceID | User Name |
Table 3
User Name | Site | Department
Help will be really appreciated!
Thanks
View 6 Replies
View Related
Sep 13, 2007
I need to build following query:
Each item has one or more manufacturers:
Is any way to get all the items with all manufacturers where one of manufacturers fit required expression.
I will try to explain in example:
items | manufacturers
------------------------------
ABC1 | golf
ABC1 | toyota
ABC1 | citroen
ABC2 | skoda
ABC2 | subaru
ABC2 | lada
ABC3 | peugeot
ABC3 | renault
ABC3 | dodge
Example for Input: *olf*
Output:
ABC1 | golf
ABC1 | toyota
ABC1 | citroen
Example for Input: *aul*
Output:
ABC3 | peugeot
ABC3 | renault
ABC3 | dodge
Thanks
View 20 Replies
View Related
Sep 17, 2005
I am trying to determine which of three stored procedure designs arefastest in the Query Analyzer:One query is a straight SELECT query with all desired rows and a dozen(tblName.RowName = @param or @param = Null) filters in the WHEREstatement.One query populates a #Temp table with the UniqueIDs from the resultsof the SELECT query in the above example, then joins that #Temp tableto get the desired rows.One query users EXEC sp_executesql @sql, @paramlist, @paramin which the @param has the dozen filters.What I'm trying to determine is which is the fastest.Each time I run the query in Query Analyzer it returns the samerecordset (duh!) but with much different Time Statistics.Are the Time Statisticts THE HOLY QRAIL as far as determining which isfastest, and what so I want to look at, the Vale or the Average? Inotice there are different numbers of bytse sen and bytes received foreach of the three queries.Any illumination on this is appreciated.lq
View 4 Replies
View Related
Sep 25, 2006
Hi there
Running query analyzer against two different server.
the first only need 1-2 secs to return the query result,
the other return 7-8 secs for the query result.
plz advice what could cause this slow performance?
thx
View 1 Replies
View Related
May 15, 2007
Hello to all,
I have now two tabels ( Ta and Tb). the tabel includes difference attributte. I want to build this two tables together.
I used this query:
select * from Ta where Ta.Id = @ID union all select * from Tb where Tb.IdOfa = @ID
but it doesn't work and the following error message comes
"All inquiries in an SQL application, which contain a union operator, must contain directly many expressions in their goal lists "
View 4 Replies
View Related
Oct 12, 2007
I am building my SELECT on the fly by use of IF statements something like:
SET @searchParam = @searchParam + ' AND tblXYZ.country = ' + @searchStringCountry
Then I am trying to use @searchParam as my WHERE clause:
WHERE@searchParam
How do I get the string out of @searchParam to use as my WHERE clause?
Thanks in advance!
View 1 Replies
View Related
Apr 22, 2008
I've got a table that stores individuals, and their organization all in one table, with basic information about each (contactId, name, primary address, phone, etc.). There is also, for each line, a true/false field 'PerOrg' that lets me know if it's a person (true), or an organization (false); and a 'OrgKeyCode' field that has the contactId for the person organization.
I'm looking to make an update query that will make the primary address for all individuals set to the primary address for their corresponding organization. Here's my queries to find the items:
This gets all of the organization, their contactId and their primary Address (as long as they have one).
SELECT ContactID, FullName, PrimaryAddressType
FROM tblContactInformation
WHERE PrimaryAddressType IS NOT NULL AND PerOrg = '0'
This gets a list of all of the individuals that have an organization assigned.
SELECT ContactID, FullName, OrgKeyContactID, PrimaryAddressType
FROM tblContactInformation
WHERE PerOrg = '1' AND OrgKeyContactID IS NOT NULL
Now I need to create an update query that sets the Primary Address for individuals to the primary address for their corresponding organization. Here's what I've got worked out so far: (just the basics).
UPDATE tblContactInformation
SET PrimaryAddressType =
WHERE PerOrg = '1'
At issue is, I'm not sure how to pull the primary address only from the organization into the SET line. Do I need to save my first query, then somehow call it in my update query, where the contactID = the OrgKeyCode? Or something like that?
View 1 Replies
View Related
Apr 22, 2008
I have a table which tracks changes by user, department and date. I want to construct a query which returns two numbers, the first is all the changes by department on a certain date. The second is all changes by user on the same date. I know that the queries seperately will look like,
SELECT COUNT(User) As NumberByUser
FROM Table
WHERE (Date = 'DateGoesHere') AND (User= 'UserGoesHere')
GROUP BY User
SELECT COUNT(Department) As NumberByDepartment
FROM Table
WHERE (Date = 'DateGoesHere') AND (Department = 'DepartmentGoesHere')
GROUP BY Department
But how do I go about linking the two in the same query? Any help would be appreciated, thanks!
View 2 Replies
View Related
Jan 8, 2007
an error, incorrect syntax, was generated when the following query was tried:
CREATE TABLE ASPNET2(id smallint IDENTITY(1,1) PRIMARY KEY,word nvarchar(50) NOT NULL,definition nvarchar(MAX) NOT NULL)
I wanted to use nvarchar(MAX) because something I read suggested that I couldn't do editing with the controls if a variable was set to text.
Thanks.
-Larry
View 3 Replies
View Related
Sep 20, 2004
Hi there,
I am trying to build a proc that uses a loop to import data into several tables. The data is copied into the appropriate table according to the contents of the variable @PracticeCode. I am also trying to add a date value to each record as it is added to the table. I thought that the best way to do this would be t use the sp_executesql stored proc. but I am having difficulty getting it to work. Here's what I have done so far:
-- insert data into proper tables with extract date added
SET @SQLString ='INSERT INTO GMS_48hrAccess.dbo.tbl_Surgery'+@PracticeCode+' SELECT
SurgeryKey,'+
@extractDate+',
ClinicianCode,
StartTime,
SessionGroup,
[Description],
SurgeryName,
Deleted,
PremisesKey
FROM GMS_48hrAccess.dbo.tbl_SurgeryIn'
EXEC master..sp_executesql @SQLString
And here's the error message that I get:
Server: Msg 241, Level 16, State 1, Line 90
Syntax error converting datetime from character string.
I understand why I am getting this error I just can't seem to fix it. I've consulted BOl and have tried various Parameter combinations but to no avail.
Can anyone help?
Thanks
View 1 Replies
View Related
Jul 20, 2005
Hi GuysI don't want to keep asking for your help all the time on each individualquery, so could you please help me to break the myths on the following:1) I have 2 tables. Once is called ACCOUNTS and the other ACCOUNTBUDGET.ACCOUNTS includes all of the usual details for customers and suppliers, egthe account code (as ACCOUNTID), the account name (as NAME), etc.ACCOUNTBUDGET basically holds a transaction line for each month and it'scorresponding year to store the turnover for that month, eg one row containsACCOUNTID, PERIOD (ie the month), YEAR and TURNOVER.Now a lot of the SQL 6.5 tables that I deal with are in this vein and theusual query is that I want to list all of the ACCOUNTIDs and NAMEs thatexist in the ACCOUNTS table and then show for example what their TURNOVER isfor a applicable PERIOD and YEAR, which are all held in the ACCOUNTSBUDGETtable.Now if I do a quick query using MS Query all I get are rows that haverelated values in both the ACCOUNTS and ACCOUNTSBUDGET table when I havespecified say a certain PERIOD and YEAR.The main point of my current reporting problem is that I want to show allthe ACCOUNTIDs and NAMEs in ACCOUNTS that have zero TURNOVER for aparticular PERIOD and YEAR.I'm positive that I have to create a 2 step query/join, but I don't know howto do it. What is the method? People in this NG, can rattle one up inseconds, but I just don't see the logic. Can you help me with this queryand let me know how you manage to fathom it.2) Are there any good web sites that explain in kiddie form how to do thissort of thing?I really appreciate your help on this.RegardsLaphan
View 1 Replies
View Related
Jun 28, 2007
Can anyone help me with this ?
I am trying to extract data from oracle 9i server and
pushing it onto the SQL Server 2005 using Data Flow Task.
Details for OLE DB Source are :
OLE DB Source Editor Details:
OLE DB Connection Manager - Oracle Source
Data Access Mode - SQL Command
SQL Command Text -
SELECT EMPNO, EMPNAME, JOB, HIREDATE
FROM EMP_DETAILS
WHERE (HIREDATE > TRUNC(SYSDATE) - 1)
SSIS parses this query succesfully but when i build the query it shows query
SELECT EMPNO, EMPNAME, JOB, HIREDATE
FROM EMP_DETAILS
WHERE (HIREDATE > TRUNC("SYSDATE") - 1)
Please note :- SYSDATE IN " "
This query returns no Result Set.
View 6 Replies
View Related
Aug 2, 2006
Is Query Analyzer being droped in MS-SQL 2005? I'm using Microsoft SQL
Server Managemant Studio and I couldn't find the Store Procedure node
and Query Analyzer anymore. Help is appreciated.
View 2 Replies
View Related
Aug 16, 2007
Say you have a state column with the name of a state in it. And you need to add a branchId to the table. I know I should not have it in there, and the application should use proper lookup tables, but that's not the case.
Can SQL Server query analyzer loop through and update the branchid field based on a lookup in a lookup table?
How would that be done?
View 3 Replies
View Related
Apr 4, 2008
Hi All,
I think I post this in a wrong section but I cant find forum for sql server and i just have a quick question
basically, I installed sql server 2005 and I need to debug a store procedure.1. Does query analyzer come with sql server 2005?2. If not how can I debug store procedure in sql server 2005?Thanks in advance
View 2 Replies
View Related
Mar 22, 2001
Does anyone know if there is a limitation in Query Analyzer in terms of the number of characters in a query that it will parse?
I am having a problem with some large queries, and I am not sure if it is Query Analyzer, or the SQL Server query parsing engine that is causing the problem.
The query just involves a simple SELECT statement, albeit for a large number of columns.
View 2 Replies
View Related
Aug 2, 2000
Hi,
I'm using SQL Server 7.0. I connect to one database in Query Analyzer and I can use the "Perform Index Analysis" option under the "Query" menu option. when I connect to one of our other databases, the "Perform Index Analysis" option under the "Query" menu option is disabled (greyed out). Does anyone know what the problem could be and how might I be able to enable this option.
Thanks in advance,
Darrin
View 1 Replies
View Related
Mar 8, 2006
Hello
In query Analyzer of SQL2000 when i select
2 Select Queries and Run it shows in same Window as 2 Grids
I have seen people using seperate grid option to view each query results in a Tab query results Pane in MSSQL 7.
Eg Select * from table1 (in one grid)
select * from table2 (in another grid)
like a TAB so that i can move from one tab to another.
Is it possible to do that in query Analyzer?
Regards
View 3 Replies
View Related
Jan 5, 2004
I would like to use the Query Analyzer to create a script that will prompt the user(dialog box) for a response in SQL Server 7.0. I found a post someone mentioned DBMS can return messages and using Query Analyzer.
Thanks for your help in advance.
View 3 Replies
View Related
