How to connect to remote server

SQL Server Express

Index ‹ SQL Server

- Previous
  - 1
    - ForEach loop with Excel Hi, I'm attempting to use the Foreach loop container to loop through the excel files located on a shared network folder. I've set up the Excel file connection manager to include the user variable generated from the container, but I get the below error messages when applying that variable to the connection string in the data flow. I've tried everything but I can't seem to get SSIS to recognize the path of the Excel files. I've tried copying the files to my PC, I tried running the package on the server, etc. The connection works fine if I set it up to point to any of the excel files in the network directory, but not with the Foreach loop connection name. Any help or suggestions would be greatly appreciated! I've looked everywhere and tried everything but to no avail... Thanks, Kevin TITLE: Microsoft Visual Studio ------------------------------ Error at GDW - RDB LOAD [Connection manager "UK RDB"]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4D. Error at Extract UK RDB [UK RDB [1]]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "UK RDB" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed. ------------------------------ ADDITIONAL INFORMATION: Exception from HRESULT: 0xC020801C (Microsoft.SqlServer.DTSPipelineWrap) ------------------------------ BUTTONS: OK ------------------------------ Tag: How to connect to remote server
  - 2
    - Column Naming Rules Hi All, Can someone plz let me know what are the column naming rules in sql2000 Tag: How to connect to remote server
  - 3
    - Connection Problem I created a package with two oledb connections in the connection manager....which was running perfectly...later i deleted these connections and added two new connections....and applied these to task with the new connection.. Now i run the package i come up with the following error...connection not found Error 1 Error loading test.dtsx: The connection "SQL-DEV.sql_dev" is not found. This error is thrown by Connections collection when the specific connection element is not found. c:\documents and settings\myname\desktop\integration services project\ssis\test.dtsx 1 1 when i double click the error it goes to xml code...i dont know what need to be done...please help Tag: How to connect to remote server
  - 4
    - Role Playing dimensions Currency Conversion issue Hi, Having issues with Currency Conversion in SSAS when using role playing dimensions of Time. When the Dim Usage in the cube is set for the role playing dimensions, i.e. defining the relation between the Exchange rate fact and the role playing time dims. the base $ measures (no calculations) return a null value. The generated conversion script in Calculations has leaves(Time), not the leaves for each role playing dim. With leaves for each along with leaves(time), the base measures return null. Regards Tag: How to connect to remote server
  - 5
    - deletescript for cleanup Hi everyone, I'm writing a small script to clean up our database. We have a couple of databases which contain many gigabytes of data. The script fills a few temp tables, with price-id's which can be deleted (based on a few rules). Then it deletes all related data first, before actually deleting the price records themselves. This works fine, except for performance issues. After 12 hours, I had to cancel the running script as it was taking too many resources. My question is, how can I increase performance Should I add 'commit' after deleting data in each table Would it help to make it a stored procedure Script looks kinda like this: SELECT priceId INTO #tobedeleted FROM prices WHERE ... (few rules) DELETE FROM price_product WHERE priceId IN ... #tobedeleted .... .... Tag: How to connect to remote server
  - 6
    - extract distinct information and order the results Hi, MSSQL 2000 T-SQL I have a problem in extracting information pertaing to a key value and matching that key value to another transaction but the order is based on another value in the same row. I've attached a sample of DB data below. tran_nr ret_ref_no msg_type description 5111 12345 420 reversal 5112 12345 200 auths 5113 15236 200 auths 5114 46587 200 auths 5115 46587 420 reversal Requirement using the above data is to extract data where the ret_ref_no is the same for more than one row but also check that the msg_type 420 happens before the 200 . Is there a way of retrieving the information in this way using the tran_nr coloumn values The tran_nr values is basically the serial number when the transaction is wrriten away to the DB. I've managed only to retrive the 1st half of my query whereby the same ret_ref_nr is being used by more then one transaction. Still need to figure out the 2nd part where the msg_type of 420 happens before the 200. SELECT * FROM SAMPLE WHERE ret_ref_no in ( SELECT ret_ref_no FROM SAMPLE GROUP BY ret_ref_no HAVING COUNT(*) > 1 ) Results of query 5111 12345 420 reversal 5112 12345 200 auths 5114 46587 200 auths 5115 46587 420 reversal If someone could assist with only retreiving the above results in bold to the query analyser i will really appreciate it. Regards Deceptive Tag: How to connect to remote server
  - 7
    - How to use a dialog retry timer for doing a web service call Hi Remus, I just started looking into SB about a week ago, so my question is likely to be pretty lame. However, that's not going to stop me from asking it :-) We're trying to do something similar to what you're describing here. How do you "reset" a dialog timer Why would you need to deal with resetting timers (or with timers at all for that matter) in the event of a success Couldn't you rewrite the logic to be as follows begin transaction receive message if message is web request save state of request (http address, caller etc) else if message is retry timer message load state of request endif commit do the web request (no transaction open) if success begin transaction send back response end conversation commit else set a retry timer on the dialog (say 1 minute) using BEGIN DIALOG TIMER endif Also, when you set the retry timer you have to associate it with the saved request state, right Otherwise, how will the service know which request to load on receipt of the timer message TIA. Tag: How to connect to remote server
  - 8
    - how to excluded the intersection of 2 dimensions ? Hi, I have the following simplified problem. I have a fact table and two dimension tables, colors and weekdays. I can make a query to select all except red and all except on monday but I like to make a query to select all except red mondays They query tool doesn't give you really the options. Any suggestions Constantijn Enders Tag: How to connect to remote server
  - 9
    - Error while BAM deployment on SQl server express 2005 C:\Program Files\Microsoft BizTalk Server 2006\Tracking>bm.exe deploy-all -Defin itionFile:testtype.bam.xml Microsoft (R) Business Activity Monitoring Utility Version 3.5.1602.0 Copyright (C) 2006 Microsoft Corporation. All rights reserved. Using 'BAMPrimaryImport' BAM Primary Import database on server 'MOHSASLPT13719\S QLEXPRESS'... Deploying Activity... ERROR: The BAM deployment failed. The ExistsOnSQLServer method has encountered OLE DB error code 0x80004005 (Clien t unable to establish connection). The SQL statement issued has failed. The ExistsOnSQLServer method has encountered OLE DB error code 0x80004005 (Clien t unable to establish connection). The SQL statement issued has failed. Tag: How to connect to remote server
  - 10
    - how to write this query? Hi, I am having trouble writing a query, My query calculates discount using some fields of a table, like, (CASE WHEN [cLineDiscTag] = '2' THEN (CASE WHEN [cReceiptsMultiDivFlag] = 'D' THEN (nTrnsQty * nLineDisc1) / [nReceiptsCurrConRate] ELSE (nTrnsQty * nLineDisc1) * [nReceiptsCurrConRate] END) ELSE (CASE WHEN [cReceiptsMultiDivFlag] = 'D' THEN (nTrnsQty * nUnitPrice * nLineDisc1 / 100) / [nReceiptsCurrConRate] ELSE (nTrnsQty * nUnitPrice * nLineDisc1 / 100) * [nReceiptsCurrConRate] END) END) As Discount1 Now in the query at the same level(it has many subqueries), i need to calculate discount2 and discount3, which needs value of discount1. I tried naming the whole table as A, then using this as subquery and so on.. but it makes things complicated. Is their any other proper method Please also tell me if calculation can be optimized further. Thank You. Tag: How to connect to remote server
  - 11
    - Out of Memory Error when running a Select Statement Hi all, In the object Explorer in SQL Server 2005 Management Studio, if I right click on the table I want to open and choose open, I eventually get an out of memory error with an error source: MSCORLIB. This table contains 40m records, but when it opens the table it goes past the 40m and wants to open 83m records, and then eventually comes up with an out of memory error. The properties of the table indicates that there are 40m records, but when you open the table, at the bottom, where it says retrieving data, it is counting of numbe rof records that surpass the 40m total. Is it because the database is too big, or is there a way to configure SQL Server 2005 to use X amount of memory Regards Tag: How to connect to remote server
  - 12
    - Change filename for flat file connection manager at runtime I need to add the current date to the end of the filename of a flat file at runtime. This was previously done with ActiveX script in SQL Server 2000. oConn = DTSGlobalVariables.Parent.Connections("FlatFileConnectionManagerName") oConn.DataSource = sNewFileName oConn = Nothing I would really appreciate if someone could give me some information on how to achieve this in SQL Server 2005. Thanks in advance! Regards, Sara Tag: How to connect to remote server
  - 13
    - Get previous sale for a customer's sale? If I have the query: select non empty [Ship Date].[Date].[Date] on 0 from ( select {([Customer].[Customer].&[15566], [Ship Date].[Date].&[1082])} on 0 from [Adventure Works]) where ([Measures].[Internet Extended Amount]) How do I figure w/ mdx that the customer's previous sale was on January 15, 2004 (I don't see .PrevMember working in this case - there is no customer sales date hierarchy estiblished ) For more date data, run this one: select non empty [Ship Date].[Date].[Date] on 0 from [Adventure Works] where ([Measures].[Internet Extended Amount], [Customer].[Customer].&[15566]) Tag: How to connect to remote server
  - 14
    - Trigger to insert multiple records into related table I'm very new to triggers (and stored procedures), I've done a bit of searching and can't seem to find the right direction. Any help would be greatly appreciated. Here is my situation: Thanks in advance! I have 2 tables: Cat_Control (parent), Det_Control (child). They are related via an auto-incrementing ID (Cat_Control_ID) When the user enters a new record into the Cat_Control table, I would like to automatically fill in several default records into the Det_Control table. These default records will be pre-defined based on the Cat_Control.Name field from the record that was just added to the Cat_Control table. I'm sure this is way off base but I hope it will help to clarify what I'm looking to do: 1) For Each Cat_Control record added 2) insert record into Cat_Control table 3) set Cat_Control_ID = @@Identity (for record just added) 4) set Cat_Control_Name = Cat_Control.Name (for record just added) 5) Insert record into Det_Control table 5a) Select Case Cat_Control_Name case 'x' insert record case 'y' insert record case 'z' insert record 6) loop back to step 5, to create 3 more "default records" into Det_Control table 7) loop back to Step 1 for next record Thanks again guys (and gals) for your help. Tag: How to connect to remote server
  - 15
    - Split - Comma seperaed values Hi Guys, I have a table with 2 fields (ID and DepartmentNames), department names column contains a comma seperated list. Can anyone please help me with a query to return the department names in individual rows Current format -------------------- ID DepartmentNames 1 IT,Finance 2 Accounting, Finance, IT Desired format ---------------------- ID Departmnt Name 1 IT 1 Finance 2 Accounting 2 Finance 2 IT Tag: How to connect to remote server
  - 16
    - Cannot Pull table with RDA Dears, I'm trying to set up a rda connection through the pocket pc emulator (standalone version 1.0) and my laptop which runs Windows Vista Home Premium. To start up the rda I first need to connect from my emulated device to my laptop which runs IIS 7.0 (complete installation with compatibility toward IIE 6.0). Connection to http://localhost from my laptop is working fine. I start the emulator (Pocket Pc with Windows Professional) and cradle the emulated device. Connection starts and I'm able to browse from my pc the content of the emulated device or to synchronize it. Then I run Internet Explorer on the emulator and try to connect to http://mycomputername (correct) but I receive a message which says that it cannot connect to the page I was looking for because connection was lost. Any suggestion on how to solve this issue Kind regards and many thanks in advance Cristian It seems that now I can connect to http://mycomputername and I'm able to browse the net with my pocket pc emulator. Anyway when I'm attempting to connect to htpp://mycomputername/subfolder/sqlcesa30.dll (the path is correct, I receive the following error message: " The page cannot be displayed or downloaded because the connection was lost. Check the connection and try later "..which is the same message I've been receiving before) What shall I do Tag: How to connect to remote server
- Next
  - 1
    - SQL 2005 Standard on Vista RTM Hi All, I've read a number of threads on here where users have managed to get 2005 working on Vista RTM. I'm having no such luck however. The issue I have is as follows: Clean Install of Vista RTM Ultimate 32bit. Attempt to install 2005 Standard. This error appears: SQL Server Setup Failed to compile the Managed Object Format (MOF) file D:\Program Files\Microsoft SQL Server\90\Shared\sqlmgmproviderxpsp2up.mof. To proceed, see "Troubleshooting an Installation of SQL Server 2005" or "How to: View SQL Server 2005 Setup Log Files" in SQL Server 2005 Setup Help documentation. I've read elsewhere that I need Sp2 of SQL Server. I can't install SQL server though so that makes it hard to apply a service pack. I have also tried the above with the Developer edition of Sql 2005. Has anyone encountered this issue and got past it T.I.A. Glen Surely Microsoft didn't release Vista without having its latest version of their database working on it Tag: How to connect to remote server
  - 2
    - Performance-Issue with variables It's weird, but the literal (untyped) version is faster than the version with the paramenters. Occurs with SQL 2005 SP1 64bit how come --FAST!!!! See execution plan SELECT count (*) FROM dbo . NewsComponent ttNC WHERE ttNC.DateImported BETWEEN '2007-04-02T00:00:00' AND '2007-04-03T23:59:59' ) --( ttNC . DateImported > '2007-01-01T00:00:00' ) --SLOW!!!! See execution plan DECLARE @dateBegin DATETIME ; SET @dateBegin = '2007-01-01T00:00:00' DECLARE @dateEnd DATETIME ; SET @dateEnd = '2007-04-03T23:59:59' SELECT count (*) FROM dbo . NewsComponent ttNC WHERE (ttNC.DateImported BETWEEN @dateBegin AND @dateEnd ) --( ttNC . DateImported > @dateBegin ) Tag: How to connect to remote server
  - 3
    - cannot connect to server i have installed SQL Server management studio when i attempt to connect to report services server i get the following error client found response content type text/html;expected text/xml i cant find where i might have went wrong in installation or report server configuration am new to reporting services so plz help me on this regard thanks sally Tag: How to connect to remote server
  - 4
    - using encryption via an asp site. how is it done ive been playing around with encryption, just encrypting a column with a symmetric key, and having that symmetric key encrypted with a cert, and all that , and it works well through the SSMS, but i want to know how that will be used if im using sql server as a back end database for an asp web site, or an application. does the sql server have to be protected with an SSL cert, and once a user has logged in, what would the steps be to view encrypted data Tag: How to connect to remote server
  - 5
    - The '[CDbl]' function does not exist. I have an MDX query that runs fine on everything except my newest instance of AS 2005. The query is... WITH MEMBER [Measures].[Pct] AS ' CDbl([Measures].[Page Views]) / 100.0 ' SELECT { [Measures].[Page Views], [Measures].[Pct] } ON COLUMNS , LastPeriods( 1, [Time].[Calendar].[Month].&[200701] ) ON ROWS FROM [Web Hit Total] ... and the error is ... The '[CDbl]' function does not exist. Thanks in advance for any help! Keith Spitz, Wall Street On Demand, Inc. Tag: How to connect to remote server
  - 6
    - How to acces report of other system from my system. Hi, Im Designed a Webpage .It consosts of a Button, whenever i click on Button report must generated.When the report is in my system report generated successfully.when I try to access a report from other system.I cudnot able to access report.Can any1 hlp me inthis aspect .any procedure in order to access the report of other system.wht shud be url for Reportserverurl and reportpath.In short When i click on btn i shud get report of other system.what previleges shud be given for other system in other to access With regards, mahender Tag: How to connect to remote server
  - 7
    - FTP Connection problem with "The Password was not allowed" error message Hi I have a simple FTP task that I am trying to connect to an FTP server. I can test the connection fine, however, when I try and execute the package from Visual Studio 2005 I get the "The Password was not allowed" message. I have found some threads that mention setting the protection level on the package to EncryptSensitiveWithPassword, however, I still get the same message appearing. Has anyone any idea as to what is causing this and how I can get around it Thanks Darrell Tag: How to connect to remote server
  - 8
    - Problem with "Transfer SQL Server Objects Task" Hi everyone, I'm currently trying to copy a database from one server to another (both SQL2005) using Business Intelligence Development Studio. I've created an SSIS package. The following parameters are defined: DropObjectsFirst true IncludeExtendedProperties true CopyData true ExistingData Replace CopySchema false UseCollation false IncludeDependentObjects true CopyPrimaryKeys true CopyForeignKeys true The package fails with the following error: Violation of PRIMARY KEY constraint 'PK_tblCallStatus' Cannot insert duplicate key in object 'dbo.tblCallStatus' I know what that error means but I don't understand why I get it. Isn't the package supposed to completely overwrite the destination database It obviously does not. When I manually delete all records from 'tblCallStatus' in the destination database it works fine. I can't remember I had to do that in a SQL2000 environment using DTS. Hope anyone can help since this is almost driving me nuts ;-) Thanks in advance, Kevin Tag: How to connect to remote server
  - 9
    - Problem running SSIS from C# program. I am able to run it from the file system but when I try running it from SQL Server I get an error message that it cannot load package. I am using the following code. Package package = app.LoadFromSqlServer("trans_hist", Properties.Resources.dbServerName, Properties.Resources.userId, Properties.Resources.password, null); Tag: How to connect to remote server
  - 10
    - Schema use in SQL2005 The problem we are having is that we are including components from 3rd parties that are expecting the default behaviour of the EXEC statement within a SP to default the schema correctly according to the invoking user. From what I can see this is not happening in SQL2005. Is this a bug in SQL 2005 or normal behaviour Tag: How to connect to remote server
  - 11
    - Granting UPDATE for only certain columns in a table I have tried using the SQL statement shown below to grant UPDATE permissions for a single column in a single table to a user with db_datareader privileges. grant update (col_1 ) on trs . dbo.table_1 to calc When I then run a SQL script that has an UPDATE for col_1 on trs.dbo.table_1, I get an error message Msg 230, Level 14, State 1, Line 2100 UPDATE permission denied on column 'col_2' of object 'table_1', database 'TRS', schema 'dbo'. Why is the error message referring to "col_2" when my SQL statement is trying to update "col_1" When I performed the "grant" I did it with an account that has db_owner, db_securityadmin, and db_ddladmin privileges. This worked in SQL Server 2000. What must I do to get it to work in SQL Server 2005 Tag: How to connect to remote server
  - 12
    - Event ID: 17055 I am running a WSUS and get this error in my event log. I am running Windows 2000 with SQL 2K SP4. 1827 CREAR/ALTER DATABASE failed because the resulting cumulative database size would exceed you licensed limit of 2048MB per database. Tag: How to connect to remote server
  - 13
    - Trouble with Pivot Task (multiple problems) - Long All, I am having difficulty with an SSIS package that is simply to pivot a table, and perform calculations used in reporting. Background: A sample set of data is being tested to pivot for reporting purposes. The sample set of data being used is 2226 rows that will pivot to 6 rows having nearly 400 columns. Problem 1: This all seems quite simple except that when I try to pivot on one set of data it works perfectly, I try two at a time, again great. However, as I add three or more groups of data from a view (thus creating 3+ pivoted rows) SSIS throws an error of "-1071636293" and points the to the column containing the values to be pivoted. The error output dumps ALL source data rows with the same error code and message, even on data groups that were successfully pivoted and written to the destination DB all with correct mappings (including the noted with the error code) Running the package on each group of data one set at a time, creating one pivot output row, always works for all data sets without throwing any errors. How is it possible that SSIS would run perfectly on each data set creating one row without any problems/errors/warnings, but when when run on all data at the same time it would fail Problem 2: When running on multiple sets of data creating many pivoted output rows, the error output viewer and log file contain duplicated data rows from the original query. For example if I received the row the pivot id 1001 ONCE in the query from the DB as it is written out to the error file and viewer I see this ID TWICE for each data group. So if each data set were to contain 100 codes to match and I ran this on three data sets, creating an original 300 rows from the db query, the error file and viewer will have 303 rows. This occurs for only different ID's in depending on the data set, but one ID in particular is duplicated for ALL. How could a row be duplicated in the error output in one case but not another when the source data does not contain ANY duplicates Problem 3: When running the package for three sets of data SSIS will note an error and redirect the row to the file I specified with no problem. However, as I run it on 4 or more data sets that cause more errors I receive the message: "The buffer manager attempted to push an error row to an output that was not registered as an error output. There was a call to DirectErrorRow on an output that does not have the IsErrorOut property set to TRUE." Why would the error logging work for one failure but have problems with more I am monitoring the package execution by: 1) logging all available output from SSIS to a file. 2) redirecting all errors from the pivot to a file which works only on the small data sets where I am expecting 1-2 rows to be the pivot result. 3) Placing data viewers on all directional arrows from original db query and pivot Finally, even with all the errors or warnings all data is pivoted as desired and inserted into the destination database as if nothing was wrong, sometimes. Same package, same data, same errors, but delaying the package with breakpoints causes SSIS to either be successful or fail. I'll question that one later as I gather more information. Any help on one or more of these questions would be a great help... Thanks. -1071636293 -1071636293 Tag: How to connect to remote server
  - 14
    - @@Version still shows Service Pack 1 We just installed the service pack 2 to our server, so I did a select @@version but it's still say Service Pack 1, even though the version number is now 3054. Is that a bug in the service pack Thanks. Tag: How to connect to remote server
  - 15
    - Linked server SQL 2005 to 2000 I have something similar... On a SQL 2005 default instance on a box named 'machine1', I define a linked server which points to a SQL 2000 instance also located on 'machine1'. In SSMS, I right-click to use 'add linked server' to add 'machine1\sql2000' (taking the SQL Server radio button as the default). The definition works fine. I then try to use the linked server in a four-part name, e.g. 'select * from machine1\sql2000.dbname.dbo.foobar' and I get a syntax error complaining abou the backslash. No amount of quotes seems to help. What's the trick for defining a linked server in a SQL 2005 instance where the linked server is a named SQL 2000 instance on the same machine and then using that linked server of the SQL 2000 instance in a T-SQL query thx Tag: How to connect to remote server
  - 16
    - SQL Questions Hi All, I was asked couple of questions yesterday and I thought I had to still learn a lot. Questions sound very normal but as for as I am concerned, don't know the answers hence this forum. Please advise. Here the questions asked to me 1) A simple insert into the table (could be from .net application or query analyzer) takes 10 minutes and times out, does not do any thing. What could be the reason and how would you identify and resolve the problem . 2) A simple select from table taking 10 minutes( assuming there are only there columns in the table,those are, id, name, description) id being identity column and has got index as well. What could be the reason and how would you identify and resolve the problem 3) How would you change the identity column value or can we change the identity value and how 4) How would you use the shared, update, exclusive locks in the sql statement. Please let me know. Regards, nw Tag: How to connect to remote server

Jay Khanpara

Hi All,

I have created my sql database script using SQL data publishing wizard, now i want to execute the same on my shared hosting database. But i am not getting any option to connect to remote server in SSMSE.

I have developed web based application with back hand as SQL Server 2005, so now i want to execute my script file onto the server. so how i will connect with my server using SSMSE

My application is located under www.briskonsales.com/sales

Any idea

Thanks,

Jay

Re: How to connect to remote server

Arnie Rowland

You will have to work with your Hosting provider to obtain access through their firewalls to access their SQL Server instance.

Re: How to connect to remote server

Jens K. Suessmeyer

Is the remote instance a SQL Server Express edition

Jens K. Suessmeyer

---
http://www.sqlserver2005.de
---

Re: How to connect to remote server

Mike Wachal - MSFT

Very few hosting providers will allow you to connect to their server using SSMS; rather they provide their own set of management tools accessable through their site. If you log into your account you will likely find a set of links that take you to the management console for your hosted database. From there you should be able to load the script file that you saved from the DPW and run it to create your objects in the hosted database.

As others mention, contact your hosting provider if you have questions about managing your hosted database.

Mike