.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

transfer sql server objects - table list made dynamic

Posted By:      Posted Date: December 04, 2010    Points: 0   Category :Sql Server

i've seen several links when searching for this problem but nothing definitely solves this...or if it does it seems to be among people who seem to know scripting really well and can figure stuff out. my problem is as follows.

i have a remote server that i connect to (need credentials and cannot use windows authentication), and from this box i need to extract certain tables from certain databases. i tried a linked server and its ridiculously slow. the other method was to build it via ssis (which needs a mapping structure for each table and cannot be made dynamic). lastly i came across this. this works really well, but i'm amazed that I cant use a variable for the table name. I've tried hardcoding the name, using a string and object variables but no go.

so i see that people have created a custom transfer objects with vb.net and c#. can someone please give me the layman's code block to enter into the script task? i've tried following instructions and end up with errors. As i paste the code some segments remain underlined and when you hover your mouse you see tooltips like Type StringCollection not defined. So maybe i'm missing some libraries or dll's that are supposed to go in and i have no clue which ones. i really like using the expressions and have iterated object variables and simple variables alike and its ridiculous that this is has room to be con

View Complete Post

More Related Resource Links

sql server query get a list of a table

Hi guys, I've a hard query to manage ... I need to get for a report the number of required people to an appointment, but this query works but just for the regarding person related to the appointment. SELECT CRMAF_FilteredContact.OwnerID ,CRMAF_FilteredContact.OwnerIDname ,SU.businessunitidname ,SU.eu_reporthubname -- ,SU.eu_reportcountryidname ,CRMAF_FilteredContact.ContactID ,CRMAF_FilteredContact.FullName ,CRMAF_FilteredContact.invoke_tiername ,CRMAF_FilteredAppointment.ActivityID ,CRMAF_FilteredAppointment.RegardingObjectID ,CRMAF_FilteredAppointment.Subject ,AP.ActivityPartyID ,AP.PartyID ,case when CRMAF_FilteredAppointment.ActivityID is null then null else CRMAF_FilteredContact.ContactID end as s_contactsvisited -- # of contactsvisited FROM FilteredContact CRMAF_FilteredContact JOIN FilteredSystemUser SU ON CRMAF_FilteredContact.ownerid = SU.SystemUserID <strong> LEFT JOIN FilteredActivityParty AP ON ( AP.PartyID = CRMAF_FilteredContact.ContactID or AP.PartyID = CRMAF_FilteredContact.OriginatingLeadId) AND AP.participationtypemask = '8' -- regarding</strong> LEFT JOIN FilteredAppointment CRMAF_FilteredAppointment ON AP.ActivityID = CRMAF_FilteredAppointment.ActivityID AND CRMAF_FilteredAppointment.ownerid = SU.SystemUserID AND CRMAF_FilteredAppointment.stat

Transfer SQL Server Objects reporting success but nothing transferred

I'm trying to "clone" the structure of one database to a new blank database. I can copy the tables over using the Transfer Sql server object without any problems. When I create a separate task "Transfer Sql server object" for the views (CopyAllViews set to true or picking individual views), it always reports success however none of the views were copied. The same problem occurs when I attempt to copy stored procedures. Anyone have an idea of what's going wrong?  

Transfer SQL Server Objects Task

I'm trying to use the Transfer SQL Server Objects Task to copy database users and database roles from one database to another. The problem is that some of the users already exists in the destination database. Is there a setting or expression or error handler that will allow me to specify to only copy the objects that don't already exist? I can ignore the failure but I won't know if it's really a copy failure or a duplicate. I read the roll-your-own blog referenced in a similar post (http://blogs.msdn.com/b/mattm/archive/2007/04/18/roll-your-own-transfer-sql-server-objects-task.aspx) but I don't know if a property exists for the transfer object with will allow me to indicate that I want to copy users that aren't already in the destination. Has anyone successfully done this? It seems like it would be a simple task.

Does Transfer SQL Server Objects task transfer objects created in the source AFTER the package has b

I created an SSIS package which contains a Transfer SQL Server Objects Task. I configured this task to copy table objects, stored procedures, and object permissions to the destination. Between the time I created my SSIS package and the time it was run, someone created a new table object in the source, and changed permissions on a stored proc in the source. My question is this, at the time the SSIS package is created, behind the scenes, does SSIS create a list of objects to transfer? I had hoped that it creates the list of what specific objects (of the pre-defined type) to transfer at runtime so that whatever changes were made to the source database would be included at runtime.

Is there any way to get the Transfer SQL Server Objects Task to not throw error if an object already


I've asked this before but never got an answer. Is there a way to configure the Transfer SQL Server Objects Task so that it will only transfer objects that don't already exist in the destination? Or to skip over objects that already exist?

I do not want to "roll my own". I want to use the task in order to save time.

Why does BI "Transfer SQL Server Objects Task" error occur?


I'm using SSIS to copy all tables and the data from server1 to server2.  Database names are same on both source and destination servers. dbo.MyTable definately exists in the source so I don't understand this error message:

 [Transfer SQL Server Objects Task] Error: Execution failed with the following error: "ERROR : errorCode=-1071636471 description=SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80040E37. An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x80040E37  Description: "Invalid object name 'dbo.MyTable'.".  helpFile=dtsmsg100.rll helpContext=0 idofInterfaceWithError={C81DFC5A-3B22-4DA3-BD3B-10BF861A7F9C}".

 There's nothing fancy about MyTable:

CREATE TABLE [dbo].[MyTable](

[MyId] [

"Transfer SQL Server Objects Task" doesn't always copy the data when it's configured to do so

Can someone enlighten me on why this is happening? I have three instances of the "Transfer SQL Server Objects Task" in an SSIS package, each instance copies tables and data from a different source database. Two of the three copy the data without a problem, but one of them does not copy the data even though I have set the CopyData property = true.  And it does not give an error. The status at the end reports Success. Why is this happening?

Ready made List Types provided by SharePoint

SharePoint provides some out of the box List Types. Some of the most used ready made list types that SharePoint provides currently are listed below:

HttpContext.Items and Server.Transfer/Execute

The problem was basically that when a Server.Transfer call was applied the script container (and also various ClientScriptProxy script embedding routines) would potentially fail to load up the specified scripts.

Temporary tables in SQL Server vs. table variables

When writing T-SQL code, you often need a table in which to store data temporarily when it comes time to execute that code. You have four table options: normal tables, local temporary tables, global temporary tables and table variables. I'll discuss the differences between using temporary tables in SQL Server versus table variables.

How to check for null/empty strings on dynamic objects?


What is the proper way to check for null or empty strings on dynamic objects? This is the only solution I've found so far:

if (admin.PasswordHash.GetType() == typeof(DBNull) || admin.PasswordHash == string.Empty)

If the field is null, the object returned is DBNull.Value, but if the field is empty string, it's return as a System.String object. This makes it impossible to do regular "admin.PasswordHash == DBNull.Value" check, as that will fail whenever the field has been set to empty string. Is my example the right (and only) way to go?

Bulk Copy From Datatable To SQL Server Table


 I have read the data of a excle file and captured the data into the dataset to a datatable, now that data is to be inserted into a SQL Server table using bulk copy option. I am using -

 public bool BulkEnterData(DataTable dt, string tblName)
        SqlBulkCopy bulk = new SqlBulkCopy(con);
        bulk.DestinationTableName = tblName;

        return true;

ERROR Getting-
A transport-level error has occurred when receiving results from the server. (provider: Shared Memory Provider, error: 0 - The pipe has been ended.) 

How to resolve the above problem or any other solution is available.


A connection could not be made to the report server http://localhost/ReportServer.



  • I created a Report using Business Intelligence Studio. Its working working well, i can able to view records. But when i try to deploy the report an error occurs specifying that "A connection could not be made to the report server http://localhost/ReportServer. (Microsoft Report Designer)".
  • I open the IIS 7 and find out virtual directories "Reports" and "ReportServer" are available.
  • I check on the browser and type "http://localhost/reports".  I find the browser direct to this url: http://localhost/reports/Pages/Folder.aspx and find error specifying the following one:

         Error:The report server is not responding. Verify that the report server is running and can be accessed from this computer.

  • when i type http://localhost/reportmanager, i found the error:

    HTTP Error 404.0 - Not Found

    The resource you are looking for has been removed, had its name changed, or is temporarily unavailable.


Cutting Edge: Pros and Cons of Data Transfer Objects


After a brief refresher on procedural and object based patterns for organizing the business logic layer, the author focuses on data transfer objects and the impact they have on the development of the software project.

Dino Esposito

MSDN Magazine August 2009

Under the Table: Programming with FileStreams in SQL Server 2008


There's always been disagreement about whether large blobs, such as document and multimedia items, should be stored in the database or file system. In SQL Server 2008 you don't have to choose; filestream storage provides the best of both approaches.

Bob Beauchemin

MSDN Magazine May 2009

Under The Table: Spatial Data Support In SQL Server 2008


New spatial data support in SQL Server 2008 opens the door to mapping and querying geometric and geographic data, allowing you to build exciting new applications.

Bob Beauchemin

MSDN Magazine February 2009

Data Points: SQL Server Management Objects


SQL Server Management Objects offer developers a robust toolset for backing up and restoring databases, and issuing DDL commands, as John Papa explains.

John Papa

MSDN Magazine June 2007

ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend