I am getting an Error on Line 4 of sample code, any idea, where?
if isdate(mid(DTSSource("Col016"), 5, 2) & "/" &
right(DTSSource("Col016"), 2) & "/" & left(DTSSource("Col016"), 4)) then
DTSDestination("DOB") = mid(DTSSource("Col016"), 5, 2) &
"/" & right(DTSSource("Col016"), 2) & "/" & left(DTSSource("Col016"), 4)
else
DTSDestination("DOB") = NULL
end if
Below is a code snippet that is throwing the following error:Error Source: Microsoft DTS PackageError Description: Error Code: 0Error Source= Microsoft VBScript runtime errorError Description: Object required: 'Server'Error on line 13In my code line 13 is the following:Set Cnxn = Server.CreateObject("ADODB.connection")I did set up a Microsoft OLE DB connection in my DTS package for thisActiveX Task. I'm new to VBScript and ActiveX. Any help would beappreciated.Thanks,-p'************************************************* *********************' Visual Basic ActiveX Script'************************************************* ***********************Function Main()Main = DTSTaskExecResult_Success' connection, command and recordset variablesDim Cnxn, strCnxn' create and open connectionSet Cnxn = Server.CreateObject("ADODB.connection")strCnxn = "data source=Pluto;initial catalog=Stats;User Id=sa;password=;"Cnxn.Open strCnxnEnd Function
View 2 Replies View Related
I have a DTS package which executes an ActiveX control written in vbscript. I need to move the ActiveX code out of the DTS environment. The code is too big to execute in a Sql Server Job Step as type "ActiveX Script." Do I have any other easy options?
Thanks,
Michael
Hi,
Thanks for reading.
I am creating a DTS package to import a .txt file into sql. I have everything in place, but the text file needs to have the last record deleted before the import. I need help with this part
I would like to delete the last record from a fixed width text file before I import it into sql. The number of rows will vary from file to file.
Can any one offer suggestions on the best way to do this.
I understand that I have to use the FSO to open and read the file, but I am not sure the best way to proceed after that.
Thanks in advance,
Steve
I need to retrieve the Global Variables set in my package configuration file within an ActiveX Script Task within an SSIS package. In DTS, I could access the Global Variables to execute a SQLXMLBulkLoad for the following statement:
==========================================
Function Main()
Response.Expires=-1
set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad")
objBL.ConnectionString =
"provider=SQLOLEDB.1;server=ABC123;database=MyDB;Trusted_Connection=Yes;"
objBL.KeepIdentity = False
objBL.CheckConstraints = False
objBL.Execute DTSGlobalVariables("gv_XSDSchemaFile").Value, DTSGlobalVariables("gv_XMLFullPath").Value
Main = DTSTaskExecResult_Success
set objBL=Nothing
End Function
=========================================
I have tried using the Script Task to write this in VB.NET, however the MSXML4.0 is not exposed within the limited object model of the Script Task Designer. I have written a Data Flow Object using the XML Source, however it requires quite a bit of effort to have the Data Flow Component parse the XML (with 10 hierarchical nodes), transform each and provide a SQL Server Destination. This works, however the XML Source Component requires a hardcoded reference to the XSD Schema file and does not allow for a Global Variable to used. (They do provide this functionality for the XML file source though).
My requirement is to allow for the Global Variable to be passed for the Schema file at runtime. The only way I can think of is to recreate what I was doing in DTS where I could simply pull in the XML and XSD Global Variables and execute the SQLXMLBulkLoad in VB Script.
Any ideas on how to write this in VBScript within the ActiveX Script Task in SSIS?...
Michael
hi
I have a VBScript converted from an AccessDB Macro to VB.
Option Compare Database
'------------------------------------------------------------
' Macro2_Export2Text5
'
'------------------------------------------------------------
Function Macro2_Export2Text5()
On Error GoTo Macro2_Export2Text5_Err
DoCmd.TransferText acExportDelim, "", "MTHCrosstabCountingUnits", "c:IMSMthCountingUnit.txt", True, ""
DoCmd.TransferText acExportDelim, "", "MTHCrosstabValue", "c:IMSMTHValue.txt", True, ""
DoCmd.TransferText acExportDelim, "", "MATCrosstabValue", "c:IMSMatValue.txt", True, ""
DoCmd.TransferText acExportDelim, "", "MATCrosstabCountingUnits", "c:IMSMATCountingUnit.txt", True, ""
Macro2_Export2Text5_Exit:
Exit Function
Macro2_Export2Text5_Err:
MsgBox Error$
Resume Macro2_Export2Text5_Exit
End Function
What I am trying to do is instead of Opening the Access application and run the macro manually, i converted the macro to VB and created an ActiveX Script Task package in SQL2000 and Copy and Paste the VB Code to ActiveX Script Task. But it didn't work. Is there a work around for this?
Many thanks in advance.
Hello,
I am currently getting the following error:
Microsoft VBScript runtime error '800a000d'
Type mismatch: 'QueryString'
/admin/equipment/addequipment.asp, line 128
This is when i try to submit a new database item in Dreamweaver. The DB I am using is SQL Server 2005.
My code is as follows:
Dim equipspec__urlid
equipspec__urlid = "1"
If (QueryString("ID") <> "") Then
equipspec__urlid = QueryString("ID")
End If
%>
<%
Dim equipspec
Dim equipspec_numRows
Set equipspec = Server.CreateObject("ADODB.Recordset")
equipspec.ActiveConnection = MM_intranet_STRING
equipspec.Source = "SELECT * FROM EquipSpec WHERE " + Replace(equipspec__urlid, "'", "''") + " = EquipSpec.ID"
equipspec.CursorType = 0
equipspec.CursorLocation = 2
equipspec.LockType = 1
equipspec.Open()
Would be greatful if anybody could help, let me know if you need more info.
Many thanks.
This error occurs when the ActiveX task tries to execute:
[ActiveX Script Task] Error: Retrieving the file name for a component failed with error code 0x001B6438.
Anybody know how to troubleshoot these errors? I can't find anything on this error code. The same script works in DTS.
Hello all
Trying to delete some data from a SSCE (2005) DB produces the exception:
SqlCeException
There was an error parsing the query. [ Token line number = 1,Token line offset = 43,Token in error = C]
Here is the code I am using
string dsc = Application.StartupPath + "\FCDB07.sdf";
conn = new SqlCeConnection("DataSource = " + dsc);
conn.Open();
cmd = conn.CreateCommand();
cmd.CommandText = "DELETE FROM DataContainer WHERE FileName =" + dgContainers[0, SelRowIndex].Value.ToString();
cmd.ExecuteNonQuery(); //There was an error parsing the query. [ Token line number = 1,Token line offset = 43,Token in error = C ]
conn.Close();
Any Idea on What causes this?
TIA
Trophus
Hey all-
I'm trying to insert some values into an SQL Compact database on a WM6 device but there is something apparently wrong with my SQL statement...
The program is going to allow users to schedule an SMS message to be sent at a certain date and time. I'm using a database to keep track of the scheduled SMS messages. The database has 3 rows: phone number, message, and the date/time to be sent.
Here is the relevent code:
private void scheduleMenu_Click(object sender, EventArgs e)
{
//connect to DB and do our scheduling magic
string message = messageBox.Text; //should rename messageBox...
string phoneNum = phoneNumBox.Text;
string dataBase = @"Program FilesSMS_Scheduler2SMSDatabase.sdf";
//SqlCeEngine eng = new SqlCeEngine(dataBase);
SqlCeConnection conn = new SqlCeConnection("Data Source=" + dataBase);
conn.Open();
//insert phone number, message text, and date/time into DB
string cmd = "INSERT INTO Scheduler(phoneNum, message, date) VALUES("+ phoneNum + ", "+ message + ", "+ dateTimePicker1.Value +")";
SqlCeCommand cmdPhone = new SqlCeCommand(cmd,conn);
cmdPhone.ExecuteNonQuery(); //error occures here...
messageBox.Text = "";
MessageBox.Show("Message Scheduled!");
}
I'm guessing it doesn't like how I am trying to get the data from the different text boxes and the DateTimePicker to go inside the SQL command. Does anyone have any ideas on how to fix my SQL command or how to get data from a textbox and DateTimePicker to be inserted into a database a different way?
Hello,
I have a DTS package that has an ActiveX task. This task works with access to create databases and push out data to them. The problem is, I'm getting an error trying to establish a connection to the SQL Server database as such:
set objConn = Server.CreateObject("ADODB.Connection")
I get this as a resolution:
http://support.microsoft.com/default.aspx?scid=kb;en-us;201740
But I wasn't sure since I'm not dealing with ASP. I'm trying to run the task as myself manually, not scheduled also, for testing purposes. Any ideas?
Thanks.
I get this error msg when I run my ActiveX script in a DTS package.
Err number: 429
Err Message: ActiveX component can't create object
When I Set crApplication = CreateObject("CrystalRuntime.Application.9")
if Err.Number <>0 then
'I get the message here
ne one know what this is about? I'm running this package on SQL server 2000 with Admistrative access
I am tying to run this DTS:
the job has an activex part which uses :
"Set fso = CreateObject("Scripting.FileSystemObject")"
this row gives me the following error:
"ActiveX Scripting encountered a Run Time Error during the execution of the script. "
the dts worked when I run it local on my station but when I try running it on the server this error comes
can u help???
10x
Hello All,
I have an ActiveX script that runs DTS pachkages. In the event that a package fails I want to display the error message. I found the following code (in bold) for doing this but I keep getting an error message as follows.
Type mismatch 'oSoStep.GetExecutionErrorInfo'
All help appreciated.
Thanks, Gary
HI Experts,
I am having 2 severs (SQL2000 & SQL2005), there is already one DTS package in SQL 2000, now i have migrated DTS package to SSIS package. Now the problem is DTS package is having ActiveX script and it was executing perfectly in 2000 server but after migrated to 2005 server if i am executing the coverted SSIS package it is giving one error and the error is displaying here.
Error1Validation error. DTSTask_DTSDataPumpTask_1: OLE DB Destination [181]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Microsoft OLE DB Provider for SQL Server Copy" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.SearsCDCC_Transfer (1).dtsx00
and i am also displaying the ACtiveX code as well and here oma11pngrdb02 is 2000 server and sant01pngrdb is 2005 server.
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
Dim gsErrorFile
gsErrorFile = "\oma11pngrdb02App_Data_ReportingSearserrors2.txt"
Function Main()
Main = DTSTaskExecResult_Success
set objftp = CreateObject("ftpx.aspftp")
objftp.sServerName = "10.1.3.175"
objftp.sUserID = "COS.FTP"
objftp.sPassword = "SENDFILE"
if objftp.bConnect then
dim fname, lfname
fname = "$IOMH04.PRDFFTAP.ST462330"
lfname = "\oma11pngrdb02App_Data_ReportingSearscdccfile.txt"
If objFTP.bGetFile (fname, lfname ) then
Main = DTSTaskExecResult_Success
Else
logError("Error getting file: " & objFTP.sErrorDesc)
Main = DTSTaskExecResult_Failure
End If
Else
logError("Error connecting: " & objFTP.sErrorDesc)
Main = DTSTaskExecResult_Failure
end if
End Function
Function logError(sErrorMsg)
Dim objErrorFile, objFSO
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objErrorFile = objFSO.OpenTextFile(gsErrorFile, 2,True)
objErrorFile.writeLine "Error!! Error!! Error!! Error!! Error!! Error!! Error!! Error!! Error!! Error!! "
objErrorFile.writeLine FormatDateTime(Now(),vbShortdate) & " " & FormatDateTime(Now(),vbShorttime)
objErrorFile.writeLine sErrorMsg
objErrorFile.writeLine "Error!! Error!! Error!! Error!! Error!! Error!! Error!! Error!! Error!! Error!! "
objErrorFile.close
Set objErrorFile = Nothing
Set objFSO = Nothing
End Function
Can any one give me the solution as early as possible, and your help is soo Appreciable
-Thanks N Regards,
Kanthi.
My ActiveX code wrote:
Function Main()
msgbox "Hello" & DTSTaskExecResult_Failure & DTSTaskExecResult_Success
Main = DTSTaskExecResult_Failure
End Function
However - it displays "Hello10" - but SSIS will not throw an error - it executes successfully.
Any ideas?
Hi,
I'm trying to reset the below Global varialbe in SSIS - Activex script.
Function Main()
DTSGlobalVariables("GxvFilename").value =null
Main = DTSTaskExecResult_Success
End Function
Getting the below Error when executing the package.
[ActiveX Script Task] Error: Retrieving the file name for a component failed with error code 0x060339E4
Please provide me solution for this.
Thanks,
I am using ActiveX Script tasks to branch out on the different tasks that I have within the DTS. When I want a particular branch of tasks not to be executed, I give a "Main = DTSStepScriptResult_DontExecuteTask"
This seems to be working fine in terms of functionality, but there is an error that is displayed saying "Task reported failure on execution".
How do I get rid of this? I need to exit the task, and still just get one message box at the end of it all that happily reads "Succesfully completed tasks"
HI Experts,
I am having 2 severs (SQL2000 & SQL2005), there is already one DTS package in SQL 2000, now i have migrated DTS package to SSIS package. Now the problem is DTS package is having ActiveX script and it was executing perfectly in 2000 server but after migrated to 2005 server if i am executing the coverted SSIS package it is giving one error and the error is displaying here.
Error 1 Validation error. DTSTask_DTSDataPumpTask_1: OLE DB Destination [181]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Microsoft OLE DB Provider for SQL Server Copy" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed. SearsCDCC_Transfer (1).dtsx 0 0
and i am also displaying the ACtiveX code as well and here oma11pngrdb02 is 2000 server and sant01pngrdb is 2005 server.
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
Dim gsErrorFile
gsErrorFile = "\oma11pngrdb02App_Data_ReportingSearserrors2.txt"
Function Main()
Main = DTSTaskExecResult_Success
set objftp = CreateObject("ftpx.aspftp")
objftp.sServerName = "10.1.3.175"
objftp.sUserID = "COS.FTP"
objftp.sPassword = "SENDFILE"
if objftp.bConnect then
dim fname, lfname
fname = "$IOMH04.PRDFFTAP.ST462330"
lfname = "\oma11pngrdb02App_Data_ReportingSearscdccfile.txt"
If objFTP.bGetFile (fname, lfname ) then
Main = DTSTaskExecResult_Success
Else
logError("Error getting file: " & objFTP.sErrorDesc)
Main = DTSTaskExecResult_Failure
End If
Else
logError("Error connecting: " & objFTP.sErrorDesc)
Main = DTSTaskExecResult_Failure
end if
End Function
Function logError(sErrorMsg)
Dim objErrorFile, objFSO
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objErrorFile = objFSO.OpenTextFile(gsErrorFile, 2,True)
objErrorFile.writeLine "Error!! Error!! Error!! Error!! Error!! Error!! Error!! Error!! Error!! Error!! "
objErrorFile.writeLine FormatDateTime(Now(),vbShortdate) & " " & FormatDateTime(Now(),vbShorttime)
objErrorFile.writeLine sErrorMsg
objErrorFile.writeLine "Error!! Error!! Error!! Error!! Error!! Error!! Error!! Error!! Error!! Error!! "
objErrorFile.close
Set objErrorFile = Nothing
Set objFSO = Nothing
End Function
Can any one give me the solution as early as possible, and your help is soo Appreciable
Hi,
I migrated DTS From sql server 2000 to 2005 .
But i have problemes with the ativex :
Error: 0xC0048006 at Loop action, ActiveX Script Task: Retrieving the file name for a component failed with error code 0x000F5544.
In DTS ActiveX scripting it was common to get a reference to the package itself using the following line of code:
Dim package = DTSGlobalVariables.Parent
This was used to navigate over the DTS object model , to loop.
Someone can help me to know haw I can do the same thing with the SSIS.
Regards,
when i run activex Script it's shows this error
[ActiveX Script Task] Error: Retrieving the file name for a component failed with error code 0x001B6438
Hi everyone,
I have a cmd file that executes sql statements:
something like this:
Code Snippet
OSQL -S %sqlServer% -E -b -n -i DATABASE.sql"
IF ERRORLEVEL 1 GOTO error
echo -
...
:error
@ECHO An error occured in [%errorLevel%]
echo [%errorLevel%] > %1
GOTO EXIT
if there is an error, I print the errorlevel in a txt file wich is a parameter in the command (%1)
What I want is: instead of sending the %errorlevel% to the txt, I want to send the error description but I don't know the variable that keeps the error description.
Any help please
Thanx in advance
'@P0' means what?
thank you!
SELECT
usr_login "User ALM EMCID",
employeeID "User PV EMPID",
usr_udf_nt_login "ALM USR LOGIN",
usr_first_name "User FirstName",
usr_last_name "User LastName",
usr_email "User Email",
usr_emp_type "Employee Type",
usr_status "ALM Status",
PeopleView.Active_Term "PV Status",
IsActive "AD Status",
benabled "ACE Status",
usr_start_date "User Start Date",
usr_end_date "User End Date",
usr_udf_manager_emcid "Manager EMCID",
manager_first_name "Manager FirstName",
manager_last_name "Manager LastName",
manager_email "Manager Email",
manager_status "Manager Status",
manager_end_date "Manager End Date"
from
(select
ALM.usr_login,
PV.employeeID,
ALM.usr_udf_nt_login,
ALM.usr_first_name,
ALM.usr_last_name,
ALM.usr_email,
ALM.usr_emp_type,
ALM.usr_status,
PV.Active_Term,
AD.IsActive,
ACE.benabled,
ALM.usr_start_date,
ALM.usr_end_date,
ALM.usr_udf_manager_emcid,
ALM.manager_first_name,
ALM.manager_last_name,
ALM.manager_email,
ALM.manager_status,
ALM.manager_end_date
from dbo.ALM_USERS ALM,
dbo.PeopleViewPV,
dbo.corp_emp_ids AD,
dbo.ALM_USERS ACE
where cast(ALM.USR_login as int)= PV.EmployeeID -- PV
and ALM.USR_login = CAST(AD.employeeid AS varchar)---AD
and ALM.USR_login = ACE.chdata ----ACE
AND ALM.USR_login NOT IN ('BTGWC1', 'DMADMIN', 'IFILL', 'TESTUSER', 'WTDEV06', 'XELOPERATOR', 'XELSELFREG', 'XELSYSADM')
AND ALM.USR_STATUS = 'Disabled')
i have a huge stored proc which has about 8-9 cursors which move data from a few temp tables to the final tables...and also do lots of calculations in between..
anytime there is an error in the stored proc i have an error page which looks like this
**********************
Server Error in '/WebApplication3' Application.
--------------------------------------------------------------------------------
Error converting data type varchar to bigint. 139 pdiscnum 37 3429 4978 139 139 4979 50.93 189.93 4980 -189.93 0 4981 139 139 4982 -139 0 4986 23 23 4987 0 23 4988 0 23 5121 139 162 5122 -328.93 -166.93 3430 4983 89 89 4984 -89 0 5096 89 89 5097 -178 -89 5135 89 0 5144 139 139 3431 4989 89 89 4990 -89 0 5100 89 89 5101 -178 -89 3432 4991 139 139 4992 4.32 143.32 4993 -143.32 0 5102 139 139 5103 -278 -139 3433 4994 139 139 4995 50.93 189.93 4996 8.64 198.57 4997 -198.57 0 5003 0 0 5123 139 139 5124 -328.93 -189.93 3434 4998 59 59 4999 -59 0 5000 59 59 5001 -59 0 5002 0 0 5041 19 19 5042 0 19 5043 -2.39 16.61 5044 -16.61 0 5045 19 19 5046 -2.39 16.61 5047 -16.61 0 5056 0 0 5084 39 39 5085 -5.85 33.15 5086 -49.76 -16.61 5104 59 42.39 5105 .........
************************************************
a very lengthy page...the numbers are prbly due to the print stmts of the results of some calculations...
but my q is..is there any way to return the xact line number where the error occured in the stored proc instead of these infinite list of numbers...its taking pretty long time to go through the entire stored proc to isolate the error...
thanks
dinakar
hi,
i got this error when i run app.
---------------
Line 1: Incorrect syntax near '='.
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.Data.SqlClient.SqlException: Line 1: Incorrect syntax near '='.
Source Error:
Line 40: Dim adpt As New SqlDataAdapter("SELECT * FROM SMS_student_class_master WHERE" & _
Line 41: "stud_id=" & sid, con)
Line 42: adpt.Fill(ds, "SMS_student_class_master")
Line 43: txt.Text = ds.Tables.Item("roll_no").ToString
Line 44: con.Close()
Source File: c:inetpubwwwrootaspnetsmsassignment_d.aspx.vb Line: 42
--------
what should i do?
anyone have any idea?
plz give solution.
it's urgent.
thanks in advance
Hi all,
i have an insert function using stored procedure with parameters. At some point while assigning parameters i make a casting problem but all i get is:
String was not recognized as a valid Boolean.
how can i find the exact line causing the error?
thank you
-shane
I have SQLEXPR (SP1) and expanded it with the /x switch.
Next, I tried the following from the command line and the installation generates an error message about my test password not being strong enough.
setup.exe /qn ADDLOCAL=ALL SECURITY MODE=SQL SQLACCOUNT="NT AUTHORITYNETWORK SERVICE" SQLPASSWORD=elinor&2JACOB2#zinno SQLBROWSER="NT AUTHORITYNETWORK SERVICE" SQLBROWSERPASSWORD=elinor&2JACOB2#zinno DISABLENETWORKPROTOCOLS=0 SQLBROWSERAUTOSTART=1
I'm trying to create a customized SQLexpr. Is this the correct way to do it? What am I doing wrong?
Thanks,
jerry
Hi,
I have a problem with a report in SSRS 2005. I tried to add some custom code to a report.
The code is absolutely simple and correct.
But when trying to show the report the debugger shows the following error message:
error [BC30494] Line is too long.
When I remove the code the report works again.
The code is about 12 words and could not be the problem.
Could anyone help please?
Best regards,
Stefoon
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.
Hi,
for some AP issue, the file I upload must be without the line feed/carriage return in the last line.
for example:
original fixed-length file (exported from SSIS)
line NO DATA
1 AA123456 50 60
2 BB123456 30 40
3 CC123456 80 90
4 <-- with line feed/carriage return in the last line
The file format that AP request. The file only has 3 records, so it should end in the third line.
line NO DATA
1 AA123456 50 60
2 BB123456 30 40
3 CC123456 80 90
Should I use script component to do it ? I am new for VB . Anyone would help me ?
Thank you all.
I need the Trend line for the following data in Line chart they are the following data. The following are the graph are my output and i need the trend line for these Key_gap value.
This is the link [URL] ....
I need the same trend line for the Bar-Chart in SSRS 2005.
I hope I'm posting this in the correct forum (forgive me if I'm not) since I'm not sure if this is an issue with inserting an item into a db or the processing of what I get out of it. I wrote a basic commenting system in which someone my post a comment about something written on the site. I wanted to keep it very simple, but I at least want the ability for a user to have newlines in their comment without having to hardcode a <br /> or something like that. Is there a way for me to detect a newline if someone, for example, is going to their next paragraph?
Let me know if you need a better explanation.
Thanks in advance!