I have what seems a simple problem....
I want to save the output from the following queries in 1 file in
'SQL Query Analyser', but I can only save each 'grid' separately.
I have about 30 queries in total. 'Messages' tells me the number of
rows affected but I need the data.
So can I save all the grids together at the same time in 1 file ?
Thanks
Mike
PRINT 'HEAT 3510'
SELECT f.persfirstname AS First_Name,
f.perslastname AS Last_Name,
a.rsrchqnumber AS Personal_nr,
b.asgtassignmentid AS Contract_nr,
ISNULL(c.tishcode,'') AS TS_Id,
ISNULL(d.tpitpayrollcode,'6900') AS Pay_Code,
d.tpitdaydate AS Payroll_Date,
c.tishactualenddate AS TS_end_date
FROM resources a
INNER join assignments b ON b.asgtrsrcguid = a.rsrcguid
INNER join timesheets c ON c.tishasgtguid = b.asgtguid
INNER join users e ON e.userguid = a.rsrcuserguid
INNER join persons f ON e.userpersguid = f.persguid
LEFT OUTER join timesheetpayrollitems d ON d.tpittishguid =c.tishguid
WHERE a.rsrchqnumber = 80000147
and b.asgtassignmentid = 0000001234
and c.tishactualenddate > '20050614'
and (d.tpitdaydate > '20050614'
or d.tpitdaydate IS null)
order by d.tpitdaydate
SELECT f.persfirstname AS First_Name,
f.perslastname AS Last_Name,
a.rsrchqnumber AS Personal_nr,
b.asgtassignmentid AS Contract_nr,
ISNULL(c.tishcode,'') AS TS_Id,
c.tishactualenddate AS TS_end_date,
g.thisdaydate AS TSH_date
FROM resources a
INNER join assignments b ON b.asgtrsrcguid = a.rsrcguid
INNER join timesheets c ON c.tishasgtguid = b.asgtguid
INNER join users e ON e.userguid = a.rsrcuserguid
INNER join persons f ON e.userpersguid = f.persguid
INNER join timesheethistory g ON g.thistishguid = c.tishguid
WHERE a.rsrchqnumber = 80000147
and b.asgtassignmentid = 0000001234
and c.tishactualenddate > '20050619'
order by g.thisdaydate
I just started using Visual Studio 2005. How do you actually save your queries to use again? Also, is there a way to export results in a user friendly format?
I opened up a new project using Visual Basic Express, and selected the SQL object to start creating a database. Well I finished putting all of my tables together and started making the queries but for some reason I can't save them like when I finished making the tables. When I finished making the tables I could just click the disk icon and name the table and it would appear in a drill down menu on the left. However when I do that with the queries it just says it's saving in the lower left corner but it doesn't do anything?
I am new to VB & SQL so any help is appreciated. Thank you.
I've a large number of stored procedures that output result sets, as part of a test script it would be useful for me to be able to save the results from these stored procedures into temporary tables, without having to pre-create these tables, i.e. use the equivalent of a select * into statement.
I am learning SSIS, and am trying to save the XML output from a SQL task executing a stored procedure. I need to either save it to a .xml file, then FTP the .xml file to an external server, or do the reverse (FTP, then save file.) I can't get past the SQL task so that it works with the File System Task, or the FTP task. Here is the stored procedure Create Procedure [dbo].[HRGCSpace] (@HRGSpace nvarchar(max) = ''Output) as Begin Set NOCOUNT On set @HRGSpace = (select CustomerNumber 'CustomerNumber/@id',StartDate 'StartDate/@id', EndDate 'EndDate/@id',Action 'Action/@id', WorkZoneNumber 'WorkZoneNumber/@id',BuildingName 'BuildingName/@id',BuildingFloorPlan 'BuildingFloorPlan/@id', FloorName 'FloorName/@id',FloorFloorPlan 'FloorFloorPlan/@id',SpaceName 'SpaceName/@id',VacantUnit 'VacantUnit/@id', SpaceFloorPlan 'SpaceFloorPlan/@id',Address1 'Street/@id',Address2 'Street2/@id',City 'City/@id', StateProv 'StateProv/@id',ZIPPost 'ZipPosCode/@id',MoveOutDate 'MoveOutDate/@id' from HRGCvCorrigoUnits order by BuildingName, SpaceName For XML Path ('Space'), Root('HRGSpaces')) End -------------------------------------- Here is the execute from the SQL Task DECLARE @HRGSpace nvarchar(max) EXEC HRGCSpace @HRGSpace Output
If I define the SSIS variable User::HRGSpaces as string, Direction = Output, Parameter Name = 0, Parameter Size = 0 , then use in the SQLTask General ResultSet = None, Bypass Prepare = True, Connection Type = OLE DB, IsQuery Stored Procedure = False, Parameter Mapping for User::HRGSpaces,Direction = Output, Parameter Name = 0, Parameter Size = 0 the Progress is "Progress: Executing query "DECLARE @HRGSpace nvarchar(max) EXEC HRGCSpace @HR". - 100 percent complete Task Execute SQL Task - select the XML Output failed Error: The wrapper was unable to set the value of the variable specified in the ExecutionValueVariable property.
If I change it so that ResultSet = XML, the Progress is " [Execute SQL Task] Error: There is an invalid number of result bindings returned for the ResultSetType: "ResultSetType_XML".
If I add ResultName = 0, and VariableName= HRGSpaces, I'm back to "Progress: Executing query "DECLARE @HRGSpace nvarchar(max) EXEC HRGCSpace @HR". - 100 percent complete Task Execute SQL Task - select the XML Output failed Error: The wrapper was unable to set the value of the variable specified in the ExecutionValueVariable property."
Leaving the ResultSet = XML, and changing the SSIS variable as Object, the Progress is " [Execute SQL Task] Error: There is an invalid number of result bindings returned for the ResultSetType: "ResultSetType_XML".
Changing the ResultSet back to None, but leaving the variable as Object, the SQL task will successfully execute, but the File System Task with IsSourcePathVariable = True and SourceVariable = User::HRGSpaces when executed errors out with "[File System Task] Error: Variable "HRGSpaces" is not a string. Error: There were errors during task validation. Validation is completed. Warning: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (3) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
So, how to I resolve the Object vs String definition. Any help in getting beyond this is appreciated!
Does anyone know where to declare the target directory for scripted output from Enterprise Manager?
Every time I get EM to script Tables I want the output not to go to c:mssql7inn . . but somewhere else. Trouble is I can't find where to set this variable. It won't work in a shortcut - I've tried that, and I can't find an ini file either.
Seems like a simple problem to fix if you just know where to look !
I am asked to create 100 procedures to a database. Any best way to create them in a database one by one by calling the files and saving the execution output files in a folder?
i m using Sqlserver 2000 and i wish to know that , Can i redirect the output of the query into a text/xls/html file ! If possible then please help me !!!!!
We have two queries that run nightly and we'd like to combine them and only have one result set instead of two. What's the best way to combine these? The only difference is the Table the information is being pulled from.
Query 1:
set nocount on select case when datalength(MICRACCTNUMBER) = 4 then convert(char(20),('001 000000000000'+MICRACCTNUMBER)) when datalength(MICRACCTNUMBER) = 5 then convert(char(20),('001 00000000000'+MICRACCTNUMBER)) when datalength(MICRACCTNUMBER) = 6 then convert(char(20),('001 0000000000'+MICRACCTNUMBER))
[code].....
Again, the only difference is the Table the info is coming from...
I have a trade data tables (about 10) and I need to retrieve information based on input parameters. Each table has about 3-4 million rows.
The table has columns like Commodity, Unit, Quantity, Value, Month, Country
A typical query I use to select data is "Select top 10 commodity , sum(value), sum(quantity) , column4, column5, column6 from table where month=xx and country=xxxx"
The column4 = (column2)/(total sum of value) and column 5=(column3)/(total sum of quantity). Column6=column5/column4.
It takes about 3-4 minutes for the query to complete and its a lot of time specially since I need to pull this information from a webpage.
I wanted to know if there is an alternate way to pull the data from server ?
I mean can I write a script that creates tables for all the input combinations i.e month x country (12x228) and save them in table (subtable-table) with a naming convention so from the web I can just pull the table with input parameters mapped to name convention and not running any runtime queries on database ??
OR
Can I write a script that creates a html files for each table for all input combinations save them ?
Yesterday I was looking to the processor usage in the Task Manager of Windows NT when a script of mine was running. The script was an InfoPump Script; which is a tool from the DecisionBase suite from CA (was previously owned by Platinum). This script contains SQL statements that select data from several tables and stores the result into another table. The SQL code used for this looks fine to me. The query was running on a Compaq Proliant 5500 with 4 500 Mhz Xeon processors, 1 GB RAM, NT Server 4, SP 5, RAID 5. The SQL Server is configured to use all resources and SQL has normal priority on NT. When the select part was running al four processors were used for about 75% and when the store happens only 1 processor is used for 100%. Why is the store not spread over all four processors? It only uses one processor and it seems to be a bottleneck.
Hi everyone, I'm very new to SQL and SQL Reporting Services. However I've been given a task.
How do I save multiple reporting services reports onto a predetermined location on a users local pc? Are there tools/applications that can be used to push files onto the users local pc?
The application uses .NET but they've created reports using SQL Reporting Services. The user will select which report they want (out of 2). My task is to figure out how can we save those reports that they have selected onto their pc?
We are running 2014 enterprise. Our users love to see related report sections saved in separate tabs of the same spreadsheet. Is there a way to control how ssrs will save a report to excel when it comes to tabs?
Are subreports a/the way (and only way) to do this?
By sections I mean they might have a grid, then another grid, then a graph then another graph and so on.
What property controls the tab name? What if they want to combine 2 or more sections into one tab?
Hi I have two queries, one which takes 1 second to execute and the other which takes less that a second to execute. I need to join them up similar to the following but when I do it take forever for them to execute. select * from table where column not in (select column from table2) It is quite complex and I have a couple of views included in the queries but it executes quickly on my dev database and when I run it on the live one it takes forever. Granted there is alot more data on the live database but the individual queries only take a second to run. Does anyone know why there would be such a time increase whenever I join the queries and run them on my live database? Any help would be appreciated. Thanks, Frankie
************************************************************* <code> tags added by moderator. Please use <code> and </code> tags to bracket comments to make code readable.
I am a beginner to this whole .net thing so my code is probablly scary, but anyway, I am looking for a way to insert records into a database and then get the last ID. I am sure there is a much better way to do this, but here is the code that I have that ins't quite working:
'SQL Insert data' Dim Conn As SqlConnection Dim Rdr As SqlDataReader Dim strConn As String = AppSettings("doConnect") Dim strSQL As String = "INSERT INTO USERS (username, password, accountNum) VALUES ('" + Email.Text.Trim() + "', '" + password.Text.Trim() + "', '" + Session("accountNum").Trim() + "')" Conn = New SqlConnection(strConn) Dim Cmd As New SqlCommand(strSQL, Conn) Conn.Open() Cmd.ExecuteNonQuery()
'SQL Get Last ID Dim ConnGet As SqlConnection Dim RdrGet As SqlDataReader Dim strConnGet As String = AppSettings("doConnect") Dim strSQLGet As String = "select MAX(id) as maxid from USERS" ConnGet = New SqlConnection(strConn) Dim CmdGet As New SqlCommand(strSQL, Conn) ConnGet.Open() If CmdGet.ExecuteScalar() <> 0 Then RdrGet = CmdGet.ExecuteReader() While RdrGet.Read() Session("theID") = RdrGet("name") End While Else Session("theID") = 0 End If
Does anyone know how to count multiple columns in a SQL Query from One table and return numbers I can use the following but don't know how to join the two statements to one.
SELECT COUNT(*) AS [NUMBER OF SERVERS] FROM DBO.OS WHERE (OSTYPE LIKE '%SER%')
The other statement is
SELECT COUNT(*) AS [NUMBER OF WORKSTATIONS] FROM DBO.OS WHERE (CAPTION LIKE '%9%') OR (CAPTION LIKE '%PRO%') OR (CAPTION LIKE '%ME%')
If any one has any idea's please reply or email me directly. I would greatly appreciate it.
I'm writing an ASP page for a project and it requires multiple queries.However, I'm trying to combine multiple SELECT statements, but can'tfigure out a way that actually works.Basically, here are the SELECT statements I want to combine:SELECT * FROM schoolrequest WHERE SLid=10SELECT SLlastName FROM academicofficeWHERE schoolrequest.SLsendToId=academicoffice.AOidSELECT SLlastName FROM academicofficeWHERE schoolrequest.SLbillToId=academicoffice.AOidSELECT SLlastName FROM academicofficeWHERE schoolrequest.SLrequestorId=academicoffice.AOidSELECT * FROM diaryentry WHERE diaryentry.DEkeyValue=10AND diaryentry.DEdeletedDate IS NULLThe academicoffice table just contains basic contact info, but needs tobe used 3 times in the query to get specific contact info for 3different contacts(SLsendToId, SLbillToId, SLrequestorId)The diaryentry table contains info entered in by students. TheDEkeyValue is actually the primary id of the schoolrequest table(SLid).Any ideas?Thanks,M
Hello everyone... I have created a report that pulls data from 5 different tables. I have created a tableadapter for this. I have a sql stored procedure with left joins that I am trying to use but keep getting key violations and not null value violations when I try to use it. I have tried setting the NULLValue to NOT throw exception but that has not helped. I have also tried to writing a query for each table. I can add each query to my tableadapter but I can use them all at the same time for some reason. Can this be done? When I go to my report and try to add fields to it from the tableadapter, I only see the results of one query. I am only trying to SELECT. I am not doing any updates or deletes to the tables on the SQL Server. Any advice would be greatly appreciated.
I get a wo_ID and want to query company data. I have the following tables, work_orders (has wo_ID, wo_name and install_id), install_ids (has install_id, comp_id) and customers (comp_id, company_name). The query process goes like this: I get a wo_ID and I need to find the install_id that corresponds with that wo_ID in that same work_orders table. Then take that install_id and find out the comp_id that corresponds with that install_id from the install_ids table. Finally take that customer_id and link it to comp_id in the customers table to find out company data. I need to join it multiple times to get my final data. I have a simple one that works right now with one join and using the install_id, but I don't know how to expand it with multiple joins. Thanks.
I hit a bit of a road block on a project I have been working on. If anyone has a suggestion or a solution for how to combine my queries that use IFELSE that would be a huge help. I noted my query below./* will remove for aspx page use */USE Database/* these params are on the page in drop down boxes*/DECLARE @ProductID int;DECLARE @BuildID int;DECLARE @StatusID int;/* static params for this sample */SET @ProductID = -1;SET @BuildID = -2SET @StatusID = -3/*the query that will build the datagrid. currently this runs and produces three different result sets.How do I combine these statements so they produce a single set of results? */IF (@ProductID = -1) SELECT * FROM tblTestLog ELSE (SELECT * FROM tblTestLog WHERE (ProductID = @ProductID))IF (@BuildID = -2) SELECT * FROM tblTestLog ELSE (SELECT * FROM tblTestLog WHERE (BuildID = @BuildID))IF (@StatusID = -3) SELECT * FROM tblTestLog ELSE (SELECT * FROM tblTestLog WHERE (AnalystStatusID = @StatusID))
If you have a dozen or so queries related to the same thing such as an exception report do you wrangle with the query to make it a one stop shop or do you do something different? Is it possible to drop them into a SP and have them all execute at the same time?
I am running queries for against business rules and wanted to do it the right way. I don't have a solid mentor at work, two guys who are experienced and off a lot but not in the world of reports.
I hit a bit of a road block on a project I have been working on. If anyone has a suggestion or a solution for how to combine my queries that use IFELSE that would be a huge help. I noted my query below.
/* will remove for aspx page use */ USE Database
/* these params are on the page in drop down boxes*/ DECLARE @ProductID int; DECLARE @BuildID int; DECLARE @StatusID int;
/* static params for this sample */ SET @ProductID = -1; SET @BuildID = -2 SET @StatusID = -3
/* the query that will build the datagrid. currently this runs and produces three different result sets. How do I combine these statements so they produce a single set of results? */
IF (@ProductID = -1) SELECT * FROM tblTestLog ELSE (SELECT * FROM tblTestLog WHERE (ProductID = @ProductID))
IF (@BuildID = -2) SELECT * FROM tblTestLog ELSE (SELECT * FROM tblTestLog WHERE (BuildID = @BuildID))
IF (@StatusID = -3) SELECT * FROM tblTestLog ELSE (SELECT * FROM tblTestLog WHERE (AnalystStatusID = @StatusID))
I have the below query that has multiple select statements from the same query. Is it possible to run this more efficiently with a SUM/ CASE statement so that its just one SELECT ?
I just used a SUM(CASE WHEN ....... statement on another query, but I am having trouble figuring out how to integrate it on this one, and if its even possible or worth it.
Thanks very much for any help!! mike123
CREATE PROCEDURE [dbo].[select_mailbox_Count] ( @userID int ) AS SET NOCOUNT ON
SELECT count(*) as totalInbox,
(SELECT count(*) FROM tblMessage M JOIN tblUserDetails UD on UD.userID = m.messageFrom WHERE messageTo=@userID AND checked = 0 and deletedbyRecipient =0 ) as totalInbox_UnRead, (SELECT count(*) FROM tblMessage M JOIN tblUserDetails UD on UD.userID = m.messageFrom WHERE messageTo=@userID AND checked = 2 and deletedbyRecipient =0 ) as totalInbox_UnReplied, (SELECT count(*) FROM tblMessage M WHERE messageFrom=@userID AND deletedByRecipient = 0 AND deletedBySender = 0 ) as totalOutbox, (SELECT count(*) FROM tblMessage M WHERE messageFrom=@userID or messageTo =@userID AND deletedByRecipient = 0 and deletedBySender = 0 ) as totalTrash
FROM tblMessage M JOIN tblUserDetails UD on UD.userID = m.messageFrom WHERE messageTo=@userID AND deletedByRecipient = 0
I'm cleaning data which involves updating ~12 million rows with threedifferent models, progressively. First clean values using the modelwith finest granularity, then the remainder with the next model,finally what's left using the last model. The first model sets ~1/2 ofthe rows, the second ~1/4, the third ~1/5, and the remaining 5% don'tget updated.It's something like this:UPDATE t SET value=value*m.AdjustmentFactor, updateFlag='updated'FROM Table t JOIN Models m ON ....WHERE m.ModelID='first model' AND t.updateFlag IS NULLStart with 'first model' then 'second model' etc.I'm wondering what happens if I submit all three queries together, oras three separate submissions, waiting for the one before to complete.If I do them all as one group, the query planner might plan for thesecond and third updates based on the initial distribution of values.However, the first update removes half of the rows from consideration,so it seems to me a new plan should be prepared for the second query,based on the distribution at that time. If I highlight the queries inQuery Analyzer and execute, are all three plans created at thebeginning? Does putting GO between them (which I do) make anydifference?This is SQL Server 2000.Thanks,Jim
I've written a SQL script to go in and pull student grades for honor roll. The first subquery in the script eliminates any students that have any D's, and the second sub query is supposed to give me a only students that have no more than 1 C of any type as the Honor Roll criteria is that a student has to have a GPA between 3.0 and 3.49 and no more than 1 C. If I run each individual sub query by itself they work and pull the students that they are supposed to. If I run the main query along with the first sub query to eliminate students with D's everything works. If I run the 2nd sub query by itself it will pull the students that meet the criteria. If I run the main query along with both sub queries or the main along with the 2nd sub query it doesn't work. Here is what the query looks like that I'm trying to run. Thanks in advance for any help.
I have some questions about making muliple T-SQL queries againt an SQL-server. I retrieve all rows from an SQL-server table called ActivityGroup and add the result to a Radio Button List. Suppose, I also wish to add the total sum of every "ActivityGroup" value to the Radio Button List. I guess this ought to be done using a SELECT COUNT statement and then retrieved using the Executescalar method?
The question is do I need to copy the code for the SELECT ('*) statement and make the neccessary changes for the SELECT COUNT statement and Executescalar method or is there a more simple way? Is it possible to use two command statements in the same code block?
The total sum is going to be calculated using the values from both an SQL-server and an Access database. The OleDb sample below is what I refer to as a code block. I appreciate any help!
In Sql 2005, I can't get this to work. I am getting an error that says SQL does not support subquery. SQL has a problem with my subquery. It works fine with one subquery, but adding multiple gives me an an error.
INSERT INTO STU_SUMMARY (SUMMARY_PERIOD, CHILD_COUNT, NO_DATA_ERRORS, PROG_CD_DESCREP, TOT_CURR_SPEDSTU) VALUES (GETDATE(), (select count(*) counter from DATA_ERROR_DETAIL), (select count(*) counter from DATA_ERROR_DETAIL), (select count(*) counter from DATA_ERROR_DETAIL), (select count(*) counter from DATA_ERROR_DETAIL), )
I am trying to automate a basic task using SQL Server 2005 Express.
Currently I have a query script that I run and then save the results as a CSV file. I need to do this on a daily basis and so I am looking to find out how best to go about this. There are a multitude of third party tools that claim to be able to do this - can anyone recommend this or enlighten me of the best way to set up this automation.
Hi, I'm having problems manipulating the results from multiple count(*) queries.
I have a table 'RECOMMENDATIONS' holding a field called 'SCORE'. This field holds a number from 1 to 10 and has no null or zero figures.
I currently have the following three queries: 1) SELECT COUNT(*) FROM RECOMMENDATIONS WHERE SCORE IN (1,2,3,4,5,6) 2) SELECT COUNT(*) FROM RECOMMENDATIONS WHERE SCORE IN (9,10) 3) SELECT COUNT(*) FROM RECOMMENDATIONS
I now need to combine these three queries so that i can divide and multiply the resulst: (query 2/query 1)*query 3
My initial idea was: SELECT (COUNT(*)/(SELECT COUNT(*) FROM RECOMMENDATIONS WHERE SCORE IN (1,2,3,4,5,6)))* (SELECT COUNT(*) FROM RECOMMENDATIONS) FROM RECOMMENDATIONS WHERE SCORE IN (9,10)
... but this gives a result of "0". I then stripped out the multiplication section to see if the divide was working: SELECT COUNT(*)/(SELECT COUNT(*) FROM RECOMMENDATIONS WHERE SCORE IN (1,2,3,4,5,6)) FROM RECOMMENDATIONS WHERE SCORE IN (9,10)
I was messing around with stored procedures and I was wondering if creating a SP that populates a single table for reporting is a good idea?
I have ~10 queries that I have to currently run manually and was hoping to drop them into a physical table and then leverage that single table to pull into excel.
Some of my queries use virtual tables or CTE's, this is to get the aggregate set correctly.
Essentially I am working out of a data warehouse and would like to eventually get all my queries in one SP or something similar and then call that query for a insert.
Speaking of which could you create a SP that has several selects than with that output drops the records into a single table by using an insert into query so the data from the all the queries would line up into the right columns?