I periodically transfer large databases across servers and have had problems with tables on the target being empty (to rule out errant deletes by other developers). I'd like a way to do a table by table row count comparison between the two servers, similar to the way sp_compare_db (found here at Swynk) does on a single server. Even a method of referencing tables by including the server name would help.
View 1 RepliesI have one table called product 1 and the second table is called product 2 i have DirNo, ProdNo, UpdCode and BranchOffice as fields i whant to loop trought every record in table product 1 and check in table product 2 if the record is the same and exist i f it does go to next record, if not delete the record in product 1 and move to the next record and so on. could someone please help me. iam very new at this.
thanks
Hi there,
I was wondering if there's a way to check if a temp table already exists on my db.
I used to do that for regular table:
IF EXISTS(SELECT name FROM sysobjects WHERE type='U' AND name = 'myTableName')
... do something ....
But it doesn't works with temp table... Is there a way to do it ?
Thanks
O :shocked:
I am curious why my stored procedures are parsing properly when you do not reference a table with its schema. The stored procs then fail when you run them.
It seems that the parser does not validate that a tables schema is missing.
This is an example stored procedure against the Person.Address table in the Adventureworks database. It will execute fine if I change the FROM clause to Person.Address.
CREATE PROCEDURE [dbo].[Address_Load]
@AddressID [int]
AS
BEGIN
SET NOCOUNT ON;
DECLARE @intError int
BEGIN TRY
SELECT A.[AddressID]
, A.[AddressLine1]
, A.[AddressLine2]
, A.[City]
, A.[StateProvinceID]
, A.[PostalCode]
FROM [Address] A
WHERE A.[AddressID] = @AddressID
IF @@ROWCOUNT = 0
BEGIN
RAISERROR('Record not found', 16, 1) -- Record not found.
END
-- Return success
RETURN 0
END TRY
BEGIN CATCH
SET @intError = ERROR_NUMBER();
-- Log error here
RETURN @intError;
END CATCH
END
The stored proc parses fine and gets saved to the database but when executing it I get the following
Msg 208, Level 16, State 1, Procedure Address_Load, Line 10
Invalid object name 'Address'.
Is there any way to change this so the parsing will generate an error and not allow this into the database?
Thanks,
Cory
I want to get a resultset of every table in the database, with thecurrent record count of each. What is the easiest way to do this?I can get the list of tables with:Select s.name from sysobjects s where xtype = 'U'each s.name is a table name, but I'm not sure how to join a record countcolumn to the resultset.Thanks,RickN
View 4 Replies View RelatedHas anyone written or come across a routine that validates start and enddate in a slow changing dimension? (eg verifies there is no overlap in any of the records).
thanks in advance
I need a generic script that will query every table (minus system tables) within a database and generate record counts for each table.
Anybody have a script that will do this ???
Thanks in advance,
Nancy
I am trying to get a count of a job received date and a job closed date from the same table. I need these counts to be grouped by which team they are for. This is what I have and it isn't working:
SELECT HEAT.dbo.Profile.PrimaryTeamName,
COUNT(CallLog1.RecvdDate) AS OpenCalls,
COUNT(CallLog2.ClosedDate) AS ClosedCalls
FROM HEAT.dbo.Profile,
HEAT.dbo.CallLog CallLog1,
HEAT.dbo.CallLog CallLog2
WHERE HEAT.dbo.Profile.CustID = CallLog1.CustID AND
HEAT.dbo.Profile.CustID = CallLog2.CustID AND
CallLog1.CallID = CallLog2.CallID AND
((HEAT.dbo.Profile.PrimarySupportGroupID = 'ATS') OR
(HEAT.dbo.Profile.PrimarySupportGroupID = 'ats'))
GROUP BY HEAT.dbo.Profile.PrimaryTeamName,
CallLog1.RecvdDate,
CallLog2.ClosedDate
HAVING (CallLog1.RecvdDate = CONVERT([VARCHAR](10), GETDATE(), 120)) OR
(CallLog2.ClosedDate = CONVERT([VARCHAR](10), GETDATE(), 120))
I can get both counts to work individually, but as soon as I try to get them to go together I get some very interesting returns. I am drawing a complete blank as to what to do. Any info would be very helpful.
Thanks
Hi:
I would like to count repeating field values in a table, and to insert the counts into the same table. I have managed by having a temp_table, into which I insert the values:
insert into temptable values (select count(*) as Count, fieldname from table group by fieldname)
I then do a join on table.fieldname=temptable.fieldname, and update table.count with temptable.count.
Is very cumbersome, and does not update counts when table changes.
Is there a way to put in a calculated member, or to put the vaues of auto stats into the count field?
TIA
Kar
Hi, my code repeats the same correct count numbers for every person in a query. I need them to count differently for each person in a table. I am using Access 2007 for now. Thanks.
SELECT tblOutlookTask.AssignedTo,DCount("*","tblOutlookTask","PercentComplete=100") AS Expr1, DCount("*","tblOutlookTask","PercentComplete<>100") AS Expr2
FROM tblOutlookTask
Group By tblOutlookTask.AssignedTo;
I also tried this but it says 'missing operator on the second line' doesn't like the when in case maybe?
SELECT
COUNT(CASE WHEN PercentComplete=100 THEN 1 ELSE NULL END) as Completed,
COUNT(CASE WHEN PercentComplete<>100 THEN 1 ELSE NULL END) as Active,
COUNT(CASE WHEN PercentComplete<>100 And [DueDate]<Date() And [DueDate]>Date()-7 And DueDate<Date() And DueDate>Date()-7 THEN 1 ELSE NULL END) as Overdue,
COUNT(CASE WHEN tblOutlookTask.PercentComplete<>100 And [DueDate]<Date() And [DueDate]>Date()-14 And tblOutlookTask.DueDate<Date() And tblOutlookTask.DueDate>Date()-14 THEN 1 ELSE NULL END) as Offtrack
FROM tblOutlookTask;
Hey guys,
I have a table with about 80 columns and 400 millions records. Each columns has different responses that I need to get frequency for. I need to get counts for each response from all the columns... I have a query that does it, but it will run forever... what is the best way to do so?
My starting query:
select res, sum(cnt) from
(
select col1 res, count(*) as cnt from table1 with (nolock)
group by col1
union all
select col2 res, count(*) as cnt from table1 with (nolock)
group by col2
........................
select col80 res, count(*) as cnt from table1 with (nolock)
group by col80
)a group by res
In my SSIS package I have flat files as a source, I have to load numbers of flat files into SQL target table. I am using For each loop container for that. I am doing it correctly. My aim is to validate the source data from all angle before writing it into target sql table. I am using below points to validate the source data , if I found any bad data I am redirecting those data to error output.
To Checking
1. To check whether data type of column.
2. To check whether buisinesskey column null.
Is there any thing which I am missing to validate source data.
Screen shot for reference
I'm using CTEs to try and get the totals of two different criteria queries that I want to group by Month depending on the associated Inquiry Date. Here is what I have right now;
Code:
;With CTE(total, InitDate) as
(
SELECT count(Inquiry.ID), Inquiry.Date from Inquiry
Inner Join Inquirer on Inquirer.ID = Inquiry.InquirerID_fk
Left Join Transfer on Transfer.TransferInquiryID_fk = Inquiry.ID
WHERE (Inquiry.Date >= '3/1/2012' AND Inquiry.Date <= '9/26/2012' AND Inquiry.Date IS NOT NULL)
AND (Inquirer.Program = 'Res. Referral Coord.')AND TransferInquiryID_fk IS NULL
Group By Inquiry.Date
[code]...
I get 170 for InitCount, but for TransCount I only get 19, not 26. I assume it is with my left outer join statement grouping but I am not sure how I would change this to get the proper counts. All I want to do is group the values together depending on the month they were done in.
Have an SSIS package running great in 2008R2. It generates several flat files based on inline database queries. The first step of the package inserts a record into a log stats table and the last step of the package updates this record with the package name, run time and execution status. Now I need to add the records counts for each flat file to the log table.
Is there a way I can update one field for run counts with each of the counts for each file. So the [run counts] table column would look something like:
file1: 43522
file2: 645367
file3: 7883
Is it possible to store the record counts and flat file names in variables then concat them at the end when updating this record?
Or, is a better way to just insert/update a new record for each flat file step and log the counts for that file for its own record?
In either case, how I can capture the file count and pass that to the update statement.
Hi when I run this sql script: I get an error message
update server.dbname.DBO.ap_payment
set pay_flag = -2 -- means were not updated in vi approved table
where ISNUMERIC(venid)<>1
and pay_flag =0
I am not sure what this means, can anyone help me. thanks
Server: Msg 7306, Level 16, State 2, Line 1
Could not open table '"CESI"."DBO"."ap_payment"' from OLE DB provider 'SQLOLEDB'. The provider could not support a row lookup position. The provider indicates that conflicts occurred with other properties or requirements.
[OLE/DB provider returned message: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.]
thanks,
Ali
Hi, How can you copy a table from one server to another server using SELECT statement?
View 1 Replies View RelatedHello All again,
I have another question, I would like to validate the data in a sql table before my code runs and inserts duplicate information. Can anyone help out with this via example?
This question is rather open ended. Basically, I'm wondering the different approaches people use when validating input into a stored procedure. The rest of my post just describes a rather simple approach I'm using, and some ideas I have, but I'm eager to know what others are doing. So, if you'd like to comment on this, feel free to do so without reading the following.
I often find myself calling a stored procedure, and needing to validate some of the inputs before proceeding with the rest of the tasks. Such as, making sure a matching record isn't already in the database before adding a new record. Ideally, this sort of validation will report back to the user interface immediately, so that the user can get real-time response to their form input, instead of the all-too-common approach of redirecting to an error page if something goes wrong in the database transaction.
It's also convenient, at times (though perhaps not efficient in all cases) to let your database perform business rules validation of inputs (let the user interface make sure data types are valid, and such), so that you duplicate less code in the event that the stored procedure is called in more than one context.
To address these issues, I've taken to making two stored procedures instead of one. Let the stored procedure be called AddRecord. I'd create that, as well as the procedure named AddRecord_Validate, which would take identical inputs as AddRecord, whenever possible. Nearly the first thing done in AddRecord would be to execute AddRecord_Validate. The user interface would also call AddRecord_Validate, and return any validation errors to the user interface.
This seems rather convenient to me, in many cases, but often it doesn't work as well as I prefer. It's not uncommon for me to end up performing the same queries in the _Validate SP as I do in the parent SP. For example, let's say I'm passing a parameter that I use to look up a record I plan to edit. In my validation, I query the database to verify that the record is found. But in the parent SP, I run the same query again in order to get the stored ID of the record I need to edit. This ends up duplicating queries, making the pair of procedures less efficient on the whole, as well as introducing the likelihood of bugs when code changes in one of the SPs, but not the other.
So I've been brainstorming other approaches. The first idea is to execute a single stored procedure, but when I want to run it in "validation" mode, I simply rollback the transaction. This would let me know if the stored procedure would have run with the specified input, but won't ultimately change anything. Unfortunately, I see some badness in this, such as the entire SP perhaps taking a lot longer to execute that simple validation would have, and side-effects such as incrementing Sequences, or locking the database, and basically just doing a whole lot more work during validation than needs to be done.
The next idea was to require that the user specify the "run mode" via a parameter, either 'validate' or 'run'. Then, all of the validation would be performed at the top of the stored procedure, and a simple IF statement would exit the SP before actually making any changes if it's run in 'validate' mode. Otherwise, it will continue, and do the real work. The only real downside I see to this is forcing the developer to deal with an extra parameter. And, perhaps, it's not really a "relational" approach.
So, what do the rest of you do?
hello
i am using visual studio 2008 to create an inventory management system and i am having trouble checking for a constraint violation. i have a partnumber colum in the database that needs to be unique and i am using datasets / tableadapters to interact with the database. when a user adds a new part number to the database i need to make sure its not already there
i have tried to use
catch ce as constraintexception
debug.print ce.tostring
end try
but the program still crashes with
System.Data.ConstraintException was unhandled ( see full error below)
no matter where i put the try statement it says its unhandeled
this error triggers when when i leave the combo box (when its validated) i have even tried placing it here
Private Sub PartNumberComboBox_Validating(ByVal sender As Object, ByVal e As System.ComponentModel.CancelEventArgs) Handles PartNumberComboBox.Validating
Try
Catch CE As ConstraintException
Debug.Print(CE.Message)
Catch EX As Exception
Debug.Print(EX.Message)
End Try
End Sub
if someone could point me in the right direction id greatley appriciate it
System.Data.ConstraintException was unhandled
Message="Column 'PartNumber' is constrained to be unique. Value '36LDVRRP' is already present."
Source="System.Data"
StackTrace:
at System.Data.UniqueConstraint.CheckConstraint(DataRow row, DataRowAction action)
at System.Data.DataTable.RaiseRowChanging(DataRowChangeEventArgs args, DataRow eRow, DataRowAction eAction, Boolean fireEvent)
at System.Data.DataTable.SetNewRecordWorker(DataRow row, Int32 proposedRecord, DataRowAction action, Boolean isInMerge, Int32 position, Boolean fireEvent, Exception& deferredException)
at System.Data.DataTable.SetNewRecord(DataRow row, Int32 proposedRecord, DataRowAction action, Boolean isInMerge, Boolean fireEvent)
at System.Data.DataRow.EndEdit()
at System.Data.DataRowView.EndEdit()
at System.Windows.Forms.CurrencyManager.EndCurrentEdit()
at System.Windows.Forms.CurrencyManager.ChangeRecordState(Int32 newPosition, Boolean validating, Boolean endCurrentEdit, Boolean firePositionChange, Boolean pullData)
at System.Windows.Forms.CurrencyManager.set_Position(Int32 value)
at System.Windows.Forms.ComboBox.OnSelectedIndexChanged(EventArgs e)
at System.Windows.Forms.ComboBox.WmReflectCommand(Message& m)
at System.Windows.Forms.ComboBox.WndProc(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
at System.Windows.Forms.UnsafeNativeMethods.SendMessage(HandleRef hWnd, Int32 msg, IntPtr wParam, IntPtr lParam)
at System.Windows.Forms.Control.SendMessage(Int32 msg, IntPtr wparam, IntPtr lparam)
at System.Windows.Forms.Control.ReflectMessageInternal(IntPtr hWnd, Message& m)
at System.Windows.Forms.Control.WmCommand(Message& m)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.GroupBox.WndProc(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
at System.Windows.Forms.UnsafeNativeMethods.CallWindowProc(IntPtr wndProc, IntPtr hWnd, Int32 msg, IntPtr wParam, IntPtr lParam)
at System.Windows.Forms.NativeWindow.DefWndProc(Message& m)
at System.Windows.Forms.Control.DefWndProc(Message& m)
at System.Windows.Forms.Control.WmCommand(Message& m)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.ComboBox.WndProc(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(Int32 dwComponentID, Int32 reason, Int32 pvLoopData)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.Run(ApplicationContext context)
at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.OnRun()
at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.DoApplicationModel()
at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.Run(String[] commandLine)
at Inventory_System.My.MyApplication.Main(String[] Args) in 17d14f5c-a337-4978-8281-53493378c1071.vb:line 81
at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args)
at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHelper.ThreadStart()
InnerException:
Hello. I need to copy all of the rows in a table from a database on one server, to another existing table of the same name in a different database on a different server. I'm trying to use a SELECT INTO statement. Any idea how to do this?I've tried SELECT * INTO DestinationServer.dbo.DestinationDB.DestinationTableFROM SourceTable AS SourceTable_1 But this doesn't work, saying there are too many prefixes. Any idea how to do this?
View 5 Replies View Related
Simple question:
I have two servers S1 and S2. Inmediately after new data on S1 is available I want to perform some actions on S2.
I can use a trigger on S1, but if S2 is down the transaction on S1 will be lost. I could use database replication but I only need one single table in S1 to report changes to S2
Is there any other approach I could use?
Thanks.
I am using the following SQl statement in a DTS vb file.
The data is coming from an Excel spreadsheet and being put into a SQl server table.
oCustomTask1.SourceSQLStatement = "select `Order No`,`Country`,`Desc`,`Amount` from `Sheet1$` WHERE `Order No` = 1 "
I want to validate the data being put into the table to ensure no duplicates records are entered UNLESS the record has changed in any way.
E.g. If record 1 had a column called "NumberOne" which changed to "Number1" change this information and move the original value to another table.
hi guys, have a good day.
i have a table with a column named "cardRange", that column can only have integer values from 0 to 5.
i want the validation in the value that will be inserted in that column to be made in my sql server rather than in my programmed software.
Is there a way to validate (through a storedprocedure, or a trigger, or a column propertie) the values i want to add to my table??
In other wordsI just want my table to make sure that only a integer from 0 to 5 (or any other simple validation) can be added to the table.
Any help? thank you very much.
Im trying to perform an insert but with only valid emails that meet the following criteria:
Valid email : not Null, length > 6 with @ character
I know the is NOT NULL, but the other two im a bit confused on. :)
... does anybody of you have a script for validating all stored procedures within a sql-server database (2000) ...
thanks in advance
Greetings
Stefan
Hi all,I am a newbie to sql and I need your help.I want to update column (email) from one table to another validating theCustomerid column in both table. Update the email address in productiontable with the email address in temp table if the customerid is same in bothtables.What would be the query?Thanks,
View 3 Replies View RelatedI'm totally new to SSIS and need some direction.I'ved worked with the Import/Export wizard to create a package that imports a text file into a SQL Server table. However, I'm told the format of the text file changes over time and that's not good. I need to program in a format validation check on the source file before it gets imported. If it changes, I'm suppose to throw an alert or something.Let's say the file has the columns: field1 (string[10]), field2 (date), field3 (integer), field4 (decimal).I did some testing and tried to change the data to a longer string in field1, and SSIS recognizes that and errors out. How do I get it to send the bad record to an bad record file? Do I just set a destination file connection for bad records and connect the red arrow from the source file to the destination file?I forget if the source file connection recognizes a bad date and errors out. I'll have to check again.But when I changed the data in the datafile for field3 from integer to decimal. It didn't recognize that as an error. It read it in "successfully". That's not good.Similar thing happened when I changed field4 from decimal to integer in the data file. But I'm not too worried about that.Any hints on how to do this or a better approach on checking for file format changes would be appreciated.Ken
View 2 Replies View RelatedHello all,
I have an InstallShield project, which launches SQL Server 2005 setup using a .INI file for the settings.
During the installation interview, I prompt the user to enter their PIDKEY. I then replace a value in the .INI with this key.
The problem is that if it is entered incorrectly, the obvious occurs where the SQL Server 2005 setup fails.
Is there a way for me to validate the PIDKEY before calling setup for SQL Server?
Thanks!
Hi,
I have one date parameter in my report. The user will be able to manually enter or use the calendar control. In the case of entering manually, is there any way to validate the input string? How can we prevent users from entering alphabets?
Also, is there any way to reduce the length of the parameter textbox. Can you please help?
Thanks,
Sonu.
Hi there,
I have a Problem with my SSIS and it is that when you open the package it says "Validating <Data Flow>" and it takes a loooooong time to finaly open ( by long I mean like 3 or 4 hours to just for open!!!). In 3 of the DF, I have conections to a Unisys server and in exactly in this DF is where it takes its time. This Unisys system its kind a slow but not that slow. I am running on a server that have 4 G and 8 processors, so it cant be the machine.
my question is, How can I make it validate faster ? I have tried to work Offline but when I run the package I need to be online.
Any Ideas? Am I doing anything wrong here??
thanks!
I am making a ASP.NET web application that involves 2 SQL Server(A & B).
I created a view in SQL server A pointing to the table in SQL Server B. I found out my application will run REALLY slow when accessing such a view. so I try to avoid using them. But in the case of 2 table joining from 2 different SQL Servers, I have no choice.
Can anyone help me with this?
Thanks!
Hi,
I tried with success to replicate a table_test_repl from my local server to an other server.
I used only one table for testing.
After that, i can't drop the 2 tables a_test_repl (on my local server and the other : I've got a message 'cannot drop the table because it is published for replication'
What can I do? I need absolutely to drop these tables to keep a clean model
and I can't drop the database and try again.
Thanks to your answers
Axel
Hi folks!
The boss has decided that the data from a table we have on one database (containing daily data) needs to be copied to a "history" table on a different database, on a different server.
The transfer will probably be done with a scheduled stored procedure, and all columns will be transferred EXCEPT for two columns in the source table, which are not present in the destination table. This means instead of an "INSERT Dest SELECT * FROM Source" I have to do an "INSERT Dest SELECT yada,yada,yada... FROM Source" in order to disregard the columns we don't care about. NO problem.
I was thinking (you should smell trouble - and sawdust burning at this point) that this leaves us open to a punch below the belt later when a new column is added to the Source table, since we are using a definitive list of columns to move rather than a *, and the new column could be added without the otherwise system-generated, *-sponsored "gentle reminder" that it also needs to be added to the Dest table. I'm not as convinced as The Boss that "Oh, I'm sure we'll remember to add it to the history table when that happens" :lol:
So, long story shorter (nah...too late for that) - I figure I can write a check at each day's historical transfer on the count of columns in one table verses the other, and send an email or fail the job if the count doesn't make sense.
For example, if the Source table has 34 columns, since we don't care about two of them, the Dest table should have 32 columns, if things are still in synch. OK, I think (insert burning sawdust smell here), I can use a SysObjects thang to count rows. BOL points out INFORMATION_SCHEMA.COLUMNS as a possibility.
Here is the code I think (sawdust) can be used:select ((select count(*) from SourceDb.INFORMATION_SCHEMA.columns
where table_name = 'Source') - (select count(*) from HISTSERVER.DestDb.INFORMATION_SCHEMA.columns
where table_name = 'Dest')) as ColumnDiff
Trouble is...this fails because apparently the INFORMATION_SCHEMA thang doesn't do well with remote servers.
Can anyone figure a way around this, or suggest an alternative? I'm still looking, but thought I'd also toss it out onto the board for your generous consideration.
Thanks - and can you BELIEVE the year is already 1/12th of the way OVER?!?!?!
Paul