Transact SQL :: How To Build Dynamic WHERE Clause In Openquery To Linked Database
Jul 17, 2015
I'd like to modify the dates within this where clause to be dynamic, building the date depending on the current year, but everything I try doesn't seem to be syntactically correct.
SELECT *
FROM Openquery(LS_CIS, 'select * from BI_WRKFLW_TASKS where (BI_EVENT_DT_TM>=''1/1/2011'' and (BI_NEEDED_DT_TM>=''1/1/2011''))OR (BI_EVENT_DT_TM>=''1/1/2011'' and BI_NEEDED_DT_TM is null)') AS derivedtbl_1
I'd like to replace ''1/1/2011''Â in the where clause with something like:
CAST(CAST(YEAR (GETDATE())-4 AS varchar) + '-' + CAST(01 AS varchar) + '-' + CAST(01 AS varchar) AS DATETIME)
I have a stored procedure which expects one parameter @Company The variable @Company holds pipe delimited value: "CNN|AOL|ABC"
I need to build a WHERE clause by parsing @Company value, so the select will look like below:
SELECT * FROM Company WHERE CompanyID IN (SELECT DISTINCT(CompanyID) FROM v_Company WHERE CompanyName = 'CNN') AND CompanyID IN (SELECT DISTINCT(CompanyID) FROM v_Company WHERE CompanyName = 'AOL') AND CompanyID IN (SELECT DISTINCT(CompanyID) FROM v_Company WHERE CompanyName = 'ABC')
I have a stored procedure which expects one parameter @Company The variable @Company holds pipe delimited value: "CNN|AOL|ABC"
I need to build a WHERE clause by parsing @Company value, so the select will look like below:
SELECT * FROM Company WHERE CompanyName = 'CNN' AND CompanyName = 'AOL' AND CompanyName = 'ABC'
P.S I know that above select doesn€™t really make sense , but I have a bigger query that would be hard to explain in this topic so I just simplified it.
I'm struggling with the syntax for qualifying an openquery's results with a where clause. I copied this from examples on the web but get the error  incorrect syntax near '32810'.
declare @sysid varchar(50) = '32810C534D01C920E7CB07EBC0A80122' declare @sql varchar(500) = 'select * from OPENQUERY(WAREHOUSE,''select * from xxx.yyyy where sys_id = ''' + @sysid + ''''')' exec(@sql) selecting @sql it looks like select * from OPENQUERY(WAREHOUSE,'select * from xxx.yyyy where sys_id = '32810C534D01C920E7CB07EBC0A80122'')
I have to build a dynamic query to go over to Oracle and get some data and bring it back via a SQL Server Stored Procedure. How I can create an IN clause passing values and ","s from a SQL Server Temp Table that I have already built.
SET @SQLQuery = @SQLQuery + 'WHERE MEMBER.MEMBER_ID IN (''''' + SELECT MEMBER.MEMBER_ID + ',' FROM #TempTable + '''''Â '
pls. let me know where I could post if this is the wrong place.
I have a Firebird 1.5 application. I created a linked server from my SQL Server 2000 to the firebird database. In SQL Server Query Analyzer I get errors from various ODBC drivers with "normal" queryies like
SELECT LVNR FROM LINKEDSRV...LVVERW
Pls. note, this all works perfectly in MS Access databases with ODBC-Links to Firebird!
From a programmer of a commercial ODBC driver I heard that this problem may be caused internally by SQL Server, there may be no solution possible in the ODBC driver. One workaround would be to use the OPENQUERY-Syntax like
SELECT * FROM OPENQUERY(LINKEDSRV, 'select LVNR from LVVERW ')
Are there any other solutions? Are there any known issues with firebird odbc-drivers and sql server? Are there any known good drivers for the use with sql-server? What is the purpose of OPENQUERY - workaround ODBC problems? Are there any settings in SQL Server 2000 (2005 Express) that could help? Are there any settings in ODBC DSN that would help?
regards
arno
PS: Here are my favorite error messages
Error -2147217900 [OLE/DB provider returned message: Dynamic SQL Error SQL error code = -104 Token unknown - line 1, char 89 "Col1014"] (Source: Microsoft OLE DB Provider for SQL Server) (SQL State: 01000) (NativeError: 7312)Error -2147217900 OLE DB-Fehlertrace [OLE/DB Provider 'MSDASQL' ICommandPrepare:repare returned 0x80004005: ]. (Source: Microsoft OLE DB Provider for SQL Server) (SQL State: 01000) (NativeError: 7300)Error -2147217900 Der OLE DB-Provider 'MSDASQL' meldete einen Fehler. (Source: Microsoft OLE DB Provider for SQL Server) (SQL State: 42000) (NativeError: 7399)
This "tricky" query does not work: SELECT LVNR FROM LINKEDSRV...LVVERW;
Error -2147217900 OLE DB-Fehlertrace [Non-interface error: Column 'ERHALTENABSCHLAG' (compile-time ordinal 35) of object 'LVVERW' was reported to have a DBTYPE of 5 at compile time and 131 at run time]. (Source: Microsoft OLE DB Provider for SQL Server) (SQL State: 01000) (NativeError: 7300)Error -2147217900 Der OLE DB-Provider 'MSDASQL' hat inkonsistente Metadaten für eine Spalte übergeben. Die Metadateninformationen wurden zur Ausführungszeit geändert. (Source: Microsoft OLE DB Provider for SQL Server) (SQL State: 42000) (NativeError: 7356)
I am doing an e-commerce project. This website will have a category of product. Each category will have sub-category. And sub-category may have another level of sub-category. This means the number of sub-category is not fixed. In the sub-category we will have products. Each product will have unpredicable number of properties (1, 2, 3 or many properties).
With the current requirment, I can know exactly the number of sub-category level and the number of properties. But the problem comes when later my boss add more category, sub-category ,... sub-category(more sub-category level), and product, as well as the products properties. At that time my database schema will not suitable for new category, products because the in can only design database with fixed number of sub-category level and fixed number of product properties(attributes or fiels in database).
Therefore, I want to ask all of you that - Is there anyway to solve this problem? - how to design database that meet extended requirements as I present above?
I need to run a report using a linked server on SQL 2005. The report was running really slow so I tried doing an OPENQUERY which makes it a lot faster. However, I need to pass some parameters and not sure how to do it using OPENQUERY. Here is the query:
SELECT RTRIM(client.ClientID) AS ClientID, client.name2 AS Client_Name, Project.Project, Project.StatutoryDueDate AS DueDate, Project.Extension1, Project.Extension2, Project.StartDate AS AsofDate, Project.PromiseDate AS CommitmentDate, Project.ReceivedDate AS InfoIn, Task.TaskID, Task.Empid, Task.ActualStartDate, Task.ActualFinishDate, Client.Partner, STAFF.PersonalTitle AS PIC, Client.Manager, MGR.PersonalTitle AS TIC, Client.Accountant, ACCT.PersonalTitle AS AIC, CASE WHEN Task.TaskID = 'PREP' THEN EMP.personaltitle END AS Prep_BY, CASE WHEN Task.TaskID = 'REV' THEN EMP.personaltitle END AS Rev_By, CASE WHEN Task.TaskID = 'PREP' THEN Task.ActualStartDate END AS Prep_Date, CASE WHEN Task.TaskID = 'REV' THEN Task.ActualStartDate END AS Rev_Date, CASE WHEN Task.TaskID = 'MAIL' THEN Task.ActualFinishDate END AS Mailed_Date, CASE WHEN Task.TaskID = 'TESNT' THEN Task.ActualFinishDate END AS TE_OUT, CASE WHEN Task.TaskID = 'TERCD' THEN Task.ActualFinishDate END AS TE_IN FROM PROJECT, PROJCUS, TASK LEFT OUTER JOIN STAFF EMP ON TASK.EMPID = EMP.EMPID, CLIENT LEFT OUTER JOIN STAFF ON CLIENT.Partner = STAFF.EmpID LEFT OUTER JOIN STAFF MGR ON CLIENT.Manager = MGR.EmpID LEFT OUTER JOIN STAFF ACCT ON CLIENT.Accountant = ACCT.EMPID WHERE CLIENT.ClientID = PROJECT.ClientID AND CLIENT.Engagement = PROJECT.Engagement AND PROJECT.Project = PROJCUS.Project AND PROJECT.Engagement = PROJCUS.Engagement AND PROJECT.ClientID = PROJCUS.ClientID AND CLIENT.ClientID = TASK.ClientID AND CLIENT.Engagement = TASK.Engagement AND PROJECT.Project = TASK.Project AND CLIENT.DroppedDate IS NULL AND ((PROJCUS.[~Custom35])='BT') AND (CLIENT.OfficeID in (@OfficeID)) AND (PROJECT.Project in (@Project))
I run openquery() from a client application(sql 2005) to query SSAS data(sql 2005) through a linked server(sql 2005), but I get the following error:
OLE DB provider "MSOLAP" for linked server "LINKEDMINING" returned message "An error was encountered in the transport layer.". OLE DB provider "MSOLAP" for linked server "LINKEDMINING" returned message "The peer prematurely closed the connection.". Msg 7303, Level 16, State 1, Procedure gettpdt, Line 3 Cannot initialize the data source object of OLE DB provider "MSOLAP" for linked server "LINKEDMINING".
And, I am sure that I have made the MSOLAP provider Allow inprocess.
Can someone please tell me what oracle software needs to be installed on a sql server machine that will link to an Oracle database on an NT4 machine.
Also can you also confirm if you have successfully executed an oracle stored procedure from sql server using the open query function. If yes, How? because I have read about people having prolems executing stored procedures with arguments using the OPENQUERY Function.
Running SQL2000 on Win2000 server. Installed Oracle8 client from Oracle for Winnt CD. This appeared to install OK could use SQL*PLUS to link to Oracle servers. Setting up a Linked server and then doing an openquery gave an error:
' msadora reported an error ole/db returned a message Oracle client & networking component were not found ..'
Any ideas? Should we have a Oracle for Win 2000 CD. It worked for SQL2000 on Winnt server.
Can anyone tell me, if, generally, the performance or the cost of executing a pass-through command on a linked server in SQL Server 2005 would be better using OPENQUERY or the new option with EXECUTE -- whether the two servers are on the same box or not? I haven't been able to find a comparison between the two.
Have there been any tests of the difference?
What effect on performance is there with 'rpc out' set with sp_serveroption so EXECUTE can be used?
To be more specific I have a development box with SQL Server 2005 and Oracle 9.2.
The new option with EXECUTE would be something like the example in MSDN (Example J.) at:
I have a simple SQL statement that runs in a web-based admin system as follows:
INSERT INTO someTable SELECT * FROM OPENQUERY (someLinkedServer, 'SELECT someTable.* FROM someTable WHERE ID = 57')
This works fine on my development system, but on the production system I get the following error:
[Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "someDB" returned message "Unspecified error".
The linked server definitely exists on the production machine; and the following stored procedure has been run: exec sp_addlinkedsrvlogin 'someLinkedServer', false, 'machinenameIUSR_macninename', 'Admin', NULLto allow the internet anonymous user account to log into the linked server.
Also: -- the internet anonymous user has full control permissions over the Access database file -- the internet anonymous user has full control permissions the administrator/local settings/temp directory (after I read somewhere that this could cause problems; though no such permission is needed to make it work on my development machine.)
As far as I can tell, the production machine (2003 Server) is configured the same way in all relevant respects as the development machine (2000 Server) where everything works.
With such a vague error message, I have no idea where to start, and would appreciate any advice.
I look trough the forum, but did not find any simular problem. Somebody, help, please! What I need to do is to write an algorithm which create a FROM clause for SQL query, using tables and joined fields, specified by the user. There could be up to 25 tables with any type of join (INNER, OUTER, FULL, CROSS). I know the basic structure of the FROM clause: "from T1 inner(or other type) join T2 on T1.field=T2.field" etc., but the main problem that users can specify tables in any order and I have to re-arrange them to create valid statement.
I thought this may be very helpful for those of you using SQL7 linked server to Oracle 7.3.4 Database. After much research and no answers when opening a case with Microsoft I finally figured out how to delete using Openquery. There are a few missing pieces of information that would of been a great help in BOL. The first is, the Microsoft OLE DB for Oracle is not the correct choice for the data provier as one might think. The correct choice is the Oracle Provider for OLE DB. The next important thing is that the Oracle table you are querying MUST have a unique index on at least one column not necessarily the column in your WHERE clause. Thirdly, you get much better performance and use of indexes if you put the WHERE clause inside the OPENQUERY statement. Here is the syntax that I found to work in my application:
Hello. Suddenly OPENQUERY had started to raise an exception 'Msg 7355, Level 16, State 1, Line 1
The OLE DB provider "SQLNCLI" for linked server "sql2000" supplied inconsistent metadata for a column. The name was changed at execution time.' The OPENQUERY stament looks like:
I don't know what has been changed - seems that everyting is OK. The problem is that sql2000 is MS SQL 2000 server and this statment is executed on the MS SQL 2005 server. dbo.sc_List is a procedure that selects data and I need insert that data to the temporary table on the sql2005 server to make some calculations. I've used OPENQUERY to prevent DTC coordinator from starting distributed transaction. Whole statement originally looks like
Code Block INSERT INTO #Templates SELECT * FROM OPENQUERY(sql2000, 'execute dbo.sc_List @AmountId = 24, @WorkMode=1, @SortMode=0')
where #Template is temporary table with columns that exactly match with stored procedure output. Help me please - what is wrong with sql servers? I know that this query has been working fine for a months and now - such strange errors. I should say also that strored procedure returns always the same columns when called - there is one large select inside it. Thank you.
I'm trying to create linked server to access DMX functions from SQL Server as per:
Executing prediction queries from the relational server http://www.sqlserverdatamining.com/DMCommunity/TipsNTricks/3914.aspx
I create the link this query
EXEC master.dbo.sp_addlinkedserver
@server = N'KLSSQL01AnalysisServerLink',
@srvproduct=N'Analysis Services 2005',
@provider=N'MSOLAP',
@datasrc=N'kls-sql01',
@catalog=N'AnalysisServicesPredictorPrototype'
GO
SELECT * FROM OPENQUERY(KLSSQL01AnalysisServerLink, 'select node_caption, node_type from [Misuse Abuse Profile].content')
where [Misue Abuse Profile] is the Mining model
Provider options: Allow in process
I receive the follwing error:
OLE DB provider "MSOLAP" for linked server "KLSSQL01AnalysisServerLink" returned message "An error was encountered in the transport layer.".
OLE DB provider "MSOLAP" for linked server "KLSSQL01AnalysisServerLink" returned message "The peer prematurely closed the connection.".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MSOLAP" for linked server "KLSSQL01AnalysisServerLink". I found this post but there was no resolution.
I'm posting this because I found this solution after much digging.
The goal here is to incorporate a variable parameter within a OPENQUERY and, ultimately build a dynamic Where clause for use within a OPENQUERY linked server routine. I'm posting because I spent a lot of time trying to get this to work and also, have seen other posts here that hinted it wasn't doable.
First of all - there a good quick article that gets close for FoxPro and possibly works as is for ACCESS:
SET @FAMILY='Touring' SET @OPENQUERY = 'SELECT * FROM OPENQUERY(VFP,'''
SET @TSQL = 'select cov,family,model from vinmast where family='+'['+@FAMILY+']'')'
EXEC (@OPENQUERY+@TSQL)
All shown are single quotes.
In Visual Foxpro, ' ' or " " or [ ] can be used a delimeters
In addition, if wanting to build a dynamic where clause, you could do something like:
SET @TSQL = 'select cov,family,model from vinmast ' IF <some condition met to include FAMILY filter> Begin SET @TSQL=@TSQL+'where family=['+@DUTFAMILY+']''' SET @TSQL=@TSQL+ ')' End ----------------- Here's the entire Stored Procedure:
CREATE PROCEDURE dbo.ewo_sp_DUTLookup ( @DUTPROJECT char(25)=NULL,--Project @DUTFAMILY char(10)=NULL,--Family @DUTMODEL char(20)=NULL,--Model @DUTYEAR char(4)=NULL,--Model Year @DUTBEGIN char(25)=NULL,--Beginning of COV/DUT number @DEBUG int=0 )
AS
DECLARE @OPENQUERY varchar(4000), @TSQL varchar(4000), @TWHERE varchar(4000), @intErrorCode int
IF @intErrorCode=0 Begin SET @OPENQUERY = 'SELECT * FROM OPENQUERY(VFP,''' SET @TSQL = ' select dut_pk,cov,family,model,project,modelyr from vinmast ' End
set @intErrorCode = @@ERROR
IF @intErrorCode = 0 and @DUTFAMILY is not NULL or @DUTMODEL is not NULL or @DUTPROJECT is not NULL or @DUTYEAR is not NULL or @DUTBEGIN is not NULL set @TWHERE=' where '
-- Check for Family criteria If @intErrorCode = 0 and @DUTFAMILY is not NULL and Len(@TWHERE)>0 SET @TWHERE=@TWHERE+' family=['+@DUTFAMILY+'] AND ' set @intErrorCode = @@ERROR
-- Check for Model criteria If @intErrorCode = 0 and @DUTMODEL is not NULL and Len(@TWHERE)>0 SET @TWHERE=@TWHERE+' model=['+@DUTMODEL+'] AND ' set @intErrorCode = @@ERROR
--Check for Project criteria If @intErrorCode = 0 and @DUTPROJECT is not NULL and Len(@TWHERE)>0 SET @TWHERE=@TWHERE+' project=['+@DUTPROJECT+'] AND ' set @intErrorCode = @@ERROR
--Check for Model Year If @intErrorCode = 0 and @DUTYEAR is not NULL and Len(@TWHERE)>0 SET @TWHERE=@TWHERE+' modelyr=['+@DUTYEAR+'] AND ' set @intErrorCode = @@ERROR
--Check for beginning of DUT If @intErrorCode = 0 and @DUTBEGIN is not NULL and Len(@TWHERE)>0 Begin SET @DUTBEGIN=RTRIM(@DUTBEGIN) SET @TWHERE=@TWHERE+' substr(cov,1,'+cast(len(@DUTBEGIN) as char(20))+')=['+@DUTBEGIN+'] AND ' End set @intErrorCode = @@ERROR
IF @intErrorCode=0 AND substring(@TWHERE,Len(@TWHERE)-3,4)=' AND ' Begin set @TWHERE=Substring(@TWHERE,1,Len(@TWHERE)-3) select @intErrorCode=@@ERROR End
SET @TWHERE=@TWHERE+''')'
IF @debug<>0 and @intErrorCode=0 Begin print @intErrorCode print @OPENQUERY print @TSQL print @TWHERE print @OPENQUERY+@TSQL+@TWHERE End
IF @intErrorCode=0 EXEC (@OPENQUERY+@TSQL+@TWHERE) GO
Server A - Oracle 10g Database Server B - SQL2005 Client PC - Sql Express
Server A holds all data. I am using a linked server to connect server A and B. I use a set of stored procedures containing the openquery() syntax to get data from Server A to Server B. These stored procedures run every 20 minutes. I then create a publisher on Server B. I subscribe from the client PC to publisher to get data down from Server B to client(Download only subscription).
When I fire up the stored procedures and attempt to replicate, everything works fine. It appears after about 4-5 hours of the stored procedures running replication begins to hang more and and more until eventually it hangs for about 10 minutes and I recieve the following error:
Command attempted: {call sp_MSreleasemakegenerationapplock} Error messages: The merge process was unable to create a new generation at the 'Publisher'. Troubleshoot by restarting the synchronization with verbose history logging and specify an output file to which to write. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147200994)
I'm not sure if there is an error with openquery() locking? There is some locking activity going on with the TempDB and and Server B database. I've also come across some threads talking about the agent profiles. I'm very new to replication and very confused by all of the options in the agent profiles. Any help would be greatly appreciated!
I am running the following query trying to return server properties across a linked server. I want to store the results in a table on the server where I an running the query.
DECLARE @BuildClrVersionx nvarchar(128)
SET @BuildClrVersionx =
(SELECT *
FROM OPENQUERY(LKMSSQLXYZ01, 'CONVERT(nvarchar(128),SERVERPROPERTY("BuildClrVersion")'))
I am getting the following errors:
OLE DB provider "SQLNCLI" for linked server "LKMSSQLADM01" returned message "Deferred prepare could not be completed.".
Msg 8180, Level 16, State 1, Line 1
Statement(s) could not be prepared.
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'CONVERT'.
If you have any ideas how I can run this query across a linked server I would appreciate it.
I have a question regarding the dynamic sql. I have a script which which deletes all the foreign keys and re- creates it. So, I have created a table instead of a temp table. Now I need to create that foreign keys table in such a way that in which ever database I run the script, this  foreign keys table should be created in that particular database.
For example:
IF OBJECT_ID('tempdb..keys','U') IS NOT NULL DROP TABLE keys CREATE TABLE keys.[@sourceserver],[@database]. (RowId INT PRIMARY KEY IDENTITY(1, 1), ForeignKeyConstraintName NVARCHAR(200), ForeignKeyConstraintTableSchema NVARCHAR(200), ForeignKeyConstraintTableName NVARCHAR(200), ForeignKeyConstraintColumnName NVARCHAR(200)
I have 5 drop down lists and 1 text box, and I need to build the WHERE portion of my SELECT statment (stored procedure). the drop down lists are named client, ptype, apptdate, inspdate, state, and the textbox is named text. they all need to be this=something AND that=another AND...AND text LIKE mytext. How would I go about building this efficiently? Would I Declare a bit value in the sp called WhereSet = 0 IF @client IS NOT NULL IF @WhereSet = 0 SET @Where = 'WHERE ClientID=@client' SET @SetWhere = 1 ELSE SET @Where = @Where + ' AND CleintID=@client' . . .... Or would this be a lot easier using adhoc SQL instead of a Stored Procedure? (note: I am using a SQL DataSource) Please help, I am going bald from pulling my hair our...
I need to update an Oracle table from SQL Server. I am trying to use Openquery Update statement. I need to pass a integer as a parameter. I will be updating a date field and a status field.
This is the gist of what I need to do in a stored procedure
DECLARE   @ID1      INT,       @SQL1      VARCHAR(8000),       @STATUS    VARCHAR(10),       @DATE      DATETIME; SET      @ID1 = 350719; SET      @STATUS = 'COMPLETED'; SET      @DATE = GETDATE(); SELECT   @ID1; SELECT @SQL1 = 'UPDATE OPENQUERY(NGDEV2_LINK2, ''select DM_IMPORT_STATUS, DM_IMPORT_DATE FROM NEXTGEN.PARTY_HISTORY WHERE PARTY_HISTORY_ID = ' + CAST(@ID1 as nvarchar(30)) + ''')' SET DM_IMPORT__STATUS = @STATUS, DM_IMPORT_DATE = @DATE; EXEC (@SQL1);
As i have to handle the empty result set from and open query call to linked analysis server in dynamic SQL. If there is no data returning from the query then i just wanted to display message with no data.In current scenario it gives me below the error.
Msg 7357, Level 16, State 2, Line 13 Cannot process the object "MDX QUery".
The OLE DB provider "MSOLAP" for linked server "CO1BMXPSQL08" indicates that either the object has no columns or the current user does not have permissions on that object.
I have a quite big SQL query which would be nice to be used using UNION betweern two Select and Where clauses. I noticed that if both Select clauses have Where part between UNION other is ignored. How can I prevent this?
I found a article in StackOverflow saying that if UNION has e.g. two Selects with Where conditions other one will not work. [URL] ....
I have installed SQL Server 2014 and I tried to use tricks mentioned in StackOverflow's article but couldn't succeeded.
Any example how to write two Selects with own Where clauses and those Selects are joined with UNION?
My existing ASP 1.0 site keeps getting hacked using SQL injections. I have rewritten the site in ASP 3.5 to stop the attacks but cannot figure out how to dynamically generate a basic keyword search. I am trying to take the keywords entered into an array and then construct the WHERE clause - not having much luck. Getting either errors or double LIKE statements. Need some help. string[] SqlKWSrch; SqlSrch = KWordSrch.Text;SqlKWSrch = SqlSrch.Split(' ', ','); int AStop = SqlKWSrch.Length; int i = 0; foreach( string a in SqlKWSearch ) { if (i <= AStop) { SqlWHR = SqlWHR + "L_Kwords LIKE '%' + " + " '" + SqlKWSrch[i] + "' " + " + '%' AND "; } else { SqlWHR = SqlWHR + "L_Kwords LIKE '%' + " + " '" + SqlKWSrch[i] + "' " + " + '%' "; } i++; } 1) I can't seem to properly terminate the final LIKE statement2) can't figure out how to pass 'SqlWHR' to the procedure GIVEN KEYWORDS: 'antique chairs' entered I want to end up with the below SP, the @SqlWHR parameter appeared to have worked once but it probably was an illusion. PROCEDURE KeyWordSearch@SqlWHR varchar(100)AS SELECT L_Name, L_City, L_State, L_Display FROM tblCompanies WHERE L_Kwords LIKE '%' + 'antique' + '%' AND L_Kwords LIKE '%' + 'chairs' + '%' AND L_Display = 1 RETURN
I am trying to build a proc that uses a loop to import data into several tables. The data is copied into the appropriate table according to the contents of the variable @PracticeCode. I am also trying to add a date value to each record as it is added to the table. I thought that the best way to do this would be t use the sp_executesql stored proc. but I am having difficulty getting it to work. Here's what I have done so far:
-- insert data into proper tables with extract date added SET @SQLString ='INSERT INTO GMS_48hrAccess.dbo.tbl_Surgery'+@PracticeCode+' SELECT SurgeryKey,'+ @extractDate+', ClinicianCode, StartTime, SessionGroup, [Description], SurgeryName, Deleted, PremisesKey FROM GMS_48hrAccess.dbo.tbl_SurgeryIn'
EXEC master..sp_executesql @SQLString
And here's the error message that I get:
Server: Msg 241, Level 16, State 1, Line 90 Syntax error converting datetime from character string.
I understand why I am getting this error I just can't seem to fix it. I've consulted BOl and have tried various Parameter combinations but to no avail.
I have the following SQL. I need the results to be in the @NSTATUS variable. How do I do this?
DECLAREÂ Â Â @HISTORY_IDÂ Â Â Â Â Â INT, Â Â Â Â Â Â @NSTATUSÂ Â Â VARCHAR(20), Â Â Â Â Â Â @IMPORT_DATEÂ Â Â DATETIME, Â Â Â Â Â Â @TSQLÂ Â Â Â Â Â Â Â Â VARCHAR(8000); SETÂ Â Â Â Â Â @HISTORY_ID = 350721; Â Â SET @TSQL = 'SELECT DM_IMPORT_STATUS FROM OPENQUERY(NGDEV2_LINK2, ''SELECT DM_IMPORT_STATUS from NEXTGEN.PARTY_HISTORY WHERE PARTY_HISTORY_ID = ''''' + CAST(@HISTORY_ID as nvarchar(30)) + ''''''')' Â Â SELECT @TSQL, @HISTORY_ID; Â Â EXEC (@TSQL) ;
