How To Get Hostname Via SQL Command?

May 21, 2004

Hi!

Can something tell me how I can obtain the machine name (either IP address or DNS name) on which SQL Server is running via a SQL query?

select @@version returns a lot of useful information, however it doesn't return the host name.

thanks in advance
- Garry

View 8 Replies


ADVERTISEMENT

How To Find Client's Hostname Using RDP From MSSQL Command.

Apr 7, 2008

Hi,
Can anyone help, how to find client's hostname using RDP from MSSQL command.
In simple words,
I am connecting to Remote desktop and using MSSQL server management studio in the remote desktop. And when I run select host_name(), it returns the RDP machine name. However, I wish to see my own computer name.
Thanks in advance.
susuown

View 3 Replies View Related

SQL Server 2008 :: How To Append Hostname / Computername To Text File Using Windows Batch Command

Mar 9, 2015

How to append an hostname/computername to a text file using windows batch command?

View 1 Replies View Related

How To Get Hostname

Sep 3, 2004

We are using sql server 2000

Problem:
========
A few records in a particular table are getting updated with incorrect values everyday somehow by some process. No one is manually changing these records.
Every morning I query the database and fix these records. But somehow they again get set to the incorrect values.

So I want to figure out how this is happening. I'm thinking of creating an update trigger on that table. And whenever this update trigger is fired, check the modified record's id and if it is the problematic record, then get the hostname, username, processid which is doing this update.

Can someone give me ideas on how to get the hostname, username.

Any other ideas are also appreciated

Thanks in Advance!

View 2 Replies View Related

Hostname

Sep 27, 2001

Hi everyone,

ON SQL 7.0

I want to capture hostname of the client computer making changes to a table on the server. I tried to use sysprocesses table, but the max(last_batch) column is giving the datetime and hostname of previously ran query. I need to find the hostname of the client who is making change at that instant of time.

Can anyone help me.

Thanks

sekhar

View 4 Replies View Related

Hostname

Jun 18, 2008

Hi,

Question:
How do I get the hostname from the instance name please?

My present design at present is something as follows:

We have several sql server 2005 machines
i.e.
ServerSQLA, ServerSQLB, etc....

Each machine may have one or more instances,
i.e.
ServerSQLAinstace1
ServerSQLAinstace2, etc...

The server name in this case is ServerSQLA

Instance naming and node convention is different for clusters

i.e.
C_ServerSQLAinstance1
C_ServerSQLAinstance2

on the node(s):
N_ServerSQLA or N_ServerSQLB


Thanks

View 6 Replies View Related

Use Of Hostname

Sep 9, 2007

Hello

Can anyone explain how HOSTNAME works?

I have problem with identitys when subscriptions uploads new inserted data, I need somehow create a partitions for each user. All user need exactly the same data (now 10 user but in future about 100 users).

In the database is 116 tables and still growing. Because this is a project i working with.

My system is a one Server, one publication and server is acting as its own distibutor, and multi users

I have really problems with conflicts plz somebody help

View 1 Replies View Related

HostName And ReinitializeSubscription

Apr 28, 2006

Hello all,

I'm having trouble with my Merge Replication setup in regards to changing the HostName property of the SqlCeReplication object. As we all know, if you modify the HostName value, you must then call the ReinitializeSubscription(true) method before calling Synchronize(). Unfortunately, this isn't working for me. Following this sequence of steps consistently results in error messages saying that I need to either call ReinitializeSubscription, or change my HostName back to it's original value. Has anyone ever experienced this problem?

Regards,

Rob Tiffany

View 5 Replies View Related

SQL Server Name Equil To Hostname

Jan 19, 2008

Can someone explain me, if it's possible to set my SQL servername equal to the hostname of the server ?
 Of the moment, i have to log in with : WS003/sqlexpress
But I want to log in like this : WS003
 
regards

View 4 Replies View Related

Incorrect Hostname In Sysprocesses

Jun 27, 2000

On SQL Server 6.5, we tried to track down the owner of a process,which was hogging the CPU. Using Currently Activity Detail, we went to the hostname that seemed to be the culprit, but the users had not accessed SQL Server in a few days. Where is SQL Server get the hostname from? We are on an NT4.0 network, using DHCP.

View 1 Replies View Related

DB Hostname, Username, Password Etc

Jun 25, 2007

I'm trying to get into an installation of v.6.5 to use with anotherserver and I need the following information:Database hostnameDatabase nameDatabase usernaneDatabase passwordIs this information available anyplace within the installation files?I'm not asked for uname and pwd to get into the database.Thanks!

View 1 Replies View Related

Can I Use A Hostname On Registering The Server With TCPIP?

Jan 7, 1999

Hi everyone,

my SQL is running TCPIP and everytime I register the server from a client, I have to enter in the IP address. Can I use a hostname to map an IP address to a server name and then enter the server name to register the server?

Thanks..Binh

View 2 Replies View Related

Restrict Login Access By Hostname/ip Possible?

Oct 2, 2007

In SQL2005, is it possible to restrict incoming sql or windows authentication logins by source hostname or IP address? Perhaps using endpoints?

We are currently using hosts file on the db server as a temporary solution, but the SA will not allow us to use this as a permanent solution.

The app team I support is asking me to build an "idiot proof" database so that an Dev or Test app server they may configure, doesn't unintentionally connect to the Production database and damage/alter data.

Don't worry, I've already asked if they can't follow some kind of M&P's to prevent this kind of silly behavior, but they insist on DBA team creating a bullet-proof database that can outsmart app developers who try to attempt something dumb like this.

Any ideas or suggestions would be greatly appreciated.

TIA,
Phil

Phil Streiff

View 2 Replies View Related

Rename Hostname On Server 2005

Nov 16, 2007

Do I have to do anything when hostname (servername) with SQL Server 2005 install was changed from OLDNAME to NEWNAME? If yes, what need to be done and syntax? I notice it under SYSSERVERS table still contain the OLDNAME.

EX. TEST1PROD01 was changed to TEST2PROD01

Thanks,

Vu

View 1 Replies View Related

HostName Function With Access2K Front End

Jul 20, 2005

In an Insert Into statement I have used the Host_Name() function toidentify which user has suppied a record to a table that holdstemporary data.I'm using an Access2K front end.Code:Alter procedure SPName@parameter1 intASSet nocount OnSet xact_abort offDeclare @myHost nvarchar(50)Set @myHost = Host_Name()Insert Into tblMyNameTEMP(UniqueID, AnyField1, AnyField2,myMachineName)SELECT tblMyName.UniqueID, AnyField1, AnyField2, @myHostFROM tblMyNameWHERE tblMyName.UniqueID = @parameter1I have two problems.In some cases (and only on one or two of maybe about 250 clientworkstations) Host_Name() returns the name of MY machine. I'm thinkingthis is because I developed the app and distributed it and for somereason it's retaining the info contained in my original connectionsetup set in the MS-Access Connection window???Also, on some occasions, I get blocking messages in my trace logfollowing this operation.Any help on these two issues is appreciated.lq

View 1 Replies View Related

SqlCeReplication.HostName Size Limit Fix?

Jul 17, 2006

We are attempting to improve our merge replication process between our SQL Server 2005 server and SqlCe Mobile 3 client by switching to Data Partitions. We are using IIS as a proxy to SQL Server 2005 running on a different box using a DOMAIN account.

We've setup row filters to use HOST_NAME() and have set the option "Automatically define a partition and generate a snapshot if needed when a new Subscriber tries to synchronize" to true in the Data Partitions options under the publication's properties in SQL server management studio.

If I use a .HostName value of "1234", everything works fine. A subdirectory is created under a our publication's folder in the shared replication directory that relates to the host_name. Data is copied to the device and can be observed through query analyzer that the data is in fact filtered properly.

However, when using a .HostName based on the GetDeviceUniqueID (which results in '3D321F7212B2AD2CC824954662B9023441BB2D20'), replication works sometimes and sometimes fails with "The merge process was unable to deliver the snapshot to the Subscriber. If using Web synchronization, the merge process may have ben unable to create or write to the message file. [etc]". The final HRESULT is 80045017.

While this error indicates a permission problem, there were no permission problems when creating the "1234" partition. In researching the problem, I ran across KB905395 which states:
Limitations on the HostName property and the Subscriber-requested snapshotIf you try to initialize a SQL Server Mobile Edition subscription by using a filtered HostName property, initialization fails. This problem occurs when the value that is supplied for the HostName
property contains more than 12 characters. To work around this problem,
disable Subscriber-requested snapshot delivery at the Publisher. You
can also use a value for the HostName property that contains fewer than 12 characters.

====

This seems to be consistent with what we've observed (which actually led to the "1234" test). Does anyone know if a fix for this exists? Has anyone else run across this issue? I saw a previous post from March that did, but never got resolved. However, the KB article above was posted in March - hmmm...

This is an existing application and filtering based on the device's id is ingrained in the application. Changing it now would be large undertaking.

Regards,
Santino Lamberti
Senior Software Engineer
Launch Technologies, Inc.

View 5 Replies View Related

Sql Express As Witness Hostname Truncation

Jun 22, 2007

Not sure if this is relevant here but I thought someone might have seen this.

I am trying to set up Express edition as a mirror witness and am running into a problem.

It seems the express instance thinks its "Host Name"[1] is foo-prod-witnes rather than it's real name foo-prod-witness (notice the additional "s")

When I try to create the security endpoints everything appearsd to work as far as connecting to the express instance but i cannot start the mirror because the address listed is

TCP://foo-prod-witnes:5022 (not a valid hostname)

My enterprise editions report foo-prod-sql01.fqdn:5022

Any ideas how to fix this?

1. As shown in the Host Name tab of the Configuration Manager

View 4 Replies View Related

Applying HOSTNAME Filter To Subscription

Mar 20, 2008

Hello,

I am using Merge replication for synching data between SQL Server 2005 and Mobile units (SQL CE 3.5). I have created one publication and there are around 300 subscribers in the production.

If I want to use a HostName() filter so that each user gets his or her own data, do I have to set up 300 different subscriptions with different HOSTNAME values or can I use the same subscription somehow?

I would appreciate if someone can let me know whats the best way to approach this problem.

Thanks

Apurva

View 1 Replies View Related

Replication When Publisher And Subscriber Hostname Conflicts?

Sep 23, 2015

We've got x2 data centers in the US:

NYC & SAC

Sadly the hostnames for the boxes I am trying to replicate are similar:

- db1.nyc.mydomain.tld
- db1.sac.mydomain.tld

The servers can talk to each other over all necessary ports however when I generate a push subscription from the publisher, I am asked to add the SQL Server subscriber and it already shows DB1 listed since itself is DB1.nyc. How can I attach the remote subscriber when setting up the subscription from the publisher if the hostname conflicts in my unique scenario?I created a entry on the publishers host file to use db2sac for the SAC IP and entering that alias in as the remote subscriber but no dice.

View 4 Replies View Related

Connection SQL Server 2005 With DNS Hostname Mismatch

Dec 29, 2006

When using the MS SQL 2005 JDBC driver, I now need to have the DNS nameresolution to the client correctly set up. If not I get the followingerror:SQLState: 08S01SQLError: 0Message: hostname : hostnameWhere "hostname" is the client host name. It doesn't seem to matter ifIuse an IP address or hostname in my connection URL. When searching thenet,I have seen a similar error, but not exactly the same, where themessage is"Hostname: hostname not found" (once again, here "hostname" is theclientmachine) where they suggested the issue was the DNS name conflict.I found the error goes away if the client machine's hostname match theDNSname used for the same IP addess that the DB server is using. Onework-around is to set hostname on the client to the IP address beingused.I know the right thing to do is set the hostname and the DNS namelookup thesame, but I have a customer who doesn't want to do this. They didn'thave tofor 2003, and they don't want to for 2005. Sigh. Is there a way todisablethis feature?

View 3 Replies View Related

SQL Server 2000: Block Access From Hostname

Apr 8, 2008



Is possible block access to sql server database, from hostame (pc name). I know only the hostname and not the user.
Is possible?


Thanks

View 1 Replies View Related

SQL 2012 :: Cluster Domain / IP / Hostname / Network Name Change

Sep 19, 2014

We have 2 node sql 2012 cluster and we have a scenario where domain,IPs,Hostanmes and SQL network name are changing.

1> Can SQL cluster role be brought online by changing the sql network name and its IP once windows team reconfigure the OS cluster or there will be any challenges.

2>should I uninstall sql cluster on both nodes then windows will destroy OS cluster and they then they need to change IP,hostname and domain and then fresh installation of sql cluster

View 1 Replies View Related

Cannot Access Report Manager Website By Hostname, Only By IP Address...?

Jul 26, 2007

Hello all - this has now happened to me on a few occasions (different installs, different sites, etc.), but my client PCs cannot access Report Manager by the server's hostname (even though I can ping by hostname from the client).

For instance, if I try:

http://ovenserver/reports

I get the infamous "Internet Explorer cannot display the webpage" message.

If I try:

http://10.0.0.20/reports

The Report Manager loads properly without a hitch.

I know I am missing something simple here - can anyone help?

Thanks!

View 10 Replies View Related

DB Engine :: SSMS Connects To Different Instance With Same Hostname - If Ports Are Same

Jun 9, 2015

We have a 2 servers  with 2 default instances, 

Environment- SQL server 2012 SP2 | Windows Server 2008 R2

1server:
hostname-serverA , Instance-serverA and port-2040 Ip-192.164.1.1

2server:
hostname-serverB , Instance-serverB and port-2040 Ip-192.164.1.2

If i do a SSMS connection-the possible connections strings which can be used to connect to Instance "serverB" should be,

a.serverBserverB
b.serverB,2040
c.192.164.1.2,2040

Note: but even the connection string "serverBserverA,2040" also connects to Instance "serverB", when i try to connect from SSMS, In SSMS it shows the wrong instance name like, "serverBserverB", but it actually connects to  Instance "serverA"

i.what is the reason behind this?
ii.Is there any way to avoid this connection, by performing server property changes, if any ?

View 5 Replies View Related

Sqlcmd With Trusted Connection And IP Address Or Fully Qualified Hostname

Nov 9, 2006

The command sqlcmd seems to fail when using trusted connection and an IP address or a fully qualified hostname. For example:

sqlcmd -E -S nnn.nnn.nnn.nnn

where nnn.nnn.nnn.nnn is the real IP address of the machine, or

sqlcmd -E -S hostname.domain.com

where hostname.domain.com is the fully qualified hostname of the machine, gives the error:

Msg 18452, Level 14, State 1, Server 380GX280B05, Line 1
Login failed for user ''. The user is not associated with a trusted SQL Server c
onnection.

On the other hand, sqlcmd -E -S 127.0.0.1 works, and so does sqlcmd -E -S hostname, or sqlcmd -E -S tcp:hostname,1433.

This is on a clean machine, with SQL Server 2005 freshly installed as Administrator with mixed authentication, and the test runned also by Administrator.

Is it normal or is it a bug?

Thanks.

Georges

View 1 Replies View Related

Sqlcmd With Trusted Connection And IP Address Or Fully Qualified Hostname

Nov 9, 2006

The command sqlcmd seems to fail when using trusted connection and an IP address or a fully qualified hostname. For example:

sqlcmd -E -S nnn.nnn.nnn.nnn

where nnn.nnn.nnn.nnn is the real IP address of the machine, or

sqlcmd -E -S hostname.domain.com

where hostname.domain.com is the fully qualified hostname of the machine, gives the error:

Msg 18452, Level 14, State 1, Server 380GX280B05, Line 1
Login failed for user ''. The user is not associated with a trusted SQL Server c
onnection.

On the other hand, sqlcmd -E -S 127.0.0.1 works, and so does sqlcmd -E -S hostname, or sqlcmd -E -S tcp:hostname,1433.

This is on a clean machine, with SQL Server 2005 freshly installed
as Administrator with mixed authentication, and the test runned also by
Administrator.

Is it normal or is it a bug?

Thanks.

Georges

View 12 Replies View Related

Data Access :: HostName / Workstation ID Connection String In Excel

Aug 9, 2011

I currently have the following connection string set up in Excel to connect to my SQL db:
 
Provider=SQLOLEDB.1;Persist Security Info=True;User ID=XXXXXX;Initial Catalog=YYYYY;Data Source=SQL;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=PROJMNT1;Use Encryption for Data=False;Tag with column collation when possible=False

 What I would like to do is replace the Workstation ID (which apparently is hard coded?) so that the user's workstation name pulls (%COMPUTERNAME%) so that I can see who is connecting, using what, for how long etc etc.

Is there anyway to accomplish this?

View 5 Replies View Related

Defining Command,commandtype And Connectionstring For SELECT Command Is Not Similar To INSERT And UPDATE

Feb 23, 2007

i am using visual web developer 2005 and SQL 2005 with VB as the code behindi am using INSERT command like this        Dim test As New SqlDataSource()        test.ConnectionString = ConfigurationManager.ConnectionStrings("DatabaseConnectionString1").ToString()        test.InsertCommandType = SqlDataSourceCommandType.Text        test.InsertCommand = "INSERT INTO try (roll,name, age, email) VALUES (@roll,@name, @age, @email) "                  test.InsertParameters.Add("roll", TextBox1.Text)        test.InsertParameters.Add("name", TextBox2.Text)        test.InsertParameters.Add("age", TextBox3.Text)        test.InsertParameters.Add("email", TextBox4.Text)        test.Insert() i am using UPDATE command like this        Dim test As New SqlDataSource()        test.ConnectionString = ConfigurationManager.ConnectionStrings("DatabaseConnectionString").ToString()        test.UpdateCommandType = SqlDataSourceCommandType.Text        test.UpdateCommand = "UPDATE try SET name = '" + myname + "' , age = '" + myage + "' , email = '" + myemail + "' WHERE roll                                                         123 "        test.Update()but i have to use the SELECT command like this which is completely different from INSERT and  UPDATE commands   Dim tblData As New Data.DataTable()         Dim conn As New Data.SqlClient.SqlConnection("Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|Database.mdf;Integrated                                                                                Security=True;User Instance=True")   Dim Command As New Data.SqlClient.SqlCommand("SELECT * FROM try WHERE age = '100' ", conn)   Dim da As New Data.SqlClient.SqlDataAdapter(Command)   da.Fill(tblData)   conn.Close()                   TextBox4.Text = tblData.Rows(1).Item("name").ToString()        TextBox5.Text = tblData.Rows(1).Item("age").ToString()        TextBox6.Text = tblData.Rows(1).Item("email").ToString()       for INSERT and UPDATE commands defining the command,commandtype and connectionstring is samebut for the SELECT command it is completely different. why ?can i define the command,commandtype and connectionstring for SELECT command similar to INSERT and UPDATE ?if its possible how to do ?please help me

View 2 Replies View Related

Using A Variable In SSIS - Error - Command Text Was Not Set For The Command Object..

Nov 4, 2006

Hi All,

i am using a OLE DB Source in my dataflow component and want to select rows from the source based on the Name I enter during execution time. I have created two variables,

enterName - String packageLevel (will store the name I enter)

myVar - String packageLevel. (to store the query)

I am assigning this query to the myVar variable, "Select * from db.Users where (UsrName = " + @[User::enterName] + " )"

Now in the OLE Db source, I have selected as Sql Command from Variable, and I am getting the variable, enterName,. I select that and when I click on OK am getting this error.

Error at Data Flow Task [OLE DB Source [1]]: An OLE DB error has occurred. Error code: 0x80040E0C.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E0C Description: "Command text was not set for the command object.".

Can Someone guide me whr am going wrong?

myVar variable, i have set the ExecuteAsExpression Property to true too.

Please let me know where am going wrong?

Thanks in advance.








View 12 Replies View Related

Do Somebody Know How Long (in Chars) Script(command) Can Be Solved By SQL Command?

Aug 30, 2004

Do somebody know how long (in chars) script(command) can be solved by SQL Command?
Thanks

View 1 Replies View Related

What Command Is Used To Get Back The Privileges Offered By The GRANT Command?

Mar 10, 2007

reply.

View 1 Replies View Related

Command Text Was Not Set For The Command Object Error

Sep 19, 2006

Hi. I am writing a program in C# to migrate data from a Foxpro database to an SQL Server 2005 Express database. The package is being created programmatically. I am creating a separate data flow for each Foxpro table. It seems to be doing it ok but I am getting the following error message at the package validation stage:

Description: An OLE DB Error has occured. Error code: 0x80040E0C.

An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E0C Description: "Command text was not set for the command object".

.........

Description: "component "OLE DB Destination" (22)" failed validation and returned validation status "VS_ISBROKEN".

This is the first time I am writing such code and I there must be something I am not doing correct but can't seem to figure it out. Any help will be highly appreciated. My code is as below:

private bool BuildPackage()

{




// Create the package object

oPackage = new Package();

// Create connections for the Foxpro and SQL Server data

Connections oPkgConns = oPackage.Connections;

// Foxpro Connection

ConnectionManager oFoxConn = oPkgConns.Add("OLEDB");

oFoxConn.ConnectionString = sSourceConnString; // Created elsewhere

oFoxConn.Name = "SourceConnectionOLEDB";

oFoxConn.Description = "OLEDB Connection For Foxpro Database";

// SQL Server Connection

ConnectionManager oSQLConn = oPkgConns.Add("OLEDB");

oSQLConn.ConnectionString = sTargetConnString; // Created elsewhere

oSQLConn.Name = "DestinationConnectionOLEDB";

oSQLConn.Description = "OLEDB Connection For SQL Server Database";

// Add Prepare SQL Task

Executable exSQLTask = oPackage.Executables.Add("STOCK:SQLTask");

TaskHost thSQLTask = exSQLTask as TaskHost;

thSQLTask.Properties["Connection"].SetValue(thSQLTask, "oSQLConn");

thSQLTask.Properties["DelayValidation"].SetValue(thSQLTask, true);

thSQLTask.Properties["ResultSetType"].SetValue(thSQLTask, ResultSetType.ResultSetType_None);

thSQLTask.Properties["SqlStatementSource"].SetValue(thSQLTask, @"C:LPFMigrateLPF_Script.sql");

thSQLTask.Properties["SqlStatementSourceType"].SetValue(thSQLTask, SqlStatementSourceType.FileConnection);

thSQLTask.FailPackageOnFailure = true;



// Add Data Flow Tasks. Create a separate task for each table.

// Get a list of tables from the source folder

arFiles = Directory.GetFileSystemEntries(sLPFDataFolder, "*.DBF");

for (iCount = 0; iCount <= arFiles.GetUpperBound(0); iCount++)

{


// Get the name of the file from the array

sDataFile = Path.GetFileName(arFiles[iCount].ToString());

sDataFile = sDataFile.Substring(0, sDataFile.Length - 4);

oDataFlow = ((TaskHost)oPackage.Executables.Add("DTS.Pipeline.1")).InnerObject as MainPipe;

oDataFlow.AutoGenerateIDForNewObjects = true;



// Create the source component

IDTSComponentMetaData90 oSource = oDataFlow.ComponentMetaDataCollection.New();

oSource.Name = (sDataFile + "Src");

oSource.ComponentClassID = "DTSAdapter.OLEDBSource.1";

// Get the design time instance of the component and initialize the component

CManagedComponentWrapper srcDesignTime = oSource.Instantiate();

srcDesignTime.ProvideComponentProperties();

// Add the connection manager

if (oSource.RuntimeConnectionCollection.Count > 0)

{


oSource.RuntimeConnectionCollection[0].ConnectionManagerID = oFoxConn.ID;

oSource.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(oFoxConn);

}

// Set Custom Properties

srcDesignTime.SetComponentProperty("AccessMode", 0);

srcDesignTime.SetComponentProperty("AlwaysUseDefaultCodePage", true);

srcDesignTime.SetComponentProperty("OpenRowset", sDataFile);

// Re-initialize metadata

srcDesignTime.AcquireConnections(null);

srcDesignTime.ReinitializeMetaData();

srcDesignTime.ReleaseConnections();

// Create Destination component

IDTSComponentMetaData90 oDestination = oDataFlow.ComponentMetaDataCollection.New();

oDestination.Name = (sDataFile + "Dest");

oDestination.ComponentClassID = "DTSAdapter.OLEDBDestination.1";

// Get the design time instance of the component and initialize the component

CManagedComponentWrapper destDesignTime = oDestination.Instantiate();

destDesignTime.ProvideComponentProperties();

// Add the connection manager

if (oDestination.RuntimeConnectionCollection.Count > 0)

{


oDestination.RuntimeConnectionCollection[0].ConnectionManagerID = oSQLConn.ID;

oDestination.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(oSQLConn);

}

// Set custom properties

destDesignTime.SetComponentProperty("AccessMode", 2);

destDesignTime.SetComponentProperty("AlwaysUseDefaultCodePage", false);

destDesignTime.SetComponentProperty("OpenRowset", "[dbo].[" + sDataFile + "]");



// Create the path to link the source and destination components of the dataflow

IDTSPath90 dfPath = oDataFlow.PathCollection.New();

dfPath.AttachPathAndPropagateNotifications(oSource.OutputCollection[0], oDestination.InputCollection[0]);

// Iterate through the inputs of the component.

foreach (IDTSInput90 input in oDestination.InputCollection)

{


// Get the virtual input column collection

IDTSVirtualInput90 vInput = input.GetVirtualInput();

// Iterate through the column collection

foreach (IDTSVirtualInputColumn90 vColumn in vInput.VirtualInputColumnCollection)

{


// Call the SetUsageType method of the design time instance of the component.

destDesignTime.SetUsageType(input.ID, vInput, vColumn.LineageID, DTSUsageType.UT_READWRITE);

}

//Map external metadata to the inputcolumn

foreach (IDTSInputColumn90 inputColumn in input.InputColumnCollection)

{


IDTSExternalMetadataColumn90 externalColumn = input.ExternalMetadataColumnCollection.New();

externalColumn.Name = inputColumn.Name;

externalColumn.Precision = inputColumn.Precision;

externalColumn.Length = inputColumn.Length;

externalColumn.DataType = inputColumn.DataType;

externalColumn.Scale = inputColumn.Scale;

// Map the external column to the input column.

inputColumn.ExternalMetadataColumnID = externalColumn.ID;

}

}

}

// Add precedence constraints to the package executables

PrecedenceConstraint pcTasks = oPackage.PrecedenceConstraints.Add((Executable)thSQLTask, oPackage.Executables[0]);

pcTasks.Value = DTSExecResult.Success;

for (iCount = 1; iCount <= (oPackage.Executables.Count - 1); iCount++)

{


pcTasks = oPackage.PrecedenceConstraints.Add(oPackage.Executables[iCount - 1], oPackage.Executables[iCount]);

pcTasks.Value = DTSExecResult.Success;

}

// Validate the package

DTSExecResult eResult = oPackage.Validate(oPkgConns, null, null, null);

// Check if the package was successfully executed

if (eResult.Equals(DTSExecResult.Canceled) || eResult.Equals(DTSExecResult.Failure))

{


string sErrorMessage = "";

foreach (DtsError pkgError in oPackage.Errors)

{


sErrorMessage = sErrorMessage + "Description: " + pkgError.Description + "";

sErrorMessage = sErrorMessage + "HelpContext: " + pkgError.HelpContext + "";

sErrorMessage = sErrorMessage + "HelpFile: " + pkgError.HelpFile + "";

sErrorMessage = sErrorMessage + "IDOfInterfaceWithError: " + pkgError.IDOfInterfaceWithError + "";

sErrorMessage = sErrorMessage + "Source: " + pkgError.Source + "";

sErrorMessage = sErrorMessage + "Subcomponent: " + pkgError.SubComponent + "";

sErrorMessage = sErrorMessage + "Timestamp: " + pkgError.TimeStamp + "";

sErrorMessage = sErrorMessage + "ErrorCode: " + pkgError.ErrorCode;

}

MessageBox.Show("The DTS package was not built successfully because of the following error(s):" + sErrorMessage, "Package Builder", MessageBoxButtons.OK, MessageBoxIcon.Information);

return false;

}

// return a successful result

return true;
}

View 2 Replies View Related

Sql TOP Command

May 19, 2007

Hi,I'm using the sql TOP command to retrieve the top N number of results where N is a value passed into the stored proc...eg: select TOP(@N) table.*from table...etc..if @N is not passed into the stored proc then by default i want it to select every row from the table. e.g to achieve something similar to...select table.*from table...how can i do this with with as few lines of code possible? thanks!      

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved