Msg 64, A Transport-level Error...
Apr 21, 2006
Hi:
I exec local procs of archiving databases on 3 SQL 2000 Enterprise Servers. All three servers with following same error messages in the middle of processing(each had processed from 10 to 100 DBs already).
----------------------------------------------------------
Msg 64, Level 20, State 0, Line 0
A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The specified network name is no longer available.)
There is no error message in above 3 SQL's error log and all of them are up running fine.
Since the procs are in local, I did not use hostname or '.' to refer as 'Local'.
Any idea?
thanks
-D
View 1 Replies
ADVERTISEMENT
Jan 16, 2007
Ok, some background information:
I'm running an asp.net application.ASP.NET 2.0I'm using a SQL Server 2000 database.I don't have very many problems with the application, but when I do, I get this error message:
Inner Source
.Net SqlClient Data Provider
Inner Message
A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)
Here is what my connection string looks like, although from the stacktrace, it looks like its failing when I do an ExecuteReader. Any ideas why I'm getting this error?
connection_string = "Data Source=database;Initial Catalog=catalog;User ID=XXXX;Password=XXXX;min pool size=1; max pool size=50";
Thanks,
Khanathor
View 1 Replies
View Related
Apr 5, 2006
I am getting the following error.A transport-level error has occurred when sending the request to theserver. (provider: Shared Memory Provider, error: 0 - The system cannotopen the file.)On performing the same operation again, it happens fine without anyerror.Please help.Regards,Shilpa
View 5 Replies
View Related
May 21, 2008
I have an SSIS package that calls a stored procedure via an ADO.NET Execute SQL Task. This stored procedure is long running - up to an hour. I am running the SSIS package on the same SQL Server that the SQL Task is connecting to.
I started to receive an error last night when calling it on my DEV box: "The semaphore timeout period has expired." I rebooted the server and tried again. Same error. I added some additional logging to see where I am within my stored procedure when the error happens and rebooted again. Same error. I made some other minor changes and rebooted and tried again. This time the error changed to: "The specified network name is no longer available".
This is running on a SQL Server 2K3 box. With the latest patches, including 4 that were installed this week.
Am I correct in interpretting these errors to say that the SSIS component is unable to maintain the SQL connection (to the same box) for the duration of the stored procedure run? Any other questions/advice?
Here are the 2 complete error messages:
Error: 0xC002F210 at Transform Invoice SQL Task, Execute SQL Task: Executing the query "EXEC dbo.sp_TransformInvoice @JobBatch_id = @JobBatch_id" failed with the following error: "A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The semaphore timeout period has expired.)". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Error: 0xC002F210 at Transform Invoice SQL Task, Execute SQL Task: Executing the query "EXEC dbo.sp_TransformInvoice @JobBatch_id = @JobBatch_id" failed with the following error: "A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The specified network name is no longer available.)". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Thanks,
Chris
View 9 Replies
View Related
Jan 10, 2007
Hi all,
I'm having a little problem with my sqlserver 2005.
I'm trying to crate a user in Microsoft SQL Server Management Studio throug a query.
Like this:
-------------------------------------------------------------------------------------------
USE [master]
GO
CREATE LOGIN [MYUSER] WITH PASSWORD=N'MYPASSWORD', DEFAULT_DATABASE=[MYDB],
DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
ALTER LOGIN [MYUSER] ENABLE
GO
USE [msdb]
GO
CREATE USER [MYUSER] FOR LOGIN [MYUSER];
GO
GRANT EXECUTE ON [sp_start_job] TO [MYUSER]
GO
EXEC sp_addrolemember 'db_datareader', 'MYUSER'
GO
EXEC sp_addrolemember 'db_datawriter', 'MYUSER'
GO
EXEC sp_addrolemember 'SQLAgentOperatorRole', 'MYUSER'
GO
EXEC sp_addrolemember 'SQLAgentUserRole', 'MYUSER'
GO
EXEC sp_addrolemember 'SQLAgentReaderRole', 'MYUSER'
GO
Use [MYDB]
GO
CREATE USER [MYUSER] FOR LOGIN [MYUSER]
GO
EXEC sp_addrolemember 'db_datareader', 'MYUSER'
GO
EXEC sp_addrolemember 'db_datawriter', 'MYUSER'
GO
------------------------------------------------------------------------------------------
And drops the user by executing following:
Use [MYDB]
GO
EXEC sp_dropuser 'MYUSER'
GO
USE [msdb]
GO
REVOKE EXECUTE ON [sp_start_job] TO [MYUSER]
GO
EXEC sp_droprolemember 'SQLAgentOperatorRole', 'MYUSER'
GO
EXEC sp_droprolemember 'SQLAgentUserRole', 'MYUSER'
GO
EXEC sp_droprolemember 'SQLAgentReaderRole', 'MYUSER'
GO
EXEC sp_dropuser 'MYUSER'
GO
USE [master]
IF EXISTS (SELECT * FROM sys.server_principals WHERE name = N'MYUSER')
DROP LOGIN [MYUSER]
------------------------------------------------------------------------------------------
-Then if I from a query, first creates the user, then drops the user, I can't create him again?? I get's the following error when trying:
Msg 10054, Level 20, State 0, Line 0
A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)
I'f i then runs the createscript again, the user is created correctly.
Is there a way to avoid this foced lockout? Or how do i "refreash" my connection, without dataloss?
Best Regards
Henrik
View 10 Replies
View Related
Mar 9, 2008
When i try to use SqlBulkCopy in vb.net to import 499 records i get the following error returned?
A transport-level error has occurred when receiving results from the server. (provider: Shared Memory Provider, error: 0 - The pipe has been ended.)
This then results in SQL Server services stopping - and forcing me to do a reset?
does any one know what would cause this and also how to fix it?
View 2 Replies
View Related
Apr 9, 2007
I just installed Microsoft SQL Server Express SP2. I setup a linked server to an Oracle database using the followings commands:
EXEC sp_addlinkedserver 'Oracle_DEV', 'Oracle', 'MSDAORA', 'DEV'
EXEC sp_addlinkedsrvlogin 'Oracle_DEV ', false, NULL, 'oracledbuser', 'dbuserpassword'
When I try and query the linked database I get the following error message:
Msg 109, Level 20, State 0, Line 0
A transport-level error has occurred when receiving results from the server. (provider: Shared Memory Provider, error: 0 - The pipe has been ended.)
I've been searching the web and have gotten a lot of hits on the error message but have yet to uncover anything specifically related to linked servers or anything closely related enough to help me resolve this problem. Any advice would be appreciated. Thanks.
View 3 Replies
View Related
Sep 27, 2007
Hi,
A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)
I am using a direct connection from my Gridview to perform a huge database search. the stored procedure will take 10- 60 seconds to execute the query.
What the problem i am facing is when the sp takes long time (more than 1 minutes), then when i try any other operation like calling another sp frm another page, i am getting the above specified error.
Anybody can tell why is it happening?
here my code snippet goes
<asp:GridView ID="searchGV" runat="server" AllowPaging="True" AllowSorting="True"
BorderColor="#999999" BorderStyle="None" CellPadding="3" Width="100%" AutoGenerateColumns="False" DataSourceID="searchObj" DataKeyNames="Recordnumber" OnDataBound="searchGV_DataBound" OnPageIndexChanged="searchGV_PageIndexChanged">
<RowStyle CssClass="gridRow" />
<PagerStyle HorizontalAlign="Center" BackColor="#999999" ForeColor="Black" />
<HeaderStyle CssClass="gridHeader" />
<AlternatingRowStyle CssClass="gridAlternateRow" />
<Columns>
<asp:TemplateField HeaderText="All">
<HeaderTemplate>
<asp:CheckBox ID="chkid" AutoPostBack="true" OnCheckedChanged ="OnCheckChangedEvent" runat="server" />
</HeaderTemplate>
<EditItemTemplate>
<asp:CheckBox ID="chkSelect" runat="server" />
</EditItemTemplate>
<ItemTemplate>
<asp:CheckBox ID="chkSelect" runat="server"/>
</ItemTemplate>
<ControlStyle Height="18px" Width="15px" />
<ItemStyle Width="5px" />
</asp:TemplateField>
<asp:BoundField DataField="Title" HeaderText="Title" SortExpression="Title">
<ItemStyle Width="150px" />
</asp:BoundField>
<asp:BoundField DataField="Artist" HeaderText="Artist(s)" SortExpression="Artist"/>
<asp:BoundField DataField="Album" HeaderText="Album" SortExpression="Album"/>
<asp:BoundField DataField="Writer" HeaderText="Writer(s)" SortExpression="Writer" ><ItemStyle Width="150px" /></asp:BoundField>
<asp:BoundField DataField="ISRC" HeaderText="ISRC" SortExpression="ISRC" />
<asp:BoundField DataField="UPC" HeaderText="UPC" SortExpression="UPC"/>
<asp:BoundField DataField="PLineLabel" HeaderText="P-Line Label" SortExpression="PLineLabel" ><ItemStyle Width="100px" /></asp:BoundField>
<asp:BoundField DataField="DistLabel" HeaderText="Distribution Label" SortExpression="DistLabel" >
<ItemStyle Width="100px" />
</asp:BoundField>
<asp:BoundField DataField="PublishInfo" HeaderText="Publisher(s)" SortExpression="PublishInfo" />
<asp:BoundField DataField="Recordnumber" HeaderText="Record Number" SortExpression="Recordnumber" />
<%-- <asp:BoundField DataField="UID" HeaderText="UID" Visible="false" />--%>
</Columns>
</asp:GridView>
with adapter
<asp:SqlDataSource ID="searchObj" runat="server" SelectCommand="gsp_titleSearchResult" SelectCommandType="StoredProcedure" ConnectionString="<%$ ConnectionStrings:ConnStr %>">
<SelectParameters>
<asp:ControlParameter ControlID="HuserID" PropertyName="Value" Name="Userid" Type="String" Size="50" DefaultValue="" />
<asp:ControlParameter ControlID="Htitle" PropertyName="Value" Name="title" Type="String" Size="50" DefaultValue=" " />
<asp:ControlParameter ControlID="Hartist" PropertyName="Value" Name="artist" Type="String" Size="50" DefaultValue=" " />
<asp:ControlParameter ControlID="Halbum" PropertyName="Value" Name="album" Type="String" Size="50" DefaultValue=" " />
<asp:ControlParameter ControlID="Hwriter" PropertyName="Value" Name="writer" Type="String" Size="50" DefaultValue=" " />
<asp:ControlParameter ControlID="Hisrc" PropertyName="Value" Name="isrc" Type="String" Size="50" DefaultValue=" " />
<asp:ControlParameter ControlID="Hupc" PropertyName="Value" Name="upc" Type="String" Size="50" DefaultValue=" " />
<asp:ControlParameter ControlID="Hpline" PropertyName="Value" Name="pline" Type="String" Size="50" DefaultValue=" " />
<asp:ControlParameter ControlID="Hdist" PropertyName="Value" Name="distlabel" Type="String" Size="50" DefaultValue=" " />
<asp:ControlParameter ControlID="Hpublish" PropertyName="Value" Name="publisher" Type="String" Size="50" DefaultValue=" " />
<asp:ControlParameter ControlID="ReleaseID" PropertyName="Value" Name="Releaseid" Type="Int32" Size="50" DefaultValue="0" />
<asp:ControlParameter ControlID="RecordLabelID" PropertyName="Value" Name="recordlabelid" Type="Int32" Size="50" DefaultValue="0" />
<asp:ControlParameter ControlID="PublisherID" PropertyName="Value" Name="Publisherid" Type="Int32" Size="50" DefaultValue="0" />
<asp:ControlParameter ControlID="ArtistID" PropertyName="Value" Name="Artistid" Type="Int32" Size="50" DefaultValue="0" />
</SelectParameters>
</asp:SqlDataSource>
Advance thanks
Anvar Sadath
View 1 Replies
View Related
Apr 7, 2006
Hi all,
I am new to SQL 2005. I have one issue and it is as follows:
I write a simple query and run it; it works fine.
Now I break the network connection and run the query; in this case it runs fine.
But when I reconnect the network and run the query, then I get an error as follows:
"A transport-level error has occurred when sending the request to the server. (provider: Named Pipes Provider, error: 0 - An unexpected network error occurred.) "
Every thing is local here. So how the network issue comes here?
View 14 Replies
View Related
Nov 27, 2006
Hi all,
I
am trying to run a stored procedure which retreives 3 lakhs of records
and updates the data and moves few of those records to some tables.
Since the number of records are more , the time taken for the storede
procedure is around 30 minutes when i directly execute it in query
analyzer.
But I need to execute it from Visula Studio.Net 2005
(c#). Whole of the application contains only one form with a single
button.When I click on the button , this stored procure has to be
executed. But I am getting an error as shown below.
"A
transport-level error has occurred when receiving results from the
server. (provider: TCP Provider, error: 0 - The specified network name
is no longer available.)"
Database used is SqlServer 2000
How to solve this error?. Any ideas are really appreciated.
Thanks and Regards,
Sukanya.
View 4 Replies
View Related
Oct 16, 2006
I've tried to search on the web for a solution for this error but i didn't find any working solutions for this problem. We have 2 servers. The first one is the server we use to develop our ASP.NET 2 application. On this server we don't have this error. On the other server (use by our client) we have this error sometime and i don't know why. Both servers have the same configuration and both application have the same web.config file.
I've tried to add a try-catch and retry the query when the error occured. This seems to be working but we don't want to have to change all our connections and since we don't have this problem on the other server we want to find the source of the problem.
Any idea ?
Thanks !
View 1 Replies
View Related
Nov 30, 2007
Greetings:
I have a database that is 1.7terabyte in size with 136gb free and throws a "transport level error" telling me to discard the results when I run dbccshrinkfile ('DBNAME', size). I have tried various increments of size, from truncateonly to 1MB below its current value, and nothing works. I have tried to detach and reattach the db, restart the service, restart the server, and none have provided a solution. Any ideas?
Regards,
Derek
View 1 Replies
View Related
Jan 24, 2007
Hi,
I am using SQL Server 2005,
while trying to retrieve data from the database; I am getting the following
error:
A transport-level error has occurred when receiving results
from the server. (Provider: TCP Provider, error: 0 - The handle is invalid.)
But I am getting this error randomly.
Can some one help me out?
Waiting for your response
Sudhakar
View 7 Replies
View Related
Mar 8, 2007
Hi,
Basically the error that I am getting is in our test automation when running as non-admin on the box (regular user). We use .Net C# SQLConnection class to connect to SQL express 2005 impersonating with admin credentials. After getting the connection we try to execute a select command and it some time fails with following error:
A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The handle is invalid.) at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error) at System.Data.SqlClient.TdsParserStateObject.ReadSni(DbAsyncResult asyncResult, TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParserStateObject.ReadPacket(Int32 bytesExpected) at System.Data.SqlClient.TdsParserStateObject.ReadBuffer() at System.Data.SqlClient.TdsParserStateObject.ReadByte() at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlDataReader.ConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader()
This happen only in the non-admin scenario mentioned above. Any idea what is triggering this?
Thanks in advance for all replies.
View 3 Replies
View Related
Sep 27, 2006
We've devoted a resource to this today, but I have to believe it's something easy that we're overlooking. The scneario is that we have a production Web application that until last weekend had a SQL 2000 back end. This weekend we installed a new instance of SQL 2005 and everything works (we tested in a sandbox environment, but someone must not have load tested enough) and never saw these exceptions. So, after the upgrade we now receive 100's of thexe SQL excptions per day:A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The specified network name is no longer available.)Does anyone know what we've overlooked that's causing this issue?Thanks for any help!
View 2 Replies
View Related
Apr 30, 2008
We get this error when we add IP addresses to the Windows system when SQL 2005
database activity is on-going:
Database error: A transport-level error has occurred when receiving results
from the server. (provider: TCP Provider, error: 0 - The semaphore timeout
period has expired.)
.NET application can be running for weeks without error, but after adding a
new IP address, application gets 5-16 'transport-level errors' before
correcting itself.
Error occurs on Windows XP computer in our case. SQL Server, running on Windows server 2003, doesn't seem to pick up on the newly added IP address.
View 12 Replies
View Related
May 24, 2006
Hi
I am trying to configure Database Mirroring. I had do set dbcc traceon(1400,-1) manually using tsql as the server does not start when configring the startup parameter using the advanced properties of sql server configuration using -T 1400.
After using the db-mirror properties and the wizard in management studio i get an error "database mirroring transport is disabled in the endpoint configuration". I also get this error when executing
alter database set partner="..."
Am I missing something?
Thanks
View 1 Replies
View Related
Jun 14, 2001
Attempt to fetch logical page ' ' in database ' '. Object belongs to ' ' not object ' '.
Please someone help me fix this. My table is corrupt and I cannot drop it and restore it because I keep getting an error message that says:
Cannot drop table ' ' there is not enough space in syslogs. blah, blah, blah.
Can anyone help me? It woould be much appreciated.
Christine
View 1 Replies
View Related
Dec 17, 2007
I am writing a user defined function but I am receiving an error message that I can figure out. Any answers out there?
Msg 102, Level 15, State 1, Procedure ufn_GetStreetNumber, Line 25
Incorrect syntax near '@StreetNum'.
-- ---------------------------------------------------------------------------------
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Description: Parses out the Street number for Address field
-- =============================================
CREATE FUNCTION dbo.ufn_GetStreetNumber
(
-- Add the parameters for the function here
@StreetAddr varchar(50)
)
RETURNS int
AS
BEGIN
-- Declare the return variable here
DECLARE @StreetNum varchar(50);
DECLARE @iIter smallint, @iNoOfChars smallint, @iStreetNo int;
SET @StreetNum=NULL;
SET @iStreetNo=0;
SET @iNoOfChars=1;
SET @iIter=1;
-- Add the T-SQL statements to compute the return value here
WHILE Isnumeric(Substring(@StreetAddr,@iNoOfChars,@iIter))=1
@StreetNum=@StreetNum + Substring(@StreetAddr,@iNoOfChars,@iIter)
@iIter=@iIter+1
BREAK
@iStreetNo=@StreetNum
-- Return the result of the function
RETURN @iStreetNo
END
GO
View 3 Replies
View Related
Aug 21, 2000
Please help with a problem - I need to execute the following process:
(1) Create about 200 empty tables in a database (this MUST be done prior to step 2)
(2) copy rows from a source database with identical tables into all of the empty tables,
(3) copy more rows from a third database source (with some duplicate rows) into the tables from step 2.
The problem is that the first copy goes OK since the tables are empty,
but the second copy fails as soon as a duplicate row triggers an error in the DTS tool.
At that point, the DTS tool goes on to the next table until the problem is repeated.
In Oracle, I can set a flag for import such as IGNORE=YES which will ignore object or row creation errors.
How can I get DTS to ignore row creation errors, due to expected duplicate row constraints,
and just carry on with the next row after each rejected row?
In other words, set DTS to simply reject duplicate rows without aborting the entire table copy.
Thanks in advance -
Joel Lieberman
View 1 Replies
View Related
Mar 22, 2002
I am getting the following error, Do you any suggesions?
Thanks
Rau
SqlDumpExceptionHandler: Process 62 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process..
View 1 Replies
View Related
Jan 2, 2006
Hi,
I am getting the following error when I use the Membeship Controls w/ Roles enabled.
Parser Error Message: It is an error to use a section registered as allowDefinition='MachineToApplication' beyond application level. This error can be caused by a virtual directory not being configured as an application in IIS.
I use ROOT>USER>WEBSITE for FTP Directory Setup.
If i need to change my old ways please inform me on the best direction.
View 3 Replies
View Related
Jun 30, 2007
Configuration: Windows XP Pro with SQL 2005 Workgroup Sp2
Hi, we have a package that runs fine in BI Studio but fails with the following error when executing it using DTExec.
Code: 0xC00470FE
Source: Data Flow Task DTS.Pipeline
Description: SSIS Error Code DTS_E_PRODUCTLEVELTOLOW. The product level is insufficient for component "Derived Column" (10660).
The package imports a CSV into a table mapping some columns. The MSDN article at http://msdn2.microsoft.com/en-us/library/ms143761.aspx describes features supported by different versions.
The article says OLE DB source/destination adapters are supported in WG edition but a couple of lines later seems to imply that all source/destination adapters are not supported in WG.
Could you clarify whether stuff like import of a csv into a table with some column mappings should work in WG edition? Also if we have a legacy SQL 2K package doing the same thing, will that work in WG edition? Thx.
Features/Integration Services Enhancements
EE (32-bit) DE (32-bit) EE (64-bit) DE (64-bit)
SE (32-bit)
WG (32-bit)
SSE (32-bit) SSEA (32-bit)
SE (64-bit)
SQL Server Import and Export Wizard and supporting connections, source and destination adapters, and tasks
Yes
Yes
Yes
No
Yes
Execute SQL Task
Yes
Yes
Yes
No
Yes
OLE DB Source and Destination Adapters
Yes
Yes
Yes
No
Yes
SSIS command prompt tools
Yes
Yes
Yes
No
Yes
SSIS Package Designer
Yes
Yes
Yes
No
Yes
Legacy support for DTS packages
Yes
Yes
Yes
Yes
Yes
SSIS Service
Yes
Yes
No
No
Yes
All other source and destination adapters, tasks, and transformations, except for those listed below
Yes
Yes
No
No
Yes
View 3 Replies
View Related
Aug 2, 2007
Hi,
I have built a stored procedure and the code is like below:
Code Snippet
DECLARE @Table_Name VARCHAR(100)
DECLARE @Table_Desc SQL_VARIANT
DECLARE @Query VARCHAR(8000)
USE HRD
SELECT @Table_Name = 'Employee'
SELECT @Query = 'IF EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N''[dbo].[DEL_' + UPPER(@Table_Name) + ']''))
DROP TRIGGER [dbo].[DEL_' + UPPER(@Table_Name) + ']'
EXEC(@Query)
SELECT @Table_Desc = (SELECT a.value
FROM
sys.extended_properties a, sys.tables b
WHERE a.major_id = b.object_id
AND a.minor_id = 0
AND a.name = 'MS_DESCRIPTION'
AND b.name = @Table_Name)
SELECT @Query = '''CREATE TRIGGER [DEL_' + UPPER(@Table_Name) + '] ON dbo.' + @Table_Name + '
FOR DELETE
AS
DECLARE @Old_Value VARCHAR(8000)
DECLARE @New_Value VARCHAR(8000)
DECLARE @P_Key VARCHAR(8000)
DECLARE @P_Key_Value VARCHAR(8000)
DECLARE @P_Key_Insert VARCHAR(8000)
DECLARE @Comment VARCHAR(8000)
SELECT @P_Key_Insert = ''''''''
EXEC(''''IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''''''''[dbo].[Temp_PrimKey]'''''''') AND type in (N''''''''U''''''''))
DROP TABLE [dbo].[Temp_PrimKey]'''')
SELECT K.COLUMN_NAME INTO Temp_PrimKey
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS T
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE K
ON T.CONSTRAINT_NAME = K.CONSTRAINT_NAME
WHERE
T.CONSTRAINT_TYPE = ''''PRIMARY KEY'''' AND T.TABLE_NAME = ''''' + @Table_Name + '''''
EXEC(''''IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''''''''[dbo].[Temp_Value]'''''''') AND type in (N''''''''U''''''''))
DROP TABLE [dbo].[Temp_Value]'''')
EXEC(''''CREATE TABLE [dbo].[Temp_Value](
[PValue] [VARCHAR](max) NOT NULL
) ON [PRIMARY]'''')
EXEC(''''IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''''''''[dbo].[Temp_Deleted]'''''''') AND type in (N''''''''U''''''''))
DROP TABLE [dbo].[Temp_Deleted]'''')
SELECT * INTO Temp_Deleted FROM deleted
DECLARE Curs_PrimKey CURSOR FOR
SELECT * FROM Temp_PrimKey
OPEN Curs_PrimKey
FETCH NEXT FROM Curs_PrimKey INTO @P_Key
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC(''''INSERT INTO Temp_Value SELECT ''''+ @P_Key + '''' FROM Temp_Deleted'''')
SELECT @P_Key_Value = (SELECT PValue FROM Temp_Value)
EXEC(''''DELETE FROM Temp_Value'''')
SELECT @P_Key_Insert = @P_Key_Insert + @P_Key + '''' = '''' + @P_Key_Value + '''', ''''
FETCH NEXT FROM Curs_PrimKey INTO @P_Key
END
CLOSE Curs_PrimKey
DEALLOCATE Curs_PrimKey
EXEC(''''IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''''''''[dbo].[Temp_Deleted]'''''''') AND type in (N''''''''U''''''''))
DROP TABLE [dbo].[Temp_Deleted]'''')
EXEC(''''IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''''''''[dbo].[Temp_Value]'''''''') AND type in (N''''''''U''''''''))
DROP TABLE [dbo].[Temp_Value]'''')
SELECT @P_Key_Insert = LEFT(@P_Key_Insert, LEN(@P_Key_Insert)-2)
SELECT @Comment = ''''' + CAST(@Table_Desc AS VARCHAR) + ' deleted, '''' + @P_Key_Insert
INSERT INTO TLOG(Log_Date, Log_Reference, Log_Comment)
VALUES (GETDATE(), @P_Key_Insert, @Comment)
'''
--PRINT @Query
EXEC(@Query)
Problem is when I run it gives error:
Msg 102, Level 15, State 1, Line 1Incorrect syntax near 'CREATE TRIGGER [DEL_EMPLOYEE] ON dbo.Employee FOR DELETEAS DECLARE @Old_Value VARCHAR(8000)DECLARE @New_Value VARCHA'.
If I print the @Query and run it in a query analyzer using the statement EXEC it worked. It's giving me headache, this supposed to be a simple exec statement. Please advise. Thanks.
View 2 Replies
View Related
Oct 28, 2005
hi we have a link server....andthe user credentials are not being trasfered to link server.....anythoughts??we get the following error messageServer message 18542 level 16 state 1--Posted using the http://www.dbforumz.com interface, at author's requestArticles individually checked for conformance to usenet standardsTopic URL: http://www.dbforumz.com/error-messa...pict266240.htmlVisit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=915273
View 1 Replies
View Related
Apr 28, 2006
I've read some threads on this topic and all have been solved by installing the SSIS service. This would be fine except for the fact that I already have SSIS installed and working on the server the package is being called from.
I have several scheduled packages that work without error and a few that fail, telling me "Error: ... it requires a higher level edition." Does SSIS need to be installed on the target server as well? Do I need to do a reinstall? Please advise. Thanks.
-Matt
View 1 Replies
View Related
Apr 23, 2008
Hello,
I am getting this error when trying to open a report created with the cube. We use dundas olap chart to generate the xml for the report from the base cube or default perspective. What we do is change the xml dynamically to a different perspective for security purposes and then load the report. If the report xml accesses a dimension that is hidden the assigned perspective then we get the error shown below. Does anyone know how to handle this error?
Exception Details: System.NullReferenceException: Internal error. Dimension level or it's parent is NULL.
This only happens with some of our dimensions. With the other hidden dimensions we get no data message on the chart which is what we want.
I am comparing our dimensions to the Adventure Works cube dimensions which seem to work (I have not tried all of their dimensions yet). Nothing sticks out to me as being a setting that will make the error go way. Anyone have suggestions?
Other things I tried but didn't help were removing the parent child hierarchies and Role based security that uses one of the dimensions that throws the error. I also did a trace on the cube when the error is thrown. I could not see any errors and I was able to execute all of the MDX queries without any problems (I copied and pasted the mdx into SSMS).
As an aside we tried used the Cube Roles and Dimension security (Hiding measures in the Measures Dimension too) but the error that MS (#Error or #Value) throws causes dundas not to work. We also looked UDFs and Cell Level Security but both are more complex/what we need for now.
Thanks in advance,
Sean
View 10 Replies
View Related
Aug 7, 2007
Dear All, I always got this error in CLR TVF:
Msg 6260, Level 16, State 1, Line 1
An error occurred while getting new row from user defined Table Valued Function :
System.InvalidOperationException: Invalid attempt to FieldCount when reader is closed.
System.InvalidOperationException:
at System.Data.SqlClient.SqlDataReaderSmi.get_FieldCount()
at System.Data.Common.DbEnumerator.BuildSchemaInfo()
at System.Data.Common.DbEnumerator.MoveNext()
Here is my code:
using System;
using System.Data;
using System.Data.Common;
using System.Data.SqlTypes;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;
using System.Collections;
public static class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction(FillRowMethodName = "rowfiller",DataAccess=DataAccessKind.Read,TableDefinition = "ActID int, ActName nvarchar(50), ActCreatorID int,ActDesp nvarchar(200),ActCreateDate datetime,ActModifyDate datetime, ActStartDate datetime, ActEndDate datetime, Status int, Cost int")]
public static IEnumerable Func_GetSchCatActivityIDTable(int CatActivityID)
{
using (SqlConnection connection = new SqlConnection("context connection=true"))
{
string sqlstring = "select * from Activity where CatActivityID=@CatActivityID;";
connection.Open();
SqlCommand command = new SqlCommand(sqlstring, connection);
command.Parameters.AddWithValue("@CatActivityID", CatActivityID);
return command.ExecuteReader(CommandBehavior.CloseConnection);
}
}
[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft Performance","CA1811:AvoidUncalledPrivateCode")]
public static void rowfiller(Object obj,
out SqlInt32 ActID,
out SqlString ActName,
out SqlInt32 ActCreatorID,
out SqlString ActDesp,
out SqlDateTime ActCreateDate,
out SqlDateTime ActModifyDate,
out SqlDateTime ActStartDate,
out SqlDateTime ActEndDate,
out SqlInt32 Status,
out SqlInt32 Cost,
)
{
SqlDataRecord row = (SqlDataRecord)obj;
int column = 0;
ActID = (row.IsDBNull(column)) ? SqlInt32.Null : new SqlInt32(row.GetInt32(column)); column++;
ActName = (row.IsDBNull(column)) ? SqlString.Null : new SqlString(row.GetString(column)); column++;
ActCreatorID = (row.IsDBNull(column)) ? SqlInt32.Null : new SqlInt32(row.GetInt32(column)); column++;
ActDesp = (row.IsDBNull(column)) ? SqlString.Null : new SqlString(row.GetString(column)); column++;
ActCreateDate = (row.IsDBNull(column)) ? SqlDateTime.Null : new SqlDateTime(row.GetDateTime(column)); column++;
ActModifyDate = (row.IsDBNull(column)) ? SqlDateTime.Null : new SqlDateTime(row.GetDateTime(column)); column++;
ActStartDate = (row.IsDBNull(column)) ? SqlDateTime.Null : new SqlDateTime(row.GetDateTime(column)); column++;
ActEndDate = (row.IsDBNull(column)) ? SqlDateTime.Null : new SqlDateTime(row.GetDateTime(column)); column++;
Status = (row.IsDBNull(column)) ? SqlInt32.Null : new SqlInt32(row.GetInt32(column)); column++;
Cost = (row.IsDBNull(column)) ? SqlInt32.Null : new SqlInt32(row.GetInt32(column)); column++;
}
};
Can anyone tell me what I am doing wrong? Many thanks
.
View 8 Replies
View Related
Dec 27, 2006
Hi,
I am receiving the following error in Sql Job agent when I try to run an SSIS package : The task "Create Excel File" cannot run on this edition of Integration Services. It requires a higher level edition. It then goes on to tell me : The Execution method succeeded, but the number of errors raised (2) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. I have tried reseting the error count to allow for the "errors" , but it stills fails. The job suceeds in Visual Studio, but not when scheduled in Sql Management Studio. Any suggestions?
Thanks,
J.
View 18 Replies
View Related
Jan 16, 2008
I want to perform column level and database level encryption/decryption....
Does any body have that code written in C# or VB.NET for AES-128, AES-192, AES-256 algorithms...
I have got code for single string... but i want to encrypt/decrypt columns and sometimes the whole database...
Can anybody help me out...
If you have Store procedure in SQL for the same then also it ll do...
Thanks in advance
View 1 Replies
View Related
Nov 19, 2007
Hi,
AM in need of SSRS 2005 design documents for a project purpose. Can somebody let me know where can i find these documents? Thanks in advance
View 1 Replies
View Related
Jul 23, 2007
This thing is driving me crazy. I have a simple flat .txt file I'm trying to import into a DB, and this USED to work perfectly with SQL Server 2000. With 2005, I get "product level insufficient."
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=217596
I've read that and I've looked around. It seems like the solution is to actually connect to the Windows box that the server itself is running on (where SSIS is installed) and import there?? But that's a pain.. that means I'd have to Remote Desktop into the server EVERY time I want to import a text file. Surely there's some other way to do this??
Any advice would be great. Thanks!
View 4 Replies
View Related
Oct 12, 2014
I am trying to enable CDC feature in SQL Server 2012 Enterprise Edition ( 11.0.2424.0).The command to enable CDC for Database worked fine, but the command for enabling CDC on Table failed.Command used for enabling CDC on table is as follows.
USE DatabaseName
GO
EXEC sys.sp_cdc_enable_table
@source_schema = N'dbo',
@source_name = N'TableName',
@role_name = NULL,
@supports_net_changes = 1
Following is the error message got
Msg 22832, Level 16, State 1, Procedure sp_cdc_enable_table_internal, Line 623...Could not update the metadata that indicates table [dbo].[TableName] is enabled for Change Data Capture. The failure occurred when executing the command '[sys].[sp_cdc_add_job] @job_type = N'capture''. The error returned was 22836: 'Could not update the metadata for database DatabaseName to indicate that a Change Data Capture job has been added. The failure occurred when executing the command 'sp_add_ jobstep_ internal'. The error returned was 14234: 'The specified '@server' is invalid (valid values are returned by sp_helpserver).'. Use the action and error to determine the cause of the failure and resubmit the request.'. Use the action and error to determine the cause of the failure and resubmit the request.
View 4 Replies
View Related
