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
ADVERTISEMENT
Apr 29, 2006
I have the following connection string in my Windows app that works fine on the Server:
workstation id=SCSXP;packet size=4096;integrated security=SSPI;data source=scsxp;persist security info=False;initial catalog=nala
But when I use that string on a workstation, it times out. I do have a Network connection to the Server.
How should I modify the connection string to work on the workstation?
Thanks,
Bill
View 1 Replies
View Related
Sep 16, 2015
I am using sqljdbc41.jar to connect with MSSQL database, it is working fine on my local machine.Where as on the remote server, same class giving me error
Login failed for user '<domain><windows loginID>'
My connection string is URL...
I am using sqljdbc41.jar and on 64 bit processor , I am using following command which included path for sqljdbc_auth.dll java -Djava.library.path= C: sqljdbc_4.1enuauthx64 TestDao and error is Login failed for user '<domain><windows loginID>' why it is not picking up username passed in connection string. I have 2 machines, one is local and other is remote. on both machine I login using my domain, it is working absolutely fine on local then why the error is coming on remote machine.Both the machines are identical.
View 4 Replies
View Related
May 28, 2014
One of my excel 2013 power pivot report was migrated from old server to new server after migration i changed the excel power pivot connection string to connect with new server but the workbook connections is still taking the old connection string of old server and there is no option of changing workbook connection string .
I am able to edit the powerpivot connection but workbook connections are not getting updated they are still taking old server connection string.
View 12 Replies
View Related
Jul 9, 2015
I am running into an issue while executing a sproc from Excel VBA. Everything connects fine, and I am passing a parameter, however, after a few seconds, it seems like the connection receives a "completed" command and continues down it's code, but the sproc is still executing. The result is that I never receive the record set from the sproc.Here is the code snippet from VBA:
' Create Recordset objects.
Dim cmd As New ADODB.Command
Dim conn As ADODB.Connection
Dim prm As ADODB.Parameter
Dim sConnString As String
Dim rs As ADODB.Recordset
Dim strQry As String
Dim rowCount As Long
[code]....
And here is the sproc that is being called. the first thing it performs after the "IF" block (there are multiple steps that would consecutively be called after this, but all of the data hinges on this first step working) is a TRUNCATE statement. After running a SQL profiler while executing the VBA code, I consistently see an "account log out" entry; almost as if the connection from the Excel workbook is sent a disconnect instruction. The sproc continues to run and perform the rest of the script in the "IF" block, but the returned recordset is never returned back to Excel.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET NOCOUNT ON
GO
[code]....
View 4 Replies
View Related
Aug 23, 2007
This method has worked beautifully for all my SSIS pkgs thus far.
Basically, I use a Script Task to derive the name of the newest file in a local directory. Then I save the name of the file to user a user variable, e.g. User::File.
Then, in my flat file properties > Expressions, I set "ConnectionString" to reference User::File.
However, when attempting to use this method with an Excel source, I get this error message:
Error at myPkg [Connection manager "Excel Connection Manager"]: The connection string format is not valid. It must consist of one or more components of the form X=Y, separated by semicolons. This error occurs when a connection string with zero components is set on database connection manager.
Error at myPkg: The result of the expression "@[User::Folder]+ @[User::File]" on property "ConnectionString" cannot be written to the property. The expression was evaluated, but cannot be set on the property.
HELP......... I need this to work!
Thanks
View 18 Replies
View Related
Apr 14, 2008
Hi all,
I am able to set dynamic source for the text file(flat file) but i want to set the connection string (file name) to excel source dynamicaly. I have tryied lots of time by taking a variable in foreachloop container . Variable is itself able to pick the file name dynamicaly but whern i am tying to set connectionstring to excel source it gives error.
Steps that i have done: -
1) Drag foreachloop container
2) set directory,FileNameRetrieval,FileSpec
3) Made VariableMapping
4) Now drag a dataflow task in the foreachloop container
5) select excel source
6) When i am selecting varaible as connectin string from properties of excel connectin manager, i am getting this error : -
TITLE: Microsoft Visual Studio
------------------------------
Error at Package3 [Connection manager "Excel Connection Manager 2"]: An OLE DB error has occurred. Error code: 0x80040E4D.
Error at Data Flow Task [Excel Source [1]]: The AcquireConnection method call to the connection manager "Excel Connection Manager 2" failed with error code 0xC0202009.
------------------------------
ADDITIONAL INFORMATION:
Exception from HRESULT: 0xC020801C (Microsoft.SqlServer.DTSPipelineWrap)
------------------------------
BUTTONS:
OK
------------------------------
Please help me, whats the problem? can i set connectionstring via variable to excel source
View 6 Replies
View Related
Apr 14, 2008
Hello,
Kindly give me the solution ASAP how to do Dyanmic Connection in ExcelConnection manager.
Thanks
Thiru
View 1 Replies
View Related
Nov 9, 2007
I have a folder of excel files with different file names and sheet names inside the file. So File A could have Sheet1A and file B could have Sheet2B.
The assumption is that the sheets would have similar data in them for all files even though sheet names are different. I was able to dynamically create the excel connection string using a for loop and choosing the Data Access mode of the excel source to be Table name or View name variable. I have run into a "[DTS.Pipeline] Error: "component "Excel Source" (1)" failed validation and returned validation status "VS_NEEDSNEWMETADATA".
Was wondering if anybody was able to play in such a scenario and resolve the issue.
By the way a lot of the posts in this blog helped me get this far so thanks for all the posts.
View 3 Replies
View Related
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
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
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
Sep 22, 2015
I'm trying to re-write my database to de-couple the interface (MS Access) from the SQL Backend. As a result, I'm going to write a number of Stored Procedures to replace the MS Access code. My first attempt worked on a small sample, however, trying to move this on to a real table hasn't worked (I've amended the SP and code to try and get it to work on 2 fields, rather than the full 20 plus).It works in SQL Management console (supply a Client ID, it returns all the client details), but does not return anything (recordset closed) when trying to access via VBA code.The Stored procedure is:-
USE [VMSProd]
GO
/****** Object: StoredProcedure [Clients].[vms_Get_Specified_Client] Script Date: 22/09/2015 16:29:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
[code]....
View 4 Replies
View Related
Jun 1, 2007
I need to hardcode my TCP port to a SQL Server Express named instance. You guys have any examples I could see. I keep getting an error when I try to connect:
Run-time error 2147467259 BBNETLIB connection open parse connection paramaters, invalid connection. I'm trying to connect to a local instance on a Windows 2003 server.
View 3 Replies
View Related
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
Jul 23, 2005
Hi all;I am trying to replicate our database by using the merge replication.[color=blue]>From server side I tried to connect to the workstation. I have[/color]installed MSDE to the client machine. I can see the machine name on theSQL Server Group however I wasnt able to create the conncetion betweenthe machines. I have checked my network connection, there is no problemI can see the machine and I can connect it through the network. I triedto use Client Network Utility with Named Pipes and TCP/IP but not agood news. I couldnt achieve to connect to the machine. So couldsomeone please help me out with this problem. I am really in trouble Ihave to find out this end of this week.RegardsAs
View 3 Replies
View Related
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
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
Oct 25, 2007
Hello,
i want to import data from an excel sheet into a database. While reading from the excel sheet OleDb automatically guesses the Datatype of each column. My Problem is the first A Column which contains ~240 Lines. 210 Lines are Numbers, the latter 30 do contain strings. When i use this code:
Code BlockDim sConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & conf_path_current & file_to_import & ";Extended Properties=""Excel 8.0;HDR=NO"""
Dim oConn As New OleDb.OleDbConnection(sConn)
Dim cmd1 As New System.Data.OleDb.OleDbCommand("Select * From [Table$]", oConn)
Dim rdr As OleDb.OleDbDataReader = cmd1.ExecuteReader
Do While rdr.Read()
Console.WriteLine(rdr.Item(0)) 'or rdr(0).ToString
Next
it will continue to read the stuff till the String-Lines are coming.
when using Item(0), it just crashes for trying to convert a DBNull to a String, when using rdr(0).ToString() it just gives me no value.
So my question is how to tell OleDB that i want that column to be completly read as String/Varchar?
Thanks for Reading
- Pierre from Berlin
[seems i got redirected into the wrong forum, please move into the correct one]
View 1 Replies
View Related
Jun 10, 2015
I have recently upgraded to SQL2014 on Win2012. The Access front end program works fine.
But, previously created Excel reports with built in MS Queries now fail with the above error for users with MS 2013. The queries still work for users still using MS 2007.
I also cannot create any new queries and get the same error message. If I log on as myself on the domain to another PC with 2007 installed it works fine, so I don't think it is anything to do with AD groups or permissions.
View 6 Replies
View Related
Oct 26, 2006
i've been reading some problems with excel source data being force as
numeric type when there are string/numeric type in the data, but adding
IMEX=1 to the extended properties will fix this problem...this is true
but not in my case...
say my excel file have about 40 rows..if row 1-39 in column A are all
NULL and row 40 has a string in it, the string in row 40 will not be
converted and the excel source is forcing this column A data type to be
numeric..having IMEX=1 in there does not work..however..if i add a
string anywhere in row 1-8 in column A, the the string in row 40 will
be converted because the external data type now is a string..
anyone know how to solve this problem?
View 3 Replies
View Related
Sep 8, 2006
Hi, Folks,
Can we have a table listing the BEST solution of DB Connection string
and getting data for each version of framework and databses?
use Oledb? SQlConnection? ODBC? what's the BEST solution?
Oracle | MS SQL
==============================================
Framework 1.0 ? | ?
Framework 1.1 ? | ?
Framework 2.0 ? | ?
thanks alot
Please have some code for the ?
View 1 Replies
View Related
Feb 27, 2007
Hello,
I am trying to import a table from Access and/or Excel. I have attempted with both programs. I get errors possibly due to some bad data over the years. Is it possible to tell SQL Server to import a table and discard any errors? or is there a way to scrub the data before importing to make sure all possible causes of errors are corrected or addressed?
View 3 Replies
View Related
May 14, 2005
Hi,
I have some tables in an ACCESS database, and would like to recreate them in a SQL2005 databse.How may this be done?I am able to create a Data Component with the ACCESS mdb file.
Likewise, how may I convert EXCEL data to SQL2005 table?Thanks.
David
View 3 Replies
View Related
Sep 4, 2007
Can I change databases for a SqlDataSource in code behind and still use the edit,inset,delete capabilities of the SQLDataSource control?
View 1 Replies
View Related
Sep 17, 2007
I need to create a report that lists other reports in the SSRS database. So I created an XML Data Source. But no matter what, I can't seem to parameterize this connection string so that it'll always point to whichever report server the report is deployed to. I know that I can change this data source once I've done deployment, however I don't want to have to change this whenever I change from SIT to UAT to Production. Is there a way to achieve this?
I tried:
"http://" & Globals!ReportServerUrl & "/ReportService2005.asmx"
="http://" & Globals!ReportServerUrl & "/ReportService2005.asmx"
http://Globals!ReportServerUrl/ReportService2005.asmx
=http://Globals!ReportServerUrl/ReportService2005.asmx
Globals!ReportServerUrl & "/ReportService2005.asmx"
=Globals!ReportServerUrl & "/ReportService2005.asmx"
All to no avail
Thanks,
Ray
View 1 Replies
View Related
Apr 7, 2008
I have a task that uses an Excel connection manager to read in an Excel file. In the file there are 6 date columns. The date columns can have either a date or the letters NA. If I look at the External Column properties from the advanced tab, 4 of the date columns have a datatype of DT_DATE. The other 2 date columns have a datatype of DT_WSTR. The 4 columns with DT_DATE, my data looks okay as when it is read by the connection manager. The issue is that the 2 columns that are DT_WSTR, all dates are turned into NULLS, while the NA's are just fine. I have tried to change the datatypes in the advanced tab to DT_DATE, but it won't work.
I don't have any idea what to do to get this working correctly. Can anyone PLEASE provide some insight and suggestions or a solution?
View 11 Replies
View Related
Feb 1, 2006
I do not know the Excel file name to load in design time.
Would like to pass the value to a variable in the package in run time?
How to do this?
Thanks,
Guangming
View 3 Replies
View Related
Dec 14, 2007
Hi,
i use sql server express 2005. I need sometimes to export data of a table to excel/access/spss ... Is it possible and how?
Thanks
Tartuffe
View 6 Replies
View Related
Jun 12, 2007
Is it possible to import data from a word table into sql table? How to import data from access and excel worksheet into sql table?
vishwa mukh
vishwamukh
View 2 Replies
View Related
Aug 10, 2015
I've created an excel spreadsheet with a data connection. This data connection uses a query that runs against a read-only database.
The issue I'm having is that the query never seems to finish running against the database, whether I open the Excel spreadsheet to view the data or run the query in SSMS.
I created the connection on the Data ribbon by going to From Other Sources --> From SQL Server and using the Data Connection Wizard.
Is there some kind of setting or property I'm missing that would allow this query to finish running?
View 6 Replies
View Related
Mar 19, 2014
"variables in SSIS for setting any connection string". I tried to Google it but cannot find any specific link.
View 4 Replies
View Related
Oct 30, 2015
I am trying to execute oracle procedure (anonymous block) with input and output parameters from ms sql code and return 2 values back:a number and a string.I am using **EXEC() AT** to execute oracle code and return a string back to ms sql. It works fine when returning numbers, but if I add a string output parameter, I am receiving an error:Msg 0, Level 11, State 0, Line 0...A severe error occurred on the current command. The results, if any, should be discarded.
The server log has the following entry in exception.txt:
Exception 0xc0000005 EXCEPTION_ACCESS_VIOLATION reading address 00000005031C07E0 at 0x0000000070F9C183
There is also a SQLDump file generated:
* BEGIN STACK DUMP:
* 10/28/15 12:45:38 spid 63
* Exception Address = 000000005040C183 Module(MSVCR100+000000000003C183)
[code]....
It looks like the problem is in passing varchar2 from oracle into ms sql. I had logging code in oracle block, and it works fine. I have found a few examples of using **exec() at** but there is always only integer output passed back.
View 3 Replies
View Related
