RDA Pull Method Not Creating Tables/inserting Data
Aug 7, 2006
I can't see what is going on, this is the situation:
I call the Pull method, specify the table to be affected, the query to be used, the connection string to the remote SQL server, the tracking options (On) and the Error table. The pull method executes with no errors however, no table is ever created. I don't know why, here's what I have done so far:
I read the SQL BOOKS ONLINE help on preparing RDA, I set up the IIS virtual directory for anonymous access and on the connection string I send in the user name and password for the SQL server, I went into the SQL Server and grated access to the user name to the database that I am going to access and I made the user a db_owner.
So, according to SQL BOOKS ONLINE I have everything right however, it won't populate, so right now I am open to suggestions on how to get this to work, heres the code:
------------------------------------------------------------------------------------------------------------------
string rdaOleDbConnectString = "Provider=SQLOLEDB;Data Source=<Server>;Initial Catalog=<DB>; User Id=<User>;Password=<Password>"; (it's not exactly like this, but in it has the proper values)
string connectionString = "Data Source="\Program Files\client\db\MobileDB.sdf"";
SqlCeRemoteDataAccess rda = new SqlCeRemoteDataAccess("http://10.1.1.206/mobile/sqlcesa30.dll",
connectionString);
IList _tableNames = new ArrayList();
IList _queries = new ArrayList();
############
Code that prepares tables and queries
############
I have created a table with the following columns...Date(datetime),Actual (Int),Planned (Int)I need to insert weekending dates starting from 23/04/04 loopingthru'for the next 52weeks automatically into the date column.Then in the actual and planned colums, I need to insert a count ofsome records in the table.I will appreciate help on a SQL query to achieve this!
I developed a console application that will continually check a messagequeue to watch for any incoming data that needs to be inserted into MSSQL database.What would be a low-cost method I could use inside this consoleapplication to make sure the MS SQL database is operational before Iperform the insert?
I am new to SQL, as old as it is. I am not new to programming Inormally just use Access.I have two tables for a little project manager I made. After updates Isent an email to the user. I need to populate the user based on the"Assigned To" field I use, but I only log the username and not theemail address. Is there a way to associate the "Assigned To" to theUser's account in the "User" table so that I can pull thier emailaddress through code?ThanksChuck
Hi, I am trying to pull e-mail addresses of certain users from a list of all the users in our company. I have two tables. One contains a single column of only the users I need. The other table contains every user and their e-mail address. Could someone tell me how to pull this data from these two tables. I am new to SQL Query and have been trying to figure out these JOIN statements, but nothing I am doing seems to work.
I am trying to create a query that will pull a variety of data from three different tables. I've had to join four tables because one set of data is in a completely different table.
I am expecting one row of data but instead I get 4 rows. I suspect this is because I am joining a table indirectly. Here is the code:
There is only one applicable row in SDH, but four rows appear when I run the query. All four rows are identical except for the GL.GLCode column, which lists the three GL codes associated with the DeductionItems table. The fourth row is a duplicate of the third.
but when i form my statement with an inner join, i miss records, with the outer, i get a ton of records (more than both tables combined). i have been working with sql for about week now and can do simple queries but this is beyond me at this point. any help what so ever would be greatly appreciated.
I have installed SQL Server 2005 Enterprise x64 on Win 2003 Enterprise 64x and I am getting the following error when call the Pull() method from my pocket application:
28022 -2147024809 An error has occurred on the computer running IIS. [,,,,,]
I have three tables EmpIDs,EmpRoles and LatestRoles. I need to write a select Query to get roles of all employees present in EmpIDs table by referring EmpRoles and LatestRoles.
Where I stuck : The condition is first look into table EmpRoles and if it has more than one entry for a particular Employee ID than only need to get the Role from LatestRoles other wise consider the role from EmpRoles .
Employee ID 2 is having two roles defined in EmpRoles so for EmpID 2 need to fetch Role from LatestRoles table and for remaining ID's need to fetch from EmpRoles .
My Final Output of select query should be like below.
Has there been any fix for this bug? The workaround in the KB article did not work for me and I cannot run my entire application from the Windows directory even if it did.
Please help.
Original Thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PageIndex=1&SiteID=1&PostID=274359
Workaround KB article: (I cannot run my app from Window directory) http://support.microsoft.com/kb/920272
Hi, I've got the error message below: "Error 80040E2F. A duplicate value cannot be inserted into a unique index [Table name=_SysRDASubscriptions, Constraint name=c_LocalTableName]"
I'm using SQL Server 2005 Compact Edition and still cannot solve the problem although the KB article (920272) said it is fixed in V3.1.
What I need to do is to drop a table and then Pull it again to the PDA but it gives me that error message during pull method. However, I can use the same process to drop and pull other tables and works fine. Will that be the problem related to primary key or to the data of the table?
I have written following SQL query, this creates temporary table, inserts rows into it. I need to create VIEW "vw_NumberOfAttachments" in the database. I initially created table using "CREATE TABLE" but then i got error as VIEW can not be filled by temporary table. Hence I am using DECLARE TABLE -------------------------------- SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE sp_GetViewNumberOfAttachments -- Add the parameters for the stored procedure here
AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON;
DECLARE @emailMessageID bigint DECLARE @metaDataStorageID bigint DECLARE @numberOfAttachments int
DECLARE ATTACHMENT_CURSOR CURSOR FOR SELECT emailMessageID, metaDataStorageID FROM ppaEmailMessage WHERE hasAttachments='true'
OPEN ATTACHMENT_CURSOR FETCH NEXT FROM ATTACHMENT_CURSOR INTO @emailMessageID, @metaDataStorageID
WHILE @@FETCH_STATUS = 0 BEGIN -- here the table name need to get dynamically the name of the attachment table -- for a moment it is written as ppaMsOfficeDoc, but that should change dynamically set @numberOfAttachments = (SELECT count(*) FROM ppaMsOfficeDoc WHERE metaDataStorageID = @metaDataStorageID)
INSERT INTO @AttachmentDetails(emailMessageID, metaDataStorageID, numberOfAttachments) VALUES (@emailMessageID, @metaDataStorageID, @numberOfAttachments)
FETCH NEXT FROM ATTACHMENT_CURSOR INTO @emailMessageID, @metaDataStorageID END
CLOSE ATTACHMENT_CURSOR DEALLOCATE ATTACHMENT_CURSOR
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'vw_NumberOfAttachments') DROP VIEW vw_NumberOfAttachments GO CREATE VIEW vw_NumberOfAttachments AS SELECT @AttachmentDetails.emailMessageID, @AttachmentDetails.metaDataStorageID, @AttachmentDetails.numberOfAttachments FROM @AttachmentDetails GO END GO
----------------------
I am getting following errors: ----------- Msg 102, Level 15, State 1, Procedure sp_GetViewNumberOfAttachments, Line 57 Incorrect syntax near 'vw_NumberOfAttachments'. Msg 137, Level 15, State 2, Procedure vw_NumberOfAttachments, Line 3 Must declare the scalar variable "@AttachmentDetails". Msg 102, Level 15, State 1, Line 2 Incorrect syntax near 'END'. ----------- Can anyone please suggest whats wrong in there? Many thanks
i want to insert data in database(sql server2000). there are some attributes in database which are present in two/three tables and these tables are related. e.g. when i create new user; it's userId and name should be inserted in 2 tables. how can i do it? i think; it should be implemented through transaction statements but not much aware about these
When you import data using DTS into a table that has triggers - do the triggers fire off if there are triggers for on insert or on after insert?Thanks,--Micah
i created an SSIS package to look for data in a table on another system and compare it with the table i have in system 2, if there are any changes to system 1 then it must apply them to system 2
My Problem:
It scans through my table and finds all the correct records to insert, but when it has to insert the new data into System 2 table i keep on getting violation and contraint errors because of the primary key and foreign key constraints. how can i get around this, or does anyone have an alternative solution for me.
Total Specification Requirements:
i have 2 systems both running SQL Server. everytime data gets updates in system 1, the same change needs to be made in system 2. The databases and tables are identical.
Hello everyone, My web application uses SQL Server database and I am connecting via standard SqlConnection object and running stored procedures using SqlCommand object. In one of my page, I have data coming from 2 different tables. Now , data from 1 table comes as only single record. But from other table it comes as multiple records. Meaning, data that I read as 1 record, goes to different textbox and dropdown controls on page. Data that comes in multiple rows, I am binding that data with DataGrid. Now, in aspx page data from both table can be updated and on aspx page I only need to provide a single save button. Now, I am not sure how to save/insert/update a single row in 1 table and multiple rows in another table in 1 transaction. I thought of stored procedure. But I don't think its straightforward with stored procedures since table with multiple records, I am not sure how to pass all the records in stored procedure's arguments.Is there any way that I can control whole transaction in ASP .NET? Thanks,Ujjaval
Hey guys up until now i've only inserted data into a single table. Now I have a form that collects information over a span of three forms. Each form has a table related to it and these three tables are related to each other.
What I want to know is: 1)How do you go abouts inserting data into multiple related tables that have constraints on them?
2)Would you use a stored procedure in an instance like this?
3)At what stage would you execute the sql queries. I assume you do this once you have collected all the required information as opposed to: Enter info into form1, submit form1 data to database... enter info into form2, submit form2 data into database etc
Any help would be greatly appreciated!
Say for instance I have three related tables.
table1 ------ tbl1_id tbl1_data1 tbl1_data2
table2 ------ tbl2_id tbl2_data1 tbl2_data2
table3 ------ tbl3_id tbl3_data1 tbl3_data2
table1 has a one-to-many relationship with table2 table3 has a one-to-one relationship with table2
Hi all,we are developing an internal application that involves "Timesheets","Support" and "Project Management".Imagine that there are 3 different databases for the above scenario,under SQL Server 2000.My task is to create one or a few table triggers forINSERT/UPDATE/DELETE operations. For example:- if a row is added in Table A of "Timesheets" database, then Table Bof "Project Management" needs to be updated.The concept is clear i think. The question is how we do the above.Note that I am a new progremmer to SQL Server (I have been dealingwith Oracle so far), and I don't know how we programmatically connectto different database within a trigger, how do we check thepriviledges etc.Can someone help me?ThanksChristos Andronicou
I have 3 tables (accnt, jobcost, and servic15). all with the same fields (code, jno, ven, date). I need to insert the data from these tables into another table called dummy with the same fields, in one statement or query.
i am trying to create a pull subscription to my publication using T-SQL. The pull subcription is created successfully but the problem is that data is not merged correctly. I mean when i run the agent ( despite of having data in subscription / publication ) the data is not merged and i get "No Data Needed To Be Merged" message.
i have such a error in my sql server db i examined arabic_ci_as and SQL_Latin1_General_CP1256_CI_AS but in my web pages that uses utf-8 codepage that retrieves data using ado and asp scripting the ouput or inserted that dispalyed in both of query analyzer and Enterprise manager replaces or display '?????' for characters . i am using nchar and nvarchar and ntext
I am trying to insert data into two tables with a SSIS package. One table has a foreign key relationship to the other table's primary key. When I try to run the package, the package will just seems to hang up in bids. I have found two ways around the issue but I don't like either approach. Is there a way to set which table gets insert first?
If I uncheck the check constraints option on the child table, the package will run very quickly but this option alters the child table and basically disables the constraint. I don't like this option because it is altering the database.
The second approach is to set the commit level on both tables to say 10,000 and make sure that the multicast component has the first output path moved to the parent table. I don't like this option because I am not sure if the records are backed out if the package should abend after records have been committed.
I am trying to insert data into two different tables. I will insert into Table 2 based on an id I get from the Select Statement from Table1. Insert Table1(Title,Description,Link,Whatever)Values(@title,@description,@link,@Whatever)Select WhateverID from Table1 Where Description = @DescriptionInsert into Table2(CategoryID,WhateverID)Values(@CategoryID,@WhateverID) This statement is not working. What should I do? Should I use a stored procedure?? I am writing in C#. Can someone please help!!
I have a text file which has rows 7 rows.I want to insert the data into SQL table using ssis In text file we have a column which has values as Y or N...I wanted to take only those rows which are Y...But we have only 6 rows in SQL table.It does not have the column with Y or N.
Hi, it is few days I posted here my question, but received no answer. Maybe the problem is just my problem, maybe I put my question some strange way. OK, I try to put it again, more simply. I have few textboxes, their values I need to transport to database. I set SqlDataSource, parameters... and used SqlDataSource.Insert() method. I got NULL values in the database's record. So I tried find problem by using Microsoft's sample code from address http://msdn2.microsoft.com/en-us/library/system.web.ui.webcontrols.sqldatasource.insert.aspx. After some changes I tried that code and everything went well, data were put into database. Next step was to separate code beside and structure of page to two separate files followed by new test. Good again, data were delivered to database. Next step: to use MasterPage, very simple, just with one ContentPlaceHolder. After this step the program stoped to deliver data to database and delivers only NULLs to new record. It is exactly the same problem which I have found in my application. The functionless code is here:http://forums.asp.net/thread/1437716.aspx I cannot find any answer this problem on forums worldwide. I cannot believe it is only my problem. I compared html code of two generated pages - with maserPage and without. There are differentions in code in ids' of input fields generated by NET.Framework:Without masterpage:<input name="NazevBox" type="text" id="NazevBox" /><span id="RequiredFieldValidator1" style='color:Red;visibility:hidden;'>Please enter a company name.</span><p><input name="CodeBox" type="text" id="CodeBox" /><span id="RequiredFieldValidator2" style='color:Red;visibility:hidden;'>Please enter a phone number.</span><p><input type="submit" name="Button1" value="Insert New Shipper" onclick="javascript:WebForm_DoPostBackWithOptions(new WebForm_PostBackOptions("Button1", "", true, "", "", false, false))" id="Button1" /> With masterpage:<input name="ctl00$Obsah$NazevBox" type="text" id="ctl00_Obsah_NazevBox" /><span id="ctl00_Obsah_RequiredFieldValidator1" style='color:Red;visibility:hidden;'>Please enter a company name.</span><p><input name="ctl00$Obsah$CodeBox" type="text" id="ctl00_Obsah_CodeBox" /><span id="ctl00_Obsah_RequiredFieldValidator2" style='color:Red;visibility:hidden;'>Please enter a phone number.</span><p><input type="submit" name="ctl00$Obsah$Button1" value="Insert New Shipper" onclick="javascript:WebForm_DoPostBackWithOptions(new WebForm_PostBackOptions("ctl00$Obsah$Button1", "", true, "", "", false, false))" id="ctl00_Obsah_Button1" />In second case ids' of input fields have different names, but I hope it is inner business of NET.Framework.There must be something I haven't noticed, maybe NET's bug, maybe my own. Thanks for any suggestion.
This question is regarding a "helper app" I'm building to go along with my ASP.NET appplication. It will be inserting/updating records in the database as a nightly process. It is a Windows application built in VB.net.
I have a table which should always only have one of each type of record in it. This table on average will have between 100k and 500k records.
Which operation would be faster and less strain on the server?
a. Use a "if exists" and see if a record of this type already exists, if it does, update it, if not, insert the new one.
b. Unconditionally issue a delete for the record I'm about to insert, then insert the new one.
c. Create a trigger that will delete the old record if a new one is inserted?
Writing to tables created by regular users on MSSQL2005
I have users creating tables through an application, I gave them ddl_admin, datareader, datawriter. They can create tables but cannot insert/update data (to their own tables), I cannot insert data either using Access or any other application to those tables created by them (under dbo schema) Is there something I am missing with permissions? Thank you very much
I am able to use user data types for creating local temporally tables in store procedures, but I receive an error at run-time about the user data type.
Msg 2715, Level 16, State 7, Procedure SP_SAMPLE_TEST, Line 4 Column, parameter, or variable #1: Cannot find data type D_ORDER_NUMBER.
The same user data type is used as parameters in several other store procedures, and they work perfect. It is also used in several table definitions.
What am I doing wrong?
Thanks in advance to any one who can help me with this problem.
I want to pull ALL users from Table1 and include information that person may have in Table2. As of now, it is only including users that have something in Table2. How do I include everyone? Thanks
