READTEXT And EXECUTE
May 6, 1999
I am working on a SQL 6.5 application that contains several tables with text fields. I would like to write a single stored procedure that could build the READTEXT statement for the text field on the fly based on parameters I pass in for the table name, column name, etc.
The procedure I want to write would look something like:
declare @txtpnt varbinary(16)
declare @column varchar(15)
declare @table varchar(15)
declare @idfield varchar(15)
declare @idval varchar(15)
EXECUTE ('select @txtpnt = textptr(' + @column + ')from ' +
@table + ' where ' + @idfield + ' = ' + @idval)
EXECUTE ('READTEXT ' + @table + '.' + @column + ' ' + @txtpnt + ' 0 10000')
Pretty as this looks, if you run this code, you will find that it doesn't work. The EXECUTE statement will not allow you to use a variable in the statement that is not declared as part of the statement. However, if I declare the variable as part of the EXECUTE statement, it is not recognized at compile time and I get an error when I create the stored procedure.
Is this the brick wall it seems to be, or am I missing something obvious here? I am using Access 97 as my front end, if that helps at all.
Any insight would be appreciated!
View 1 Replies
ADVERTISEMENT
Aug 16, 1999
I am having issues READING an entire text field.
Also the SET TEXT command is returning only 255, too! Help!
My code:
DECLARE @ptrval binary(16)
, @TextSize int
SELECT @TextSize = (Select dataLength(pr_info)
FROM pub_info pr INNER JOIN publishers p
ON pr.pub_id = p.pub_id
AND p.pub_name = 'New Moon Books')
SELECT @ptrval = TEXTPTR(pr_info)
FROM pub_info pr INNER JOIN publishers p
ON pr.pub_id = p.pub_id
AND p.pub_name = 'New Moon Books'
READTEXT pub_info.pr_info @ptrval 0 @TextSize
View 2 Replies
View Related
May 2, 2008
hi, has anyone ever tried saving the result of 'readtext' (ofcourse, not the entire text - just some of it usnig offsets in the readtext as : readtext texttab.textcolumn @ptr 0 100 ) into a 'varchar' variable ? if so, can you give me an example pls ?
thanks.
View 1 Replies
View Related
Apr 12, 2004
SELECT @@TEXTSIZE
SET TEXTSIZE 4096
SELECT @@TEXTSIZE
GO
DECLARE @ptrval varbinary(16)
SELECT @ptrval = TEXTPTR(emailTemplates.Data)
FROM emailTemplates WHERE [Name] = 'AccountInfoReceipt1'
READTEXT emailTemplates.Data @ptrval 0 2000
GO
Thats my code. and it never returns the entire email template. why?
thx in adv
View 2 Replies
View Related
Apr 29, 2004
First question :
How can I set the return value of READTEXT to a variable of type nvarchar.
Second question :
I have a table t1 with a ntext column n1.
The ntext column has has words separated by empty space.
Each word can be assumed to be of size <= 255 characters.
How can I extract all the keywords in the ntext column to a table t2 with a column word nvarchar(255).
Assume that the text in column n1 is big enough so that it cannot be cast into a nvarchar or any other simpler type.
Any help on this is greatly appreciated.
Please do provide a sample code.
Alok.
View 1 Replies
View Related
Aug 31, 2007
In my stored procedure I need to do a WRITETEXT on some text data which is being returned from a READTEXT on other text data. I thought that this sort of thing would work:
DECLARE @comment varbinary(16)
DECLARE @comment_len int
DECLARE @comment_new varbinary(16)
...
SET @comment = TEXTPTR(atextcolumn) from atable where ...
SET @comment_len = DATALENGTH(atextcolumn) from atable where ...
SET @comment_new = TEXTPTR(anothertextcolumn) from anothertable where ...
WRITETEXT anothertextcolumn @comment_new READTEXT atextcolumn @comment 0 @comment_len
but I get the message from SQL Query Analyzer:
"Data stream missing from WRITETEXT statement."
I thought the READTEXT returned a data stream.
There must be a way to do what I want. Does anybody know what it is ?
View 3 Replies
View Related
Dec 6, 2006
Dear all:
I had got the below error when I execute a DELETE SQL query in SSIS Execute SQL Task :
Error: 0xC002F210 at DelAFKO, Execute SQL Task: Executing the query "DELETE FROM [CQMS_SAP].[dbo].[AFKO]" failed with the following error: "The transaction log for database 'CQMS_SAP' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
But my disk has large as more than 6 GB space, and I query the log_reuse_wait_desc column in sys.databases which return value as "NOTHING".
So this confused me, any one has any experience on this?
Many thanks,
Tomorrow
View 5 Replies
View Related
Apr 19, 2007
I'm looking for a way to refer to a package variable within any
Transact-SQL code included in either an Execute SQL or Execute T-SQL
task. If this can be done, I need to know the technique to use -
whether it's something similar to a parameter placeholder question
mark or something else.
FYI - I've been able to successfully execute Transact-SQL statements
within the Execute SQL task, so I don't think the Execute T-SQL task
is even necessary for this purpose.
View 5 Replies
View Related
Mar 6, 2008
Hi.
I have a master package, which executes child packages that are located on a SQL Server. The Child packages execute other child packages which are also located on the SQL server.
Everything works fine when I execute in process. But when I set the parameter in the mater package ExecutePackageTask to ExecuteOutOfProcess = True, I get the following error
Error: 0xC00470FE at DFT Load Data, DTS.Pipeline: SSIS Error Code DTS_E_PRODUCTLEVELTOLOW. The product level is insufficient for component "Row Count" (5349).
Error: 0xC00470FE at DFT Load Data, DTS.Pipeline: SSIS Error Code DTS_E_PRODUCTLEVELTOLOW. The product level is insufficient for component "SCR Custom Split" (6399).
Error: 0xC00470FE at DFT Load Data, DTS.Pipeline: SSIS Error Code DTS_E_PRODUCTLEVELTOLOW. The product level is insufficient for component "SCR Data Source" (5100).
Error: 0xC00470FE at DFT Load Data, DTS.Pipeline: SSIS Error Code DTS_E_PRODUCTLEVELTOLOW. The product level is insufficient for component "DST_SCR Load Data" (6149).
The child packages all run fine when executed directly, and the master package runs fine if Execute Out of Process is False.
Any help would be greatly appreciated.
Thanks
Geoff.
View 7 Replies
View Related
Jun 25, 2007
I have a SSIS package contains an "Execute SQL Task". The SQL will raise error or succeed. However, it sounds the package won't pick up the raised error?
Or is it possible to conditional run other control flow items according the the status of SQL task execution?
View 1 Replies
View Related
Jan 25, 2007
I am trying to execute a SP in the execute SQL task in SSIS 2005..
but I keep getting an error:
SSIS package "Package.dtsx" starting.
Error: 0xC002F210 at Load_Gs_Modifier_1, Execute SQL Task: Executing the query "exec Load_GS_Modifier_1 ?, ?" failed with the following error: "Could not find stored procedure 'exec Load_GS_Modifier_1 ?, ?'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: Load_Gs_Modifier_1
SSIS package "Package.dtsx" finis
I have set up two user parameters: startdate and enddate.. I am not sure what I am doing wrong????
View 3 Replies
View Related
Mar 3, 2008
I have trigger, but not execute somedata because insert few row in every second. I use java to insert data to SQL server 2005. Data inserted to a table but not executing trigger for some data.
For example 100 data every second inserted to a table.
If insert data one by one to a table trigger fires success.
Please Help me.
View 1 Replies
View Related
Sep 24, 2002
I am trying to return a recordset to an ASP page from a stored procedure.
The stored procedure creates a temporary table and then builds an SQL statement into a string declared with the SP. The string contains an INSERT INTO statement to insert into the temporary table and followed by a select of the rows I wish to insert into the table.
I then use the EXECUTE method to execute the SQL string. After this I have a SELECT which should select from the temporary table and return the rows i am interested in from the stored procedure.
For some reason this does not seem to work. If I execute the SP from query analyzer I get the resultset returned. However when I try to call the stored procedure from my ASP no recordset is returned. I have done this type of thing before and it has worked perfectly. Does anyone have any ideas why this may not be working? Many Thanks.
View 2 Replies
View Related
Oct 28, 2001
Newbie Question: I'm in EM and I want to execute a simple SQL statement (e.g. exec a sproc). How do I do it? Do I have to fire up QA? or is there a simpler way?
View 1 Replies
View Related
Oct 4, 2004
I don't remember where I saw it, but it looked something like this: ...exec (0x0A738... The result was identical to executing: select getdate() Anybody seen it anywhere?
View 4 Replies
View Related
Mar 26, 2004
hi all,
i am having stored procedure like the below one:
create procedure pro_emplname
@name varchar
as
select * from employee where ename=@name
i have tried like this to execute:
exec pro_emplname kamal
but i am not getting the output....
could any one tell.....
thanks
View 1 Replies
View Related
May 29, 2004
HI
i want to rite a sysntax in StoreProcedure that execute sysntaxes in My_file.sql .
How can i do this?
thanks
View 1 Replies
View Related
Apr 15, 2008
I have two instances running on a machine...
The following code will run on the named instance but not on the default instance.
On the default instance I get the following error:
Cannot execute as the user 'SA', because it does not exist or you do not have permission.
However if I specify another account it does work.
use dbReport
go
DROP procedure dbo.clarktest
GO
create procedure dbo.clarktest
WITH EXECUTE AS 'SA' -- 'DomainSqlCmdShellDev'
as
EXEC xp_cmdshell 'dir'
go
grant exec on clarktest to [spexec]
exec clarktest
use master
go
Any assistance is appreciated as the developers code needs to be coded generically i.e. "sa" this is to prevent code manipulation during migrations...
Thank you
You can do anything at www.zombo.com
View 4 Replies
View Related
Jun 10, 2006
I want to start of run a job from DOS. I have MS SQL 2000 using Enterprise Manager.
Thank You,
Ernie
View 3 Replies
View Related
Dec 1, 2006
hi,this is my stored procedure:
CREATE PROCEDURE loop As
declare @fromage bigint
begin
select @fromage=max(age) from emp1
end
begin
insert into loop values(@fromage)
end
how to run thi spc in sqlserver,please tell me
View 1 Replies
View Related
Aug 6, 2007
hi,
What is wrong with this please?
I am passing two variables to execute a ssis package.
Thanks
set @cmd = 'dtexec /f ' + @FullPackagePath + ' /set Package.Variables[User::FileName].Properties[Value];"' + @FullFilePath + '"' +
' Package.Variables[User::ConnectionPath].Properties[Value];"' + @ConnectionPath + '"'
print @cmd
error is:
Option "Package.Variables[User::ConnectionPath].Properties[Value];Data Source=server1databasename" is not valid.
please note I just retyped the data source name here.
View 5 Replies
View Related
Feb 27, 2008
Hi Everyone,
I have problem in applying 'execute as'.
Here is an exsample SP
Create procedure test
with execute as 'user1'
as
select * from people.dbo.profile
I have 2 accounts: user1 and user2.
user1 has permission to select data from people.dbo.profile but user2 doesn't. due to some reasons I don't want to grant select permission to user2.
I use user2 to execute this SP and get error message
The server principal "user1" is not able to access the database "people" under the current security context.
How can I solve it? I have been looking for solution for the whole morning.
Thank you in advance.
View 1 Replies
View Related
Dec 10, 2007
Hi,
I have some problems with the execute as command.
I have a proc in a database, lets call it 'source'. The proc looks like this:
create proc backup_proc
with execute as 'account2'
as
select * into destination.dbo.loaddata from source.dbo.loaddata
exec dbo.backup_proc
I also have two accounts:
account1 - limited rights
account2 - extended rights
I wan't to be able to run the proc which uses account2(extended rights) to copy a table to another database. I get this error when i try to execute the proc!
The server principal "destination" is not able to access the database "source" under the current security context.
View 1 Replies
View Related
Apr 3, 2007
Hi all,
I have an SQL stored procedure that executes the following MDX query over an OLAP cube:
SELECT * FROM OpenRowset(''MSOLAP.3'',''DATASOURCE=localhost;Initial Catalog=Analysis Services Project1;'','select {[Measures].[Qty]} on columns from [DB ATEST]')
If I call the stored procedure from Management Studio, it executes perfectly well and returns the result I am expecting: the result of the MDX query.
However, when I call this same stored procedure from Reporting Services, as I try to build up a new report, I get the following message:
TITLE: Microsoft Report Designer
------------------------------
An error occurred while executing the query.
Cannot initialize the data source object of OLE DB provider "MSOLAP.3" for linked server "(null)".
The OLE DB provider "MSOLAP.3" for linked server "(null)" reported an error. The provider did not give any information about the error.
SELECT * FROM OpenRowset('MSOLAP.3','Initial Catalog=DB ATEST;','select {[Measures].[Qty]} on columns from [DB ATEST]')
------------------------------
ADDITIONAL INFORMATION:
Cannot initialize the data source object of OLE DB provider "MSOLAP.3" for linked server "(null)".
The OLE DB provider "MSOLAP.3" for linked server "(null)" reported an error. The provider did not give any information about the error.
SELECT * FROM OpenRowset('MSOLAP.3','Initial Catalog=DB ATEST;','select {[Measures].[Qty]} on columns from [DB ATEST]') (Microsoft SQL Native Client)
I've tried it with both an SQL Server and a OLE DB datasource.
I'm connecting locally on my PC (localhost).
Any clues as to why this works via Management Studio but not when called from RS ????
Many thanks to all,
John
View 4 Replies
View Related
Mar 8, 2007
I have a job that requires me to call and execute a SSIS package as the first step in a SQL2k5 Stored Procedure.
Can someone please give me a basic DTEXEC example?
Thanks in advance !
View 3 Replies
View Related
Mar 19, 2007
Hellou
I'm trying to execute simple sp with 5 input parmeters from vb.net. This sp works great when it is run from SQL Server management studio, but when I want to execute it from vb.net, I allways get this error:
"Syntax error, permission violation, or other nonspecific error"
Here's the code:
Cnn.Open("...")
Comm.ActiveConnection = Cnn
Comm.CommandType = ADODB.CommandTypeEnum.adCmdStoredProc
Comm.CommandText = "spSklVnos-Prenos" 'that's thename of the sp
RetParam.Direction = ADODB.ParameterDirectionEnum.adParamOutput
RetParam.Type = ADODB.DataTypeEnum.adInteger
Comm.Parameters.Append(RetParam)
P1.Direction = ADODB.ParameterDirectionEnum.adParamInput
P1.Type = ADODB.DataTypeEnum.adVarChar
P1.Size = 1
P1.Value = LocParA
Comm.Parameters.Append(P1)
P2.Direction = ADODB.ParameterDirectionEnum.adParamInput
P2.Type = ADODB.DataTypeEnum.adInteger
P2.Value = LocPar1
Comm.Parameters.Append(P2)
P3.Direction = ADODB.ParameterDirectionEnum.adParamInput
P3.Type = ADODB.DataTypeEnum.adInteger
P3.Value = LocPar2
Comm.Parameters.Append(P3)
P4.Direction = ADODB.ParameterDirectionEnum.adParamInput
P4.Type = ADODB.DataTypeEnum.adInteger
P4.Value = LocPar3
Comm.Parameters.Append(P4)
P5.Direction = ADODB.ParameterDirectionEnum.adParamInput
P5.Type = ADODB.DataTypeEnum.adInteger
P5.Value = PrevzemIDx
Comm.Parameters.Append(P5)
Comm.Execute()
Am I missing something?
Thanks for any suggestion.
Dido
View 1 Replies
View Related
Feb 7, 2007
Hello,I have an SQL procedure as follows: ... INSERT dbo.Levels (LevelName) VALUES (@LevelName) ... LevelName is an input parameter of nvarchar type. What should be the best way to execute this procedure from my C# / VB.Net code? And what would it return?Thanks,Miguel
View 7 Replies
View Related
Sep 20, 2007
I'm trying to do something like the code below, but it's saying "specified cast is not valid"
If i change the value returned to an "int", it works fine. My issue is, i'd like to get the value returned with more accuracy than an int as there will be 2 decimal places.protected float getProjectHours(string project)
{string selectCmd = "SELECT SUM(hours) FROM tasks WHERE project=@project";
string strConnection = ConfigurationManager.ConnectionStrings["TimeAccountingConnectionString"].ConnectionString;SqlConnection myConnection = new SqlConnection(strConnection);
SqlCommand myCommand = new SqlCommand(selectCmd, myConnection);myCommand.Parameters.Add(new SqlParameter("@project", SqlDbType.VarChar));myCommand.Parameters["@project"].Value = project;
myConnection.Open();float total = (float)myCommand.ExecuteScalar();
myConnection.Close();
return total;
}
View 6 Replies
View Related
Mar 25, 2008
I hope this is a right form for ADO .net type of question.
My question is, can you call SQL function the way you call stored procedure from ADO .net. I coded it this way and does not seems to be getting result set back. The DataReader is seems to be coming back with nothing. Can someone post an example. I know you can write "SELECT udf_function()" but I really mean the way the stored procedure is called. Thanks.
View 2 Replies
View Related
Apr 18, 2008
I found a example of using a button inside of a gridview at http://msdn2.microsoft.com/en-us/library/system.web.ui.webcontrols.buttonfield.aspx.I modified the code-behind and added: // Insert the producer into the database SqlDataSource sds = selectedRow.FindControl("sqlItemInsertIntoListing") as SqlDataSource; sds.Insert(); but the page throws an error.Can someone look at my code-behind and show me how to execute the line 56 in the code-behind? Thanks. Object reference not set to an instance of an object.
Description: An
unhandled exception occurred during the execution of the current web
request. Please review the stack trace for more information about the
error and where it originated in the code.
Exception Details: System.NullReferenceException: Object reference not set to an instance of an object.
Source Error:
Line 55: // Insert the producer into the databaseLine 56: SqlDataSource sds = selectedRow.FindControl("sqlItemInsertIntoListing") as SqlDataSource;Line 57: sds.Insert();Line 58:Line 59: } Below is my code: ASPX PAGE: <asp:Label ID="Message" ForeColor="Red" runat="server" AssociatedControlID="CustomersGridView" /> <!-- Populate the Columns collection declaratively. --> <asp:GridView ID="CustomersGridView" DataSourceID="CustomersSqlDataSource" DataKeyNames="ItemID" AutoGenerateColumns="False" OnRowCommand="CustomersGridView_RowCommand" runat="server" AllowPaging="True" AllowSorting="True" PageSize="50"> <Columns> <asp:BoundField DataField="ProducerName" HeaderText="Producer" SortExpression="ProducerName" /> <asp:BoundField DataField="ItemName" HeaderText="Item" SortExpression="ItemName" /> <asp:BoundField DataField="Year" HeaderText="Year" SortExpression="Year" /> <asp:BoundField DataField="RegionMasterName" HeaderText="Region" SortExpression="RegionMasterName" /> <asp:BoundField DataField="CountryName" HeaderText="Country" SortExpression="CountryName" /> <asp:BoundField DataField="StateName" HeaderText="State" SortExpression="StateName" /> <asp:TemplateField HeaderText="ItemID" InsertVisible="False" Visible="false" SortExpression="ItemID"> <ItemTemplate> <asp:Label ID="ItemID" runat="server" Text='<%# Bind("ItemID") %>' Visible="false"></asp:Label> <asp:SqlDataSource ID="sqlItemInsertIntoPart" runat="server" ConnectionString="<%$ ConnectionStrings:VBJimboConn %>" InsertCommand="INSERT INTO [ZCPart] ([PartUserId], [PartItemID]) VALUES (@PartUserId, @PartItemID)" OnInserting="sqlItemInsertIntoPart_Inserting" > <InsertParameters> <asp:Parameter Name="PartUserId" /> <asp:ControlParameter Name="PartItemID" ControlID="ItemID" PropertyName="Text" Type="Int32" /> </InsertParameters> </asp:SqlDataSource> </ItemTemplate> </asp:TemplateField> <asp:ButtonField ButtonType="Button" CommandName="Select" Text="Add to Part" Visible="True" /> <asp:HyperLinkField DataNavigateUrlFields="ItemID" DataNavigateUrlFormatString="ItemDetails.aspx?ItemID={0}" Text="Details" /> </Columns> </asp:GridView> <asp:SqlDataSource ID="CustomersSqlDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:CONNSTG %>"....> </asp:SqlDataSource> CODE-BEHIND:using System;using System.Collections;using System.Configuration;using System.Data;using System.Linq;using System.Web;using System.Web.Security;using System.Web.UI;using System.Web.UI.HtmlControls;using System.Web.UI.WebControls;using System.Web.UI.WebControls.WebParts;using System.Xml.Linq;partial class Inventory_InventoryList : System.Web.UI.Page{ protected void Page_Load(object sender, System.EventArgs e) { if (!Page.IsPostBack) { if (!User.Identity.IsAuthenticated) { CustomersGridView.Columns[CustomersGridView.Columns.Count - 2].Visible = false; } } } protected void sqlItemInsertIntoPart_Inserting(object sender, System.Web.UI.WebControls.SqlDataSourceCommandEventArgs e) { e.Command.Parameters["@PartUserId"].Value = Membership.GetUser().ProviderUserKey; }// Source for gvInventoryList to display message: http://msdn2.microsoft.com/en-us/library/system.web.ui.webcontrols.buttonfield.aspx protected void CustomersGridView_RowCommand(Object sender, GridViewCommandEventArgs e) { // If multiple ButtonField column fields are used, use the // CommandName property to determine which button was clicked. if(e.CommandName=="Select") { // Convert the row index stored in the CommandArgument // property to an Integer. int index = Convert.ToInt32(e.CommandArgument); // Get the last name of the selected author from the appropriate // cell in the GridView control. GridViewRow selectedRow = CustomersGridView.Rows[index]; TableCell contactName = selectedRow.Cells[1]; string contact = contactName.Text; // Display the selected author. Message.Text = "You selected " + contact + "."; // Insert the producer into the database SqlDataSource sds = selectedRow.FindControl("sqlItemInsertIntoListing") as SqlDataSource; sds.Insert(); } }}
View 2 Replies
View Related
Jun 18, 2008
Hi all,
i want to write a stored procedure to get the user details from userprofile table based on the time schedule from another table called reminders.
the structure of reminder table is called
jobtype - type of job that is sending mails to user or sending mails to employee like that
frequency - how frequenctly sending mails that is daily or weekly or monthly
weekday - if the above option is weekly then we have to take which day on weekly to send mail
dayofmonth - if the frequency option is monthly then we have to take this field for which day on month
jobtime - At what time to send mails it may be daily or weekly or monthly
so i want to write the stored procedue based on above values to execute some task( that is getting userdetails and sending mails).
please help me.
Thanks!
View 6 Replies
View Related
Jul 13, 2004
Hello,
I am trying to figure out the best way to make VB6 execute an auto-generated .SQL file from SQL Server 2000.
SQL Server 2000 has created a script (about 1000 lines long), creating views and SPs, and I need to execute said script from a VB client application. Similar to the effect of pasting it into the Query Analyser, only automated.
Anyone have a suggestion (besides reformatting evey like and hard-coding it?)
Thanks a ton!
View 2 Replies
View Related
Mar 31, 2005
How can I execute am .sql script that was generated from SQL Server?
View 4 Replies
View Related
