I have a stored procedure that uses containstable and want to make it a little dynamic so I was going to add a parameter that consist of the column names that needed to be search. But when I add a variable I get an error saying incorrect syntax....
Can you not use a variable as a column list?? I have a variable for search criteria and it works fine...
Here is my syntax
containstable([tablename],@columnlist,@srch)
I have been looking online and can't seem to find anything that says I can or cannot use a variable.
I have an integer variable which i had to cast as a string in order for it to show as an integer in the sql expression which is a string in order for it to INSERT as an integer? Read that 5 times fast!!!
all the variable beginning with rc are Int32 variables being loaded using the Row Count task. they are being inserted into an integer field in the table. Is this whack or what???
I was trying to cast is using DT_I4 but couldn't find any samples nor could I get it to work. The above is successful!!!
I have a client program that writes to sql server database 10 records per second . i want to compute the CPU usage and the memory usage for the whole program or CPU usage,memory usage for the insert statement in the program .
Hello, When I am seeing SQL Server 2005 Management studio Server Dashboard> I am seeing my(USERS) databases and msdb database usage is very small % of in CPU Usage(%), Logical IO Performed (%) Usage pie chart.
90% of Total cpu usage is showing for Adhoc Queries. what excatly this means in Dashboard? if application uses more than it would have shown in Database level or not?
sicerely this dashboard is good, if any one is watching daily, please advice their experiences here.
I have n Tables: T_1, T_2, ... T_n that all have the same exact fields/columns. Ultimately, I want to search through ALL n tables and return a single table of relevant results from all tables, arranged in order by rank. I'm not sure if this is possible. So far, I have: 1 SELECT RANK, field_1, field_2, ..., field_m FROM 2 ( 3 SELECT RANK, field_1, field_2, ..., field_m FROM T_1, 4 CONTAINSTABLE(T_1, field_i,@searchText) searchTable 5 WHERE KEY = T_1.field_i 6 UNION 7 SELECT RANK, field_1, field_2, ..., field_m FROM T_2, 8 CONTAINSTABLE(T_2, field_i,@searchText) searchTable 9 WHERE KEY = T_2.field_i 10 UNION 11 . 12 . 13 . 14 UNION 15 SELECT RANK, field_1, field_2, ... field_m FROM T_n, 16 CONTAINSTABLE(T_n, field_i,@searchText) searchTable 17 WHERE KEY = T_n.field_i 18 ) 19 ORDER BY RANK DESC
I haven't tried it yet, but it seems wrong. How do we actually do this?
I would like to use LINQ to generate a sql statement that does not use LIKE, but rather uses CONTAINS. Is this possible? If not, my second question is whether or not I can parameterize a SqlCommand that uses CONTAINS. For example the following statement works just fine when I pass in the parameter via SqlCommand.Parameters.AddWithValue()SELECT * FROM [event] WHERE CONTAINS(comments, @searchTerm1)However, the following results in a variable not defined error.SELECT * FROM [event] WHERE (comments LIKE @searchTerm1)Any ideas? Thanks for your help.
I'm trying to write a query to associate to an alternate key. Cansomeone provide the right syntax/keyword I need to accomplish this?Here's the line that keeps giving me the error (Operand type clash:uniqueidentifier is incompatible with int):JOIN CONTAINSTABLE(VocabularyFrench, *, 'FORMSOF(INFLECTIONAL,"remorques")') SRCH ON P.PRODUCT_ID = SRCH.[KEY]Thanks,Jeff
i use two containstable (one of them with 'formsof') and one freetexttable attributes in a select command and i want to combine them with the logical 'or'.can i do this in the same command? what is the syntax?my code :select table1.field1,a.rank,b.rank,c.rankfrom table1containstable(table1,field,'"word"') as aor containstable(table1,field,'formsof(inflectional(word)') as b)or freetexttable(table1,field,'word') as cwhere table1.id=a.[key] and table1.id=b.[key] and table1.id=c.[key]the above syntax is wrong. i tried ',' instead of 'or' but the results were not right. thanks
Hi,I'm doing a search function for recipe database and have the query:1 SELECT K.RANK, tRecipe.sHeadline, tRecipe.sIngredients, tRecipe.sImagePath 2 FROM tRecipe 3 INNER JOIN 4 FaktaRecipe ON tRecipe.iRecipeID = FaktaRecipe.iRecipe 5 INNER JOIN 6 CONTAINSTABLE(tRecipe, *, 'ISABOUT (chick* WEIGHT(0.2))') AS K 7 ON tRecipe.iRecipeID = K.[KEY] 8 WHERE (FaktaRecipe.iRecipeFakta = 5) 9 ORDER BY RANK DESC I want to return records like 'chicken pie' etc, hence using the wildcard in chick* BUT the wildcard doesn't work! It works fine if I use the whole word 'chicken' but of course a user won't always do that... I am using SQL server 2000. Any ideas? - I'm tearing my hair out! Thanks,Paul
This works when @searchString is used in containstable (provided searchString has value)...
set @searchStringNoneOfWords = 'not(Airplane)' SET @searchString = @searchString + ' AND ' + @searchStringNoneOfWords
This does NOT work when @searchString is used in containstable...
set @searchStringNoneOfWords = 'not(Airplane)' SET @searchString = @searchStringNoneOfWords
I understand it is because the syntax is AND NOT, but what if I have a list of words that I do not want included? How do I start out with a NOT using containstable? It is kind of like Google's advanced search except that if you enter a word in the "without words" section with the other fields blank it would return everything under the sun except for things found with those words.
I am using the following query to search all columns in the 'dashboard'table for the value 'Meets':SELECT * from dashboard AS FT_TBL INNER JOIN CONTAINSTABLE(dashboard,*,'meets') AS KEY_TBL ON FT_TBL.employee = KEY_TBL.[KEY]I have multiple records that contain the word 'Meets', but none areshowing up as a result of this query. Any ideas?Also, anytime I use a space in my search condition (Meets Expectationsinstead of Meets) I am getting an error:Syntax error occurred near 'Expectations'. Expected ''''' in searchcondition 'Meets Expectations'.Any ideas?Thanks in advance.
Can somebody help me in identifying the difference between FREETEXTABLE and CONTAINSTABLE statements?
I have recently started experimenting with the free text search and I feel that FREETEXTTABLE can look for the search string into multiple columns by breaking it into tokens.
For example
select ft_tbl.saon, ft_tbl.paon, ft_tbl.street, ft_tbl.postcode ,key_tbl.rank from temp as ft_tbl INNER JOIN freetextTABLE(temp, (saon, paon, street), '80 ridge avenue', 15) as key_tbl ON FT_tbl.ID = key_tbl.[key]
Now in my case €œ80€? is in PAON column and €œRidge Avenue€? is in STREET column. Among the resulting rows it also displays the required row. If I try to achieve this with CONTAINSTABLE using
select ft_tbl.saon, ft_tbl.paon, ft_tbl.street, ft_tbl.postcode ,key_tbl.rank from temp as ft_tbl INNER JOIN containsTABLE(temp, (saon, paon, street, postcode), '80 ridge avenue') as key_tbl ON FT_tbl.ID = key_tbl.[key]
It gives me error Syntax error near 'ridge' in the full-text search condition '80 ridge avenue'.
If I make search string as €˜€?80 ridge avenue€?€™ instead of €˜ridge avenue€™ then it doesn€™t give me error but also displays no results because 80 ridge avenue does not appear as whole in any single column. If I search by only specifying €œridge avenue€? in search string then I get a full list where street is ridge avenue.
My question is Does FREETEXTTABLE can search in all listed column by breaking the search string into tokens and CONTAINSTABLE only in one as whole?
Is there a simple and direct way to perform a fulltext query in a table with multiple columns, and to use AND over multiple columns? I've noticed that AND only works within one column.
Example: Take a column 'lastname' with 'jones' and 'smith' in it. Another column 'firstname' with 'alan' where lastname=smith. Search for 'alan and smith', and no results are returned because they are in different columns. If you would have a lastname 'alan smith', that would be found.
I have a few questions related to using CONTAINSTABLE in a query that I hope someone can help with.
I am working on a project to add document search capabilities to my companies product using fulltext indexing. Part of this requirement is an ability to breakdown the component parts of of the search query and provide information on *why* documentX ranked higher than documentY. This is a bit convoluted, but taking this (very simple) example - the user wishes to search for 2 skills - "HTML" and/or "XML". The generated query looks a little like :-
Select DOC.DOC_ID, RANK1.RANK, RANK2.RANK, RANK3.RANK from DOCS DOC inner join CONTAINSTABLE(docs, doc, 'HTML AND XML') as RANK1 on RANK1.DOC_ID=DOC.DOC_ID inner join CONTAINSTABLE(docs, doc, 'HTML') as RANK2 on RANK2.DOC_ID=DOC.DOC_ID inner join CONTAINSTABLE(docs, doc, 'XML') as RANK3 on RANK3.DOC_ID=DOC.DOC_ID
This returns the "overall" rank, and a rank for the 2 component parts, so I can say this doc ranked XXX overall because it scored "rank1" for HTML and scored "rank2" for XML etc....
My question on this part is about the values for the "overall rank". If the query contained an OR it always seems to return the highest of the "rankX" values, and if it doesnt, it returns the lowest. e.g. for the example for java and word and excel and access - the overall ranking is 2 , java=36, word=2, excel=16 and access=36 for java and word or excel and access - the overall ranking is 16 , java=36, word=2, excel=16 and access=36 for (java and word) or (excel and access) - the overall ranking is 16 , java=36, word=2, excel=16 and access=36
So in the first example, regardless of what the other values are, the rank returned is always 2 (the score for "word"). My resultset has 100ish rows, all with a rank of < 5 for word, but all with ranks of 18-100 for the other 3 values - yet the "overall" rank always matched the "word" rank.....?? This doesnt feel right to me somehow, I would expect a different value as if the document ranked really highly for one value but low for the other, it doesnt feel right the value is clamped to the lowest? Or am I just understanding it wrong? If I use "freetexttable" the overall rank is a little more meaningful - but unfortunately I also need to use weighting, which brings me to my next question . . .
This question is about rankings returned from the ISABOUT function. In the following example, select * from documents as DOC inner join containstable(docs,doc,'project') as doc0 on DOC.DOC_ID=doc0."key" inner join containstable(docs,doc,'ISABOUT (project weight (1.0))') as doc1 on DOC.DOC_ID=doc1."key" inner join containstable(docs,doc,'ISABOUT (project weight (0.5))') as doc2 on DOC.DOC_ID=doc2."key" inner join containstable(docs,doc,'ISABOUT (project weight (0.1))') as doc3 on DOC.DOC_ID=doc3."key" inner join containstable(docs,doc,'ISABOUT (project weight (0.0))') as doc4 on DOC.DOC_ID=doc4."key" order by doc0.rank desc
The values I get from the doc1/2/3/4.RANK columns dont seem right. In this example,
doc0.rank = 133 doc1.rank = 150 doc2.rank = 330
doc3.rank = 924
doc4.rank = 0
These values dont make any sense to me, as the rank seems to go UP when the documentation on ISABOUT says it goes down (I think it says somewhere the calculated rank is multiplied by the weight?). Once again, is there something I missed or am I understanding it wrong?
Thanks in advance for any help into understanding the whys of this...
This works when @searchString is used in containstable (provided searchString has value)...
set @searchStringNoneOfWords = 'not(Airplane)' SET @searchString = @searchString + ' AND ' + @searchStringNoneOfWords
This does NOT work when @searchString is used in containstable...
set @searchStringNoneOfWords = 'not(Airplane)' SET @searchString = @searchStringNoneOfWords
I understand it is because the syntax is AND NOT, but what if I have a list of words that I do not want included? How do I start out with a NOT using containstable? It is kind of like Google's advanced search except that if you enter a word in the "without words" section with the other fields blank it would return everything under the sun except for things found with those words.
I am using the ContainsTable function to search a database from my (c#) app. This works relatively well and all fields of the table are indexed and searched. That is, any column, but per record only one column. What I mean is this: when searching for "chris 2007", I want to retrieve all items where author contains chris and year contains 2007. Currently, a search for chris brings up all items where author (or any other field) contains chris, a search for 2007 works as well, but chris 2007 fails as there is no -one- field where chris and 2007 are located. Can anybody help me achieve this? My code is:
Code Block SELECT FT_TBL.ID, FT_TBL.Type, FT_TBL.Author, IsNull(FT_TBL.Author, FT_TBL.Editor + ' (Ed.)') AS CorrectedAuthor, FT_TBL.Editor, FT_TBL.Title, FT_TBL.Abstract, FT_TBL.Comments, FT_TBL.Year, FT_TBL.City, FT_TBL.Publisher, FT_TBL.ISBN, FT_TBL.Pages, FT_TBL.Journal, FT_TBL.Issue, FT_TBL.Hyperlink, FT_TBL.Tags, KEY_TBL.RANK FROM Sources AS FT_TBL INNER JOIN CONTAINSTABLE(Sources, *, @searchQuery) AS KEY_TBL ON FT_TBL.ID = KEY_TBL.[KEY] ORDER BY KEY_TBL.RANK DESC;
I'm trying to use CONTAINSTABLE to do a product search on a number of columns. I want matches on some columns to be weighted higher than for other columns.
It seems like there should be a way to do this with CONTAINSTABLE - I know I can search by multiple strings and weight those matches variably, but what I want is to vary the weight by which column a single term matched. What I have now is a series of UNION queries, which are quite slow: =================== SELECT m.TitleCode, m.ShortName, m.ShortDescription, a.Rank, 50 as theWeight FROM CONTAINSTABLE (Product, ShortName, @theSearchTerm) as a, Product m(NoLock) WHERE a.[KEY] = m.TitleCode
UNION ALL
SELECT m.TitleCode, m.ShortName, m.ShortDescription, a.Rank, 40 as theWeight FROM FREETEXTTABLE (Product, ShortName, @theSearchTerm) as a, Product m(NoLock) WHERE a.[KEY] = m.TitleCode
UNION ALL
SELECT m.TitleCode, m.ShortName, m.ShortDescription, a.Rank, 30 as theWeight FROM FREETEXTTABLE (Product, MatchKeywords, @theSearchTerm) as a, Product m(NoLock) WHERE a.[KEY] = m.TitleCode
UNION ALL
SELECT m.TitleCode, m.ShortName, m.ShortDescription, a.Rank, 20 as theWeight FROM FREETEXTTABLE (Product, ShortDescription, @theSearchTerm) as a, Product m(NoLock) WHERE a.[KEY] = m.TitleCode
I'm working on an SSIS package that uses a vb.net script to grab some XML from a webservice (I'd explain why I'm not using a web service task here, but I'd just get angry), and I wish to then assign the XML string to a package variable which then gets sent along to a DataFlow Task that contains an XML Source that points at said variable. when I copy the XML string into the variable value in the script, if do a quickwatch on the variable (as in Dts.Variable("MyXML").value) it looks as though the new value has been copied to the variable, but when I step out of that task and look at the package explorer the variable is its original value.
I think the problem is that the dataflow XML source has a lock on the variable and so the script task isn't affecting it. Does anyone have any experience with this kind of problem, or know a workaround?
I have a SQL Task that updates running totals on a record inserted using a Data Flow Task. The package runs without error, but the actual row does not calculate the running totals. I suspect that the inserted record is not committed until the package completes and the SQL Task is seeing the previous record as the current. Here is the code in the SQL Task:
DECLARE @DV INT; SET @DV = (SELECT MAX(DateValue) FROM tblTG); DECLARE @PV INT; SET @PV = @DV - 1;
I've not been successful in passing a SSIS global variable to a declared parameter, but is it possible to do this:
DECLARE @DV INT; SET @DV = ?; DECLARE @PV INT; SET @PV = @DV - 1;
I have almost 50 references to these parameters in the query so a substitution would be helpful.
I'm new to SSIS, but have been programming in SQL and ASP.Net for several years. In Visual Studio 2005 Team Edition I've created an SSIS that imports data from a flat file into the database. The original process worked, but did not check the creation date of the import file. I've been asked to add logic that will check that date and verify that it's more recent than a value stored in the database before the import process executes.
Here are the task steps.
[Execute SQL Task] - Run a stored procedure that checks to see if the import is running. If so, stop execution. Otherwise, proceed to the next step.
[Execute SQL Task] - Log an entry to a table indicating that the import has started.
[Script Task] - Get the create date for the current flat file via the reference provided in the file connection manager. Assign that date to a global value (FileCreateDate) and pass it to the next step. This works.
[Execute SQL Task] - Compare this file date with the last file create date in the database. This is where the process breaks. This step depends on 2 variables defined at a global level. The first is FileCreateDate, which gets set in step 3. The second is a global variable named IsNewFile. That variable needs to be set in this step based on what the stored procedure this step calls finds out on the database. Precedence constraints direct behavior to the next proper node according to the TRUE/FALSE setting of IsNewFile.
If IsNewFile is FALSE, direct the process to a step that enters a log entry to a table and conclude execution of the SSIS.
If IsNewFile is TRUE, proceed with the import. There are 5 other subsequent steps that follow this decision, but since those work they are not relevant to this post. Here is the stored procedure that Step 4 is calling. You can see that I experimented with using and not using the OUTPUT option. I really don't care if it returns the value as an OUTPUT or as a field in a recordset. All I care about is getting that value back from the stored procedure so this node in the decision tree can point the flow in the correct direction.
The SSIS package passes the FileCreateDate parameter to this procedure, which then compares that parameter with the date saved in tbl_ImportFileCreateDate.
If the date is newer (or if there is no date), it updates the field in that table and returns a TRUE IsNewFile bit value in a recordset.
Otherwise it returns a FALSE value in the IsNewFile column.
SELECT @CreateDateInTable = FileCreateDate FROM tbl_ImportFileCreateDate WHERE ProcessName = @ProcessName
IF EXISTS (SELECT ProcessName FROM tbl_ImportFileCreateDate WHERE ProcessName = @ProcessName)
BEGIN
-- The process exists in tbl_ImportFileCreateDate. Compare the create dates.
IF (@FileCreateDate > @CreateDateInTable)
BEGIN
-- This is a newer file date. Update the table and set @IsNewFile to TRUE.
UPDATE tbl_ImportFileCreateDate
SET FileCreateDate = @FileCreateDate
WHERE ProcessName = @ProcessName
SET @IsNewFile = 1
END
ELSE
BEGIN
-- The file date is the same or older.
SET @IsNewFile = 0
END
END
ELSE
BEGIN
-- This is a new process for tbl_ImportFileCreateDate. Add a record to that table and set @IsNewFile to TRUE.
INSERT INTO tbl_ImportFileCreateDate (ProcessName, FileCreateDate)
VALUES (@ProcessName, @FileCreateDate)
SET @IsNewFile = 1
END
SELECT @IsNewFile
The relevant Global Variables in the package are defined as follows: Name : Scope : Date Type : Value FileCreateDate : (Package Name) : DateType : 1/1/2000 IsNewFile : (Package Name) : Boolean : False
Setting the properties in the "Execute SQL Task Editor" has been the difficult part of this. Here are the settings.
General Name = Compare Last File Create Date Description = Compares the create date of the current file with a value in tbl_ImportFileCreateDate. TimeOut = 0 CodePage = 1252 ResultSet = None ConnectionType = OLE DB Connection = MyServerDataBase SQLSourceType = Direct input IsQueryStoredProcedure = False BypassPrepare = True
I tried several SQL statements, suspecting it's a syntax issue. All of these failed, but with different error messages. These are the 2 most recent attempts based on posts I was able to locate. SQLStatement = exec ? = dbo.p_CheckImportFileCreateDate 'GL Account Import', ?, ? output SQLStatement = exec p_CheckImportFileCreateDate 'GL Account Import', ?, ? output
Parameter Mapping Variable Name = User::FileCreateDate, Direction = Input, DataType = DATE, Parameter Name = 0, Parameter Size = -1 Variable Name = User::IsNewFile, Direction = Output, DataType = BYTE, Parameter Name = 1, Parameter Size = -1
Result Set is empty. Expressions is empty.
When I run this in debug mode with this SQL statement ... exec ? = dbo.p_CheckImportFileCreateDate 'GL Account Import', ?, ? output ... the following error message appears.
SSIS package "MyPackage.dtsx" starting. Information: 0x4004300A at Import data from flat file to tbl_GLImport, DTS.Pipeline: Validation phase is beginning.
Error: 0xC002F210 at Compare Last File Create Date, Execute SQL Task: Executing the query "exec ? = dbo.p_CheckImportFileCreateDate 'GL Account Import', ?, ? output" failed with the following error: "No value given for one or more required parameters.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: Compare Last File Create Date
Warning: 0x80019002 at GLImport: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "MyPackage.dtsx" finished: Failure.
When the above is run tbl_ImportFileCreateDate does not get updated, so it's failing at some point when calling the procedure.
When I run this in debug mode with this SQL statement ... exec p_CheckImportFileCreateDate 'GL Account Import', ?, ? output ... the tbl_ImportFileCreateDate table gets updated. So I know that data piece is working, but then it fails with the following message.
SSIS package "MyPackage.dtsx" starting. Information: 0x4004300A at Import data from flat file to tbl_GLImport, DTS.Pipeline: Validation phase is beginning.
Error: 0xC001F009 at GLImport: The type of the value being assigned to variable "User::IsNewFile" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.
Error: 0xC002F210 at Compare Last File Create Date, Execute SQL Task: Executing the query "exec p_CheckImportFileCreateDate 'GL Account Import', ?, ? output" failed with the following error: "The type of the value being assigned to variable "User::IsNewFile" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object. ". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. Task failed: Compare Last File Create Date
Warning: 0x80019002 at GLImport: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (3) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "MyPackage.dtsx" finished: Failure.
The IsNewFile global variable is scoped at the package level and has a Boolean data type, and the Output parameter in the stored procedure is defined as a Bit. So what gives?
The "Possible Failure Reasons" message is so generic that it's been useless to me. And I've been unable to find any examples online that explain how to do what I'm attempting. This would seem to be a very common task. My suspicion is that one or more of the settings in that Execute SQL Task node is bad. Or that there is some cryptic, undocumented reason that this is failing.
I am in the middle of taking course 2073B €“ Programming a Microsoft SQL Server 2000 Database. I noticed that in Module9: Implementing User-Defined Functions exercise 2, page 25; step 2 is not returning the correct answer.
Select employeeid,name,title,mgremployeeid from dbo.fn_findreports(2)
It returns manager id for both 2 and 5 and I think it should just return the results only for manager id 2. The query results for step 1 is correct but not for step 2.
Somewhere in the code I think it should compare the inemployeeid with the previous inemployeeid, and then add a counter. If the two inemployeeid are not the same then reset the counter. Then maybe add an if statement or a case statement. Can you help with the logic? Thanks!
Here is the code of the function in the book:
/* ** fn_FindReports.sql ** ** This multi-statement table-valued user-defined ** function takes an EmplyeeID number as its parameter ** and provides information about all employees who ** report to that person. */ USE ClassNorthwind GO /* ** As a multi-statement table-valued user-defined ** function it starts with the function name, ** input parameter definition and defines the output ** table. */ CREATE FUNCTION fn_FindReports (@InEmployeeID char(5)) RETURNS @reports TABLE (EmployeeID char(5) PRIMARY KEY, Name nvarchar(40) NOT NULL, Title nvarchar(30), MgrEmployeeID int, processed tinyint default 0) -- Returns a result set that lists all the employees who -- report to a given employee directly or indirectly AS BEGIN DECLARE @RowsAdded int -- Initialize @reports with direct reports of the given employee INSERT @reports SELECT EmployeeID, Name = FirstName + ' ' + LastName, Title, ReportsTo, 0 FROM EMPLOYEES WHERE ReportsTo = @InEmployeeID SET @RowsAdded = @@rowcount -- While new employees were added in the previous iteration WHILE @RowsAdded > 0 BEGIN -- Mark all employee records whose direct reports are going to be -- found in this iteration UPDATE @reports SET processed = 1 WHERE processed = 0
-- Insert employees who report to employees marked 1 INSERT @reports SELECT e.EmployeeID, Name = FirstName + ' ' + LastName , e.Title, e.ReportsTo, 0 FROM employees e, @reports r WHERE e.ReportsTo = r.EmployeeID AND r.processed = 1 SET @RowsAdded = @@rowcount -- Mark all employee records whose direct reports have been -- found in this iteration UPDATE @reports SET processed = 2 WHERE processed = 1 END RETURN -- Provides the value of @reports as the result END GO
insert into #t(branchnumber) values (005) insert into #t(branchnumber) values (090) insert into #t(branchnumber) values (115) insert into #t(branchnumber) values (210) insert into #t(branchnumber) values (216)
[code]....
I have a parameter which should take multiple values into it and pass that to the code that i use. For, this i created a parameter and temporarily for testing i am passing some values into it.Using a dynamic SQL i am converting multiple values into multiple records as rows into another variable (called @QUERY). My question is, how to insert the values from variable into a table (table variable or temp table or CTE).OR Is there any way to parse the multiple values into a table. like if we pass multiple values into a parameter. those should go into a table as rows.
Hello, I run the DTS to copy data from Progress to SQL Server. How can match/convert the date variables to check. The field p-date as format 'mm-dd-year' . It has the value of 10/09/2001. The field s-date as varchar format. It has the value 2001-10-09. How can use the where condition ( Select ...... WHERE p-date = s-date.)
Hello! I'm using SQL Server 2000. I have a variable which contains the name of another variable scoped in my stored procedure. I need to get the value of that other variable, namely:
CREATE table #myTable(value VARCHAR(20)) INSERT into #myTable values('@operation')
SELECT top 1 @parameterValue = value from #myTable -- Now @parameterValue is assigned the string '@operation' -- Here I need some way to retrieve the value of the @operation variable I declared before (in fact -- another process writes into the table I retrieved the value from), in this case 'DIS'
DROP TABLE #myTable
I've tried several ways, but didn't succeed yet! Please tell me there's a way to solve my problem!!!
I have an SSIS package that creates a csv file based on a execute sql task.
The sql is incredibly simple select singlecolumn from table.
In the properties of the execute sql task I specify the result set as "full result set" when I run it I get the error that: Error:
The type of the value being assigned to variable "User::CSVoutput" differs from the current variable type.
Variables may not change type during execution. Variable types are strict, except for variables of type Object.
If I change the resultset to single row then I only get the first row from the DB (same if I choose none), If I choose XML then I get the error that the result is not xml. What resultset type do I need to choose in order to get all the rows into the CSV? The variable I am populating is of type string and is User::CSVoutput
