sql server - Failed to open a connection to the database" While creating a Table Adapter in C# -

so, i'm mantaining software ex co-worker did. uses xsd create tableadapters reports , other stuff.

the thing is, need add new tableadapter , aforementioned error. says dread "a network-related or instance-specific error occurred while establishing connection sql server."

i @ loss here , don't know do. here facts:

• i have 2 connection strings, 1 production environment , 1 development environment (my computer).
• when go settings , edit window of connection string, hit "test connection" , says works fine.
• both sql server 2008r2 , visual studio 2010 on same machine, machine.
• i not have named instance.
• i right click on xsd design view , click "add -> tableadapter" , use local connection string (the 1 tested , worked)
• i click "use existing stored procedures" , hit [next]. able pick stored procedure select section , can see columns.
• i click finish, system locks while , error.
• named pipes, tcp/ip enabled in sql server configuration.

i'm kinda lost , wasted 2 days on this. have tried little know , nothings works. hope can find within wisdom.

sorry long post wanted provide info in 1 single shot.

ok, after trial , error, found solution. @ least, made work on environment.

what @rusland said, made me think , searched settings file. did not exist. decided erase connection strings , settings file see if project compiled. expected, threw ton of errors.

so, not file.

i decided add 1 one connection strings , test added them. first 1 added, connection string used in development (localconstr). configuration of table adapter shown in first image. can see, localconstr selected (as has been) time, having 1 connection string received error, different error before. 1 in second image.

how possible tableadapter ask connection string if there no refferences it? deleted , connection property of table adapter set localconstr. so... did desperate man do: ctrl+f , find refferences rpttbl_loansagingtableadapter. reasoning somewhere in code, connection string hardcoded predecessor. 1 thing worth mentioning: used "loansagingtableadapter" search criteria because that's 1 tableadapter trying modify in first instance.

after minutes, navigating xsd @ code level (it's xml) , surprise, found element named connection , there not 1 two!. make things clear, file has been untouched me till today (i mean, did not touch hand, changes had been made ide) 2 connection strings were:

<connection appsettingsobjectname="settings" appsettingspropertyname="localconstr"... 

and

<connection appsettingsobjectname="settings" appsettingspropertyname="loanconstr" ... 

yes, know alike, notice local , loan. decided delete second 1 (loanconstr) since not in settings file anymore (loanconstr original connection string left predecessor, localconstr created me switch connection strings in runtime instead of modifying connection string , recompile every time had make change , deploy again).

after that, made sure datatables in dataset used same connection string (localconstr) , recompiled. tried things giving me troubles:

• first: created new datatable: action successful
• second: modified existing datatable: success again

last, not least, tried execute report. ran app , went report , success! apparently, problem solved.

what had happened? not sure, if had guess, tableadapter trying use connection string pointed server did not exist in network (my client's server) so, every time tried configure or change anything, visual studio unable contact server , that's why gor connection error.

why tableadapter trying use connection string when local connection string explicitly specified? don't know, leavin in xsd 1 connection string solution.

as side note, might ask "but compa, happen when user wants run report uses localconstr?" simple, change connection string @ runtime when report window opened 1 being used whole app.

sorry long post, , if want tl;dr version: go xds dataset file, delete unwanted connection strings , go design view , make sure tableadapters use connectionstring specified in xsd