Different Results Same Query Between Original And Copied Db
Sep 29, 2005
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.
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,
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!
However, as you can see, the original select query is run twice and joined together.What I was hoping for is this to be done in the original query without the need to duplicate the original query.
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!
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.
This program gets the values of A and B passed in. They are for table columns DXID and CODE. The textbox GET1 is initialized to B when the page is loaded. When I type another value in GET1 and try to save it, the original initialized value gets saved and not the new value I just typed in. A literal value, like "222" saves but the new GET1.TEXT doesn't.
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.
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,
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.
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
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.
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
At one of our client side a wired log shipping issues has come up.while monitoring those two server i noticed that although log-shipping report says both server are in sync, report displays information related to both backup and restore , it doesn't shows information related to copy that is when was last file copied and last file copy column is showed blank. Same is when i execute proc "sp_help_log_shipping_monitor" . I get same result ...
When i expand copy job history to analyse its what i found is although job has executed successfully , but in depth reading each steps says that no .trn file was copied .
My copy directory is at secondary server itself where .trn files are placed.And from this location itself files are begin restored.
SQL server and agent on both servers are run by same domain account ....
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.
I'm using the methods of the Microsoft.SqlServer.Management.Smo namespace within a .NET application to create a backup file from a SQLEXPRESS database. I can then restore the database from that backup device using methods in the same namespace. Here is a snippet from the restore code:
srv = New Server("MYPCSQLEXPRESS")
db = srv.Databases("washmaster")
Dim bdi As New BackupDeviceItem(BackupFileName, DeviceType.File)
Dim recoverymod As RecoveryModel
recoverymod = db.DatabaseOptions.RecoveryModel
rs.NoRecovery = False
rs.Devices.Add(bdi)
rs.Database = "washmaster"
rs.ReplaceDatabase = True
srv.KillAllProcesses("washmaster")
rs.SqlRestore(srv)
This works great as long as I used one of the backup files that I created directly on the disk. However, my application has a utility that allows the user to copy the backup files onto another drive, such as a CD or a thumb drive and when I try to restore from the copy of the backup, I get the following exception:
....Cannot open backup device..[filename]...Operating system error 5(Access is denied.)
The reason I get this error is that the "NETWORK SERVICE" account was removed from the file permissions when the file was copied.
How can I copy a backup to another drive and preserve the "NETWORK SERVICE" account? If I can't do that, is it wise to try to add the account back to the file before using it to restore or is there a better way?
I've a SSIS 2008 parent/child package solution to manage data transfers between two different data sources, so we can copy multiple tables and capture how many rows were transferred and duration for each transfer. This solution was working fine up until last week, when I made some changes to allow the package to perform a source count using standard SQL determined by an expression, or SQL provided from configuration tables, I also changed the package to Truncate or not the destination table, again controlled by configuration settings in a table. The child packages which perform the data flows have not changed!
The day after the controlling package promotion to live, I saw the bizarre behaviour of the Package log stating all rows transferred, but the actual table counts were not what the log stated, see attached file. The package solution works ok on other servers and was ok in DEV, but there were less tables and rows transferred.Re-running the package gave the same errors, but on some of the same tables and some different ones.
As it is the child packages doing the transfers and nothing has changed in them. I cannot see how the log would be able to say all rows are transferred and yet not all of the rows are actually moved?
Process output - where you can see counts and log Table transfer controller (as txt not dtsx)
An example of the data transfer child packages (as txt not dtsx)When I set the ExecuteOutOfProcess = True the package worked fine, unfortunately, this is not a good solution as SSIS 2008 does not tidy up the Dtshost.exe processes it starts and I'd be left with a memory issue after a very short time, we transfer hundreds of tables each day. ( I could write a .net script in the controlling package to kill the child processes, but that would still have hundreds of processes running before I could end them, as we have three parallel streams to allow a bit better performance.
-- 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....?
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.
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
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.
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')
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)
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?
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?
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???
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.
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..
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
