Pass variables to an OPENQUERY

Transact-SQL

Index ‹ SQL Server

- Previous
  - 1
    - From SQL Server 2005 to 2000 Thank you all for replying. One question, how to change the compatibility level Tag: Pass variables to an OPENQUERY
  - 2
    - Retrieving a datetime with a time of midnight (from a typical datetime) Nothing difficult, I just need a way to generate a new datetime column based on the column [PostedDate], datetime. So basically I want to truncate the time. Thanks a lot. Tag: Pass variables to an OPENQUERY
  - 3
    - Possible to use a Sharepoint List as a Datasource? The error that you are getting for the GetList method is most likely caused by not using the correct name of the list. The listName must be either the title or the GUID for the list. For more info on this method http://msdn.microsoft.com/library/default.asp url=/library/en-us/spptsdk/html/soapmListsGetList_SV01034346.asp Tag: Pass variables to an OPENQUERY
  - 4
    - Sybase and SQLServer2005 Connection Problem Dear all, Installed sybase adaptive server Enterprize version 12.5.1 on Win2k3. Database is working fine and started on Port No 2638. While connecting to Sybase from Diver Sybase ASE ODBC Driver i am not able to connect to the database and getting error "connection time out" and "Verify Host Name and Port Number". When i create a Connection from Integration Services 2005 i can connect to the Sybase Database and retive the table name but when i try to preview the table information i get the following error. "Unable to retrieve column information from the DataSource". Do i need to any other ODBC Driver to connect to sybase database. regards, sufian Tag: Pass variables to an OPENQUERY
  - 5
    - Canned SMSS reports How does one make the canned SMSS reports such as disk usage available from 1.) Local workstation for administrators of SQL can run then on SQL instances from the Object view of Management studio. All we see is the reports tab greyed out. 2.)From a reports server. My goal is to be able to run these reports on all of the SQL servers and not just a local instance. Do those servers need anything installed It was my thought that one should be able to highlight a server / database and run these reports. Have I errorred in my believes in this Yes, I know these seems rather basic but it seems there is a right way to make this occur and a wrong way. MDSN has yet to give up this information to me. If anyone knows the links or answers kindly post in the blog and also email me at brucehelp@yahoo.com . This link covers the discussion but does not have the methods to achieve the installation of the canned reports. http://blogs.msdn.com/sqlrem/archive/2006/05/16/SSMS_Reports_1.aspx "Did you know that there is a set of pre-made reports in Management Studio that can help database administrators gather information in many common areas There are reports for CPU, memory, disk, sessions, queries, transactions, and more baked right into Management Studio. You can access these reports in SQL Server 2005 and SQL Server 2005 SP1 by following these steps: " Tag: Pass variables to an OPENQUERY
  - 6
    - Complex Math functions It seems i have rejoiced to early: if i do a simple query like select power(27,1/3), the resulting value is 1 not 3. why is that Regards Tag: Pass variables to an OPENQUERY
  - 7
    - Default parameter = current year I would like to default my first parameter, which is year, to the current year. I'm querying against a cube. I've tried setting the default =Non-queried =year(today), tried a few MDX field names, and also tried setting the current year as the default in my data set. I can't seem to get the parameter to accept a default. Tag: Pass variables to an OPENQUERY
  - 8
    - "XML Source" xsd relative path Thank you for the detailed answer. I thought it would be easiest, bit obviously I do not have much choice. Best regards, pc Tag: Pass variables to an OPENQUERY
  - 9
    - Creating a blank row in a table between positive & negative values I have a table that displays a bunch of data based on their percentage changes. There is a parameter (let's say 5%) that the user can set to display only the data which either increased more than 5% or decreased more than 5%. I want to have a space between the positive changes and the negative changes. ie, to create a blank row between the +'s and the -'s. Does anyone know of a way to do this Thanks, Steven Tag: Pass variables to an OPENQUERY
  - 10
    - SQL Server 2005 rejects remote internet connections First, you'll have to make sure you can connect via telnet from a remote site to your sql port. I think that you dont need to spend time in sql server, becouse, its very probable you have a NAT problem or a not enable port to pass the sql packets to your server. Tag: Pass variables to an OPENQUERY
  - 11
    - DTS package Yes, im using DTS. any idea how to do it using DTS many thanks Dmitry Tag: Pass variables to an OPENQUERY
  - 12
    - Unable to Create New web Site in IIS 5.1 Hi, I need to create a new web site in Internet Information Service. This can be done by opening IIS manager. In IIS Manager, expand the local computer, right-click the Web Sites folder, point to New , and then click Web Site . The Web Site Creation Wizard appears. But if i right click the Web Site Folder. only Properties and Refresh option is showing. New option is not shown. I will appreciate if any one give solution to solve this problem. I am using Windows XP with SP2. Thank you Tag: Pass variables to an OPENQUERY
  - 13
    - SQL Server 2005 Installation On Windows Vista Home Premium I am having the exact problem as you are and tried the iis settings in KB Article 92020, still Reporting Services. Have you made any progress Tag: Pass variables to an OPENQUERY
  - 14
    - Exception from HRESULT: 0xC0048004 Hi, there; I created a SSIS package in a ASP.NEP application which importing data from some .dbf file. "Exception from HRESULT: 0xC0048004" happened to two tables. I had a look this exception at http://msdn2.microsoft.com/en-us/library/ms345164.aspx, it says: The index is not valid. But there is no index defined in my destination table. Does anybody how to resolve it Thanks Tag: Pass variables to an OPENQUERY
  - 15
    - Changing destination database for SSIS Package Hi, Thanks for the help. I read through that thread and it is very close to answering my question. What I don't get though is how to punctuate the connection string. In the examples from the other post, they didn't have a long connection string with spaces in it, so it didn't have any punctuation (or the ones that did were the ones that were causing the OP trouble, so it's hard to say if his punctuation was correct). Can you help me with where to put my quotation marks please E:\>"C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DTExec.exe" /file e:\testimport.dtsx /set \Package.Conne ctions[DestinationConnectionOLEDB].Properties[ConnectionString];"Data Source=(local);Initial Catalog=TestDB;Provider=SQLNCLI;Integrated Security=SSPI;Auto Translate=false;" The above gives me the error: Argument ""\Package.Connections[DestinationConnectionOLEDB].Properties[ConnectionString];Data Source=(local);Initial Catalog=TestDB;Provider=SQLNCLI;Integrated Security=SSPI;Auto Translate=false;"" for option "set" is not valid. You can see it appears to have taken my quotes from around the connection string and put them around the entire /set argument... So how do I fix that Tag: Pass variables to an OPENQUERY
  - 16
    - I4 to I8 in Aggregation I'm using an Aggregation task to summarize an input file by item and week before inserting it into a SQL table. Two of the fields I'm summing, because their totals per record can occasionally exceed 32k, are defined as int (I4) instead of smallint (I2). However, the summarized total never exceeds the value an int can hold. I ran into a problem on the insert, however, with SSIS telling me it couldn't insert an I8 value into an I4 table field. I discovered the metadata for the summed totals had automatically been set to bigint (I8), and the mapping was failing. I didn't see a way to change that metadata within the Aggregation task itself, so I added a Data Conversion task to convert the totals to four-byte signed integers and enable the mapping. Was that the proper workaround Tag: Pass variables to an OPENQUERY
- Next
  - 1
    - Order By ignoring the hyphen character Hi, I'm using the following SQL script to return a list of part number and the order is not what I expect. Perhaps this is a collation problem but I have no idea where to look to modify that. Thanks in advance, John select part from transactions T where (T.transdate between '20070701' and '20070705') and (T.transtype = 'ISSU' or T.transtype = 'RTRN') order by part Here is the beginning of the Transactions table create script CREATE TABLE [Transactions] ( [RecNo] [int] IDENTITY (1,1) NOT NULL, [Part] [nvarchar] (30) NOT NULL , [TransDate] [nvarchar] (8) NOT NULL , [TransType] [nvarchar] (4) NOT NULL , [FromLoc] [nvarchar] (10) , The 'Part' column is an alphanumeric field. The problem I am having is that the Order By seems to ignore the hyphen character '-' when the returned rows are ordered by the Part (which can contain hyphens in any column). Here is an example of what I get. 130909N9 130909N9 130909N9 1-480698-0 * These two should not be here 1-480699-0 * 15-423 164-07700 164-07700 164-07700 1683 I was expecting this ( and I get this in and older database ). 068-03000 068-03000 06A19956 074-03200 077-367-0 08DU08 1-480698-0 * These should be here eariler in the data 1-480699-0 100-364072 100-364072 Tag: Pass variables to an OPENQUERY
  - 2
    - can't replicate table with column name too long (from sql server 2005 to oracle) You can't do this through the GUI. In order to accomplish what you are trying to do, you have to use the @creation_script parameter for sp_addarticle and specify your own creation script for the subscriber. You then also need to use the @ins_cmd, @del_cmd, and @upd_cmd parameters and write your own custom replication stored procedures. Tag: Pass variables to an OPENQUERY
  - 3
    - Logical equivalent IN operator in a Conditional Split Transformation Jamie Thomson wrote: Phil Brammer wrote: No such thing. Really What about FINDSTRING() Something like the following FINDSTRING("12345,12346,12347",[PatientZip],1) Tag: Pass variables to an OPENQUERY
  - 4
    - SSIS package failing The SSISOperations in an OLEDB connection - it connects to a SQL database that drives the rest of the SSIS package execution. Shouldn't the 'standard' providers be installed as part of Katmai Thanks, Ed Tag: Pass variables to an OPENQUERY
  - 5
    - MDX Dynamic parameter syntax I have a SSRS report that I'm passing a dynamic parameter to another SSRS report. The datasourse is a SSAS cube. Can anyone tell me the if following syntax is correct for receiveing report. I'm having trouble creating the datasource for the second report. I'm new at SSRS so any help would be appreciated. When I parse this code in management Studio I get the following An MDX Statement was expected. An MDX expression was specified. '= [SELECT NON EMPTY { [Measures]].[Qty Paid]], [Measures]].[US Cost]], [Measures]].[Qty Recd]] } ON COLUMNS, NON EMPTY { ([Division_Plant]].[Hierarchy]].[Plant Desc]].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( STRTOSET(([ + " Parameters ! DivisionPlantDivisionDesc.Value + "[), CONSTRAINED) ) ON COLUMNS FROM [New GOLD]]) WHERE ( IIF( STRTOSET(([ " + Parameters ! DivisionPlantDivisionDesc.Value + " [), CONSTRAINED).Count = 1, STRTOSET(([ " + Parameters ! DivisionPlantDivisionDesc.Value + " [), CONSTRAINED), [Division_Plant]].[Division Desc]].currentmember ) ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS' Tag: Pass variables to an OPENQUERY
  - 6
    - User Defined Function Help Needed so . . what is the problem then Are you getting any error message Tag: Pass variables to an OPENQUERY
  - 7
    - transaction log is full Its all depends (a) If you don't need FUll Recovery model change to SImple... (b) If you don't take TL Backup change to simple recovery... IF you do... then increase the TL Backup frequency... you don't need to truncate the log then.. (c) Keep a good comfortable size for your TL so that system no need to allocate the space then you shrink it daily night. I don't think you should do it daily... check all the above mentioned points and then decide Madhu Tag: Pass variables to an OPENQUERY
  - 8
    - Sub Query in where clause on SS2K5 I've got a problem with a specific query, which when running on the exact database, is super quick on SQL 2000, and takes forever on SQL 2005. The problem seems to arise from the concatenation done in the where statement. select * from [users] where [users].[name] + '|' + convert(nvarchar,[users].[age]) not in (select [listing].[name] + '|' + convert(nvarchar,[listing].[age]) from [listing]) This happens on all MSSQL 2005 servers I've tested it on, with or without service packs. Any ideas Tag: Pass variables to an OPENQUERY
  - 9
    - [DBNETLIB][ConnectionWrite (send()).] General network error. Occurs when SQL times out on new Windows 2003 sp2 server I am experiencing a very similiar/urgent issue as well. We have Biztalk 2002 installed on a windows 2000 server. The biztalk 02 databases reside on a windows 2003/sql 2005 server. We recently applied the new service pack (SP2) to the windows 2003 server and now intermittently revceive the "general network error" as well as many "unspecified" errors from the biztalk COM+ objects, these generally happen when the BT server is trying to retrieve coinfiguration from it's config database under load. The only change that has been made has been the service pack. Are there any other COM+, Networking or the like modifications made by the sp2 patch that could cause such issues. I'm at a loss but this sounds similiar. Tag: Pass variables to an OPENQUERY
  - 10
    - Global variable: MaxPledgeID used in Parameter mapping of a property in a DTS I need to understand why someone would use a global variable in a parameter mapping inside of a DTS package. The reason I ask is that I have (finally) successfully recreated a production environment involving ASP pages that push user data into a table on a SQL server across a firewall. Everything works great up to getting into the T_Pledge_Web table on my test server. However now I need to add to the DTS, an execute SQL task that in essence deletes the user data from the table on the other side of the firewall. It's basically a house keeping step. Here is the current code for this step: Delete FROM T_Pledge_Web Where ID <= The live production DTS has this house keeping step at the end of the DTS and uses this same code but with a different table name (Delete FROM T_Pledge Where ID <= ), however it also references a global variable called "MaxPledgeID". I have no idea where to find this. I've checked other tables and even the ASP pages. I realize you can't see my stuff but maybe one of you can help me think through this and in doing so I'll realize the answer. My manager doesn't want me to add this variable until I understand exactly what it's doing. He is worried something on the live table might get deleted. Any help with this would be greatly appreciated. Tag: Pass variables to an OPENQUERY
  - 11
    - Data Driven Subscription Help Enclose the list of members inside { } when passing multiple members. Tag: Pass variables to an OPENQUERY
  - 12
    - Reaading and Processing a Recordset from Com object Hi everybody. I need help with the next topic. From a stored procedure in sqlserver, I need to call a com+ dll, this dll connect ot another database diferent to sqlserver y return this dll must return to stored procedure a recordser for ther processing. I tried with sp_OACreate, sp_OAMethod, sp_OAGetProperty but I did not know how to process a recordset Thanks Erik Tag: Pass variables to an OPENQUERY
  - 13
    - end conversation : security context could not be retrieved what is the event subclass Tag: Pass variables to an OPENQUERY
  - 14
    - Problem installing SQL Server 2005 Express Edition with Advanced Services SP2 According to the end of your log your problem is that your database is in a compressed folder (usually blue letters in Explorer). I think you should uncompress the c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA folder (right-click on the folder, Properties, Special button and uncheck). Or mark it as a read-only database but I don't think you want to do that. Tag: Pass variables to an OPENQUERY
  - 15
    - SQL Server Agent Question Hi, I am using SQL Server 2005. Under SQL Server Agent there is a job...Job1. Which has been disabled by the previous DBA. Needless to say that the DBA no longer works for the company. I am trying to get more infor about this Job. So I right clicked the job and "Script Job As" --> Create to File and view it..All it shows is: Exec msdb.dbo.sp_add_job Exec dbo.sp_add_jobstep Exec msdb.dbo.sp_add_jobschedule This is not what I want. I want to know what the job does. Then I right clicked the Job and did properties and then Steps and finally Edit..That gives me: NameOfDBServer\DTS Packages\Copy Database Wizard Packages\Name of Job.. But what I want to find out is what does the job actually do Please help me. Tag: Pass variables to an OPENQUERY
  - 16
    - How to use variables for server connections Mr.Bean wrote: So now what I'm reading here ... http://www.databasejournal.com/features/mssql/article.php/3509601 DTExec /FILE SamplePackage.dtsx /SET \Package.Connections[ConnectionID].InitialCatalog;tempdb Is really the intended way to pass in server names Someties yes. Sometimes no. Depends on your scenario. Mr.Bean wrote: What if (like in several of my processes) I connect to multiple servers for different steps Then you'll have multiple connection managers. Mr.Bean wrote: I really have to retrieve the GUID of those tasks and populate them appropriately like above Not sure I understand what you mean. You never have to know about task GUIDs. Mr.Bean wrote: I'm just now starting to look into the Package Configurations ... not sure what I'm doin here just yet, but would this be a better solution Possibly. Depends on your scenario. Most people find that configurations are the way to go rather than passing in connectoin details from the command-line. Mr.Bean wrote: I miss DTS ... Well there is a steep learning curve with SSIS but most people find it worth it when they get there. Configurations, for example, are wonderfully powerful and remove many of the headaches people have in DTS. -Jamie Tag: Pass variables to an OPENQUERY

Vicky G

Hi all,

Does anyone know if it is possible to pass variabes to an openquery statement

I am trying to do something like this:

OPENQUERY(LinkServer, 'SELECT column1, column2 FROM table WHERE column3 ='' + @@variable1 +'' AND column4 = '' +@@variable2 +'')

Many thanks in advance,

-Vicky

Top

Re: Pass variables to an OPENQUERY

hunchback

Hi vicky,

If you are using SS 2005, then take a look to EXEC in BOL. You can use the "at linked_server_name" feature and use also parameters. If you are using 2000, then you will have to deal with dynamic sql.

The Curse and Blessings of Dynamic SQL

http://www.sommarskog.se/dynamic_sql.html#OPENQUERY

AMB

Top

Re: Pass variables to an OPENQUERY

Vicky G

Thanks a lot!. This was helpful.

Top