Adding numerical field to filters in openquery is as easy as typing the
number but I am having major problems using strings.
I have tried a few solutions including the usual concantenations but no
solution. Is there a simple way of including strings with the filters
for a query below, possibly without declaring variables and using the
string directly in the code? I have included one of the solutions I was
given..
Thankyou for any help ...
DECLARE @SQL VARCHAR(8000) DECLARE @strVar VARCHAR(1000)
SET @strVar = '22'
SET @SQL = '
select * FROM OPENQUERY(ISERIES,
"SELECT OOLINE.OBWHLO, OOLINE.OBCUNO, OCUSMA.OKCUNM, OOLINE.OBORNO,
OOLINE.OBPONR, OOLINE.OBITNO, OOLINE.OBDWDZ, OOLINE.OBORQA,
OOLINE.OBATV3, OOLINE.OBATV4,
OOLINE.OBATV5, OOLINE.OBROUT, OOHEAD.OADLSP, OOHEAD.OADSTX,
OCUSAD.OPCUNM, OCUSAD.OPCUA1, OCUSAD.OPCUA2, OCUSAD.OPCUA3,
OCUSAD.OPCUA4, MHDISH.OQDLIX, OOHEAD.OAFACI, CFACIL.CFFACN,
OOHEAD.OARGDT, OOHEAD.OAPRTX, mitbal.MBPUIT, mitbal.MBSUWH,
OOHEAD.OARESP, OOHEAD.OARGTM, OOLINE.OBORST, mitbal.MBOPLC,
(OOLINE.OBATV0) As mark_no,(OOHEAD.OACUOR) As po_no,OOLINE.OBATV6) As
cust_bund_ID,OOLINE.OBFACI,(OOLINE.OBRORN) As DO_no, MHDISH.OQDSDT
FROM
mvxcdtprod.OOHEAD oohead INNER JOIN mvxcdtprod.OCUSAD ocusad ON
OOHEAD.OACONO = OCUSAD.OPCONO
AND OOHEAD.OACUNO = OCUSAD.OPCUNO
AND OOHEAD.OAADID = OCUSAD.OPADID
INNER JOIN mvxcdtprod.CFACIL cfacil ON OOHEAD.OACONO = CFACIL.CFCONO
AND OOHEAD.OADIVI = CFACIL.CFDIVI
AND OOHEAD.OAFACI = CFACIL.CFFACI
INNER JOIN mvxcdtprod.OCUSMA ocusma ON OOHEAD.OACONO =
OCUSMA.OKCONO AND OOHEAD.OACUNO = OCUSMA.OKCUNO
INNER JOIN mvxcdtprod.OOLINE ooline ON OOHEAD.OACONO =
OOLINE.OBCONO AND OOHEAD.OAORNO = OOLINE.OBORNO
INNER JOIN mvxcdtprod.MITBAL mitbal ON OOLINE.OBCONO =
MITBAL.MBCONO AND OOLINE.OBWHLO = MITBAL.MBWHLO
AND OOLINE.OBITNO = MITBAL.MBITNO
INNER JOIN mvxcdtprod.MITMAS mitmas ON OOLINE.OBCONO =
MITMAS.MMCONO AND OOLINE.OBITNO = MITMAS.MMITNO
LEFT OUTER JOIN mvxcdtprod.MHDISL mhdisl ON MHDISL.URRIDN =
OOLINE.OBORNO
AND MHDISL.URRIDL/100 = OOLINE.OBPONR
AND MHDISL.URCONO = OOLINE.OBCONO
LEFT OUTER JOIN mvxcdtprod.mhdish mhdish ON OOLINE.OBCONO =
MHDISH.OQCONOAND MHDISL.URDLIX = MHDISH.OQDLIX
WHERE mitbal.MBOPLC = 3 and OOLINE.OBORST = (''''' + @strVar +
''''')'') '
Post installation of SQL Server 2014 Express edition, I am able to connect to the Database Instance.
But while opening a new query window in SSMS or opening a table getting the error:
Package 'RadLangSvc.Package, RadLangSvc, Version 12.0.0.0, Culture=Neutral, Public Token=89845dcd8080cc91' failed to load
Object reference not set to an instance of an object. (mscorlib)..Have already tried installing the componentsDACProjectSystemSetup_enu.msi, TSql LanguageService_enu.msi, DACFramework_enu.msi from path VS 2010 WCU DAC.
SELECT * FROM OPENQUERY (linked server name, 'SQL Syntax')
...I'm having problems with the " ' " symbol. The SQL Syntax inside the OPENQUERY has a condition in the WHERE clause that has to be enclosed in apostophes. If it's a numeric value, I think that it doesn't need to be enclosed in apostrophes. But because the values are letters, it has to be enclosed in apostrophes. is there any way to work around this problem? I'm fetching data from an Oracle linked server by the way...
I have successfully connected to a sybase 11 database and have successfully run a couple of open query statements against this database, I have now placed this open query in a stored procedure and it works well, but when I want to pass parameter variables has part of my open query it does not like it. It asks me to declare the variable which is a parameter of the stored procedure. Is there any way I can pass in a variable value has part of my query
************************************************** *********************8 CREATE PROCEDURE qse_check_label_projectid @projectid char(18) AS
Select * from openquery(MRTEST32,'SELECT Project_id FROM DBO.MRT_PROJECT WHERE Project_id = @PROJECTID ') ************************************************** *************************** Please I believe there should be a way to pass a value through this open query function!
Hello Everyone Can someone please confirm, wether it is possible to execute a stored procedure in a sybase/or any other database from within an open query function in a SQL 7 database. I know that we can execute a dynamic SQL statement, what about a stored procedure in the linked server. Can we execute that with parameters??
Hi, SQL2K+SP4 When I load up Query Analyzer and click File -> Open to open up a query file, the open file dialog is always sorted by - i don't know alphabetically. How can I set the default to open up with files ordered alphabetically?
i have a linked server that i use a variable to get the last invoice date and only pull in the latest records that are after the last invoice date but i dont know how to insert that into a table?
DECLARE @TSQL varchar(MAX), @LastDate As Datetime, @LastRunDate As Varchar(6) SET @LastDate = (SELECT MAX(OrderDate) FROM [SYNC_REPORT].[DPY_SyncReport_Prod].[Stage].[Sales]) Set @LastRunDate = ( select right(convert(char(4), year(@LastDate)), 2) + right('000' + convert(varchar(3), datediff(dd, convert(datetime, '01/01/' + convert(char(4), year(@LastDate))), @LastDate+1)), 3) +100000 )
[code]....
but i get msdtc on server xxxxx is unavaiable?how to get my linked server query into my table
I'm using OLE DB model to insert long texts into ntext field.My code looks like :==BOOL COleDBRowData::Execute(CString strSQL){if(!m_bIsOpen)return FALSE;CCommand<CNoAccessor, CNoRowset>rs;HRESULT hr = rs.Open(m_session, strSQL);if(FAILED(hr))return FALSE;rs.Close();return TRUE;}==When strSQL length is over 5127XX bytes, Open function fails. Is thefunction supposed to be so? Is there any suggestion that I can avoidthis failure? I'm new to OLE DB thing.What is better is if somebody can tell me SQL command to insert a wholetext file content into the field directly. The sql command that I'mcurrently using is==INSERT INTO MyTable(Idx, Value) Values(N'{index}', N'{loooooongtext}')==Please help this poor soul. Thanks.--Daewon YOON
Is there a keystroke such as "CTRL-N" that would allow me to havemultiple .SQL files open at once in SQL Query Analyzer?I feel like I am "jumping through hoops" to edit multiple documents.I have to keep selecting "connect" from the menu bar to get a newdocument for editing.TIA-Brian
Whenever I open a new query I see 3 vertical lines on the screen, from top to button. Did a full reinstall, and they are still there. How to get rid of them?
In Enterprise Manager, I would right click on the table, choose Open Table and Query where I could select specific records and (most importantly) could alter data in a record by deleting the text, adding or over-typing.
In 2005 Server Management Studio I just cannot figure how to do this. I'm guessing that I need the 'Script Table as' option but then what?
I have managed to open selected data using the New Query and then Design Query in Editor, but the results only appear in a kind of view form and I cannot seem to alter any of the data entries, I get dotted lines around the selected field.
Bit of a strange one here. We have a SQL Express instance running with one database which is accessed by a VB6 client application.
Performance between the application and the database isn't great, but bizarrely if you open a Query window from the Management Studio (against the database) the performance dramatically improves - to the extent that it is visually noticeable from within the application.
So, I'm thinking that the database connections being made by the application are taking longer because of instance name resolution or the like and with the Query window open this resolution is performed quicker.
Has anyone come across this situation? I can re-create it on several different computers each with their own Express instance and I've not found anything about this on the net.
Here's the connection string used by the application - I have tried various permutations of values to no avail:
Looking to pass in the @targetDbName into the Open Query.
The target DB is PostGres and requires 2 single quotes around the dataset name.
I have tried many possible variations using the '+ @variableName +'
USE JonathanDB declare @dqzDateVer int; declare @targetDbName varchar(25); select @targetDbName = DB_NAME(); select @dqzDateVer = dqz_date_ver FROM OPENQUERY(ofr_meta_db, 'select dqz_date_ver from ofr_registry.dataset_feed_state where dataset_name=''JonathanDB'' and state = ''Done'' order by row_iddesc limit 1'); print @dqzDateVer ; print @targetDbName;
I get the below error only when my IDE open. It connects well when it is found closed. [SqlException (0x80131904): Cannot open user default database. Login failed.Login failed for user 'JPASPNET'.] I could solve this by giving the logged in windows user to impersonate under IIS window > WEBSITE > ASP.NET tab > EDIT CONFIG > APPLICATION tab But I wish someone could give me the proper solution. I almost tried all from giving ASPNET user as a administrator to configuring the same in Express management tool. Environment: XP pro, VWD and SQL Express
I got an approach like that: 1) Read something from DB - check the value, if true stop if false go on2) Read the second Value (another SQL Statement) - check the value etc. Now I could open the connection at 1) and if I have to go to 2) I leave the connection open and use the same connection at 2). Is it ok to do that? The other scenario would be opening a connection at 1), immediately close it after I read the value and open a new connection at 2). Thanks for the input!
Here's a challenge that is killing me:I've got 2 web servers and a SQL Server and about 5,000 'users' whostay connected to the site all day. I have a page that is supposed tobe 'real-time', so to do this, I have a 1px frame that refreshes every15 seconds (so the other frame doesn't have to reload all the time--thetop only reloads when a new record or a changed record hits the db).The real time data can be filtered in about 8 different ways.Currently, I have each user querying a table that contains 1 record,including the max ID and the most current insert/update posting date.The browser contains a cookie with that date. When the browser receivesnotification that there is some new info on the server, it refreshesthe top page and reloads the data. This is happening for all users. So,I thought to eliminate the 5,000 users running the same (or closevariations) of the same query each time a records is inserted/updated,that I would generate an XML file with the current day's data.In a dev environment this works 'ok'. I'm doing this by running anActiveX job on the SQL Server that calls a stored proc (FOR XML) andwrites the content to a file. Then from the web servers, I'm queryingthis file for the new timestamp and then if newer than the cookie,grabbing the XML (using the httprequest in the ASP XMLDOM) and usingXSLT to transform the data instead of parameterizing the queries.Theoretically I love this solution. Problems happen in a LIVEenvironment where either the file is being written to or the job isn'table to run. When 2 records are trying to be written within the samesecond, the file isn't being written (or maybe that the http requestingthe XML is keeping the file locked?)....anyway...this is a HUGE problethat I can't seem to solve. Once we roll to .NET I think storing thedataset in cache and updating cache (still don't know how I'll triggerthat without each user checking the db)....Long winded, sorry...help?
Does anyone know why this statement would fail? I have created my own assembly which is on the server and when i run the mdx query call myassembly.mystoredproc() it returns data, but now when I use call myassembly.mystoredproc() it returns an error.
using an mdx query this works fine call AsmTest.Asm.Analysis.TestClass.NameMe()
When using openquery i get an error select * from openquery(NV, 'call AsmTest.Asm.Analysis.TestClass.NameMe()' )
OLE DB provider "MSOLAP.3" for linked server "NV" returned message "Prepare is not safe during execution of the NameMe stored procedure.". Msg 7321, Level 16, State 2, Line 1 An error occurred while preparing the query "call AsmTest.Asm.Analysis.TestClass.NameMe()" for execution against OLE DB provider "MSOLAP.3" for linked server "NV".
linked server definition
USE master GO
/* Add new linked server */ EXEC sp_addlinkedserver @server='NV', -- local SQL name given to the linked server @srvproduct='', -- not used @provider='MSOLAP.3', -- OLE DB provider (the .2 means the SQL2K version) @datasrc='nvsifwfp', -- analysis server name (machine name) @catalog='ARTSDW' -- default catalog/database
Just a quick question about connection management. My application willnever need more than 1 or 2 connections about at any given time. Also, I donot expect many users to be connected at any given time. For efficiency, Iwould like to keep connections alive throughout the lifetime of the objectsrequiring them, rather than opening a new connection, executing code andthen closing it again. What is the most efficient way of doing this?Should I perform the open/close or just one open when I create the objectand a close when I dispose of it?
Hello Im trying to open an example RDL file I downloaded..the file type is XML ..when i open it up it contains all XML
How can i view this in GUI format from Visual Studio? It is an example off how to use default 'select all' in multivalued parameters so i assume I can see it working as a project ? Not sure how to use this XML file?
what are all the minimum required permissions to do ddl , dml statements on database. we have so many logins .which and every having sysadmin, i removed sysadmin permission and assign db_owner permission to 7 users and only 3 users having error
cannot open database ' db_nam' requested by login. the login failed. Login failed for user 'ohmvarun1'
Hi all, now, i create a job to open a page on timely basis. The job's Vb script is like that:Dim WshShellSet WshShell =CreateObject("WScript.Shell")WshShell.Run ("http://www.google.com")Set WsShell = Nothing when i run that job, i got that error:The job succeeded. The Job was invoked by User hostusername. The last step to run was step 1 (Step 1). how can i solve it? i still don't know whether that script will open a webpage or not
Hello everybody I would like to know more about the number of possible connection to a sql server is it by pool ? or there is max for all the database ? all the server ? how I can get the number of connection open ? Thx in advance
When I try to open a .MDF file with SQL Server Management Studio Express last version I get an error : There is no editor available for "DATABASE.MDF" Make sure the application for the file type (.MDF) is installed. How can I open the MDF file.I tried to use also the database NORTHWIND.MDF provided with MSSQL 2000 sample but the same error. What can I do ? I am using Microsoft SQL Server 2005 Express. I want to open the .MDF file to create a index for Full Text Search. Thank You !
"There is already an open datareader associated with this command which must be closed first." I have received this same error before, but I'm not sure why I'm getting it here.'Create a Connection object. MyConnection = New SqlConnection("...............................")
'Check whether a TMPTABLE_QUERY stored procedure already exists. MyCommand = New SqlCommand("...", MyConnection)
With MyCommand 'Set the command type that you will run. .CommandType = CommandType.Text
'Open the connection. .Connection.Open()
'Run the SQL statement, and then get the returned rows to the DataReader. MyDataReader = .ExecuteReader()
'Try to create the stored procedure only if it does not exist. If Not MyDataReader.Read() Then .CommandText = "create procedure tmptable_query as select * from #temp_table"
MyDataReader.Close() .ExecuteNonQuery() Else MyDataReader.Close() End If
.Dispose() 'Dispose of the Command object. MyConnection.Close() 'Close the connection. End With As you can see, the connection is opened and closed, and the datareader is closed. Here's what comes next...'Create another Connection object. ESOConnection = New SqlConnection("...")
If tx_lastname.Text <> "" Then If (InStr(sqlwhere, "where")) Then sqlwhere = sqlwhere & " AND lname like '" & Replace(tx_lastname.Text, "'", "''") & "%'" Else sqlwhere = " where lname like '" & Replace(tx_lastname.Text, "'", "''") & "%'" End If End If If tx_firstname.Text <> "" Then If (InStr(sqlwhere, "where")) Then sqlwhere = sqlwhere & " AND fname like '" & Replace(tx_firstname.Text, "'", "''") & "%'" Else sqlwhere = " where fname like '" & Replace(tx_firstname.Text, "'", "''") & "%'" End If End If
dynamic_con = sqlwhere & " order by arr_date desc "
'create the temporary table on esosql. CreateCommand = New SqlCommand("CREATE TABLE #TEMP_TABLE (".............", ESOConnection)
With CreateCommand 'Set the command type that you will run. .CommandType = CommandType.Text
'Open the connection to betaserv. ESOConnection.Open()
'Run the SQL statement. .ExecuteNonQuery()
End With
'query our side ESOCommand = New SqlCommand("SELECT * FROM [arrest_index]" & dynamic_con, ESOConnection)
'loop through recordset and populate temp table While ESODataReader.Read()
MyInsert = New SqlCommand("INSERT INTO #TEMP_TABLE VALUES("......", ESOConnection)
'Set the command type that you will run. MyInsert.CommandType = CommandType.Text
'Run the SQL statement. MyInsert.ExecuteNonQuery()
End While
ESODataReader.Close() 'Create a DataAdapter, and then provide the name of the stored procedure. MyDataAdapter = New SqlDataAdapter("TMPTABLE_QUERY", ESOConnection)
'Set the command type as StoredProcedure. MyDataAdapter.SelectCommand.CommandType = CommandType.StoredProcedure
'Create a new DataSet to hold the records and fill it with the rows returned from stored procedure. DS = New DataSet() MyDataAdapter.Fill(DS, "arrindex")
'Assign the recordset to the gridview and bind it. If DS.Tables(0).Rows.Count > 0 Then GridView1.DataSource = DS GridView1.DataBind() End If
'Dispose of the DataAdapter MyDataAdapter.Dispose()
'Close server connection ESOConnection.Close() Again, a separate connection is open and closed.I've read you can only have 1 datareader available per connection. Isn't that what I have here? The error is returned on this line: MyInsert.ExecuteNonQuery() Help is appreciated.
Hi Forum, I dont have a lot of experience with SQL and have a problem running .sql file. I use VWD 2005 ex and SQL server 22005 ex Im following some instructions to create the .mdf database I need for API. Step one; was to create a blank .mdf, which ive done! Step two; Create the Tables and Stored Procs. Tables and stored procs are defined in two script files CreateTables.sql and CreateStoredProcs.sql within Database folder. Run CreateTables.sql first
How do I open and run the .sql files in VWD 05? many thnaks Paul
