Getting First 200 Chars Of Text Stored In Variable
Apr 21, 2008Hi all,
I have a variable called Description which is set from a database field - How do i set another variable to grab the first 200 chars from this var?
hope this makes sense
ADVERTISEMENT
Help - Still Trying To Insert 255+ Chars Into Text
Jun 7, 1999How do you insert more than 255 characters into a text field? or am I using READTEXT and DATALENGTH incorrectly?
I am trying to concatenate two 255 varchar strings. Both do have 255 characters of data.
I want to insert the concatenated strings into a field define as a text data type.
if I try to ' insert into tempTbl(textFieldDataType) values (@text1+@text2) it appears only the @text1 is inserted.
I have been told the data is really there, but I can only read the 256 position + with READTEXT.
DECLARE @textptr varbinary(16)
select @textptr = (select TEXTPTR(textFieldDataType) from tempTbl where pktempTbl = 33)
READTEXT tempTbl.textFieldDataType @textPtr 255 10
This should have read 10 text positions after the 255th position. However, I get msg
Msg 7124, Level 16, State 1
The offset and length specified in the READTEXT command is greater than the actual data length of 255.
If I use the datalength function, it appears to confirm that @text2 was never inserted
declare @length int
select @length = (select datalength(textFieldDataTypet) from tempTbl where pktempTbl = 33)
select @length
@length is returned as 255
All Text Fields Truncated To 255 Chars
Jun 25, 2006Hi
I defined the following table in MSSQL 2000
sql Code:
Original
- sql Code
CREATE TABLE saved_query (
id INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
name VARCHAR(32) NOT NULL,
query_text VARCHAR(2048) NOT NULL
)
CREATE TABLE saved_query ( id INT NOT NULL IDENTITY(1,1) PRIMARY KEY, name VARCHAR(32) NOT NULL, query_text VARCHAR(2048) NOT NULL)
and whenever I insert or update the query_text field, it's always truncated to 255 characters. Why? I clearly specified a larger field size. I also tried defining query_text as a "TEXT" but got the same result.
Thanks for any help
Ken
SQL Server 2K Truncating Text Fields At 255 Chars
Jul 20, 2005I'm running SQL Server 2K (sp3a) and when I run the following query (inquery analyzer):SELECT id, LEN(ForeignWord) as Length, ForeignWordFROM WordsWHERE Language ='Thai' and LEN(ForeignWord) > 300ORDER BY Length descI receive the following results:id Length ForeignWord------- ----------- -----------34756 445 เ#...truncated at 25534839 412 เ$...truncated at 25537613 350 โ#...truncated at 25537808 315 โ#...truncated at 25538140 315 โ#...truncated at 255(The ForeignWord field is defined as varchar(1000))Note that even though the server says that the lengths are in excess of255 characters, the results are all truncated at 255 characters.I read that SQL Server 6.5 and 7.0 had some issues related to nativeaccess versus OleDb or ODBC, but this is Server 2K patched with thelatest updates.Any idea why my varchar fields are being truncated?-- RickHere is my table definition:CREATE TABLE [Words] ([ID] [bigint] IDENTITY (1, 1) NOT NULL ,[Language] [varchar] (50) NOT NULL ,[Module] [int] NOT NULL ,[Lesson] [int] NOT NULL ,[EnglishWord] [varchar] (1000) NOT NULL ,[ForeignWord] [varchar] (1000) NOT NULL ,[Note] [varchar] (2000) NULL ,[Military] [tinyint] NOT NULL CONSTRAINT [DF_Table1_Military]DEFAULT (0),[Supplemental] [tinyint] NOT NULL CONSTRAINT [DF_Table1_Supplemental] DEFAULT (0),[SoundFileName] [varchar] (1000) NULL)
View 4 Replies View RelatedBULK Insert From A Text File Name Stored Into A Variable
Mar 25, 2004Hello I need to write a proc to load data from txt files I receive into a table. It works fine when I specify
bulk insert.... from 'myfilename.txt'
BUT my filename will always change and I store it into a variable @filename
When I try to run the bulk insert instruction ... from @filename it doesn't work..
do you know why?
Thank you in advance
Can You Parse Out A Text String &> 8000 Chars Long??
Mar 30, 2005Any way to parse out a text value (not varChar, using text data type) that is > than 8000 characters long? I'm looping through 1 big string passed to the DB that is pipe delimited, but I find myself needing the substring function to keep track of which segment I'm acting on (after an update, I then need to take that segment and remove it from the string)...but the subString function won't take anything larger than 8000 chars.
Say I have this string that is text data type...
'aaa|bbb|ccc|ddd|....'
..and so on, surpassing 8000 char length, how could you parse it out using the pipes as the delimter, then do an Update using that segment? Afterward, return to that string and find the next segment, then use it, and so on (in a loop). I tried using an update to set the string = replace(string, segmentJustUsed, '') to "erase" it, but replace can't take text as the datatype. Any help? Hope this isn't to confusing.
Syscomments Table Text Field Does Not Allow More Than 8060 Chars
Oct 18, 2006Hello,
I have a table in SQL Server 2000 which has few triggers. When I try to update a record in that table, I get following message:
Warning: The table '[TABLE_NAME]' has been created but its maximum row size (17275) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.
where the data I am updating is well less than the field limit. The triggers are stored in syscomments table and when I dropped these triggers, the update statements were executed without this message. Is there anyway I can change the data type of field text in syscomments from nvarchar to ntext?
Thanks
Din
Full-text Ignores Special Chars In Sql 2005
Oct 12, 2007We had a sql 2000 db that had full text implemented on it. We upgrade that server to a 2005 server and now the full text searches that once worked don't. Most do work just the ones with ; or special characters in the query string don't work.
Anyone have the same behavior and how to get it to wrok again?
thanks
TEXT Data Type Column: Replacing Chars : Why Isn't This Routine Working?
Jul 20, 2005Hi;I am trying to write a rountine ( below ) that will go into a colum oftext data type ( fae.pmcommnt ) locate the word "to" and replace it.I have the routine below. I get no error messages, but it also seemsto do nothing :).Any clues would be greatly appreciated.ThanksSteve================================================== =============declare @ptrP intSELECT @ptrP = PATINDEX('%to%', pmcommnt)from fae where projid ='00013'declare @ptrPC binary(16)select @ptrPC = TEXTPTR(pmcommnt)from faeif( TEXTVALID ('fae.pmcommnt', @ptrPC ) > 0 )print 'works'print @ptrPUPDATETEXT fae.pmcommnt @ptrPC @ptrP 2 'JJ'select projid, pmcommnt from fae
View 2 Replies View RelatedTSQL - Select First 3 Chars Where Not Special Chars
Feb 5, 2002Say I have a column called 'NAME' in a table called 'CLIENT' and the values in NAME are Surnames or company names like:
NAME
----------------------
1-FOR-ALL
A.B. SMITH (TOOLS LTD)
BROWN
THOMSON
VW CAR SALES
I want my select to return the first 3 characters, excluding special characters (only characters between 1 and z).
In example, the following would be returned for the data above:
NAME
----------------------
1FO
ABS
BRO
THO
VWC
TSQL - Select First 3 Chars Where Not Special Chars
Feb 11, 2002Say I have a column called 'NAME' in a table called 'CLIENT' and the values in NAME are Surnames or company names like:
NAME
----------------------
1-FOR-ALL
A.B. SMITH (TOOLS LTD)
BROWN
THOMSON
VW CAR SALES
I want my select to return the first 3 characters, excluding special characters (only characters between 1 and z).
In example, the following would be returned for the data above:
NAME
----------------------
1FO
ABS
BRO
THO
VWC
Non English Chars Are Being Shown As Junk Chars
Jan 9, 2008Hi All
I have loaded some data to the application using flat files
which has non english chars.
all the columns in the database are NVARCHAR type.
but in db and in application UI, the non english chars are being diplayed as junk chars. ???121
The application supports UTF-8 format
is there any setting at db level to be modified to display the non english char set as is.
Thanks
Chinese Chars Through Stored Procedures...
Jul 20, 2005Hi,I have a problem with my Stored Procedures...Recently we decided to change the type of our column in our databse fromvarchar to nvarchar because of new customers (chinese).Everything works fine EXCEPT the stored procedures... When i try to passchinese characters for a simple SP that those a basic insert in my table, itinserts ??? instead of chinese characters...Did i miss something obvious?Thanks a lot!Etiennep.s.: you can email me at Join Bytes! or reply on thisnewsgroup.
View 1 Replies View RelatedTruncation Error: 255 Chars To 2 Chars.
Aug 27, 2007Hi,
I am very new to using SSIS.
Trying to import data from MS Access 2000.
I receive the error "
[OLE DB Destination [1907]] Warning: Truncation may occur due to inserting data from data flow column "GENDER" with a length of 255 to database column "GENDER" with a length of 2. " on the source flow.
I have done some googling and came up with this post: http://torontosql.dotnetnuke-portal.com/Default.aspx?tabid=32233 which I thought may help, but it does not.
The query against the access datasource features the column: iif([sex]=1, 'm', 'f'). I tried using left(..., 2) as well, but SSIS is determind to treat the field as 255 characters for some reason.
I don't even particualrly care that the field is 255 chars and the sources is only two, I just want the data in! I have other fields coming up with similar error.
Can someone please advise?
PS, what is th significance of the "External Columns" Vs "Output Columns" on the Input and Output Properties tab in Advanced Editor?
I am really struggling with SSIS, it is not as intuitive as DTS.
How Do You Construct A Dynamic Stored Procedure With More Than 4000 Chars
Sep 12, 2007My problem (excuse me for being a novice) is that whenever the query becomes complex
in the @whereclause (and it does) it cant exceed the character limit of a nvarchar. Now if someone could shed some ligfht on this for me I would be
incredibly grateful. I have tried doing the exec(@var1 + @var2) but this did not seem to work.
I have included the stored procedure below.
ALTER PROCEDURE [dbo].[SearchTenderMultiRegions]
(
@whereclause nVarChar(MAX), --4000 chars
@PageIndex int,
@PageSize int
) AS
SET NOCOUNT ON;DECLARE @sql nvarChar(MAX)
SET @sql= 'SELECT * FROM (SELECT DISTINCT TOP (' + CONVERT(nvarchar(10),(@PageIndex*@PageSize))
SET @sql= @sql + ') ROW_NUMBER() OVER (ORDER BY Tenders.ID DESC)AS Row,
Tenders.ID,Tenders.Title,Address.Suburb,Tenders.ClosingDateTime,Tenders.IsApproved,Tenders.TendersSourceID, Tenders.SourceDate,
UserTracking.CreateUserID,UserTracking.CreateDateTime FROM Tenders LEFT JOIN TenderContact ON Tenders.ID = TenderContact.TendersID
LEFT JOIN TenderCategory ON Tenders.ID = TenderCategory.TendersID
LEFT JOIN TenderRegion ON Tenders.ID = TenderRegion.TendersID
LEFT JOIN RegionStateCountry ON TenderRegion.RegionStateCountryID = RegionStateCountry.ID
LEFT JOIN Address ON Tenders.ID = Address.TendersID
LEFT JOIN UserTracking ON Tenders.ID = UserTracking.TendersID
WHERE '
SET @sql= @sql + @whereclause
SET @sql = @sql + ') as TenderSearchEntries
WHERE Row between ('
SET @sql= @sql + CONVERT(nvarchar(10), @PageIndex)
SET @sql= @sql + ' - 1) * '
SET @sql= @sql + CONVERT(nvarchar(10), @PageSize)
SET @sql= @sql + '+ 1 and '
SET @sql= @sql + CONVERT(nvarchar(10), @PageIndex)
SET @sql= @sql + ' * '
SET @sql= @sql + CONVERT(nvarchar(10), @PageSize)
EXEC(@sql)
SSIS: Problem Mapping Global Variables To Stored Procedure. Can't Pass One Variable To Sp And Return Another Variable From Sp.
Feb 27, 2008I'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.
CREATE PROCEDURE [dbo].[p_CheckImportFileCreateDate]
/*
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.
Example:
exec p_CheckImportFileCreateDate 'GL Account Import', '2/27/2008 9:24 AM', 0
*/
@ProcessName varchar(50)
, @FileCreateDate datetime
, @IsNewFile bit OUTPUT
AS
SET NOCOUNT ON
--DECLARE @IsNewFile bit
DECLARE @CreateDateInTable datetime
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.
Thanks for your help.
Text Local Variable
Jan 17, 2007Hi All,
How to declare TEXT datatype as local variable . My data is getting truncated as it cross varchar(8000) characters.
Saju
Variable And Text File
Nov 30, 2007Hi All,
I was wondering if anybody knows how to put the values in the variable to a text file. I have a stored procedure that has a cursor that pulls certan records and then place them into a variable. I want to put the variable values into a text file. Any ideas?
Thanks.
Text String As Variable?
May 9, 2014I want to run the following code:
Declare@Testnames as Varchar(500)
Set@Testnames = ('CT,MRI')-- List all radiology type
SELECT*
FROMRPT_OUTPATIENT_IMAGING
WHEREservice_level2 In (@Testnames)
Nothing comes back.
When I run:
SELECT*
FROMRPT_OUTPATIENT_IMAGING
WHEREservice_level2 In ('CT','MRI')
Then I get results.
Text Variable In TRIGGER
May 19, 2006recently I wrote a TRIGGER , but I found the text is not allowed to be used.
such as:
in the trigger:
declare @content text
Is it illegal??? And why?
thank you!
Using TEXT Datatype As Local Variable In SP Or UDF
Mar 12, 2003I've created a stored procedure that converts an input string in richtext format (input as type TEXT) to plain text. I would like to be able to return this newly converted string, but I need to have some way of storing it in a local variable. My problem is that since I can't use the TEXT datatype as a local variable, I have no way of storing the large amounts of text I converted within the procedure. The VARCHAR(8000) just isn't large enough for my purposes. Anyone have any suggestions on how to go about doing this?
View 2 Replies View RelatedSQL 2012 :: How To Assign Text To A Variable
Sep 22, 2015I have this big huge SQL statement. I am trying to see whether there is some quick magic way to assign it to the variable @SQL
Declare @SQL NVARCHAR(MAX)
Set @SQL = <????>
SELECT ClaimSurgical.FormNbr as ClaimIdentifier,ClaimSurgical.MemberTID, ClaimSurgical.Membernbr as MemberNumber, ClaimSurgical.AdmitDate AS ServiceDateYYYYMMDD,
CAST(CAST(ClaimSurgical.AdmitDate AS VARCHAR) AS DATETIME) ServiceDate,ClaimSurgical.ProviderNbr as ClaimPRVNO, ClaimSurgical.VendorNbr as ClaimTaxID,
ClaimSurgical.AssignedProvider1 as PCPPRVNO,ClaimSurgical.CAPVendor as PCPTaxID, EventCode_1.EventCodeTypeID,
[code]....
How To Add Variable To Sql Command Text Field
Sep 7, 2007In the data flow task I have multiple OLE DB source task to retreive data from a cube using MDX. Below is an example of the query from sql command text field: I have a few questions on how to modify this statment to make it more robust.
1) The where clause [Calendar].[Quarter 2 (2007)] should actually be some kind of variable that will change each time
the user runs this project. So I am wondering how to make this a variable and get it into this field.
2) I had thought that I could use the SSIS variable. I have created one, but I can not figure how to get it into the field that
contains the given select statment.
3) I believe that once I get this variable part to work then I want a way to have user set this data value. Either by selecting data from a table in database or through a user interface where user enters data. I did do some resarch on creating a user interface, but I did not understand what I had to do, so if any one knows where to find a tutorial on how to do this let me know, or what they believe the best/easiest way is to get data from user to fill this where clause.
select * from OPENROWSET('MSOLAP', 'DATASOURCE=local; Initial Catalog=Patient Demographics 2005;',
'with member [Measures].[TimeDisplayName] as [Calendar].CurrentMember.Name
SELECT NON EMPTY { [Measures].[TimeDisplayName],[Measures].[Adjusted Relative Weight],[Measures].[Adjusted Case Weight]} ON COLUMNS,
({[Facility].[REGION].[NATCODE], [Facility].[REGION].[REGCODE]} *
[RIC].[CMGGRPCD].ALLMEMBERS) ON ROWS FROM [ESR]
WHERE [Calendar].[Quarter 2 (2007)]
')
Using Variable In DataReader's SqlCommand Text
Jan 17, 2007I'm attempting to configure a DataReader's SqlCommand text to use a package-level variable and I'm having trouble. I've read about using the "sql command from variable" option but although I see that during the configuration of Ole DB source, I don't see the same option for DataReader source. Viewing the properties of the DataReader and selecting the SqlCommand property does have button to configure the value but only displays a string value editor window. (I was hoping for the expression editor.)
The configured connection to the DataReader uses the odbc Data Provider (under .Net providers). I'm connecting to mySql, if that matters.
What I'm looking to do is to use a variable in the where clause of the DataReader's SqlCommand. Any hints how to accomplish this or other ideas would be greatly appreciated.
Thanks
Using Table Name Stored In A Scalar Variable In Stored Procedure Problem
Mar 27, 2006Hello to all!
I have a table name stored in a scalar variable (input parameter of my stored procedure). I need to run SQL statement: SELECT COUNT (*) FROM MyTable and store the result of my query in a scalar variable:
For example:
declare @countRows int
set @countRows = (select count(*) from MyTable)
The problem is that the name of MyTable is stored in the input variable of my stored procedure and of corse this does not work:
declare @countRows int
set @countRows = (select count(*) from @myTableName)
I also tried this:
declare @sqlQuery varchar(100)
set @sqlQuery = 'select count(*) from ' + @myTableName
set @countRows = exec(@sqlQuery)
But it looks like function exec() does not return any value...
Any idea how to solve this problem?
Thanx,
Ziga
How? : Using A Varchar, Text Data Type Variable As Valid Column Name.
Jun 2, 2008Cannot use dynamic sql in current context. So need some help regarding this.I am developing a stored procedure to update a table. Sending Column names as parameters, but not able to use them as given below.INSERT INTO Books (@Column1, @Column2) values.. Any way to execute without using dynamic sql?..Thanx.
View 1 Replies View RelatedSSIS Newbie Question - Get Strings From Text File Into A Variable
Aug 23, 2007HI All. I'm trying to tweak the Transfer Logins task to exclude Windows Logins that are local to the Server (e.g. servernameusername) which obviously can't be transferred off the server. Annoying that we have a couple of local Logins on this system instead of all Domain Groups, but we're stuck with them due to firewall issues, and a policy excluding SQL Logins.
My idea is to create a text file as part of my Package that lists Logins to be Excluded From the Transfer - I think I then need to create a New File Connection to the Text File as a Connection Manager, then somehow get that data into a Variable, and then use an Expression to populate the 'LoginsList' Collection from syslogins where loginame not equal to logins in my textfilevariable?
Or maybe I'm over complicating this, and there's an easier solution? Lots of info in Books Online about Expressions and Variables, but having trouble finding examples that I can use. As a DBA, this is my first foray into SSIS, and as you can possibly tell, I'm floundering....
Integration Services :: How To Read A Specific String From Text File As Output Variable In SSIS
Nov 5, 2015I am downloading a webpage as a text file in order to read a specific string to assign it as a variable/parameter in order to create an output file name. I would like to know how would I be able to look for a specific string and output as another variable for the rest of the package.
2015 Conforming Loan Limits
------------------------------------------------------------------------
o _Loan Limits for Calendar Year 2015--All Counties _[XLS]
</DataTools/Downloads/Documents/Conforming-Loan-Limits/FullCountyLoanLimitList2015_HERA-BASED_FINAL_FLAT.xlsx>_ ,
_[PDF]
</DataTools/Downloads/Documents/Conforming-Loan-Limits/FullCountyLoanLimitList2015_HERA-BASED_FINAL.pdf>_
o _List of 46 Counties with Increases in Loan Limits for 2015
[Code] ...
To explain it a more better way, I have a sample webpage text here. I should be searching for "FullCountyLoanLimitList" appended by the current year (like FullCountyLoanLimitList2015) and copy the entire file name in the text file and assign it to another variable so that I can download that specific file using WebClient connection.
Remove Chars
Mar 13, 2001I have a field name call firstname and other field middleinit, now the table contains data. and i want to extract data, the firstname field sometimes contains the middle initial, for for example JOHH D.
now I would like to set the firstname field with JOHN only and and set the middleinit field like D only
thanks
Can't Contain Non-alphabetic Chars
Jun 11, 2008With this procedure,can't show if GiftTitle contains space ,nonalphabetic chars(&,-,..) and if start with numnber.
How can i modify ??
Plz help me.
Thanks.
CREATE PROCEDURE dbo.trymatrix AS
DECLARE @SQLCommand varchar(4096), @column sysname
SET @SQLCommand = 'select c.CustomerID as Cust_ID,Name as Co_Name, ' + char(13)
DECLARE curGift CURSOR STATIC LOCAL FOR
select distinct GiftTitle from Redemption1
OPEN curGift
WHILE 1 = 1
BEGIN
FETCH curGift INTO @column
IF @@FETCH_STATUS <> 0
BREAK
SET @SQLCommand = @SQLCommand +
'sum(case GiftTitle when ''' +@column +''' then 1 else null end) as '+ @column + ','+char(13)
END
DEALLOCATE curGift
SET @SQLCommand = left (@SQLcommand, len(@SQLcommand)-2) + char(13)
SET @SQLCommand = @SQLCommand +
'from Customer c
inner join Redemption1 d
on c.CustomerID=d.CustomerID
group by Name,c.CustomerID
order by c.CustomerID'
PRINT @SQLCommand
EXEC (@SQLCommand)
Set Variable With Stored Procedure Or...
Jan 31, 2007hi how can I set my variable value with a query??? I am about to make a procedure to get a comma seperatet array and insert into the table. if I omit ID (primary key) and set it as Identity every thing is ok but I don't want it to be Identity. so I have to find the max of ID and then add it to the first of my comma seperated values. the problem is I have a table name as nvarchar in the parameters. so I have to first make a query as string and then execute it. anyway I need to get the value of the query string and assingn @ID variable to it's return value. how can I do it? so just suppose that I have @Values, @TableName (values are in the right order) then I want to find max(ID)+1 in @TableName (something like "select max(id)+1 from @TableName" I know that it will not work. so I make a string and execute it ) and add it to the first of my @Values then my problem will solve thank you in advance regards
View 3 Replies View RelatedUsing Top @variable In A Stored Procedure?
Oct 2, 2000I am building a stored procedure to get a list of rows, and I want to limit the rows returned to a variable. However, i keep getting a syntax error.
SQL:
SELECT TOP @TopArticles ArticleTable.*, ArticleSubSectionTable.SubSectionID AS SSID, ArticleTable.ArticleDate AS EXPR1
FROM ArticleTable
ERROR
Error 170: Line 28: Incorrect Syntax near '@TopArticles'
Any help would be appreciated,
PK
Variable DB Name In Stored Prcd.
Oct 19, 1999Hi
Does anybody know how to define a DB Name in stored procedure
so that DB Name is variable???
i.e.
insert into DB1.dbo.Table1
select * from DB2.dbo.Table1
DB1 and DB2 should be variable.
Thanks
Markus