Query Diff Results From Ent Manager Query And Query Analizer
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 Complete Forum Thread with Replies
Sponsored Links:
Related Messages:
Prevent Query-Analizer From Caching The Query Execution?
Hi all I'm using Sql server 2000 and sometimes i need to run my Queries in Query analizer before using them in my application just to test them...BUT most of the time when i run a query in query-analizer for second time ,query analizer populates the result (records) more quicker then the first time. Apparently it caches the query !!! i don't know but for some reasons i dont't want this , so how can i prevent Query-analizer from doing so? Thanks in advance. Regards.
View Replies !
View Related
I've Been Given A Query Analizer For MSDE Are There Others
Greetings, I've been sent an alternative "Query Analizer" thingy to play with and it seems to do the job - but it ain't my area. I've posted the tool in a free public place for a short time: http://www.adoanywhere.com/members/yeohray/7C1_SQLTool.zip But, for the benefit of people reding this from the archives, please follow the registerable author link [ http://81.130.213.94/myforum/forum_posts.asp?TID=78&PN=1&TPN=1 ] Apart from needing documentation (we all know about that old scenario) it seems good. It didn't waste my time (and I wouldn't waste yours I hope). Could I have your comments please ? Also, I'm looking for other tools that do the same as this one - so I can compare functionality. Even without try the freebie, any links please ? TIA
View Replies !
View Related
If, For, Loop,... In Sql Query Analizer
HI I am quite new in query building so I would like to know where can I find some useful information about if, for,.. clauses to use in query analizer. I would like to set a variable for date(datetime) and use it in if, for,... clause to get results for each day in selected month.For example: SELECT Uporabnik AS Expr1, MIN(Ura) AS Expr3, MAX(Ura) AS Expr5, COUNT(*) AS Expr4, Datum AS Expr2 FROM BatchIndeksNEW GROUP BY Uporabnik, Datum HAVING (Datum = CONVERT(DATETIME, '2001-11-30 00:00:00', 102)) ORDER BY COUNT(*) DESC thank's Tomaz
View Replies !
View Related
Easy SQL Question. How To Display Query Results In Query Analyzer
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 Replies !
View Related
Opening Up Odbc Data Source In The Query Query Inside Of The Server Manager
I'm trying to find the command to open up an odbc conection inside sql2005 express. I only have ues of an odbc connector, we're conection to remedy. We will eventually be using stored procedures to extract the data we need from remedy and doing additional data crunching. I'm a foxpro programmer so once I get the correct syntax for making the odbc connector I shold be ok. Also I need a really good advanced book on sql2005. The type of book that would have my odbc answer. I've spent all morning trying to find this information and was unable to. Thanks in advance Daniel Buchanan. If this was the wrong forum to post this on, please move this question to the correct one. I need this answer soon.
View Replies !
View Related
Report Wizard Doesn't Take A Simple Query In The Query String - Query Works In Query Builder When I Supply The Parameters
The following query in the query string: execute p_rpt_cli_v_index_reg_adj_exp_by_bkt2 @as_of_date='06/06/2007', @client_type=3, @index_as_of_date='05/31/2007' produces following error: There is an error in the query, invalid object name '#CLI_1', Invalid object name '#index'. When I open up the query Builder, and provide the same query and run, it asks for those 3 parameters values and after I provide those parameters, the query runs, but, clicking on Ok, produces same error as above in the 'Microsoft Report Designer' information window. The above query works in the Query Analyzer fine. What's so different in the Reporting Services env?
View Replies !
View Related
Query Designer Encountered A Query Error Via Enterprise Manager
I am trying to run queries against any of the user tables in my MS SQL 7.0 database. I get a message the Query Designer encountered a query error. We have tried rebooting the SQL Server and I am still getting these messages. Also, the SQL error logs look fine - all database maintenance are running successfully including the DBCCs which show no errors. Any help would be greatly appreciated as we are to go into production in a few days.
View Replies !
View Related
Query A Query With Multiple Results
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 Replies !
View Related
.net Security Assembly Returns Results Of An MDX Query To A Report Viewed In Report Manager - EnvironmentPermissions Error
Hi, I've got a Security assembly implemented in .net 2.0, which returns the results of an MDX query to a report viewed in Report Manager. This is the error: Request for the permission of type 'System.Security.Permissions.EnvironmentPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed If I cut out the code that asserts the need for unlimited FileIOPermissions, then the error changes to: Unable to retrieve security descriptor for this frame So I can't get away with just asserting less permissions. I assume that this is something to do with using MDX in this way. Clearly the permissions on the assembly are inadequate, so I tried updating the rssrvpolicy.config file: <configuration> <mscorlib> <security> <policy> <PolicyLevel version="1"> <SecurityClasses> <SecurityClass Name="AllMembershipCondition" Description="System.Security.Policy.AllMembershipCondition, mscorlib, Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"/> <SecurityClass Name="AspNetHostingPermission" Description="System.Web.AspNetHostingPermission, System, Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"/> <SecurityClass Name="DnsPermission" Description="System.Net.DnsPermission, System, Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"/> <SecurityClass Name="EnvironmentPermission" Description="System.Security.Permissions.EnvironmentPermission, mscorlib, Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"/> <SecurityClass Name="FileIOPermission" Description="System.Security.Permissions.FileIOPermission, mscorlib, Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"/> <SecurityClass Name="FirstMatchCodeGroup" Description="System.Security.Policy.FirstMatchCodeGroup, mscorlib, Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"/> <SecurityClass Name="IsolatedStorageFilePermission" Description="System.Security.Permissions.IsolatedStorageFilePermission, mscorlib, Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"/> <SecurityClass Name="NamedPermissionSet" Description="System.Security.NamedPermissionSet"/> <SecurityClass Name="PrintingPermission" Description="System.Drawing.Printing.PrintingPermission, System.Drawing, Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"/> <SecurityClass Name="ReflectionPermission" Description="System.Security.Permissions.ReflectionPermission, mscorlib, Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"/> <SecurityClass Name="RegistryPermission" Description="System.Security.Permissions.RegistryPermission, mscorlib, Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"/> <SecurityClass Name="SecurityPermission" Description="System.Security.Permissions.SecurityPermission, mscorlib, Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"/> <SecurityClass Name="SocketPermission" Description="System.Net.SocketPermission, System, Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"/> <SecurityClass Name="SqlClientPermission" Description="System.Data.SqlClient.SqlClientPermission, System.Data, Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"/> <SecurityClass Name="StrongNameMembershipCondition" Description="System.Security.Policy.StrongNameMembershipCondition, mscorlib, Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"/> <SecurityClass Name="UnionCodeGroup" Description="System.Security.Policy.UnionCodeGroup, mscorlib, Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"/> <SecurityClass Name="UrlMembershipCondition" Description="System.Security.Policy.UrlMembershipCondition, mscorlib, Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"/> <SecurityClass Name="WebPermission" Description="System.Net.WebPermission, System, Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"/> <SecurityClass Name="ZoneMembershipCondition" Description="System.Security.Policy.ZoneMembershipCondition, mscorlib, Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"/> </SecurityClasses> <NamedPermissionSets> <PermissionSet class="NamedPermissionSet" version="1" Unrestricted="true" Name="FullTrust" Description="Allows full access to all resources" /> <PermissionSet class="NamedPermissionSet" version="1" Name="Nothing" Description="Denies all resources, including the right to execute" /> <PermissionSet class="NamedPermissionSet" version="1" Name="Execution"> <IPermission class="SecurityPermission" version="1" Flags="Execution" /> </PermissionSet> <PermissionSet class="DirectoryServicesPermissionSet" version="1" Name="DSPermissionSet" Description="A special permission set that grants browse access to DirectoryServices"> <IPermission class="DirectoryServicesPermission" version="1" Browse=""/> <IPermission class="SecurityPermission" version="1" Flags="Assertion, Execution"/> </PermissionSet> </NamedPermissionSets> <CodeGroup class="FirstMatchCodeGroup" version="1" PermissionSetName="Nothing"> <IMembershipCondition class="AllMembershipCondition" version="1" /> <CodeGroup class="UnionCodeGroup" version="1" PermissionSetName="Execution" Name="Report_Expressions_Default_Permissions" Description="This code group grants default permissions for code in report expressions and Code element. "> <IMembershipCondition class="StrongNameMembershipCondition" version="1" PublicKeyBlob="0024000004800000940000000602000000240000525341310004000001000100512C8E872E28569E733BCB123794DAB55111A0570B3B3D4DE3794153DEA5EFB7C3FEA9F2D8236CFF320C4FD0EAD5F677880BF6C181F296C751C5F6E65B04D3834C02F792FEE0FE452915D44AFE74A0C27E0D8E4B8D04EC52A8E281E01FF47E7D694E6C7275A09AFCBFD8CC82705A06B20FD6EF61EBBA6873E29C8C0F2CAEDDA2" /> </CodeGroup> <CodeGroup class="FirstMatchCodeGroup" version="1" PermissionSetName="Execution" Description="This code group grants MyComputer code Execution permission. "> <IMembershipCondition class="ZoneMembershipCondition" version="1" Zone="MyComputer" /> <CodeGroup class="UnionCodeGroup" version="1" PermissionSetName="FullTrust" Name="Microsoft_Strong_Name" Description="This code group grants code signed with the Microsoft strong name full trust. "> <IMembershipCondition class="StrongNameMembershipCondition" version="1" PublicKeyBlob="002400000480000094000000060200000024000052534131000400000100010007D1FA57C4AED9F0A32E84AA0FAEFD0DE9E8FD6AEC8F87FB03766C834C99921EB23BE79AD9D5DCC1DD9AD236132102900B723CF980957FC4E177108FC607774F29E8320E92EA05ECE4E821C0A5EFE8F1645C4C0C93C1AB99285D622CAA652C1DFAD63D745D6F2DE5F17E5EAF0FC4963D261C8A12436518206DC093344D5AD293" /> </CodeGroup> <CodeGroup class="UnionCodeGroup" version="1" PermissionSetName="FullTrust" Name="Ecma_Strong_Name" Description="This code group grants code signed with the ECMA strong name full trust. "> <IMembershipCondition class="StrongNameMembershipCondition" version="1" PublicKeyBlob="00000000000000000400000000000000" /> </CodeGroup> <CodeGroup class="UnionCodeGroup" version="1" PermissionSetName="FullTrust" Name="Report_Server_Strong_Name" Description="This code group grants Report Server code full trust. "> <IMembershipCondition class="StrongNameMembershipCondition" version="1" PublicKeyBlob="0024000004800000940000000602000000240000525341310004000001000100272736AD6E5F9586BAC2D531EABC3ACC666C2F8EC879FA94F8F7B0327D2FF2ED523448F83C3D5C5DD2DFC7BC99C5286B2C125117BF5CBE242B9D41750732B2BDFFE649C6EFB8E5526D526FDD130095ECDB7BF210809C6CDAD8824FAA9AC0310AC3CBA2AA0523567B2DFA7FE250B30FACBD62D4EC99B94AC47C7D3B28F1F6E4C8" /> </CodeGroup> <CodeGroup class="UnionCodeGroup" version="1" PermissionSetName="FullTrust"> <IMembershipCondition class="UrlMembershipCondition" version="1" Url="$CodeGen$/*" /> </CodeGroup> <CodeGroup class="UnionCodeGroup" version="1" PermissionSetName="FullTrust" Name="SharePoint_Server_Strong_Name" Description="This code group grants SharePoint Server code full trust. "> <IMembershipCondition class="StrongNameMembershipCondition" version="1" PublicKeyBlob="0024000004800000940000000602000000240000525341310004000001000100AFD4A0E7724151D5DD52CB23A30DED7C0091CC01CFE94B2BCD85B3F4EEE3C4D8F6417BFF763763A996D6B2DFC1E7C29BCFB8299779DF8785CDE2C168CEEE480E570725F2468E782A9C2401302CF6DC17E119118ED2011937BAE9698357AD21E8B6DFB40475D16E87EB03C744A5D32899A0DBC596A6B2CFA1E509BE5FBD09FACF" /> </CodeGroup> <CodeGroup class="UnionCodeGroup" version="1" PermissionSetName="FullTrust" Name="MyCompany.MIS.Security" Description="A special code group for my custom assembly."> <IMembershipCondition class="StrongNameMembershipCondition" version="1" PublicKeyBlob="00240000048000009400000006020000002400005253413100040000010001003F099786BD0BDCD36FF941A6EC9E6612103F15D0D34DF124FF4792C237EF88E88C23C3BAD09A57FAD1C3364003C4C27E7EFA206520286982563134566247AFCFFFDCA1B91B9AA0EBAEA81F20A3BB6769037D882DFB38F9D7FFE2F19B8975C18CED382BC2BB911C7D4B8484B39EE3AC651530A1DDD21DCE50A24EEED613CF8DBA" Name="MyCompany.MIS.Security" AssemblyVersion="3.0.0.4"/> </CodeGroup> </CodeGroup> </PolicyLevel> </policy> </security> </mscorlib> </configuration> I've added 'Full Trust' to my specific assembly. The new clause there was generated using caspol.exe so I think it should be correct. However, this made no difference at all to the error! However, if I upgrade Report_Expressions_Default_Permissions (see above) to Full Trust, the problem is 'solved'. Unfortunately giving Full Trust to any and all Report Expressions is not an option. Can anyone help? Dr. Bunsen
View Replies !
View Related
Query Performance Diff
Hi @all, I designed a query in report designer, data tab. This query runs 24 secs, when I execute it from the data tab in report designer. Publishing the report and running it from the report server. The query needs 40x the time as in report designer. Looking at the Execution Log, the report needs 800 secs for TimeDataRetrieval. Why is it so much slower to run the query from report server? The report designer has also to retrieve the data, and runs so much faster. Thanks for any hints T
View Replies !
View Related
[help] Possible To Script A Diff Into SQL Query?
My goal is to add a diff into a query that grabs data from 2 different tables. The code: SELECT MIN(TableName) as TableName, ID1, COL1, COL2, COL3, COL4, COL5, COL6, COL7, COL8 ,COL9, COL10, COL11, COL12, COL13, COL14, COL15, --COL16, COL17, COL18, COL19, COL20, COL21 FROM ( SELECT 'Table A' as TableName, SessionID as ID1, StartDateCode as COL1, StartTimeCode as COL2, EndDateCode as COL3, EndTimeCode as COL4, HandledByCode as COL5, DispositionCode as COL6, DNISCode as COL7, CallServiceQueueCode as COL8, ApplicationCode as COL9, IVREndPointCode as COL10, BankCode as COL11, TotalQueueTimeSS as COL12, TotalAgentTalkTimeSS as COL13, TotalAgentHoldTimeSS as COL14, TotalAgentHandleTimeSS as COL15, --TotalIVRTimeSS as COL16, AfterHoursFlag as COL17, SourceSystemID as COL18, anubisTransferExtNumber as COL19, anubisEndPoint as COL20, AccountNumber as COL21 from [pdx0sql45].Rubicon_Marts.dbo.INB_Call_Fact where startdatecode between 2738 and 2769 UNION all SELECT 'Table B' as TableName, SessionID as ID1, StartDateCode as COL1, StartTimeCode as COL2, EndDateCode as COL3, EndTimeCode as COL4, HandledByCode as COL5, DispositionCode as COL6, DNISCode as COL7, CallServiceQueueCode as COL8, ApplicationCode as COL9, IVREndPointCode as COL10, BankCode as COL11, TotalQueueTimeSS as COL12, TotalAgentTalkTimeSS as COL13, TotalAgentHoldTimeSS as COL14, TotalAgentHandleTimeSS as COL15, --TotalIVRTimeSS as COL16, AfterHoursFlag as COL17, SourceSystemID as COL18, anubisTransferExtNumber as COL19, anubisEndPoint as COL20, AccountNumber as COL21 from pdx0sql04.Rubicon_Marts.dbo.INB_Call_Fact where startdatecode between 2738 and 2769 ) tmp GROUP BY ID1, COL1, COL2, COL3, COL4, COL5, COL6, COL7, COL8 ,COL9, COL10, COL11, COL12, COL13, COL14, COL15, --COL16, COL17, COL18, COL19, COL20, COL21 HAVING COUNT(*) = 1 ORDER BY 2,1 --------- Is it possible to add a command into the query to output diff/compare scenario? Thanks in advance for any help.
View Replies !
View Related
Datetime Diff Query Syntax
Hi.I'm trying but not getting correct results.I have two tablesone with app, msg, time(varchar,datetime,varchar)app1 start 2006-04-03 13:33:36.000app1 stuff 2006-04-03 13:33:36.000app1 end 2006-04-03 13:33:36.000app1 start 2006-04-03 13:33:36.000app2 start 2006-04-03 13:33:36.000app2 stuff 2006-04-03 13:33:36.000app2 end 2006-04-03 13:33:36.000app2 start 2006-04-03 13:33:36.000app3 start 2006-04-03 13:33:36.000app2 end 2006-04-03 13:33:36.000app2 start 2006-04-03 13:33:36.000app2 end 2006-04-03 13:33:36.000app2 start 2006-04-03 13:33:36.000app2 end 2006-04-03 13:33:36.000app3 end 2006-04-03 13:33:36.000app1 end 2006-04-03 13:33:36.000and another with dr watson crash info(varchar, datetime)app1 2006-04-03 13:33:36.000app2 2006-04-03 13:33:36.000app1 2006-04-03 13:33:36.000app1 2006-04-03 13:33:36.000app3 2006-04-03 13:33:36.000I'm trying to make a query that will allowme to see what entries in the first tableoccurred wtihin, say, a minute, or maybe 40seconds of any of the entries in the secondtable.I want all the entries in the second table tobe present, so I know it has to be some sortof join, probably an outer join.my syntax is giving me bad results, probablybecause I'm just out of practice.can someone tell me how to put a query togetherso I see the data I'm looking for?ThanksJeffJeff Kish
View Replies !
View Related
Diff. Performance In Query Analyzer Than When Using Stored Procedure
Hi group,I have a select statement that if run against a 1 million recorddatabase directly in query analyzer takes less than 1 second.However, if I execute the select statement in a stored procedureinstead, calling the stored proc from query analyzer, then it takes12-17 seconds.Here is what I execute in Query Analyzer when bypassing the storedprocedure:USE VerizonGODECLARE @phonenumber varchar(15)SELECT @phonenumber = '6317898493'SELECT Source_Identifier,BADD_Sequence_Number,Record_Type,BAID ,Social_Security_Number ,Billing_Name,Billing_Address_1,Billing_Address_2,Billing_Address_3,Billing_Address_4,Service_Connection_Date,Disconnect_Date,Date_Final_Bill,Behavior_Score,Account_Group,Diconnect_Reason,Treatment_History,Perm_Temp,Balance_Due,Regulated_Balance_Due,Toll_Balance_Due,Deregulated_Balance_Due,Directory_Balance_Due,Other_Category_BalanceFROM BadDebtWHERE (Telephone_Number = @phonenumber) OR (Telephone_Number_Redef =@phonenumber)order by Service_Connection_Date descRETURNGOHere is what I execute in Query Analyzer when calling the storedprocedure:DECLARE @phonenumber varchar(15)SELECT @phonenumber = '6317898493'EXEC Verizon.dbo.baddebt_phonelookup @phonenumberHere is the script that created the stored procedure itself:CREATE PROCEDURE dbo.baddebt_phonelookup @phonenumber varchar(15)ASSELECT Source_Identifier,BADD_Sequence_Number,Record_Type,BAID ,Social_Security_Number ,Billing_Name,Billing_Address_1,Billing_Address_2,Billing_Address_3,Billing_Address_4,Service_Connection_Date,Disconnect_Date,Date_Final_Bill,Behavior_Score,Account_Group,Diconnect_Reason,Treatment_History,Perm_Temp,Balance_Due,Regulated_Balance_Due,Toll_Balance_Due,Deregulated_Balance_Due,Directory_Balance_Due,Other_Category_BalanceFROM BadDebtWHERE (Telephone_Number = @phonenumber) OR (Telephone_Number_Redef =@phonenumber)order by Service_Connection_Date descRETURNGOUsing SQL Profiler, I also have the execution trees for each of thesetwo different ways of running the same query.Here is the Execution tree when running the whole query in theanalyzer, bypassing the stored procedure:--------------------------------------Sort(ORDER BY:([BadDebt].[Service_Connection_Date] DESC))|--Bookmark Lookup(BOOKMARK:([Bmk1000]),OBJECT:([Verizon].[dbo].[BadDebt]))|--Sort(DISTINCT ORDER BY:([Bmk1000] ASC))|--Concatenation|--IndexSeek(OBJECT:([Verizon].[dbo].[BadDebt].[Telephone_Index]),SEEK:([BadDebt].[Telephone_Number]=[@phonenumber]) ORDERED FORWARD)|--IndexSeek(OBJECT:([Verizon].[dbo].[BadDebt].[Telephone_Redef_Index]),SEEK:([BadDebt].[Telephone_Number_Redef]=[@phonenumber]) ORDEREDFORWARD)--------------------------------------Finally, here is the execution tree when calling the stored procedure:--------------------------------------Sort(ORDER BY:([BadDebt].[Service_Connection_Date] DESC))|--Filter(WHERE:([BadDebt].[Telephone_Number]=[@phonenumber] OR[BadDebt].[Telephone_Number_Redef]=[@phonenumber]))|--Compute Scalar(DEFINE:([BadDebt].[Telephone_Number_Redef]=substring(Convert([BadDebt].[Telephone_Number]),1, 10)))|--Table Scan(OBJECT:([Verizon].[dbo].[BadDebt]))--------------------------------------Thanks for any help on my path to optimizing this query for ourproduction environment.Regards,Warren WrightScorex Development Team
View Replies !
View Related
Return The Results Of A Select Query In A Column Of Another Select Query.
Not sure if this is possible, but maybe. I have a table that contains a bunch of logs. I'm doing something like SELECT * FROM LOGS. The primary key in this table is LogID. I have another table that contains error messages. Each LogID could have multiple error messages associated with it. To get the error messages. When I perform my first select query listed above, I would like one of the columns to be populated with ALL the error messages for that particular LogID (SELECT * FROM ERRORS WHERE LogID = MyLogID). Any thoughts as to how I could accomplish such a daring feat?
View Replies !
View Related
Loading Data From Same Table (oracle) In To Diff SQL Tables With In Same Package(same Query)
Hi, I have Table A . we already have 80 columns . we have to add 65 more columns. we are populating this table from oracle .and we need to populate those 65 columns again from the same table. Is it a better idea to add those new 65 columns to the same table or new table. If we go for the same table then loading time will be double, If I go for new table and If i am able to run both the packages which loads table data from same oracle server to difffrent sql tables then we should be good. But if we run in to temp space issues on oracle server . Then i have to load the two tables separately which consumes the same time as earlier one. I was thinking if there is a way in SSIS where I can pull data from same oracle table in to two diff sql tables at same time?
View Replies !
View Related
Need To Display Results Of A Query, Then Use A Drop Down List To Filter The Results.
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 Replies !
View Related
Results Of One Query Used In A Second Query
I don't know the correct approach to this problem. Nested Queries perhaps? I have users enter stock symbols into a textbox. The symbols are then written in a table that has 3 columns named UserID, Portfolio_Name and Portfolio_Symbols. In order to look the symbols up (Portfolio_Symbols), I search by UserID and Portfolio_Name; this returns a string consisting of the symbols. What I need, however are the results of (another) query based on these symbols(Portfolio_Symbols)--the result would be various pieces of data for each company (symbol)being returned to a dataset that will be read into a datagrid. Do nested queries seem a good approach, or is there some other way that would be better? I've never written a nested query before, that's why I ask this question. Thanks
View Replies !
View Related
Using Results Of Query In A Query
Hello all,I'm looking for some hints as to how to use the results of a query inanother query. I assume I'm 'thinking' wrong in how to solve this, soI'm hoping someone can clobber me and send me in the right direction.My question is best illustrated by simplified example. I have onetable (table A) with a single column called "Word". This tablecontains 100 records of single words. Another table (table B)contains whole sentences in a single column. I want to eventuallycount the occurances of all of the words in Table A, in each of thesentences in Table B and store these results in another table. Here'sthe 'thinking wrong' part. The only way I can see to do this is toselect the entire table A using a cursor, then select 'LIKE's of theresult against each individual row in table B. This seems horriblyinefficient, and I know there have to be one or two MUCH better waysto do this, but I can't get me head wrapped around thinking in SQL atthis stage. Any info appreciated.Thanks,Stan
View Replies !
View Related
Query Runs Fine In Query Analyser But Not The Query Debugger
I'm running a query, actually its an insert that works when using the TSQL below. However when I try to use the debugger to step through and using the exact same values as those below I get the following error: [Microsoft][ODBC SQL Server Driver]Invalid character value for cast specification Its Killing me because everything else works, but this. Can somebody help. DECLARE @NoteID INT,-- NULL OUTPUT, @Note_Description NVARCHAR(3000),-- = NULL, @Date DateTime,-- = NULL OUTPUT, @ByWho NVARCHAR(30),-- = NULL, @FK_Action_Performed NVARCHAR(40),-- = NULL, @FK_UserID INT,-- = NULL, @FK_JobID INT,-- = NULL, @Job_Date DateTime,-- = NULL, @Start DateTime,-- = NULL, @Finish DateTime,-- = NULL, @BeenRead NVARCHAR(10),-- = NULL @FK_UserIDList NVARCHAR(4000)-- = NULL --SET @NoteID = 409 --NULL OUTPUT, SET @Note_Description = 'Tetsing' --SET @Date DateTime = NULL OUTPUT, SET @ByWho = 'GeorgeAgaian' SET @FK_Action_Performed = 'Worked hard' SET @FK_UserID = 5 SET @FK_JobID = 29 SET @Job_Date = 28/01/03 SET @Start = '1:00:20 PM' SET @Finish = '1:00:20 PM' SET @BeenRead = 'UnRead' SET @FK_UserIDList = '1,2,3' --AS --SET NOCOUNT ON SET NOCOUNT ON SET XACT_ABORT ON BEGIN TRANSACTION SET @Date = GETDATE() -- Insert Values into the customer table INSERT Note (Note_Description, Date, ByWho, FK_Action_Performed, FK_UserID, FK_JobID, Job_Date, Start, Finish) SELECT --@NoteID, @Note_Description, @Date, @ByWho, @FK_Action_Performed, @FK_UserID, @FK_JobID, @Job_Date, @Start, @Finish -- Get the new Customer Identifier, return as OUTPUT param SELECT @NoteID = @@IDENTITY -- Insert new notes for all the users that the note pertains to, in this case this will be by the assigned -- users. IF @FK_UserIDList IS NOT NULL EXECUTE spInsertNotesByAssignedUsers @NoteID, @FK_UserIDList -- Insert New Address record -- Retrieve Address reference into @AddressId -- EXEC spInsertForUserNote -- @FK_UserID, --@NoteID, -- @BeenRead -- @Fax, -- @PKId, -- @AddressId OUTPUT COMMIT TRANSACTION -------------------------------------------------- GO
View Replies !
View Related
Select Query Based Upon Results Of Another Select Query??
Hi, not exactly too sure if this can be done but I have a need to run a query which will return a list of values from 1 column. Then I need to iterate this list to produce the resultset for return. This is implemented as a stored procedure declare @OwnerIdent varchar(7) set @OwnerIdent='A12345B' SELECT table1.val1 FROM table1 INNER JOIN table2 ON table1. Ident = table2.Ident WHERE table2.Ident = @OwnerIdent 'Now for each result of the above I need to run the below query SELECT Clients.Name , Clients.Address1 , Clients.BPhone, Clients.email FROM Clients INNER JOIN Growers ON Clients.ClientKey = Growers.ClientKey WHERE Growers.PIN = @newpin) '@newpin being the result from first query Any help appreciated
View Replies !
View Related
Sql Query Results #?
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 Replies !
View Related
Query Results
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 Replies !
View Related
No Query Results
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 Replies !
View Related
Help Grabbing Top Three Results From A SQL Query!
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 Replies !
View Related
Faster Query Results
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 Replies !
View Related
Use Results From First Query As Parameter In Second
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 Replies !
View Related
Different Results In Query Analyzer And .net
ok the following statement returns the correct results in sql query analyzer but in the .net environment with c# it returns back less 3 records SELECT SUM(ao.amount),bl.dpc,bl.city from billinglocation bl inner join shippinglocation sl on bl.billinglocationid = sl.billinglocationid inner join absorbentorder ao on sl.shippinglocationid = ao.shippinglocationid group by bl.dpc, bl.city billinglocation 1 - 1 shippinglocation 1 - many absorbentorder anybody have any ideas why this might be occuringthanks!
View Replies !
View Related
Executing A SP With Results From A Query
I am wondering if it's possible to do this:--Procedure Expects 5 parametersEXEC MyStoredProcedure SELECT P1, P2, P3, P4, P5 FROM someTable WHERE someValue = 1or--Procedure Expects 5 parameters, AnotherStoredProcedure returns 1 row of 5 columns)EXEC MyStoredProcedure (EXEC AnotherStoredProcedure 1)Is it possible to do this sort of work? Currently we're building all our parameters in the SP's, but something like this would speed things up.
View Replies !
View Related
Paged Results From SQL Query?
I have been searching this topic on and for quite some time and can't seem to find a decent answer. Is it feasible to do your paging strictly from a SQL query? Pass the query the pagesize, what page to return and what to sort by?
View Replies !
View Related
Paradoxical Query Results?
Hi -I am getting perplexing, seemingly inconsistent results from two simplequeries, and I was wondering if I'm doing something obviouslybrainless.The first query is:select colX, colY from(select distinct colX from tblX) x full outer join(select distinct colY from tblY) yon x.colX=y.colYorder by y.colYThis query returns 28109 rows, the first 38 of which are NULL in colY,but NOT NULL in colX. The second query is:select colX from tblX where colX not in (select colY from tblY)This query returns nothing, and that's the problem. Shouldn't thisquery return the 38 values that are in colX but not colY? Shouldn't thesubquery return the list of values in colY, which as the first querydemonstrates, is lacking exactly 38 values that appear in colX?Thanks for any help.
View Replies !
View Related
Printing Query Results From Sp
Is there any way to send the results of a query executed within a storedprocedure to the default windows printer, or even to a text file? (Ithinking of something similar to Oracle's DBMS_OUTPUT, or UTL_FILEcommands.)?I want the query to run unattended, but send the results directly to theprinter.Thanks
View Replies !
View Related
Tripling End Results Of A Query
I need to add expense types to various projects in our ERP database. Iperform this queryselect DISTINCT a.project_idfrom project as a, project_cost_element as bwhere a.project_id = b.project_idand a.project_status in ('T','A')and b.cost_element_id between '7012000' and '7012999'The result is projects that this query is affected.0001000020001620018700242I need to put this in a load file where for every 4 lines is the same project....I would like the end result000100001000010000100002000020000200002000162001620016200162etc--Message posted via http://www.sqlmonster.com
View Replies !
View Related
Conditional Query Results
Hello everybody,After several attempts of writing the query, I had to post myrequirement in the forum.Here is what I have, what I need and what I did.Table ACol1 Col21 Nm12 Nm23 Nm3Table BCol1 Col210 10020 200Table CCol1 (A.Col1) Col2 (B.Col1)1 102 10Table DCol1 (A.Col1) Col21 Value12 Value2I need results based on below criteria,1.Criteria - B.Col2 = 100ResultsetA.Col1 D.Col11 Value12 Value22.Criteria - B.Col2 =""A.Col1 D.Col11 Value12 Value23 NULL3.Criteria - B.Col2 =200Empty resultsetHere is the query I tried, but looks its not working. Probably there isa better way to do this.DDL and DML statements:create table #tab1 (a1 int, a2 nvarchar(20))create table #tab2 (b1 int, b2 int)create table #tab3 (c1 int, c2 int)create table #tab4 (d1 int, d2 nvarchar(20))insert into #tab1 values (1, 'nm1')insert into #tab1 values (2, 'nm2')insert into #tab1 values (3, 'nm3')insert into #tab2 values (10, 100)insert into #tab2 values (20, 200)insert into #tab3 values (1, 10)insert into #tab3 values (2, 10)insert into #tab4 values (1, 'value1')insert into #tab4 values (2, 'value2')selecta.a1, d.d2from #tab1 aleft join #tab3 bon a.a1 = b.c1left join #tab2 con b.c2 = c.b1left join #tab4 don a.a1 = d.d1wherec.b2 = [100 or 200 or ''] or exists (select 1 from #tab4 dwhere a.a1 = d.d1and c.b2 = [100 or 200 or ''] )The above query works well to give results for Criteria 1 and Criteria3, but doesn't return for '' (criteria 2). I couldn't manage crackingthe solution. I shall try once again, but meanwhile if anyone couldhelp me in this, that would be great.Thanks.
View Replies !
View Related
Help With Search Results Query
I need some help with a query. I have a table with inventory that Ineed to allow customer searches on. Based on their search criteria, apreference level is calculated; the higher the preference level, thehigher on the order on the search results.The hard part is when the results are supposed to be limited to amaximum number of stores and items. Let's say that they only want tosee 3 stores and a max of 5 items per store. What needs to bereturned is the 3 stores with the best Preference and the 5 best itemsat each store.Create Table Inventory( StoreId int, ItemId int, Preference int )
View Replies !
View Related
Saving Query Results
I am trying to find a way that I can save the query reuslts that show up in the results pane to a csv file, but with the field names included. I am able to save the results as a csv, but the field names do not included Does anyone know how I can accoplish this?
View Replies !
View Related
Possible Bug? Query Returns Different Results
SQL2000 sp3a If I run the following query I get a different value for isntgroup on different databases: select g.name, u.name,l.loginname,u.isntgroup,u.status from (select uid,name from sysusers where issqlrole = 1 and name != 'public') g left join sysmembers m on g.uid = m.groupuid left join sysusers u on u.uid = m.memberuid left join master..syslogins l on u.sid = l.sid order by 1, 2,3 i.e. I know that no one has db_accessadmin role, so I would expect a null value returned. Yet at times I get a 0. Status, which is used in the derivation of isntgroup, returns null. Is this a bug?
View Replies !
View Related
Different Query Results For Different Users?
I have an SQL2K5 database where a group of users share a custom database role and access the database via trusted connections (Windows authentication). I have a stored procedure which reports all of the users in this database role using an SQL statment something like this: select name from sysusers inner join sysmembers on sysmembers.memberuid=sysusers.uid and sysmembers.groupuid=7 As the programmer, and the dbo, when I log in to execute this query, I get the list of a dozen, or so, users. When one of the users executes the same query, they only see a return of their own username. I'd like to have the users be able to execute this query and get the full list of users. Can someone tell me how to accomplish this? I assume that this is some security, or permissions, thing involving system tables, or Windows authentication security. Thank you. BTW--the users never actually directly execute the stored procedure I mentioned; They are using a VB client application that affects the execution.
View Replies !
View Related
Query Results To A CSV File
HI Im running a query using isql from the command line and outputting the results to a file. Ideally I'd like to have the file in csv format so that it can be used by a client to import the results into their system. I can run a query and output the results to a file but get i dont get the csv format I require. I can separate the columns using /s parameter but I also additional space within the column fields. I know it is possible to create the csv format using the SQL query tool and ISQL_w but I was wondering if there was some parameter that I can call that will format the output like the format options in ISQL_w allows me to do. Thanks in advance
View Replies !
View Related
Query And Parsing Results
First, I'm a complete noob to anything other than basic SQL queries so any help is greatly appreciated. I have a project I'm working on that involves analyzing syslog messages from our web filter. It's been pretty simple in getting the data into a SQL table, but now I'm fumbling around with setting up the data analysis portion. The data I need to analyze is space delimited in a single column in the table. I can query the data, but I don't know how to parse the results and insert each field into it's own column in another table. Would one of the great SQL gurus around here help a brotha out? Thanks.
View Replies !
View Related
|
TRACKER
