How To Connect From SQL Server Database To Another SQL Server Database

Transact-SQL

Index ‹ SQL Server

- Previous
  - 1
    - The specified package could not be loaded from the SQL Server database The PackagePassword should be set on the Application object before the package is loaded. Tag: How To Connect From SQL Server Database To Another SQL Server Database
  - 2
    - VS 2005 standard prevents using free SQL Express functionality? Hi Philip I did not see the menu that you are talking about on the Web Site but to change the client configuration options you want to go to Program Files -> SQL Server 2005 -> Configuration Tools -> sql server configuration Manager. If have express installed and you want to upgrade your tools to the full edition 1) run the Dev Setup 2) choose Tools 3) When it recognized that you have tools installed it should give you a check box to select to upgrade your tools. Michelle Tag: How To Connect From SQL Server Database To Another SQL Server Database
  - 3
    - Joining mdx results You cannot put aggregate directly into an axis, it returns a numeric amount. You need to create a calculated member and put that in the axis. eg. WITH MEMBER [Product].[Product Name].[My Products] as Aggregate ({[Product].[Product Name].[Touring Tire], [Product].[Product Name].[Decal 1]}) SELECT {[Product].[Product Name].[My Products]} on columns FROM [Adventure Works] Which from your original post, should translate to something like: WITH Member [Date].[Date].[My Dates1] AS Aggregate([Date].[Date].[May 31, 2003] : [Date].[Date].[September 8, 2003]) Member [Date].[Date].[My Dates2] AS Aggregate([Date].[Date].[January 1, 2003] : [Date].[Date].[March 31, 2003]) SELECT non empty {[Date].[Date].[My Dates1],[Date].[Date].[My Dates2]} on COLUMNS ,{[Measures].[Internet Sales Count]} on ROWS FROM [Adventure Works] Tag: How To Connect From SQL Server Database To Another SQL Server Database
  - 4
    - import data from excel data sheets Ok Thanks Tag: How To Connect From SQL Server Database To Another SQL Server Database
  - 5
    - How to display column from Inserted table Now I understood your requirement, but trigger might not help you to achieve this. I don＊t know what operation you are performing on your component (watcher). You can try to use the Notification Service which will trigger your component when the data modified on your specific table. Achieving the same delegation from database will be complex. Have a look on Notification Service, http://www.microsoft.com/sql/technologies/notification/default.mspx Tag: How To Connect From SQL Server Database To Another SQL Server Database
  - 6
    - Last working day from last year! PedroCGD wrote: How can I get the last working day for the last year for each row in data source.. how can I include it inside a transform like derivaded column Help me!! What is considered the last working day of the year The last Friday Tag: How To Connect From SQL Server Database To Another SQL Server Database
  - 7
    - Check data changes in table Hi all! I need to check data changes in some tables from specified date. Can it be done without triggers for each table SQL Server Management Studio always says "Data was changed" if another user updates data and you try to update old version. How it checks data modification date I found only this: USE [ScheduleDB] GO SELECT * FROM sys.objects WHERE object_id = OBJECT_ID ( '[dbo].[TTest]' ) AND type in ( 'U' ) but it can only show structure modification date. Tag: How To Connect From SQL Server Database To Another SQL Server Database
  - 8
    - Get certain raw row from table You need to declare and set @i before you can use it. Then you need to enclose it in parands within the SELECT statement. Code Block declare @tblStates table (stateName varchar(50)) insert into @tblStates select 'Florida' union all select 'Georgia' union all select 'Alabama' union all select 'South Carolina' declare @i int set @i = 3 select top 1 stateName from @tblStates where stateName in (select top (@i) stateName from @tblStates order by stateName desc) order by stateName asc As was pointed out, SS doesn't guarantee the order of a result set unless you specify an Order By clause. If you need to work on a row level operation then you could use a CURSOR. But most times, what people do with cursors is easily done with set-based operations. Tag: How To Connect From SQL Server Database To Another SQL Server Database
  - 9
    - SQL Server 2005 Service Pack 2 -- Now Available Greg Hyzak wrote: I read that there is a post sp-2 hotfix to apply and that the sp-2 files on MS site will be ammended for this hotfix. Any idea where to get the hotfix For more information please visit one of the following sites: My blog (quick summary): http://blogs.msdn.com/sqlrem/archive/2007/03/06/Maintenance-Plan-Changes-Update.aspx Knowledge Base Article (detail you need to be prepared): http://support.microsoft.com/kb/933508 Paul A. Mestemaker II Program Manager Microsoft SQL Server Manageability http://blogs.msdn.com/sqlrem/ Tag: How To Connect From SQL Server Database To Another SQL Server Database
  - 10
    - SQL task works running the package but not when called via C# Installing the SQL2005 update fixed all of my issues. I appreciate the replies. -BEP Tag: How To Connect From SQL Server Database To Another SQL Server Database
  - 11
    - MDX throws error after server upgrade Hi all, I deployed the "Adventure Works DW Standard Edition" cubes on my machine and I wrote the following simple MDX : MDX 1 select { [Measures].[Reseller Tax Amount] } on columns , { ([Geography].[Country].&[Australia]:[Geography].[Country].&[Germany], [Product].[Category].&[1]) } on rows from [Adventure Works] The above MDX runs fine on my machine and gives me the output. The following version of SQL Server 2005 is installed on my machine : Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) Standard Edition I tried running the same MDX on a server with the following configuration : Microsoft SQL Server 2005 - 9.00.3054.00 (Intel X86) Enterprise Edition . When I run this MDX I get the following error: "The Tuple function expects a tuple expression for the argument. A tuple set expression was used." I modified this MDX like this : MDX 2 select { [Measures].[Reseller Tax Amount] } on columns , { ([Geography].[Country].&[Australia]:[Geography].[Country].&[Germany] * [Product].[Category].&[1]) } on rows from [Adventure Works] The modified MDX runs fine on the server and gives the output. To summarize, MDX 1 and MDX 2 both run fine on my local machine and give me the same output whereas on the server with a higher build of SQL Server 2005, MDX 2 runs fine while MDX 1 fails with the error - "The Tuple function expects a tuple expression for the argument. A tuple set expression was used." I am unable to understand as to why this is happening Is this a compatibility issue which has been documented or is this a bug I request the community members to help me out. Thanks in advance, CodeKraker. Tag: How To Connect From SQL Server Database To Another SQL Server Database
  - 12
    - System.Data.SqlServerCe Sorry being a little vague but if you can point me in the right direction. I have just created a new VB 每 Windows Mobile 5.0 pocket PC 每 Device Application. (which will be connected to a PDA) Do i have to use System.Data.SqlServerCe or System.Data.SqlClient to connect to my db. Tag: How To Connect From SQL Server Database To Another SQL Server Database
  - 13
    - Cant get Access Database Synchronizer set up on client machine I have created a PPC application that uses ADS to synchronize my sqlce db with a desktop MS Access database. It works fine on my computers, but my client cant get it to work on his. Here is the situation. His "Computer Name" as it's stated in his 'My Computer' properties is " PAVSGP024FE.NWFSC.NOAA.GOV " However his Access.MDW File is located in the directory "C:\Documents and Settings\ Joe Nobody \Application Data\Microsoft\Access\System.MDW". There is no directory called "C:\Documents and Settings\ PAVSGP024FE.NWFSC.NOAA.GOV \Application Data\Microsoft\Access\System.MDW". His current config.xml file looks like this. < xml version="1.0" encoding="UTF-8" > <SystemNode> <AccessDB>C:\Program Files\NOAA Release Logger\NOAA_ReleaseLogger.mdb</AccessDB> <DeviceDB>\Program Files\NOAA Release Logger\NOAA_ReleaseLogger.sdf</DeviceDB> <Login>Joe Nobody</Login> <URL>http://Joe Nobody:1024/</URL> <WinDir>C:\</WinDir> </SystemNode> He has tried adding port 1024 as an exception for his firewall settings, has installed Access Database Synchronizer on his computer, and I have also tried using the "long" computer name as the url setting in the config file. (i.e. <URL>http:// PAVSGP024FE.NWFSC.NOAA.GOV :1024/</URL> but this throws the error "The HTTP request failed due to incorrect format or content. Try restarting the IIS server.[]" I dont know what else to try! Anybody have any suggestions Tag: How To Connect From SQL Server Database To Another SQL Server Database
  - 14
    - SQL Server slower after 2005 upgrade refer this it may help u http://www.sql-server-performance.com/forum/topic.asp TOPIC_ID=19622 Madhu Tag: How To Connect From SQL Server Database To Another SQL Server Database
  - 15
    - Filtering on a part of a field - 3 Hello, new question about an old topic ( http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=1232451&SiteID=1 ): I want to set a filter on a field, but the it must set a filter on a part of the field. I think it will be more clear with an example: I have a list with ItemNumbers and ItemDescriptions. ItemNo ItemDescription 1 Pineapple yellow 2 Cauliflower size 6 3 Orange sweet yellow etc. etc. Now I want that the user of this report can set a filter just by entering a part of the description. So, if he enters 'flower' he only see the cauliflower. If he enters 'yellow' he only see the pineapple and the orange, etc. etc. If a blank value is left, all the items must be shown. After this, Jarret, suggest to me to insert the next code into my query: where ItemDescription like '%' + @ItemDescParam + '%' This helped, but I want to have 2 options extra: 1) The part of the description which the user inserts must be independent of capitals 2) It also have to be possible that the user inserts more than one part. For example "pine" and "fellow" Can some one help me out Thx again Tag: How To Connect From SQL Server Database To Another SQL Server Database
  - 16
    - Multi-user access through a data-access layer/remoting server Hi guys, I've been developing desktop client-server and web apps and have used Access and SQL Server Standard most of the time. I'm looking into using SQL CE, and had a few questions that I can't seem to get a clear picture on: - The documentation for CE says that it supports 256 simultaneous connections and offers the Isolation levels, Transactions, Locking, etc with a 4GB DB. But most people say that CE is strictly a single-user DB and should not be used as a DB Server. Could CE be extended for use as a multi-user DB Server by creating a custom server such as a .NET Remoting Server hosted through a Windows Service (or any other custom host) on a machine whereby the CE DB would run in-process with this server on the machine which would then be accessed by multiple users from multiple machines Clients PCs -> Server PC hosting Remoting Service -> ADO.NET -> SQL CE - and further more can we use Enterprise Services (Serviced Components) to connect to SQL CE and further extend this model to offer a pure high-quality DB Server Clients PCs -> Server PC hosting Remoting Service -> Enterprise Services -> ADO.NET -> SQL CE Seems quite doable to me, but I may be wrong..please let me know either ways Thanks, CP Tag: How To Connect From SQL Server Database To Another SQL Server Database
- Next
  - 1
    - How to create Vertical Partioning Hi All, I setup a Transactional replication between SQL Server 2000 and SQL Server 2005 via wide area network . The intial snapshoot failed because run into problems on a table with column 379 columns. How do ceate Vertical Partioning a table on Publication Thank you. TJ_1 Tag: How To Connect From SQL Server Database To Another SQL Server Database
  - 2
    - xp_smtp_sendmail My DTS program requires an email functionality. But I cant use xp_SendMail , its already configured to use for some other purposes. Then I came to knew about master.dbo.xp_smtp_sendmail, but my problem is its not executing (giving a procedure not found message). What may be the possible causes How can we able to rectify it Thanks in advance Tag: How To Connect From SQL Server Database To Another SQL Server Database
  - 3
    - Error Message Text in Multi Languages Basically I am making ETL Project in which there is need to display Error Message in Multi Languages e.g.: US English, Japanese. User may have option to see Error Log Table in Japanese or US English, so I was working on it and found solution is below Sample Code, in which I＊m checking same validation twice first for US English language and second for Japanese language. I feel it will take little bit extra time. Is there any better option to achieve this Please Help＃＃.. SIMPLY COPY PASTE AND RUN SET NOCOUNT ON --> DECLARING @PhoneNumber TABLE VARIABLE HAVING PhoneNumber List DECLARE @PhoneNumber TABLE ( PhoneNumbebr NVARCHAR ( 7 )) --> INSERTING PhoneNumber List INTO it. --> Phone Number should be Integer Type. INSERT INTO @PhoneNumber VALUES ( '2766105' ) INSERT INTO @PhoneNumber VALUES ( '2766110' ) INSERT INTO @PhoneNumber VALUES ( '2766112' ) INSERT INTO @PhoneNumber VALUES ( '2710XYZ' ) --> Invalid Phone Number INSERT INTO @PhoneNumber VALUES ( '2766116' ) INSERT INTO @PhoneNumber VALUES ( 'ABC6105' ) --> Invalid Phone Number INSERT INTO @PhoneNumber VALUES ( '2766199' ) INSERT INTO @PhoneNumber VALUES ( '2766120' ) --> DECLARING @ErrorTable TABLE VARIABLE FOR STORING Error Messages in English and Japanese Lenguages. DECLARE @ErrorTable TABLE ( EnglishErrorText NVARCHAR ( 1000 ), JapaneseErrorText NVARCHAR ( 1000 )) --> DECLARING @DistinationTable TABLE VARIABLE WILL STORE VALIDATED Phone List. DECLARE @DistinationTable TABLE ( PhoneNumbebr INT ) --> DECLARING THE VARIABLES TO STORE THE VALUES RETURED BY FETCH DECLARE @PhoneNo NVARCHAR ( 50 ), @EnglishText NVARCHAR ( 1000 ), @JapaneseText NVARCHAR ( 1000 ) DECLARE MultiLanguageErrorCursor CURSOR FOR SELECT PhoneNumbebr FROM @PhoneNumber OPEN MultiLanguageErrorCursor /* PERFORM THE FIRST FETCH AND STORE THE VALUES IN VARIABLES. NOTE: THE VARIABLES ARE IN THE SAME ORDER AS THE COLUMNS IN THE SELECT STATEMENT. */ FETCH NEXT FROM MultiLanguageErrorCursor INTO @PhoneNo --> CHECK @@FETCH_STATUS TO SEE IF THERE ARE ANY MORE ROWS TO FETCH. WHILE @@FETCH_STATUS = 0 BEGIN --> INITILISATION OT VARIABLES SET @EnglishText = '' SET @JapaneseText = '' BEGIN TRY --> CHANGING LANGUAGE SETTING TO us_english SET LANGUAGE us_english INSERT INTO @DistinationTable SELECT CONVERT ( INT , @PhoneNo ) FETCH NEXT FROM MultiLanguageErrorCursor INTO @PhoneNo END TRY BEGIN CATCH SET @EnglishText = ERROR_MESSAGE () --> IT WILL RETURN English ERROR MESSAGE TEXT. --> CHANGING LANGUAGE SETTING TO Japanese TO GET ABOVE ERROR IN JAPANESE. SET LANGUAGE Japanese BEGIN TRY INSERT INTO @DistinationTable SELECT CONVERT ( INT , @PhoneNo ) END TRY BEGIN CATCH SET @JapaneseText = ERROR_MESSAGE () --> IT WILL RETURN Japanese ERROR MESSAGE TEXT. END CATCH /* RESETTING THE LANGUAGE TO us_english TO PREVENT FROM ERROR NO 16958: Could not complete cursor operation because the set options have changed since the cursor was declared. */ SET LANGUAGE us_english --> INSERTING ERROR MESSAGE TEXT INTO @ErrorTable TABLE VARIABLE. INSERT INTO @ErrorTable VALUES ( @EnglishText , @JapaneseText ) FETCH NEXT FROM MultiLanguageErrorCursor INTO @PhoneNo CONTINUE END CATCH ; END CLOSE MultiLanguageErrorCursor DEALLOCATE MultiLanguageErrorCursor SELECT * FROM @ErrorTable GO Tag: How To Connect From SQL Server Database To Another SQL Server Database
  - 4
    - Copy Folders and Files to different location I have a situation from where I need to loop through different folders and files in these folders. After processing these files, I need to archive these folders to different location. e.g., C:\MainFolder\Mar01 ==> Multiple files in Mar01 folder C:\MainFolder\Mar02 ==> Multiple files in Mar02 folder Does any one know the best way to do this in SSIS Thanks in advance. BC Tag: How To Connect From SQL Server Database To Another SQL Server Database
  - 5
    - Permissions to view Stored Procedure Property If you just want to see information about the stored procedures, not the code, but the permissions on the procedures, have you looked into granting VIEW DEFINITION on the procedures Thanks Laurentiu Tag: How To Connect From SQL Server Database To Another SQL Server Database
  - 6
    - data file blanks to nulls Thanks I Tag: How To Connect From SQL Server Database To Another SQL Server Database
  - 7
    - Send an email on non-fatal errors from Data Flow anderdw2 wrote: I'm having the same problem. I'm using a conditional split to send rows on the main data path and a set of invalid data to a flat file. I'd like to send notification to a user if rows are sent to the flat file. However, I do see where you can select any of these events as fire points. Dave What I would do is add a row count transformation between the conditional split and the flat file destination. Then, back in the control flow, hook the data flow task up to a send mail task and then using precedence constraints you can check the value of the variable that was populated in the row count transformation. If it is greater than zero, send an e-mail. Tag: How To Connect From SQL Server Database To Another SQL Server Database
  - 8
    - Service Broker "TO service could not be found" Message origin: "Transport" Make sure the event occurs in the database you expect (check dbid column in profiler), and make sure the case is correct. Services, contracs and message type names are always compared using a case sensitive collation, no matter the server or database collation. Tag: How To Connect From SQL Server Database To Another SQL Server Database
  - 9
    - Distinct Count with Junk Dimension Attribute Hi, Suppose I have following Model Dimension which is formed based on Junk Dimension definition. JunkID ModelID ModelCode ModelTypeID ModelTypeCode ModelSpecificationID ModelSpecificationCode 50 50 BAO 2 FFCVT 2 F1C1 53 53 BGO 1 FFAT 1 FW4A4 55 55 NBO 3 FRAT 9 V4A5 60 60 NCO 3 FRAT 9 V5A5 97 55 NBO 3 FRAT 11 V4A5 99 53 BGO 1 FFAT 13 F4A4 And In Fact Table Suppose there is data as below... JunkID Amount 50 5210983 53 9576 55 7994 60 7872 97 4306 99 6519 Now my requirement is to make Distinct Count on ModelID. If I makes Distinct Count on DimModel.ModelID then it gives me 5 on every combination in Cube. If I make Distinct Count on FactTable.JunkID then it gives me 6 in places of 5 as above seen data. Please help, how can I make Distinct Count on ModelID in this situation Thanks in Advance Bhudev Tag: How To Connect From SQL Server Database To Another SQL Server Database
  - 10
    - Trying to set output variable to row count result of SQL Execute Task I am doing that. Tag: How To Connect From SQL Server Database To Another SQL Server Database
  - 11
    - How can I set SO_TIMEOUT on the sockets used by the JDBC 2005 driver? Hello, One of our customers is experiencing a problem that makes me think there is a network problem, because we make a request and never return from the driver, but the transaction is still open on the server awaiting the next command (and holding locks blocking other processes). I see a number of other driver writers have described similar problems, and their "workaround" is to set SO_TIMEOUT on the sockets (setSoTimeout) they create which is a last defense so that the transaction won't block forever. I do not see this in the API documentation- how can I set this Many other drivers use a connection string parameter, do you offer the same Is there any logging I can enable that can show me for sure that the driver is waiting indefinately on socket.read (thats not terribly expensive) Thanks, Steve Tag: How To Connect From SQL Server Database To Another SQL Server Database
  - 12
    - xquery - order by problem Hi! In MSS Management Studio it is very irritating that queries not can be sorted. So I looked at the ssmssqlproj-file. I want to order by filename, but: declare @x xml ; select @x = '<LogicalFolder Name="Queries" Type="0"> <Items> <FileNode Name="updateTbl.sql"> <AssociatedConnectionMoniker /> <AssociatedConnSrvName /> <AssociatedConnUserName /> <FullPath>updateTbl.sql</FullPath> </FileNode> <FileNode Name="insertTbl.sql"> <AssociatedConnUserName /> <FullPath>insertTbl.sql</FullPath> </FileNode> <FileNode Name="selectTbl.sql"> <AssociatedConnUserName /> <FullPath>selectTbl.sql</FullPath> </FileNode> <FileNode Name="deleteTbl.sql"> <AssociatedConnUserName /> <FullPath>deleteTbl.sql</FullPath> </FileNode> </Items> </LogicalFolder>' ; --select @x.query('for $item in /LogicalFolder/Items -- return count($item/FileNode) ') select @x . query ( 'for $x in /LogicalFolder/Items/FileNode order by $x/FullPath return $x ' ) --for xml path(''), root('LogicalFolder'); --Msg 2389, Level 16, State 1, Line 29 --XQuery [query()]: 'order by' requires a singleton (or empty sequence), found operand of type 'xdt:untypedAtomic Can anyone help Thanks Bjorn Tag: How To Connect From SQL Server Database To Another SQL Server Database
  - 13
    - Very hard question on how to query data and return by months say what Tag: How To Connect From SQL Server Database To Another SQL Server Database
  - 14
    - Database attach at setup Hello, Our product has a MDF file included with the setup, this needs to be attached at the current SQL Server (Express or not). Where can we copy the MDF file during setup so it can be attached successfully We need a place where the account SQL Server is running under has write permissions. Please help ! Thanks Tag: How To Connect From SQL Server Database To Another SQL Server Database
  - 15
    - File System Watcher can not read excel from SharePoint Thank you everybody. Sorry I used file Watcher Task, as provided on SQLIS.com, in SSIS and used UNC paths and http to connect SharePoint but it did not connect . Am i missing somethig Since if I used the watcher task to connect to my local computer it worked. Tag: How To Connect From SQL Server Database To Another SQL Server Database
  - 16
    - how to set date field in sql server 2005 table hi friends i wish to set a date data type to a column "Date". when i use datetime format, the values is inserted in the U.S. date & time format. i want the values to be in the british english format. i.e dd/mm/yyyy only and does not require time value. can any one help me to insert the date values in the british english format regards.., sekar. Tag: How To Connect From SQL Server Database To Another SQL Server Database

Rgoal

I want to be able to access a table from another server for example

using a stored procedure:

I am in server A

I want to do a select in a table (dbo.test) that is located on server b. so how can i connect to server b using windows

authentication then access the table dbo.test

thanks

Re: How To Connect From SQL Server Database To Another SQL Server Database

DaleJ

You might want to do a search on this forum, and look at BOL, for topics relating to LINKED SERVERS.

Re: How To Connect From SQL Server Database To Another SQL Server Database

Suprotim Agarwal

Hi,

Follow these steps:

1. Link the Server B to Server A. Use the sp_addlinkedserver

2. Try this query then

select * from [ServerB].[dbname].[dbo].[test]

HTH,
Suprotim Agarwal

-----
http://www.dotnetcurry.com
-----

Re: How To Connect From SQL Server Database To Another SQL Server Database

Barry Andrew

Just to add that this is the link that you are looking for; sp_addlinkedserver

Re: How To Connect From SQL Server Database To Another SQL Server Database

Arnie Rowland

As well as linking the servers using sp_addlinkedserver, ALL users accounts is ServerA that need to access the data in ServerB MUST have logins in ServerB, as well as permissions to access the database and table.

This thread might be useful.

http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=2318301&SiteID=1

Re: How To Connect From SQL Server Database To Another SQL Server Database

Rgoal

Thanks Guys.

This is what i did:

select * from OPENROWSET('MSDASQL','DRIVER={SQL Server};SERVER=SeverA',projectserver.dbo._Test))