URGENT! Need To Extract Default Values And Update Similar Tables
May 19, 2008
I am using SQL 2005 merge replication with a publisher managing about 45 articles(tables) with about 10 subscribers (remote servers). The problem is that we had to re-start replication from scratch and noticed that, although the publisher's tables have the default values, the subscribers did not get the default values with the initial snap shot, schema building..?!?
I now have to go over 450 tables (10 remotes SQl servers at 45 tables each) and 'reset or set' over 1,000 default values. Meanwhile, the system is down...omg...so not good.
Is there a script out there that automatically extracts the default values from a table and set it to another exact table with the same structure? any ideas?
I have a grid like this and when I change Designation value of Name X from PA to PAT, this change should reflect to Sathish Designation too. Changing similar values should reflect all over the grid. Like update database on change of value of similar kind.
By far I use this query to update the grid. So a where clause should be used now to update the grid to fit this scenario.
("UPDATE AppInvent_Test SET Name = @Name, Designation = @Designation, City = @City WHERE EmpID = @EmpID");
EmpID Name Designation City 21 X PA Chn 2 Sathish PA Chn 3 Shiva A Cbe 17 Venkat M Hyd 22 Y SM Cbe 18 Vignesh SA Hyd
I have a table that I am basically reduplicating a couple of times for each part of this database that I want to create.Each table basically has the same data: The tables will be called motherTable, fatherTable, sonTable, daughterTable and so on.I am pretty much using the following in each column: UserID, MotherID(or FatherID or SonID, etc., etc. and so on for each unique table), FirstName, LastName, MiddleName, BirthPlace, Photo, Age.I don't see an option to copy a table and just modify the second ID part and rename that table accordingly.How can I make this an easier way of creating these similar tables without retyping all these columns over and over again?Thanks in advance.
Does anyone know if or how I can insert a default value of the (+) character into a column? when I try to save it I get an error message saying it has a problem with ')'.
Hi, I am trying to replicate some of my production tables into development tables in an automated way. Decided to use the Select INTO command because it seemed to be the easiest way. This command does copy all the col sizes, but does not copy over the "default value or binding" fields. How do I do this? I saw that in create table you can use "INCLUDING COLUMN DEFAULTS "
But this doesn't seem to work for the select into command. Can you tell me how I can do this? Currently the command I am using is:
SELECT * INTO " + DevTableName + " From " + ProdTableName
However, this won't copy over the default column values that I have specified inside the table ProdTableName. Can you tell me how I can do this?
I have several databases set up for transactional replication to another instance of SQL Server 2005 for fail over purposes. Today, I restored one of those replicated databases to my development machine and discovered two surprising problems:
1) The Default Values settings in the replicated tables are missing. They are there in the publishing tables, just as they were before I set up replication. However, they are not in the subscribing tables. Now, this is not such a big issue, since I tend to send all default values in insert queries as necessary.
2) The second problem is a more of an issue, since I use auto-numbered Identity columns in my tables (yes, I know that's just plain lazy...). Anyway, in the replicated tables, €œIs Identity€? is indeed set to yes, but despite that fact that there are thousands of records with incrementally unique IDs, SQL server is trying to insert a record starting with 1. This, of course, throws a PK constraint error.
Obviously, if I am use them for failover purposes, these replicated databases need to be identical in every way.
So, what did I do to cause this situation, and how to I fix it?
How can i enter Default Values of " " to all the columns of type characterof all the tables (excluding system tables) and Default Values of 0of all columns of type numbers. Excluding all primary key columns.Thank you
I am really stuck here, I hope to get some helpful answers on this forum.
Ok, I have three four tables in my db, 1- Stages 2- Activities 3- Tasks 4- Subtasks Structure is like: Stages 1- SrNo (Unique) 2- Stage 3- StartDate 4- FinishDate
Now what i want is to update Tasks, StartDate and FinishDate according the Min(StartDate) and Max(FinishDate) of related Subtasks and same for Activities and Stages. I have tried following query to Update tasks, StartDate and FinishDate
Update Tasks Set Tasks.StartDate=(Select Min(Subtasks.StartDate) from Subtasks,Tasks where Tasks.SrNo=Subtasks.SrNo1) from Subtasks,Tasks where Tasks.SrNo=Subtasks.SrNo1
But this query updates all Tasks with Min and Max date from Subtasks regardless of their relation.
I have two tables and I need to update values in them via a stored procedure. Tried too much to update but some times it update the first table only, others the second or even fail due to cannot allow duplicates. Also when it updates the WHOLE data in the table becomes the same as the new updated ones. I've now reached to this error after all these lines of codes
Cannot insert the value NULL into column 'Emp_ID',table 'DatePics'; column does not allow nulls. UPDATE fails.The statement has been terminated
Here is the SQL code :
ALTER procedure [dbo].[UpdateEmp] @EmpName nvarchar(100), @Nationality nvarchar(30), @Passport nvarchar(20), @ContractDate date, @HealthDate date
I created one stored proc, then copied it to create another similar stored proc with just some filtering changes in the second. Now I want to obtain the results from both like this:
ProjFee ProjGross DailyRunRate Var1 InHouse1 InHouse2 GrossGoal Group Name PostedAmount --------------------------------------------------------------------------------------------------------------------------- Row# 1 from current stored proc Row # 2 from called stored proc
so something like this I should get in the end for example when both results are combined:
The first stored proc (CurrentMonthCollections_AZ) attempts to include the results of the second stored proc at the end (CurrentMonthCollections_IL) by calling it and inserting the fields into a temp table.
I was told by Angel to instead convert the second stored proc into a UDF...but having trouble with this.
What is the best approach to get the desired 2 rows back that I need in that fasion?
Here is the code for the 2 stored procs (very long so I will post as as links):
Look at the end of CurrentMonthCollections_AZ.txt to see where I'm stuck in trying to select the results (ProjFee ProjGross DailyRunRate Var1 InHouse1 InHouse2 GrossGoal Var1 PostedAmount ) from both stored procs.
I don't think UNION is what I want because it will combine stuff...I just want 2 separated rows
I am using the following select statement to get the row count from SQL linked server table.
SELECT Count(*) FROM OPENQUERY (CMSPROD, 'Select * From MHDLIB.MHSERV0P')
MHDLIB is the library name in IBM DB2 database. The above query gives me only the row count of table MHSERV0P. However, I need to get the names, rowcounts, and sizes of all tables that exist in MHDLIB librray. Is it possible at all?
There are more than hundred tables, so i wanna automate this. i am tryin to do this in a cursor: please guide!
declare @name VARCHAR (50) declare cur cursor fast_forward for select name from sysobjects where type='u' and status not like '-%' open cur WHILE (1=1) BEGIN FETCH NEXT FROM cur INTO @name IF @@fetch_status = 0 BEGIN ALTER TABLE @name ADD created_by [VARCHAR] (25) GO ALTER TABLE @name ADD created_by [VARCHAR] (25) GO ALTER TABLE @name ADD created_date [DATETIME] GO ALTER TABLE @name ADD modified_by [VARCHAR] (25) GO ALTER TABLE @name ADD modified_date [DATETIME] END ELSE BREAK END DEALLOCATE cur
I also want that if one column for a table exists; the other columns should be created rather than it quits.
I have a table that I am basically reduplicating a couple of times for each part of this database that I want to create.
Each table basically has the same data: The tables will be called motherTable, fatherTable, sonTable, daughterTable and so on.
I am pretty much using the following in each column: UserID, MotherID(or FatherID or SonID, etc., etc. and so on for each unique table), FirstName, LastName, MiddleName, BirthPlace, Photo, Age.
I don't see an option to copy a table and just modify the second ID part and rename that table accordingly.
How can I make this an easier way of creating these similar tables without retyping all these columns over and over again?
For every table in my database there is a duplicate table with same columns. For example, employee is the name of main table, there is employee_dup table in same database.
There is only one column extra in _dup tables i.e.,idn column.
Now, I want to know all the columns present in main table which are not present in corresponding _dup table. There might be a chance of missing one or two columns in _dup tables. So i want a query to find out all the columns present in main table that are not present in hx table.
I am trying to write an SQL command for my crystal report. I need to compare the same column in 3different tables & get the data from each table for only the matching data.. I understand I need to create a temporary table, get the data into it & then work around.. I am quite new to SQL.
Eg: Considering one customer account Table 1 Cust.No Name Amt_Counter AmtPaid 123.456 sam 0 0 123.456 sam 1 50
Table 2 Cust.No Name Freq_Counter Frequency 123.456 sam 1 0 123.456 sam 2 15
SqlCommand cmd = new SqlCommand("Select * from Details where Emp_Code in (" + selectedValues + ")", con);
I want to join a table called Materials to this now. Material table also has an Emp_Code column. How can I write a select sql query to fetch Emp_Code from both Details and Materials table.
SqlCommand cmd = new SqlCommand("select D.Emp_Code, M.Emp_Code from Details D, Materials M where D.Emp_Code = M.Emp_Code in (" + selectedValues + ")", con);
For every table in my database there is a duplicate table with same columns. For example, employee is the name of main table, there is employee_dup table in same database.
There is only one column extra in _dup tables i.e.,idn column.
Now, I want to know all the columns present in main table which are not present in corresponding _dup table. There might be a chance of missing one or two columns in _dup tables. So i want a query to find out all the columns present in main table that are not present in hx table.
I have two tables in the same SQL database. Both have a similar numericfield. One field has the same number as the other field but is prefixedwith various letters. I know how to use LIKE but won't know the partialstring I am looking for. I am trying to use LIKE '%' + Field A orsomething that will do this.Eg.Table 1 Field A is 'A12345"Table 2 Field B is '12345"I want to find every record in Table 1 Field A that contains the exactField B data.*** Sent via Developersdex http://www.developersdex.com ***
What I am working with unfortunately is a very poorly designed and non-normalized database. Please don't criticize the design. I didn't design it, but I have to write queries against it.I have 2 tables. 1 is called EnterVolume. The other is ExitVolume. Similar columns exist in each.
The rules of the database state that for every EnterVolume row (for a given TrialID, SBOINumber, and OwnerBOI) there must be a corresponding ExitVolume row in the ExitVolume table.What I need to do is to capture the paired TimeOfEvent entries from each table for each paired row. Nothing says that an SBOI cannot enter and exit a OwnerBOI's volume several times during the same Trial.Every time a SBOI Enters an OwnerBOI's volume during a certain trial, a row is created in the EnterVolume table. And Likewise when Exiting a OwnerBOI's volume during a certain trial, a row is created in the ExitVolume table.
So here is a query that I attempted, but gave undesirable results:
SELECT EV.TimeOfEvent AS [Enter Time], XV.TimeOfEvent AS ExitTime FROM IntegratedTest1.EventPortion.EventEnterVolume AS EV INNER JOIN IntegratedTest1.EventPortion.EventExitVolume AS XV ON EV.TrialID = XV.TrialID AND EV.SBOINumber = XV.SBOINumber AND EV.OwnerBOI = XV.OwnerBOI
Here is some sample data:
EnterVolume TrialIDÂ Â Â Â Â Â Â Â Â Â Â Â SBOIÂ Â Â Â Â Â Â Â Â Â OwnerBOIÂ Â Â Â Â Â Â Â TimeOfEvent 1Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â AÂ Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â DÂ Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 5 1Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â AÂ Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â DÂ Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 2000
I have my stored procedure set to Territory_code IN (@Territory)
, now , how do i enter in more then one value. When i select the multi value check box, it gives me more spaces. But then doesnt recognize the values when i put in more then one. am i doing something wrong?
Hi,I m searching for some Script / Function ... to find difference in datab/w 2 similar tables (exactly same fields structure) in sql 2000.plz update me asap !thanks in advance !
A have a multi-valued parameter (B) which is dependent on a single-valued parameter (A) on my report. When a value is selected in A, I want all matching values in B to be selected by default and the "Select All" option checked. To do this I have set the Default Values section in B to point to the same dataset as the "Available Values" section. Both A and B have default values so the report runs automatically.
One of the values in parameter A (say Value1) yields more values in parameter B than the other (say Value2).
If I run the report the first time with Value1 selected as the default for parameter A, all values in B are checked correctly. If I run the report with Value2 selected the first time and then change the selected value to Value2 and run my report, all values in B are displayed but only the values that were previously checked (when Value1 was selected), are now checked, leaving the "Select All" unchecked.
What am I doing wrong? Why are all the values in B not checked? The dataset is the same in "Available Values" section and "Default Values" section.
I have two tables in my database called CartItems and OrderItems. Istore all of a session's shopping cart items in the CartItems tableusing the sessionID as the identifier (called cartID in my DB). Afteran order is placed and is approved, I would like to copy all of theitems in the CartItems table for that given cartID to the OrderItemstable given a new orderID.I will know the cartID and orderID ahead of time and would like tosend them both into a stored procedure and have the transfer takeplace.Example:take this data...CartItems (table)--------------------------------------cartID | itemID | quantity | price--------------------------------------12345 2 1 12.9512345 7 2 17.95and make it this data...OrderItems (table)--------------------------------------orderID | itemID | quantity | price--------------------------------------00001 2 1 12.9500001 7 2 17.95via some stored procedure that I send (@cartID,@orderID)Any help would be greatly appreciated!!
It seems that there should be a solution for my situation, but for the life of me I can't seem to figure it out.
I need to compare two "like" tables, containing similar data. Tbl 1 is "BOOKED" (which is a snapshot of inventory) and tbl 2 is "CURRENT" (the live - working inventory table). If I write my query as follows the the subsequent result is "duplicate" data.
Code Block SELECT booked.item, booked.bin, booked.quantity, current.bin, current.quantity FROM BOOKED LEFT JOIN CURRENT ON booked.item = current.item
No matter what type of join I use, there is duplicate data displayed for each table. For example, if there are more bins in the BOOKED table that contain a certain product then the CURRENT table will repeat data and vica versa.
As follows:
Item Bin Quantity Bin Quantity
12345 A01 500 A01 7680
12345 B01 6 A01 7680
12345 C01 20 A01 7680
54321 G10 1032 E15 1163
54321 G10 1032 F20 523
54321 G10 1032 H30 750
98765 Z20 7000 Z20 8500
98765 Y15 2500 Y15 3000
98765 X10 1200 Y15 3000
What I would like to do is display Bin and Quantity only once and the repeating values as NULL or [BLANK]. Or, to display all of the bins from both tables and only the quantities from each table in relation to the bin found in that table, returning a "0" if no quantity exists.
This is what I'm after:
Item Bin Quantity Bin Quantity
12345 A01 500 A01 7680
12345 B01 6 B01 0
12345 C01 20 C01 0
54321 G10 1032 E15 1163
54321 F20 0 F20 523
54321 H30 0 H30 750
98765 Z20 7000 Z20 8500
98765 Y15 2500 Y15 3000
98765 X10 1200 X10 0
Is this possible? If so, how?
I also might add that it is ok for each table to contain multiple entries for any given item. This is basically being requested as an inventory variance report - inventory before physical count and immediatly after physical count - and will only be run once a year.
----------------------------------------------- Just thinking out loud here: What if I created three subqueries, the first containing only BOOKED information, the second containing only CURRENT information and the third being a UNION of both tables? Something like this:
Code Block SELECT q3.bin, q1.item, ISNULL(q1.quantity, 0) as QTY_BEFORE, ISNULL(q2.quantity, 0) as QTY_AFTER
FROM
(select item, bin, quantity from BOOKED)q1 Left Join
(select item, bin, quantity from CURRENT)q2 on q1.item = q2.item Left Join
(select bin, item from BOOKED UNION CURRENT)q3 on q1.item = q3.item
Order By q1.item
I don't know if I wrote the UNION statement correctly, but I will have to try this when I get back to work...
Hi, I have a table with one XML type column. This column holds custom field information. Its used as a way of storing ad hoc fields and data that don't fit the DB design. <?xml version="1.0"?><contact><Reference>A39390TFH</Reference><Misc>all kinds of stuff go in here</Misc></contact>I want to provide a way of displaying the data stored in this column in the same DataTable as normal relational data from the same table. I have been able to achieve this goal BUT I want to know if the community had any ideas on how I could speed the process. I am using the XML value() function. It allows me to extract the data I need. SELECT Name, Number, Reference FROM
GROUP BY Name, Number, Reference anyone know if there are better xml functions to get this data out of the XMLColumn?????? There is no schema, because each xml fragment has different tags and different values. Regards Niall
Once column, in the CSV file, has a format of general and is only populated by numbers (0 - 126); no decimal places.
During the extract, the column is copied into my staging table as an NVARCHAR value. However, after the extract, the values have all been appended with a ".0". What could be causing this?
I am creating a scheduling web application. I have managed to insert data into the database. The code is as follow:
Dim insertSQLDatasource As New SqlDataSource() insertSQLDatasource.ConnectionString = ConfigurationManager.ConnectionStrings("ScheduleConnectionString").ToString
Try insertSQLDatasource.Insert() Catch ex As Exception Panel1.Visible = True InsertMsgLabel.Text = ex.Message.ToString Finally insertSQLDatasource = Nothing End Try
Now I am trying to select the values for a particular event. For example, if the user selects an event with id 40, the values that corresponds to that row will be read and filled into the respective controls. I tried the following code, but doesn't work.
Dim sqlConn As SqlConnection = New SqlConnection("ScheduleConnectionString") Dim com As SqlCommand = New SqlCommand("SELECT * FROM Demo_Theatre_DB WHERE ID=@ID", sqlConn) sqlConn.Open() Dim r As SqlDataReader = com.ExecuteReader() While r.Read() StartTimeTextBox.Text = r("StartTime") EndTimeTextBox.Text = r("EndTime") CompanyNameTextBox.Text = r("CompanyName") PurposeTextBox.Text = r("Purpose") AccountManagerTextBox.Text = r("AccountManager") PresenterTextBox.Text = r("Presenter") ColorDDL.SelectedValue = r("ColorCode") StatusRadioButtonList.SelectedValue = r("Status") CommentTextBox.Text = r("Comments") End While
From the table i want everything highlighted with a *I wanted an SQl expression to look at values in Column 1 (ID), look atthe corresponding values in the second column (F1) and select the rowwith the highest value, and then if there was more than one row forthat ID with the same value in F1, look at column 3 (F2) and selectthe row with the highest value in this column.SQL> select * from test;ID F1 F2---------- ---------- ----------1 12 4* 1 12 61 11 11 9 122 3 5* 2 9 132 9 93 1 23 1 1* 3 7 5I try the followingSQL> select * from test t12 where f1 = (select max(f1) from test t2 where t2.id = t1.id)3 ;And get the followingID F1 F2---------- ---------- ----------1 12 4* 1 12 6* 2 9 132 9 9* 3 7 5If I add another line with an AND statement after line 2 I either getnothing or rubbish as the output.Ideas?Cheers
I have a report which is redirecting to a subreport. The main report is having multi value parameter. I need to pass these  multi values to sub report. Passing parameters from MDX report to T-sql report. So, I'm using the below exp.
=SPLIT(REPLACE(TRIM(Join(Parameters!Grade.Label,",")),",  ",","),",") The value will look like this 01-Manger 02-Senior Mange 21-Associate 25-Associate Trainee
This is working for me in all the cases except one. In all other cases, the parameter's Label and Value field has same data in the sub report. But, in a specific parameter I'm getting Label and Value data are different. I'm getting an alpha numeric string value from MDX report , but I need to pass only the numeric values to the sub report since its value field contains only numeric value. The numeric value is coming at the starting of the string data. So I have used Mid()
=SPLIT(Mid(REPLACE(TRIM(Join(Parameters!Grade.Label,",")),",  ",","),1,2),",")
Result will be  01
But, mid() will give only the first value. It is working for single value. But I need to extract multiple values.
Hi, I have a task that requires me to pull a list of tables used in a select query and put them into a string array. For arguments sake, let's say this is my query: select * from table1 tb1, table2 tb2 where ...
I can easily extract the 'from 'clause, in this case: table1 tb1,table2 tb2, but I still have to split out the Alias before I can use it. Can anyone show me a regex that can do this instead?
