I have a pocket pc app that is using a sql ce (.sdf) database. The inserts, updates and deletes are not persisting when I exit debug. It seems like it's getting put in the database because I bind drop down lists after the inserts, updates, etc and the new/changed data shows correctly after rebinding the data (when I repopulate the drop downs I make a call to the database, I don't just manually add the new item to the drop down so it seems like the changes are getting to the database). However, once I stop debugging and restart debugging all my changes are gone. I'm not using transactions but is there something that I need to do to commit the db changes?
I was able to write successfully a Script Task to set the values of several package variables. But when the execution completes, the variables still contain values which were specified by default.
What I want is a method of persisting the values assigned to a variable through a script. I believe this was possible in DTS
Hi all,I am rather new to database design and modelling concepts in generaland was hoping for some advice on a problem I am trying to solve. Ihave designed a piece of software that creates a tree with pluggablenodes. Each node class can have 0 to n distinct classes plugged intoit to define the type for that node.For example, a node plugged with a 'customer' class and an 'engineer'class would indicate that this node in the tree is an engineer who isalso a customer. We could also have a 'owner', 'engineer' etc.I now want to persist this tree in an SQL Server 2000 Database. I havechosen to implement the nested set model, and have thought about thefollowing table design:table NODE_TABLE:lft INTEGERrft INTEGERpropsID INTEGERtable PROPERTIES_TABLE:propsID INTEGERtableName VARCHARtable CUSTOMER_TABLE:propsID INTEGERfirstname CHARlastname CHARtable ENGINEER_TABLE:propsID INTEGERnum_completed_projects INTEGERdegree CHARschool CHARtable OWNER_TABLE:propsID INTEGERcompanyName CHARSo, given the above example - I would have a NODE_TABLE that links to 2entries in PROPERTIES_TABLE. One entry would link to an entry in theCUSTOMER_TABLE, the other to an entry in ENGINEER_TABLE.Are there any more efficient solutions to this problem? As i said, Iam very new to DB design and would welcome any feedback or suggestionsof how else I might model my pluggable tree in a Database. Thank you,Bob Yohan
I have built a Table Valued SqlFunction which streams out filtered results from a table. The way that I have implemented this is by using a custom class which implements the IEnumerator interface.
The class internally stores a SqlDataReader and a SqlConnection as private member variables. The Class initializer (ie: the New function) creates a SqlCommand which is executed with ExecuteReader into the SqlDataReader and returns.
The IEnumerator.MoveNext method loops through the SqlDataReader until it finds a result which matches the heuristic filter, and the IEnumerator.Current method returns the current result from the SqlDataReader.
Unfortunately as soon as the initializer returns the SqlDataReader is automatically closed, and I can't figure out why. I've debugged through this and at the end of the Initializer the SqlDataReader is definately open, and as soon as first call to MoveNext is run it is closed.
Can anyone offer any suggestions on how I can fix this.
In the interim i've had to load all of the filtered results into a temporary ArrayList in the Initializer, which defeats the purpose of streaming out the results.
I've recently started a new position and our production box. Contains a procedure that uses 30 + temp tables. I'm currently not in a position to change this as it's production and I would have to be granted a window to re-design.
However the tempDb is showing some strange activity.
If a table is created #CarrierService (CarrierServiceID,DeliveryZoneID,CollectionZoneID) for example
Once the procedure is called It will appear in the tempDB with the session info appended as expected
However once the session has ended the above table will get dropped and a new one created
#2C78E45A, I now have 7000 of these different Tables in the TempDB
When I Interrogate this using
SELECT o.name, o.create_date,o.modify_date , c.Name,C.Column_Id FROM tempdb.sys.Objects o Inner join tempdb.Sys.Columns c ON o.object_id =c.Object_ID WHERE o.type ='U'
I get the Following results
name create_date modify_date object_id name #2C78E45A26/04/2015 18:0930/04/2015 14:55746120282CarrierServiceId #2C78E45A26/04/2015 18:0930/04/2015 14:55746120282CollectionZoneId #2C78E45A26/04/2015 18:0930/04/2015 14:55746120282DeliveryZoneId
Push data from the Integration Services pipeline or a custom application. The data can flow directly into an Analysis Services partition from the Integration Services package pipeline, without intermediate storage. This scenario can be used to reduce the latency (and storage cost) of analytical data.
What I think this means is that using SSIS I could bypass porting the data from ODS to DW, and instead use SSIS control flow/data flow components to direct the data straight to the Analysis Services Cube database storage partition.
I was hoping by posting this thread on this MSDN forum I could get details or links from those more knowledeable than I on this subject pointing me to how I may go about using SSIS to implement this approach. Perhaps package code examples or instructions about the components I could use to do this?
I am seeing a particular problem in the XML Source Editor "Columns" configuration where it is not persisting the "Output name" selection.
Control Flow Tab: 1. I use a "Exec SQL Command" to drop, create, or alter the destination tables in the database that I want to be repository for the inbound XML data. The data types are fairly straightforward.
2. I add a singular "Data Flow"
Data Flow Tab:
1. I add a "XML Source" task, and assign a well-defined XML file. I then use the "Generate XSD" option in the "Connection manager"; and I am fairly satisfied with the generated XSD.
2. I create "OLE DB Destination"
3. I wire the "XML Source" to the "OLE DB Destination". In the "XML Source" in the "Columns".
4. I go to the dropdown list of "Output name" and see the list ordered with the various complex-types that I want to map and transfer to a target table.
For the sake of this report, I select the 5th one down on the list (for which I already have a target table) - let's call this "Mesh"
5. In the "Input Output" dialog, I select the "output" to be the desired 5th item, "Mesh"
6. I check all my mappings so that they map one-to-one ... XML name entries match SQL table destination mapping entries; correct types; correct size
7. Check the metadata and it all looks good.
8. When I hit "Debug" to test the package the failure occurs at the "XML Source". The error report comes back saying that it failed because "field xxx in Contributor was truncated". However, "Contributor" corresponds to the 1st name in the dropdown list presented in "Columns" "Output name:".
If I select return to Step 4, when I open up "Columns" I see that my previous selection of the 5th item on the list named "Mesh" was not persisted, but invariably and no matter how often I select item #5 "Mesh" and save to ensure that selection sticks, it is not persisted.
I hand-edited the .dtsx file and only then was I able to make this stick. However, if I ever re-save the package this non-persistency pops up again.
Am I doing something wrong here or is this a known defect? As I have several dozen XSD mappings that I want to transfer to tables, hand-editing is not something I relish.
I have a project that consists of a SQL db with an Access front end as the user interface. Here is the structure of the table on which this question is based:
Code Block
create table #IncomeAndExpenseData ( recordID nvarchar(5)NOT NULL, itemID int NOT NULL, itemvalue decimal(18, 2) NULL, monthitemvalue decimal(18, 2) NULL ) The itemvalue field is where the user enters his/her numbers via Access. There is an IncomeAndExpenseCodes table as well which holds item information, including the itemID and entry unit of measure. Some itemIDs have an entry unit of measure of $/mo, while others are entered in terms of $/yr, others in %/yr.
For itemvalues of itemIDs with entry units of measure that are not $/mo a stored procedure performs calculations which converts them into numbers that has a unit of measure of $/mo and updates IncomeAndExpenseData putting these numbers in the monthitemvalue field. This stored procedure is written to only calculate values for monthitemvalue fields which are null in order to avoid recalculating every single row in the table.
If the user edits the itemvalue field there is a trigger on IncomeAndExpenseData which sets the monthitemvalue to null so the stored procedure recalculates the monthitemvalue for the changed rows. However, it appears this trigger is also setting monthitemvalue to null after the stored procedure updates the IncomeAndExpenseData table with the recalculated monthitemvalues, thus wiping out the answers.
How do I write a trigger that sets the monthitemvalue to null only when the user edits the itemvalue field, not when the stored procedure puts the recalculated monthitemvalue into the IncomeAndExpenseData table?
Hi... I have data that i am getting through a dbf file. and i am dumping that data to a sql server... and then taking the data from the sql server after scrubing it i put it into the production database.. right my stored procedure handles a single plan only... but now there may be two or more plans together in the same sql server database which i need to scrub and then update that particular plan already exists or inserts if they dont...
this is my sproc... ALTER PROCEDURE [dbo].[usp_Import_Plan] @ClientId int, @UserId int = NULL, @HistoryId int, @ShowStatus bit = 0-- Indicates whether status messages should be returned during the import.
AS
SET NOCOUNT ON
DECLARE @Count int, @Sproc varchar(50), @Status varchar(200), @TotalCount int
SET @Sproc = OBJECT_NAME(@@ProcId)
SET @Status = 'Updating plan information in Plan table.' UPDATE Statements..Plan SET PlanName = PlanName1, Description = PlanName2 FROM Statements..Plan cp JOIN ( SELECT DISTINCT PlanId, PlanName1, PlanName2 FROM Census ) c ON cp.CPlanId = c.PlanId WHERE cp.ClientId = @ClientId AND ( IsNull(cp.PlanName,'') <> IsNull(c.PlanName1,'') OR IsNull(cp.Description,'') <> IsNull(c.PlanName2,'') )
SET @Count = @@ROWCOUNT IF @Count > 0 BEGIN SET @Status = 'Updated ' + Cast(@Count AS varchar(10)) + ' record(s) in ClientPlan.' END ELSE BEGIN SET @Status = 'No records were updated in Plan.' END
SET @Status = 'Adding plan information to Plan table.' INSERT INTO Statements..Plan ( ClientId, ClientPlanId, UserId, PlanName, Description ) SELECT DISTINCT @ClientId, CPlanId, @UserId, PlanName1, PlanName2 FROM Census WHERE PlanId NOT IN ( SELECT DISTINCT CPlanId FROM Statements..Plan WHERE ClientId = @ClientId AND ClientPlanId IS NOT NULL )
SET @Count = @@ROWCOUNT IF @Count > 0 BEGIN SET @Status = 'Added ' + Cast(@Count AS varchar(10)) + ' record(s) to Plan.' END ELSE BEGIN SET @Status = 'No information was added Plan.' END
SET NOCOUNT OFF
So how do i do multiple inserts and updates using this stored procedure...
I have a web form that collects details on books (as an example), and in that form is a checkboxlist that displays an entry for each potential author in the database (as an example).
The user can obviously tick as many authors as they want to represent Authors of the book. The ticked entries form the entries in the BooksToAuthors table which only has BookID and AuthorID columns.
I have a number of questions:
How do I take what is in the CheckBoxList to the database and how does this relate to Stored Procedures?
Do I fill the checkbox selections into an Array? How do I get these 'many items' to a Stored Procedure that runs a transaction to put the book in and then the many rows in AuthorsToBooks.
What is being passed? Can you pass an array or something to a stored procedure?
As far as performance goes should I avoid using "IN" with update statements?Example:update table_nameset x = 5where y IN (select z from table_name1 where a = b and c = d)If this is terribly inefficient what are the alternatives?Thanks...
I have 3 queries and I need to see if there is a way to combine them since they do the same thing, or if there is a more efficient way I am missing. I run the query below on Table A to find the product first for TableA.Model=1, then for Model=2 and then Model=3. The reason I split it into three queries is I need Model=1 Customers only, then Model=2 only if there isn't a Model=1 Customer, etc.
UPDATE Table1 INNER JOIN TableA ON Table1.Product = TableA.Product SET Table1.Customer = [TableA].[Customer] WHERE (((TableA.Model)="1") AND ((Table1.Customer) Is Null));
okay, so i have about 37 different updates i need to do to a table that is rather large (71million) and has no indexes. i know it's gunna table scan, and honestly, i'm not really worried about that. my question is, is there a way up squeeze all of these updates into one?
here is what i was going to do, but each one will take about an hour to run... (here are 5 of the 37 updates, but they are all basically the same concept)
update t1 set books_music='' from mailorder t1 where books_music is null update t1 set Car_Buff='' from mailorder t1 where Car_Buff is null update t1 set Childrens_Items_Buyers='' from mailorder t1 where Childrens_Items_Buyers is null update t1 set Computer='' from mailorder t1 where Computer is null update t1 set Crafts_Sewing='' from mailorder t1 where Crafts_Sewing is null
in FoxSlow (foxpro) i could just do something like this:
do while !eof() replace books_music with '' for books_music=null replace car_buff with '' for car_buff=null skip enddo
I have an SQL 2005 STD server, full install, that we use to run SQL and reporting services. When I run Microsoft Update, it shows updates for Visual Studio 2005 SP1 and MS Office 2003 SP3. The server does have MS Office 2003 web components installed, installed as part of the initial SQL server install, but not the MS Office suite software. It also has loaded the reporting services version of Visual studio 2005 that installs with reporting services, but not the entire version of Visual Studio 2005. Should I install these service pack updates? Is there any benifit?
Hi! I 'd like to update the database..I 'd like to update the same field, the first update would set all to 'No' and the second update woyuld set specific records to 'Yes': 1) "Update tblDept SET IsTop ='No' 2) "Update tblDept SET IsTop = 'Yes' WHERE id = 200 " Cheers!
Hi, I just started learning ASP.NET this week and have watched a mountain of videos from this website which has helped me alot However I have been stuck on a problem for 2 days now. I have created an SQL database with the Following 2 tables: USERS COMPUTERSUserid Computerid firstname Useridsecondname Manufacturer Model I have made a relationship between the 2 tables. I then created a dataset with the following query:SELECT COMPUTERS.Computer_ID, COMPUTERS.Manufacturer, COMPUTERS.Model, USERS.First_Name, USERS.Last_Name FROM COMPUTERS INNER JOIN USERS ON COMPUTERS.User_ID = USERS.User_ID I however do not get the option in my grid view when i output this data, to UPDATE. The best i have found from google is that i need to use subqueries and not innerjoins but i just cant seem to get my head around them, please help as i feel my head may just explode if i think about this or try any more ways to get this to work :D
I am using MSDE Release A was wondering what is the best way to find out when there are critical updates for MSDE. Is there a notification service for MSDE like there is for Windows?
I'm pretty new to T-SQL and have an *easy* problem, for you experts, that I can't get seem to get solved. I'd like to loop through a list of items in TABLE "Items". I then want to use that list to loop through and SUM SALES and QTY for each item from a TABLE called "Shipments". As I loop through each item, I want to UPDATE the "Items" table with the Summary data. So, logically I'd do something like this:
SELECT item_no FROM Items
BEGIN
SELECT SUM(sales) AS Total_Sales, SUM(qty) AS Total_Qty WHERE item_no=@item_no
UPDATE Items SET Sales=@Total_Sales, Qty=@Total_Qty WHERE item_no=@item_no
END
I've tried somewhat successfully to use cursors to create my loop query, but I cannot seem to get the SELECT and UPDATE correct in the loop itself. Can anyone steer me in the right direction (or better yet, provide a solution)?
I have a number of columns with predefined character length but user can input more from gui. i want to trucncate automatically to the desired length and insert or update the database right now it does not allow me to update , or insert the values can i do it and how this is urgent
Hi I am using SQL Server 6.5 as my backend for a VB6 Application. This is a OLTP kind of package that I have developed with about 600 Users. I use BeginTran and CommitTran between my Update/Insert Queries in my VB Application. My database has grown considerably and all of a sudden, all the CommitTran failed. None of the records were saved.
When I stopped and restarted my SQL Server, the operation became normal all Update/Insert queries for the same Application succeeded. (Note that I have not stopped my Server for the last 45 days).
WHY DOES THIS HAPPEN? HOW DO I AVOID THIS IN FUTURE... PLEASE HELP Thanks Jivee
How would I check a db to see if a record exists and if it does then do an update but if it doesn't do an insert. so: Set oRS = objDB.Execute("select loginid from computers where loginid='" & WshNetwork.Computername & WshNetwork.UserName & "'")
If that returns something then do an update but if not then do the insert.
I recently inherited multiple databases for a research study. These databases use an Access front end with the tables stored on SQL Server. Currently, there is a folder for each database on a network drive. I make changes to the front ends (forms, reports etc.) in a development version of the dbs, test them, have a user test them, and then import them to the production front end.
Each user has a copy of the front end on their 'C:' drive. The previous developer put together a separate VB app that copies the changed mdb files from the network drive to their 'C:' drive. This doesn't seem like the best solution to me but I haven't come up with a better one. I would appreciate any input.
I have two sql 2000 server tables one is active and one is terminated (I inherited this db) and I was thinking of having the active table automatically update the terminated table when an employee is terminated. Access is at the front end and in the form of the active table theres a drop down text box that has the employement status(active, terminated, on leave...etc) so when the status turns into terminated i want the acitve table to send those records to the terminated table, ( the data in both tables are not exactly the same). looking into a trigger or stored procedure. This is the first time I've done this so I'm doing some reasearch on how to handle it. Any suggestions
well as I get further into this project of automatic updates I'm fining more and more barriers. The combo list box which indicates whether the employee is terminated or active might be a problem with sql since you cant create a Row source and a Row source type in a sql table. that combo box exsist in the properties of the form. The Row Source Type is a Value List. Shoot :(
Hello, I am working on a web app and am at a point where I have multiple rows in my GUI that need to be sent to and saved in SQL Server when the user presses Save. We want to pass the rows to a working table and the do a begin tran, move rows from working table to permanent tables with set processing, commit tran. The debate we are having is how to get the data to the work table. We can do individual inserts to the work table 1 round trip for each row (could be 100's of rows) or concatenate all rows into 1 long (up to 8K at a time) string and make one call sending the long string and then parse it into the work table in SQL Server. Trying to consider network usage and overhead by sending many short items vs 1 long item and cpu overhead for many inserts vs string manipulation and parsing. Suggestions?
Hello guys, I am askng for any help ...am trying to get this SQL language.
I want to provide an SQL query to set all the priority to 1 for all customers that have all their orders being for a product with importance of 100. -------------------------------------------------------------------- There are three tables in the database: Customer, Product and Orders.
The Customer table has three column: Customer_id (PK), priority, Address.
The Orders table has three colums as well: Order_id (PK), Customer_id (FK), Product_id (FK)
The Product table has three columns as well: Product_id(PK), Product_name, Importance.
So the order table is connected to both the product and the customers table by respective foreign key.
i want to provide an SQL query to set all the priority to 1 for all customers that have all their orders being for a product with importance of 100. Thank you in advance. Shcema---------------------------------------------------------------- There are three tables in the database: Customer, Product and Orders.
The Customer table has three column: Customer_id (PK), priority, Address.
The Orders table has three colums as well: Order_id (PK), Customer_id (FK), Product_id (FK)
The Product table has three columns as well: Product_id(PK), Product_name, Importance.
So the order table is connected to both the product and the customers table by respective foreign key.
Hi all, working as an intern at MS for a year (SDET) and I've been asked to find a way of transfering items from one sharepoint lists to another (intranet to extranet) without access to the underlying SQL servers'. So what I've done is use an access databaes as a bridge creating active views of the two sharepoint lists. I wrote a C# console application that executed some SQL on the bridge to copy data between the two sharepoint lists, this was great could handle updates, deletions and additions of new items. Now I've been asked to make this update process 2 way on a certain field (status) I've been trying for a couple of days now and I'm having no joy. The method I was using (polling) allowed for the first couple of status changes (used 2 statements) but then wrote back the wrong way see below for example SQL:
Extranet owned statuses: Declined, Approved, Not Started, In-Progress, Completed. Intranet owned statuses: Needs Review, MS Declined
UPDATE intranet JOIN extranet ON (blahblah=blahblah)SEt intranet.status=extranet.status WHERE extranet.status IN (all extranet owned statuses);
UPDATE extranet JOIN intranet ON (blahblah=blahblah)SEt extranet.status=intranet.status WHERE intranet.status IN (all intranet owned statuses);
Can anybody tell me where I'm going wrong and offer any suggestions?
I have 2 databases with the same tables, etc. I'm trying to copy one column from the same table from db1 to db2. Here is my sql:
update db1.dbo.table1 set db1.dbo.table1.memtype = db2.dbo.table1.memtype where db1.dbo.table1.memid = db2.dbo.table1.memid
When I parse it, it completes successfully but when I execute it, I get this error message: The multi-part identifier "db2.dbo.table1.memid" could not be bound.
Can someone help me with the following? I need to update all columns in a table where the value is zero with NULL where the row id's (primary keys) are between two values. I was hoping that I could do this without "hard coding" the column names as there are many columns. i.e:
UPDATE TABLENAME FOR ALL COLUMNS SET VALUE = NULL WHERE VALUE = 0 FOR ROWS BETWEEN n and m
Can we use transactional replication with updatable subscriptions when we have more updates and transactional changes at the Subscriber side??
We are using SQL Server 2005 SP1. We will be using 6 publications and 4 subscriptions for each publications, so totally 24 subscriptions. Both the Publisher and the Distributor is the same server. It is set to run in continuous mode. Type : Pull Subscriptions.
