Different Results Same Query Between Original And Copied Db
Hi all,
I restored a backup of a database running SQL Server in W2K to my own laptop (Windows XP) for report testing pourposes. The restore worked perfectly, but when I ran the store procedure that returns my "report" set I noticed that several of the fields within the result set are different, the number of rows and customers are a perfect match to the production report. The fields that are different are calculated fields that invoque a user defined function, which again are exactly the same on both databases. I tried dropping the stored procedure and the 4 functions and recreating them again but I get the same results, the number of rows, the customers and all "non" function calculated fields are perfect, only the fields calculated with the functions are wrong.
Has anybody seen this behavior?
Thanks for your help
Luis Torres
View Complete Forum Thread with Replies
Related Forum Messages:
Copied Database Maintains Original Name In Backups
I made a copy of a database "sac_prod" and named the new copy "vgs_prod". Now, when I do a backup of the new database, it still shows the name of the original. Is there any way to change this so it will be the same as the new database name?Here is the BACKUP script:BACKUPdatabase vgs_prod TODISK='\sac-srvr1data$TechnicalSharedProductionSQLBackup LasVegasvgs_prod_CopyOnly.BAK' with COPY_ONLYHere is the messages I received from this BACKUP:Processed 1752 pages for database 'vgs_prod', file 'sac_prod' on file 1.Processed 6 pages for database 'vgs_prod', file 'sac_prod_log' on file 1.BACKUP DATABASE successfully processed 1758 pages in 0.412 seconds (34.955 MB/sec). I would like to change the file 'sac_prod' to be 'vgs_prod' in lines 1 and 2 just above. Thanks,
View Replies !
SqlDataSource Returns Fewer Results Than Original Sql Query
I have a query that I created in SqlServer and then merely copied the code to a sqldatasource. They are both connected to the same db, however the sqldatasource query only returns 6 records and when run in SqlServer, it returns 52 rows which is the correct number. Any ideas on what might be causing this? There are no filters on the sqldatasource. It only has that one query. Do they differ in the way they execute sql code? Thanks for any help!
View Replies !
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 !
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 Replies !
Is It Possible To Prevent Databases From Being Copied?
Hi, We have a point of sale application (C# .NET 2.0) and a Sql Server 2005 database back end. Our customers are concerned that employees could create a backup of the SQL Server database (or even of the MDF file) and use it to steel customer data. Very often, the application is running on a single PC in a shop using Sql Server Express Edition 2005 under Windows XP. The users usually log on as local administrator. It's hard for us to force our customers to change their local security policies. Ideally, I would like some form of security mechanism that prevents a backup from being restored on to another PC without either a password or some other form of authentication. Is this possible? Regards, Sigol.
View Replies !
Attach Db File Copied Previously
I got a mdf file from my co-workerwho just stopped SQL server and delete ldf fileand give me a mdf file.if it was detached, it would work finebut it was just copied.Is there any way to attach this mdf file?I tried sp_attach_db and sp_attach_single_file_dband both failed.thanks,
View Replies !
Replication SPs Copied W/CRLF In Names
Hi folks - New DBA (longtime developer) here. I've just noticed something strange on a couple of my databases (SQL 2k). There are some stored procs that have a CRLF or CR+CRLF prefixing the name (they show up as unprintable-char black squares in EM). They all appear to be replication-related SPs (e.g. ). The names are as follows: sp_MSdel_<replicated_table_name> sp_MSins_<replicated_table_name> sp_MSupd_<replicated_table_name> There are corresponding normally-named SPs. Not all the replication-related SPs have been copied this way. The create date of all the new SPs is the same, and is (IIRC, I wasn't involved then...) the date of the SP4 application to the server. Anyone ever seen this? More importantly, is there a way to determine if these SPs are being used? I'd like to delete them if I can, but not until I'm sure of what's happened. TIA for any input.
View Replies !
Char Varchar Not Copied While Transferring
Hi, I have the following problem. I am using DTS connecting SQL SERVER 2005 and Sybase9. I successfully copy from Sybase9 to SQL SERVER 2005. Now when i am copying from SQL SERVER 2005 to Sybase9 char and varchar types are not copied. The data types are the same as well as data type length. Integers, numeric and date values are copied. I have tried data conversion but that did not help. sql server 2005 collation is Greek_CI_AI and Sybase9 collection is 1253ELL Any solution how to solve loosing char and varchar types when copying from SQL SERVER 2005 to Sybase9 ? Note: I connected to Sybase using System DSN Regards, Vasilis
View Replies !
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 !
One Table Is Ultra Slow At Being Copied, Others Are Fine
This is a really really odd problem. Here's the situation. I've got a DB with several tables. Let's pick out two of those tables from the bunch.. they both have approx 2500 rows in them. I take one and duplicate it (right click, All Tasks->Export Data, copy tables, etc) and it duplicates just fine into another DB. Now, the second table.. takes FOREVER. Yet it has the same amount of rows in it! And this is a flat table export.. I'm not including dependent objects or anything like that. Not to mention this is slow, but when I use Red-Gate SQL Data Compare, it gets stuck when it hits this specific table. Anyone have any idea what would cause this?
View Replies !
SSIS Error With Duplicate IDs Of Copied Packages
Hi, I recently encountered an error when I created several copies of one package. It's always nearly the same package with small modifications. I call this packages from a parent package which is part of our datawarehouseing-framework. The problem is, when copying a packages or using a packages as template the packages' IDs and Task's-IDs are the same. And this isn't only an issue concerning logging!! : When the parent package calls one of the copied packages the first task is executed in every package parallely. Furthermore ... when I for example set a breakpoint on a data transformation task in one of the packages, the breakpoint is set in all packages on the same task! This is resulting in strange errors because the tasks-states and variable values seem to get mixed up. Unfortunately there is only a possibility to change the package's ID, but the IDs of tasks are readonly! One solution is, to create a new package and copy all the tasks to the new package which creates new IDs, but doing so, I have to manually recreate a long list of variables, all the configurations, all the connection-managers once again. Furthermore I loose the layout of tasks. I found some posts about it here http://groups.google.de/group/microsoft.public.sqlserver.dts/browse_thread/thread/6f85a31ea190608a/0eae312aa8440cf8?lnk=gst&q=pitfall&rnum=1&hl=de#0eae312aa8440cf8 or http://groups.google.de/group/microsoft.public.sqlserver.dts/browse_thread/thread/760093d58bf6ccb5/32ced2f2020ef3f7?lnk=st&q=data+flow+task+id+copy&rnum=2&hl=de#32ced2f2020ef3f7 saying the issue will be fixed by SP2, but now I don't see any comment on it in the CTP of Service Pack 2. Is there any solution to this problem or official roadmap about a fix from Microsoft?? Greetings Monte
View Replies !
Backup Files In Append Mode To Be Copied.
Hi, I have a database on sqlserver 2005.I usually take a full db backup and sequential transaction log backups and append each of the backups it to a backup file.Now if i want to copy my second transaction log file to a specific folder on my server.Do we have any procedure to do it. Regards Arvind L
View Replies !
Merge Replication Issue With Records Not Being Copied To Subscriber
I have an issue with merge replication between sql2005 sp2 and spl2000 sp4 the merge replication runs fine to 6 other sql servers but the sql2000 sp4 server has a lot of locking going on, but the merge replication doesnt fail it just loses several hundred records without notifying me I have to manually copy the missing records every week or so I have dropped the replication and rebuilt it twice but it still happens
View Replies !
Report Security - Parent Folder Not Copied To Child
Hi Guys, In the reproting services web page I have inserted different users who can run reports. I have home page and sub folders for each company department. only problem is when I add a new user I have to add him/her to all the sub folders one by one (although the user is inserted in the parent folder as well) What am I doing wrong? Cheers Sonny
View Replies !
Identity Field Settings Not Copied By Import Wizard (2005)
It appears that when you use the import/export wizard from within Microsoft SQL Server Managment Studio, the identity attributes of the table being copied are not transferred. For example, say the source table has a column [ref] [int] IDENTITY ( 1 , 1 ) NOT NULL, When the import wizard is done the destination table will have a column named ref, but will not be an identity column. The column definition will be [ref] [int] NOT NULL, instead. Is there a way to change this behavior somewhere in the gui? When doing the import, the only options seems to be 'Enable Identity Insert', but checking this does not affect the definition of the column. -Eric
View Replies !
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 !
ORIGINAL SIN IN SQL 2005!
from the December 2004 _Redmond Magazine_, "Finding Gold in Yukon":http://redmondmag.com/columns/artic...ditorialsID=838"Developers will also pick up the usual new-version enhancements to T-SQL,including error handling, recursive queries, a ROW_NUMBER function, a PIVOTrelational operator and more."ROW_NUMBER???? What possible purpose could that have???
View Replies !
Decrypted Value Is Different From The Original One
Hi, I got an issue with encrypt and decrypt. I encrypted a data, say 'ok', then I decrypt the encrypted data, but what I got is not 'ok'. but if I encrypt a column, then after decryption, those data asr same with the original data in that column. In my case, ... create table mytable ( column Password varbinay(128) ) ... create symmetric key with certificate ... OPEN SYMMETRIC KEY Sym_Key_01 DECRYPTION BY CERTIFICATE Cert; UPDATE mytable SET Password = EncryptByKey(Key_GUID('Password_01'),'ok') select CONVERT(nvarchar, DecryptByKey(Password)) AS "Decrypted Password" from mytable here, I didn't get the value 'ok' but a another wierd word (like a chinese word). does someone know the reason? Thanks, Jone
View Replies !
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 !
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 !
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 !
@original And Stored Procedures
When using @original_{0}, how does this work if you are using a stored procedure instead of inline SQL? For example, if my update parameters are like this : <UpdateParameters> <asp:Parameter Type="String" Name="ProductType" /> <asp:Parameter Type="String" Name="OpportunityType" /> <asp:Parameter Type="Double" Name="Value" /> <asp:Parameter Type="String" Name="Probability" /> <asp:Parameter Type="Int32" Name="OpportunityID" /> </UpdateParameters> And my stored procedure like this : CREATE procedure dbo.UpdateOpportunity ( @ProductType int, @OpportunityType int, @Value money, @Probability int, @OpportunityID int) as update Opportunity set ProductType = @ProductType, OpportunityType = @OpportunityType,[Value] = @Value, Probability = @Probabilitywhere OpportunityID = @OpportunityID Do I need to change both cases of @OpportunityID in my stored procedure to @original_OpportunityID for it to work?
View Replies !
Fuzzy Grouping Using Original Key
I managed to get fuzzy grouping working. The relevant output (_key_in and _key_out) are stored in a new table that is a copy of the old table + fuzzy grouping columns. How do i get SSIS to store the _key_in and _key_out in the original table? The new matching column _key_out refers to the new key: _key_in. How could i get SSIS translate that to a matching column that refers to my original key?
View Replies !
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 !
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 !
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 !
Restrict Query Results
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 Replies !
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 !
Using Results From One Query As Criteria For Next
I have a database with some over normalized tables in it. The best I can do with one query is get the file ID. In the second query I want to get all the file names, based on all the fileID's I got from the first query. How would I go about doing this?
View Replies !
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 !
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 !
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 !
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 !
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 !
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 !
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 !
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 !
|
TRACKER
