Looping Through Results Of A OSQL Query
Jul 23, 2005
Hello,
I have the following problem.
I wrote a batch file that runs a sql script on SQLServer 2000. This
script must be executed on several databases. This works fine.
The problem is that in my database is a table that holds a databases i
have to update with this script. What i want is run a query with osql
in my batchfile to retrieve this these records so I can loop through
them an run the script for those databases.
I managed to execute the query that return the records and write them
to a textfile.
What i want is store the results in some kind of a resultset so i can
loop through them in my batchfile.
Anyone have the solution
Thanks in advance
Patrick
View 1 Replies
ADVERTISEMENT
Feb 4, 2015
Query should only return less than 3000 records but its returning over 4M. It needs to show all duplicates records.... All the info are on the same table VENDFIl, so I used a self join but it seems to be looping..
SELECT A.FEDTID, B.VENDOR, C.NPI_NUMBER
FROM VENDFIL A, VENDFIL B, VENDFIL C
GROUP BY A.FEDTID, B.VENDOR
View 5 Replies
View Related
Mar 28, 2008
Im having a issue. Im not sure how I am going to carry out but I have two tables in SQL server 2005
TABLES
Category SubCategory (PK)CategoryName (PK) SubCategoryNameCategoryID SubCategoryIDDate Date (Just shows the date inserted) (FK)CategoryID
On the front page, I need to have it querys out the CategoryName from Categorys but also querys out all....Well not all but atleast 5 subcategorys that relate to that categoryName. Once its down it moves to the next category and does the same and so on. Does anyone know the trick ?
View 5 Replies
View Related
Sep 14, 2000
Does anyone know of a way to prevent the results of an OSQl query from breaking into multiple lines. The results come in a mess I need to clean them up to import into a spreadsheet.
Thanks
View 1 Replies
View Related
Aug 16, 2005
I am not sure if what I wish to do is possible, but I shall ask anyway;
My project examines a database log of all the pages of an online teaching tool. Once the user has completed all the pages they are to be issued a certificate. Users may complete the teaching tool in any order, and the pages are always stored whenever they are acccessed, regardless of certification.
I have created a number of views that extract the data into a list of all the possible completion dates; i.e. where all the pages have been completed within any 12 month period. I need to write a query/view that uses the view to extract the first possible user completion date followed by every completion 12 months after that, then after that etc. to present day.
Can I do this?
Am I making sense ?
A no is acceptable in this case; I know I can do this with multiple queries from withing an application. I'd just rather not.
View 4 Replies
View Related
Apr 9, 2008
This is the TABLE I have
IDBRANCH STATUS
1A ACTIVE
1BINACTIVE
1CINACTIVE
2BINACTIVE
2CINACTIVE
2DINACTIVE
3A ACTIVE
3B ACTIVE
3C ACTIVE
4B ACTIVE
5DINACTIVE
----------------------------------------------------------------
Following is the desired View that I need for the above table.
Any ID which has atleast one ACTIVE branch will have ACTIVE status and any company which have all of its branches INACTIVE will have INACTIVE status. Thanks for your help
IDSTATUS
1 ACTIVE
2INACTIVE
3 ACTIVE
4 ACTIVE
5INACTIVE
View 2 Replies
View Related
Aug 14, 2007
Can anyone give me a hand on how to loop this query until there are no results left, this is going to be part of a stored procedure by the way.
Code Snippet
declare @strcharid varchar(21)
set @strcharid = (select top 1 struserid from userdata where class = '108' and authority = '1')
update tb_user set strauthority = '255', BanReason = 'Master Skill Hack', TermDate = getdate() where straccountid in (select top 1 straccountid from ip_tracker where strcharid = @strcharid)
update userdata set loyalty = '0' where struserid = @strcharid
View 7 Replies
View Related
Dec 10, 2007
Hi All,
The command showed below provides the output shown:
set @cmd = 'osql -S bcgsql1cgsqldev -U checkdb -P checkdb -q "set nocount on; select errorlogdesc from ##errors where datediff(dd,errorlogdatetime,getdate()) = 1 and errorlogdesc like ''%Error:%''" -h-1 -w 900 -o J:ScriptsTestStuffErrorLogMsg.txt'
Here is the output:
Error: 17883, Severity: 1, State: 0
Error: 17883, Severity: 1, State: 0
I need to remove the space between the two lines so I set the -w parameter to 900 but that didn't fix it. Any idea how I can accomplish this?
Thanks
View 4 Replies
View Related
Feb 22, 2008
All,
I am facing one problem while running below given query from SSMS or
QA while the same
query running fine while running through osql at command prompt.
while running through command prompt it is giving correct value like
0,1,2 or 3 . With SSMS everytime it is giving null as result.
exec xp_cmdshell 'osql -S SMOSVR0686 -E -Q"declare @test int
Exec master..xp_regread @rootkey = ''HKEY_LOCAL_MACHINE'',@key =
''SOFTWAREMicrosoftMSSQLServerMSSQLServer'',@val
ue_name = ''AuditLevel'',@value=@test OUTPUT select @test"'
p.s - while running at command prompt please remove extra quotes..
Any help is much appreciated
nj
View 1 Replies
View Related
Jan 4, 2001
I've had a thousands instances where this technique would come in handy. Can somebody help me with a way to do a select, like a join parent and child table. Then say something like:
declare @variable varchar(30)
--some kind of loop through the result set
set @variable = queryfieldresult
if (@variable is not null)
begin
select * from table where productId = @variable
end
--end loop
Thanks,
Rick
View 1 Replies
View Related
Mar 9, 2006
Good Morning,
I am dealing with a complex query that seems to generate stack compile and timeout errors. It was suggested that the query be rewritten to become more simplified, and while I need to still display the same required data, I am wondering if incorporating a loop in the statement would help... and how would I go about doing so?
A working example would be helpful. Thanks!
View 13 Replies
View Related
Jun 12, 2006
I'm running a query with osql, and I'm trying to get some clean output that is comma delimited. So far my line looks like this:osql -E -n -d mydb -i custom.qry -o "c:output
esults.csv" -h-1 -s ","This works off a table with only two columns. I'm still left with a lot of extra spaces between the first column output and second column output, and at the bottom the text "(50 rows affected)".So instead of this:data1a (lots of spaces here) ,data1bdata2a (lots of spaces here) ,data2bdata3a (lots of spaces here) ,data3b(50 rows affected)I want to see this:data1a,data1bdata2a,data2bdata3a,data3bThanks very much for any help. :)
View 2 Replies
View Related
Apr 1, 2007
hi, like, if i need to do delete some items with the id = 10000 then also need to update on the remaining items on the with the same idthen i will need to go through all the records to fetch the items with the same id right? so, is there something that i can use to hold those records so that i can do the delete and update just on those records and don't need to query twice? or is there a way to do that in one go ?thanks in advance!
View 1 Replies
View Related
Feb 12, 2008
Hello. I currently have a website that has a table on one webpage. When a record is clicked, the primary key of that record is transfered in the query string to another page and fed into an sql statement. In this case its selecting a project on the first page, and displaying all the scripts for that project on another page. I also have an additional dropdownlist on the second page that i use to filter the scripts by an attribute called 'testdomain'. At present this works to an extent. When i click a project, i am navigated to the scripts page which is empty except for the dropdownlist. i then select a 'testdomain' from the dropdownlist and the page populates with scripts (formview) for the particular test domain. what i would like is for all the scripts to be displayed using the formview in the first instance when the user arrives at the second page. from there, they can then filter the scripts using the dropdownlist.
My current SQL statement is as follows.
SelectCommand="SELECT * FROM [TestScript] WHERE (([ProjectID] = @ProjectID) AND ([TestDomain] = @TestDomain))"
So what is happening is when testdomain = a null value, it does not select any scripts. Is there a way i can achieve the behaivour of the page as i outlined above? Any help would be appreciated.
Thanks,
James.
View 1 Replies
View Related
Feb 13, 2008
I have a stored procedure that I want to basically pull in a code/codes which correspond to people. I then convert the code to the persons actual name that I then want to send back and add to a listbox.
Right now with the code I have, it returns just one name. I know I am doing something wrong, but I am not sure if what I want is possible with a stored procedure or if I am on the right track.
my code is as follows.
The proceedure I am running is as follows...
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[GetAttySectyData] (@id as int)
AS
BEGIN
DECLARE @First as varchar(200)
DECLARE @Middle as varchar(50)
DECLARE @Last as varchar(200)
DECLARE @Code as varchar(200)
Select @Code = [SectyCode] From tblMain2 as M LEFT JOIN tblSectyData as S ON M.Code = S.Code WHERE M.Code = @id
SELECT @First = [value] FROM dbo.[text] WHERE efield_id = 10741 AND employee_id = @Code
SELECT @Middle = [value] FROM dbo.[text] WHERE efield_id = 10906 AND employee_id = @Code
SELECT @Last = [value] FROM dbo.[text] WHERE efield_id =10740 AND employee_id = @Code
Select ISNULL(@First, '') + ' ' + ISNULL(@Middle, '') + ' ' + ISNULL(@Last,'') AS FullName
END
////which is called from SqlCommand ShowTeam = new SqlCommand("Exec dbo.GetAttySectyData '" + selectedEmployee + "' ", IntranetConnection);
To populate the listbox, I was running this. As I mentioned, I get one name.
IntranetConnection.Close();
IntranetConnection.Open();
IntranetReader = ShowTeam.ExecuteReader();
while (IntranetReader.Read())
{
TeamList2.Items.Add(IntranetReader["FullName"].ToString());
}
IntranetConnection.Close();
View 5 Replies
View Related
Nov 10, 2003
I have the following .bat file that I call to register users to my MSDE instance...
echo osql -E -S %COMPUTERNAME%OfficiumInstance -Q "sp_grantlogin '" + %COMPUTERNAME% + "ASPNET'"
echo osql -E -S %COMPUTERNAME%OfficiumInstance -d Officium -Q "sp_grantdbaccess '" + %COMPUTERNAME% + "ASPNET'"
echo osql -E -S %COMPUTERNAME%OfficiumInstance -d Officium -Q "sp_addrolemember 'db_owner', '" + %COMPUTERNAME% + "ASPNET'"
My question is how do I correctly append %COMPUTERNAME% as a string to
I have tried using + signs...
"sp_grantlogin '" + %COMPUTERNAME% + "ASPNET'"
But that doesn't work.
I have looked for .bat file tutorials on the web but can't find the correct way to do this.
Greg
View 1 Replies
View Related
May 24, 2007
i am creating a log file, and at 1 point I record how many rows are deleted after an update process.. I want to also record which productID are actually deleted... how would I write that?!
Basically I need know how I would get the list, and I am using PRINT command... I need to somehow write a loop that works through the list and PRINTS to the file....
Thanks in advance
View 1 Replies
View Related
May 28, 2008
ok can someone tell me why i get two different answers for the same query. (looking for last day of month for a given date)
SELECT DATEADD(ms, - 3, DATEADD(mm, DATEDIFF(m, 0, CAST('12/20/2006' AS datetime)) + 1, 0)) AS Expr1
FROM testsupplierSCNCR
I am getting the result of 01/01/2007
but in query analizer I get the result of
12/31/2006
Why the different dates
View 4 Replies
View Related
Sep 22, 2015
-- The 3rd query uses an incorrect column name in a sub-query and succeeds but rows are incorrectly qualified. This is very DANGEROUS!!!
-- The issue exists is in 2008 R2, 2012 and 2014 and is "By Design"
set nocount on
go
if object_id('tempdb.dbo.#t1') IS NOT NULL drop table #t1
if object_id('tempdb.dbo
[code]....
This succeeds when the invalid column name is a valid column name in the outer query. So in this situation the sub-query would fail when run by itself but succeed with an incorrectly applied filter when run as a sub-query. The danger here is that if a SQL Server user runs DML in a production database with such a sub-query which then the results are likely not the expected results with potentially unintended actions applied against the data. how many SQL Server users have had incorrectly applied DML or incorrect query results and don't even know it....?
View 2 Replies
View Related
Jul 30, 2015
For each customer, I want to add all of their telephone numbers to a different column. That is, multiple columns (depending on the number of telephone numbers) for each customer/row. How can I achieve that?
I want my output to be
CUSTOMER ID, FIRST NAME, LAST NAME, TEL1, TEL2, TEL3, ... etc
Each 'Tel' will relate to a one or more records in the PHONES table that is linked back to the customer.
I want to do it using SELECT. Is it possible?
View 13 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 2, 2012
linking two tables together to get an end result
find below the code i have used
The first part of the query provides me with the info i need
SELECT sub.*,
case when rm_sales_band = '2M to 4M' then 'Kirsty' else RM end as rm
into #rmtmp
[Code].....
View 1 Replies
View Related
Jul 10, 2015
I have a query that performs a comparison between 2 different databases and returns the results of the comparison. It returns 2 columns. The 1st column is the value of the object being compared, and the 2nd column is a number representing any discrepancies.What I would like to do is use the results from this 1st query in the where clause of another separate query so that this 2nd query will only run for any primary values from the 1st query where a secondary value in the 1st query is not equal to zero.I was thinking of using an "IN" function in the 2nd query to pull data from the 1st column in the 1st query where the 2nd column in the 1st query != 0, but I'm having trouble ironing out the correct syntax, and conceptualizing this optimally.
While I would prefer to only return values from the 1st query where the comparison value != 0 in order to have a concise list to work with, I am having difficulty in that the comparison value is a mathematical calculation of 2 different tables in 2 different databases, and so far I've been forced to include it in the select criteria because the where clause does not accept it.Also, I am not a DBA by trade. I am a system administrator writing SQL code for reporting data from an application I support.
View 6 Replies
View Related
Aug 22, 2005
Hi,New to .Net and SQL. I have two tables that I have joined together. RentalControl_Main has the rental informationd and an Adjuster ID that links to the ADjuster table and the adjusters name. I am trying to create a report that gives the "Single" adjuster name and the totals for all of their contracts. I have a details report that gives each contract info. for each specific adjusters rentals. However, I want to just list the adjuster once and give all of their totals. In my SQL statement I have all of it written out and just need to knowwhat to do in place of 'Alex Early' that will give me all of the distinct adjusters.Do I need to code this on the page with a do while loop?Appreciate any help.SELECT SUM(dbo.RentalControl_Main.Rate) / COUNT(dbo.RentalControl_Main.Rate) AS AmtAvg, SUM(dbo.RentalControl_Main.DaysBilled) / COUNT(dbo.RentalControl_Main.DaysBilled) AS DayAvg, SUM(dbo.RentalControl_Main.Rate * dbo.RentalControl_Main.DaysBilled) / COUNT(dbo.RentalControl_Main.Rate) AS TotAvgFROM dbo.RentalControl_Main INNER JOIN dbo.RentalControl_Adjuster ON dbo.RentalControl_Main.AdjusterID = dbo.RentalControl_Adjuster.AdjusterIDWHERE (dbo.RentalControl_Adjuster.AdjusterName = 'Alex Early' AND (dbo.RentalControl_Main.DateClose IS NOT NULL) AND (dbo.RentalControl_Main.AgencyID = '2')
View 1 Replies
View Related
Apr 20, 2004
I was wondering if there was a keyword that would allow you to return the number of results from a query such as (this is fake just giving an example so that someone can give me the real answer)
select TotalReturned
from table
where field=""
View 7 Replies
View Related
Oct 7, 1999
I am a beginner with SQL, so please excuse any ignorance here.
Under SQL 6.5, when executing a SELECT DISTINCT query against a table in a certain database, the results were returned alphabetized. Now, after migrating the database to SQL 7.0, the same query does not return the results alphabetized. If I add an index for the column specified in the query to the table, the results come back alphabetized. Is there any way to correct this other than adding indexes for all columns in all tables that are queried in this manner?
TIA,
Paige
View 2 Replies
View Related
Mar 2, 2005
I am running the same exact query in SQL 2000 and I get different results. Should I try running DBCC CHECKDB and look for errors?
View 5 Replies
View Related
Apr 30, 2008
Hi,
I have this line of code that when run in the query doesn't return anything:
srv.AGENT_S_ACC_CODE IN ('" & Replace(Replace(Parameters!arcode.Value," ",""),",","','") & "')
The parameter is setup and the query works when I hardcode it with a value but will not return anything with this, does anyone know why?
Thanks,
Rhonda
View 2 Replies
View Related
Oct 25, 2006
Hi guys, say i wrote a query that returns 1,000 records.. what kinda query could i write that only returns say the first 50 records of the 1,000 recs..
View 4 Replies
View Related
May 13, 2007
Hello,
I want to select all the customerIDs where an email address exists. Easy: select customerid from customers where emailaddress = @emailaddress
Now I want to use the resulted customerIDs from the above query as a parameter to select all the email addresses with emailstatus equal to 3.
How do I create this type of while statement in a stored procedure? I prefer to not create a temporary table.
My idea was to do it like this:
select emailaddress from emailaddresseswhere emailstatus = 3 and customerid = (select customerid from customers where emailaddress = @emailaddress)
This doesn't seems to work. I get the error:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
thanks
View 4 Replies
View Related
Jul 17, 2007
I have a pretty big SQL query with a one to many sort of relationship...
There are client accounts that we're reporting on. Each account has four different historical categories attached. Each historical category can have maybe fifty entries in each, sorted by date.
I need to figure out how to grab the unique accounts and show only the three most recent results per each historical subcategory with the account... the three most current results from each of the four subcategories, displayed by the parent client account number.
I've created four views to isolate the subcategories as a start, thinking I could bring them into a parent query ...
but my question would be... how do I generate JUST the top three historical transactions by account number? If I select TOP 3, I get only the 3 newest in the MASTER LIST, not per client account, which is what I need to do.
Does this make sense? I could really use a good SQL helping hand with this one.
Thank you!
View 12 Replies
View Related
Oct 17, 2007
Can anyone give me the basics of speeding up reports that use queries or views or nested views?
Current reports are now taking over 2 minutes to show.We have thousands and sometimes even millions of records to report against.Queries have 4 and 5 table joins etc.
We are using ASP.NET 2.0 in Visual Studio 2005 and Crystal Reports.
Thanks
View 4 Replies
View Related
Sep 1, 2004
Hi,
I would like to use the result table of the first query in a number of other queries. How do I do this ?
Thanks.
View 2 Replies
View Related
