EXEC Procedure Behave Different In DTS/QueryAnalyzer
Nov 9, 2000
Hi,
My DTS package performs the following:
1. 4 transformations to transform data from Sybase tables A,B,C,D to the temp tables in MSSQL
tmpA, tmpB,tmpC,tmpD
2. Next, I have a task to run 4 stored procedures to load the 4 tmp* tables to actual tables A,B,C,D
(so the task is "exec spA, exec spB, exec spC, exec spD".
3. There are 9/26/280/10000 records in the tables A,B,C,D
4. each stored procedure basically checks whether the record in the tmp* tables exist in the actual
table baesd on the primary key and then perform an insert/update.
The strange thing is:
1. All 9 records in the tmpA is loaded to A. Only 17 records from tmpB is loaded to B
2. The same codes "exec spA, exec spB, exec spC, exec spD", if copied to query analyzer,
they all run to completion, ie all 10000+ records are loaded. so there is no pbm with the data.
3. If I "split" the task such that task1 loads A and task2 loads B,C,D (and task2 runs after task1)
now again all data for A is loaded, but 25 records from B is loaded
4. I tried to catch the @@error in the stored procedure for the insert/update statements
but there's no error. Most importantly, the stored procedures run fine in query analyzer
Is there some sort of timeout or buffer issue here that is causing this strange behaviour.
All help is appreciated.
Thanx
Jes
View 3 Replies
ADVERTISEMENT
Nov 6, 2007
I am building a stored procedure that changes based on the data that is available to the query. See below.
The query fails on line 24, I have the line highlighted like this.
Can anyone point out any problems with the sql?
------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------
This is the error...
Msg 8114, Level 16, State 5, Procedure sp_SearchCandidatesAdvanced, Line 24
Error converting data type varchar to numeric.
------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------
This is the exec point...
EXEC [dbo].[sp_SearchCandidatesAdvanced]
@LicenseType = 4,
@PositionType = 4,
@BeginAvailableDate = '10/10/2006',
@EndAvailableDate = '10/31/2007',
@EmployerLatitude = 29.346675,
@EmployerLongitude = -89.42251,
@Radius = 50
GO
------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------
This is the STORED PROCEDURE...
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[sp_SearchCandidatesAdvanced]
@LicenseType int = 0,
@PositionType int = 0,
@BeginAvailableDate DATETIME = NULL,
@EndAvailableDate DATETIME = NULL,
@EmployerLatitude DECIMAL(10, 6),
@EmployerLongitude DECIMAL(10, 6),
@Radius INT
AS
SET NOCOUNT ON
DECLARE @v_SQL NVARCHAR(2000)
DECLARE @v_RadiusMath NVARCHAR(1000)
DECLARE @earthRadius DECIMAL(10, 6)
SET @earthRadius = 3963.191
-- SET @EmployerLatitude = 29.346675
-- SET @EmployerLongitude = -89.42251
-- SET @radius = 50
SET @v_RadiusMath = 'ACOS((SIN(PI() * ' + @EmployerLatitude + ' / 180 ) * SIN(PI() * p.CurrentLatitude / 180)) + (COS(PI() * ' + @EmployerLatitude + ' / 180) * COS(PI() * p.CurrentLatitude / 180) * COS(PI()* p.CurrentLongitude / 180 - PI() * ' + @EmployerLongitude + ' / 180))) * ' + @earthRadius
SELECT @v_SQL = 'SELECT p.*, p.CurrentLatitude, p.CurrentLongitude, ' +
'Round(' + @v_RadiusMath + ', 0) AS Distance ' +
'FROM ProfileTable_1 p INNER JOIN CandidateSchedule c on p.UserId = c.UserId ' +
'WHERE ' + @v_RadiusMath + ' <= ' + @Radius
IF @LicenseType <> 0
BEGIN
SELECT @v_SQL = @v_SQL + ' AND LicenseTypeId = ' + @LicenseType
END
IF @PositionType <> 0
BEGIN
SELECT @v_SQL = @v_SQL + ' AND Position = ' + @PositionType
END
IF LEN(@BeginAvailableDate) > 0
BEGIN
SELECT @v_SQL = @v_SQL + ' AND Date BETWEEN ' + @BeginAvailableDate + ' AND ' + @EndAvailableDate
END
--SELECT @v_SQL = @v_SQL + 'ORDER BY CandidateSubscriptionEmployerId DESC, CandidateFavoritesEmployerId DESC, Distance'
PRINT(@v_SQL)
EXEC(@v_SQL)
-----------------------------------------------------------------------------------------------------------------
View 4 Replies
View Related
Jul 20, 2005
A colleague of mine has a query which fails to run under SQLAgentbatch with the following error:The conversion of a char data type to a datetime data type resulted inan out-of-range datetime value. [SQLSTATE 22007] (Error 242) Thestatement has been terminated. [SQLSTATE 01000] (Error 3621). Thestep failed.He can run the same query sucessfully via query analyzer (i.e. noerrors, and it does what he wants)If I try to run the same query through Query Analyzer on myworkstation, I get a different error altogether:Server: Msg 242, Level 16, State 3, Procedure DateForGrouping, Line 11The conversion of a char data type to a datetime data type resulted inan out-of-range datetime value.Any idea what might be causing these differences in behaviourdepending on how and/or where the query is run from?The (working) statement in question is:================================================== ======================insert into Summary_ReferrerSalesselect DateCreated,ReferrerID,ReferrerIDCount,PUIDCount,ReferrerDescription,0 as TotalOrderValue,0 as TotalOrderLinesfrom vw_ReferrerPopularityWarning: Null value is eliminated by an aggregate or other SEToperation.(11996 row(s) affected)================================================== ====================And the table / view / function definitions (I take no responsibilityfor the view definition!) are:CREATE TABLE [Summary_ReferrerSales] ([DateCreated] [datetime] NULL ,[ReferrerID] [char] (2) NULL ,[ReferrerIDCount] [int] NULL ,[PUIDCount] [int] NULL ,[ReferrerDescription] [nchar] (100) NULL ,[TotalOrderValue] [numeric](18, 0) NULL ,[TotalOrderLines] [numeric](18, 0) NULL) ON [PRIMARY]CREATE VIEW dbo.vw_ReferrerPopularityASSELECT TOP 100 PERCENT COUNT(dbo.LogReferrerID.Referrer) ASReferrerIDCount,COUNT(DISTINCT dbo.LogReferrerID.PUID) AS PUIDCount,dbo.DateForGrouping(dbo.LogReferrerID.DateUsed)AS DateCreated,dbo.LogReferrerID.Referrer AS ReferrerID,dbo.LookupReferrerID.ReferrerDescriptionFROMdbo.LogReferrerID INNER JOIN dbo.LookupReferrerIDON dbo.LogReferrerID.Referrer = dbo.LookupReferrerID.ReferrerIDWHERE (dbo.LogReferrerID.DateUsed > CONVERT(DATETIME, '2003-09-0100:00:00', 102))GROUP BY dbo.LogReferrerID.Referrer,dbo.DateForGrouping(dbo.LogReferrerID.DateUsed),dbo.LookupReferrerID.ReferrerDescriptionHAVING (dbo.LogReferrerID.Referrer <> 'WS')AND (COUNT(dbo.LogReferrerID.Referrer) IS NOT NULL)AND (dbo.LookupReferrerID.ReferrerDescription IS NOT NULL)AND (dbo.DateForGrouping(dbo.LogReferrerID.DateUsed) >CONVERT(DATETIME, '2003-09-01 00:00:00', 102))AND (COUNT(DISTINCT dbo.LogReferrerID.PUID) IS NOT NULL)ORDER BY dbo.DateForGrouping(dbo.LogReferrerID.DateUsed) DESCCREATE TABLE [LogReferrerID] ([LogReferrerID] [int] NULL ,[Referrer] [varchar] (2) NULL ,[DateUsed] [smalldatetime] NULL ,[HTTPReferrer] [varchar] (1000) NULL ,[TargetURL] [varchar] (1000) NULL ,[QueryString] [varchar] (1000) NULL ,[PUID] [varchar] (50) NULL) ON [PRIMARY]CREATE TABLE [LookupReferrerID] ([ReferrerID] [char] (2) NOT NULL ,[ReferrerDescription] [varchar] (100) NOT NULL ,CONSTRAINT [PK_Lookup_ReferrerID] PRIMARY KEY CLUSTERED([ReferrerID]) ON [PRIMARY]) ON [PRIMARY]CREATE FUNCTION dbo.DateForGrouping (@RowDate datetime)RETURNS datetime ASBEGINdeclare @datestring char(10)set @datestring=cast(datepart(dd,@RowDate) as char(2)) + '/' +cast(datepart(mm,@RowDate) as char(2)) + '/' +cast(datepart(yyyy,@RowDate) as char(4))return cast(@datestring as datetime)END
View 3 Replies
View Related
Jul 23, 2005
Hi NG!I wrote a stored procedure which at first deletes 100.000 rows and theninserts 100.000 new rows.There is a huge difference between executing this SP in thequery-analyzer (runtime approx. 2 minutes), and scheduling this SP as anJob (runtime > 30 minutes!!!).Why? Whats the issue?And how can i improve the "job"-runtime?The INSERT-Statement gets the data from a joined and grouped SELECT -here is the code (exemplary):INSERT INTO [Table1](Field1, Field2,... )SELECT DISTINCT Field1, Count(field2), (Count(field2) * (-1))FROM Table2 T2INNER JOIN Table3 T3 ON T2.[ID] = T3.[ID]INNER JOIN Table4 T4 ON T2.[DID] = T4.[ID]INNER JOIN Table5 T5 ON T2.[SID] = T5.[ID]GROUP BY field1, field2...I have noticed something strange: After executing the SP thequeryanalyzer outputs "1 Row(s) affected." line by line - 100.000times... Obviously every line is inserted one by one.Any idea?Would it help, to create a temporary-table with SELECT INTO, and thenjust INSERT this data (w/o aggregation) into the target table?Any help is greatly appreciated, tia and best regards---Heiko
View 10 Replies
View Related
Mar 2, 2007
I have SQL stored proc that calls a CLR function. This function does a "select ... for xml" statement, manipulates the XML a little, and returns the manipulated XML to the stored proc.
This all works fine when I call the stored proc from a query window, but when I have BizTalk call the stored proc, the CLR function fails. I have a feeling this may have to do with BizTalk using MSDTC , but I am not sure.
Here's a code snippet from where CLR function fails:
SqlConnection conn = new SqlConnection("Context Connection=true");
conn.Open();SqlCommand cmd = new SqlCommand("Select * From Items FOR XML AUTO",conn);XmlDocument xdoc = new XmlDocument();xdoc.Load(cmd.ExecuteXmlReader());
Under BizTalk, the last line fails with: System.InvalidOperationException "Invalid command sent to ExecuteXmlReader. The command must return an Xml result."
Now to see why XmlReader doesn't like the returned data, i changed the last 2 lines of that snippet to this:
SqlDataReader dr = cmd.ExecuteReader();
dr.Read();Object obj = dr[0];
If i have a breakpoint after that last line, obj is of type string when i call the proc myself, but it is a byte[] under Biztalk. If i look at the bytes themselves, its close to the expected xml...but with some nontext bytes sprinkled around. I can't seem to cast or encode the byte array into anything useful.
Anyone have any idea what is going on here? Why would the same code return different types based on a) who is calling it, or b) the type of transaction used?
Thanks,
Ron
View 1 Replies
View Related
Jun 26, 2007
I have a report where I use Globals!ReportName in the header of the report for the report title. In Development and on SSRS stand alone the value for Globals!ReportName is in mixed case and the file extension is omitted. When the report is published to a MOSS server integrated with SSRS the value for Globals!ReportName is all in lower case and the file extension is included.
Is there any reason for this change in behavior and is there a way I can put back the mixed case and omit the file extension?
View 2 Replies
View Related
Jun 25, 2015
i want to create a new measure that will behave based on the dimension dropped,ex. if i added the employee dimension only it will aggregate data from the #Calls Count but if i added the product dimension it should display # Product Calls at the product level and #Calls Count at the employee level as shown in the screen shot.
View 7 Replies
View Related
Nov 5, 2007
Hello
Which is faster :
to write a a big stored procedure with if conditions, or to separate them and call them using exec??
i.e:
if @id=1 insert into ....else if @id=2 update...-----------------------orif @id=1 exec InsertProcedureelse if @id=2 exec UpdateProcedurePlease help
View 8 Replies
View Related
Jul 20, 2005
HelloI need to find out what stored procedure was executed when I click ona button in a third party application, is there a way to find thisinformation out in some sort of log?Some kind of profiling to see what procedure was executed and withwhat parameters.Kind RegardsMattwww.fruitsalad.org
View 1 Replies
View Related
Jan 10, 2006
Hi,
Hope someone can help me out here - I'm trying to design a data flow in SSIS which relies in part on a stored procedure as a data source - I'm running it through OLE DB.
Sure Exec MystoredProc works fine on preview and on parsing, but it refuses to acknowledge any of the columns, when I go to Edit-->Columns everything is blank.
Just out of interest - the reason I am using a stored procedure is because I dump the data into a temp table and then amend a couple of the columns to make it the same as my other database (for example where len(field) = 6 then field = '0000' + field).
Possibly I'm better off taking the raw data through the OLE connection and then transforming it through SSIS, but my gut feeling is I should minimise what I'm dumping into SSIS and offload the processing onto the local DB. Any thoughts?
Thanks
Rich
View 6 Replies
View Related
Aug 20, 2007
Hi,
I'm new to SSIS and SQL Server 2005 and this is now driving me very mad!!
I have an OLE DB Command in my data flow task that I want to update a table with. I have looked round this forum and on Google and just can not find a solution or what I am doing wrong. So any help would be great!
The ole db command calls a stored procedure with two input variables:
exec stp_updedgrsholds status, temp_cr_num
from debugging the ssis it says it has updated 4 rows and also from doing a data view, the data it is updating seems all correct.
but nothing gets updated in the database.
If I call the stored procedure the following way
exec stp_updedgrsholds 'C', 87
It updates fine! I have tried a number of different way with @ symbols and assignment p_status = @status
but nothing seems to work.
Any ideas are much appreciated.
Ninder Bassi
View 4 Replies
View Related
May 23, 2007
hi,
how do I exec stored procedure that accept parameter and return a single value?
here is example of report
stu_id = ******
stu_name = ****
subject | marks
aa****** | call sp_mark and return student mark for that particular student id and subject
bb****** | call sp_mark and return student mark for that particular student id and subject
cc****** | call sp_mark and return student mark for that particular student id and subject
thks,
View 3 Replies
View Related
Oct 2, 2007
I have this code in a stored procedure: DECLARE @SQLString VarChar(200)
SET @SQLString = 'SELECT ' + @LookupField + ' FROM ' + @DBTable + ' WHERE (' + @IDField + ' = ''' + @IDValue + ''')'
Exec (@SQLString)
it works fine - with just one issue - I must grant select permission on the table.
Is there a way to do this WITHOUT granting the select permissions?
View 7 Replies
View Related
Mar 12, 2008
I Have a problem When I execute a stored procedure from query analyzer
(Exec storedname @parameter1='', @Parameter2='') it take 7 min. and I stop running
If I copy stored procedure , past it in Query analyzer and declare parameters
it take 3 sec.
View 3 Replies
View Related
Feb 12, 2008
Hello,
If I grant execute permissions on stored procedures in a database and the proc in turn creates tables in the DB, and if the user is not a db_owner, will the procedure be allowed to create those tables? or will the stored procs fail?
Thanks
Arun
View 4 Replies
View Related
Sep 20, 2005
Is there a way to execute a stored procedure and have it move on without waiting for a response from the stored procedure. I am trying to create a button on a webpage that will execute a stored procedure but the procedure takes to long to run and my page times out. Instead I would like the button to start the procedure and the webpage look at a table of data. When the table of data is empty then I will know the stored procedure is complete. Is this possible?
View 1 Replies
View Related
Feb 21, 2004
Hi everybody, I would like to know if it's possible to execute a stored procedure, passing it parameters, using not CommandType.StoredProcedure value of sqlcommand, but CommandType.Text.
I tried to use this:
sqlCmd.CommandType = CommandType.Text
sqlCmd.Parameters.Add(sqlPar)
sqlCmd.ExecuteNonQuery()
With this sql command:
"exec sp ..."
I wasn't able to make it to work, and I don't know if it's possible.
Another question:
if it's not possible, how can I pass a Null value to stored procedure?
This code:
sqlPar = new SqlParameter("@id", SqlDbType.Int)
sqlPar.Direction = ParameterDirection.Output
cmd.Parameters.Add(sqlPar)
sqlPar = new SqlParameter("@parent_id", DBNull)
cmd.Parameters.Add(sqlPar)
doesn't work, 'cause I get this error:
BC30684: 'DBNull' is a type and cannot be used as an expression.
How can I solve this?
Bye and thanks in advance.
P.S. I would prefer first method to call a stored procedure ('cause I could call it with 'exec sp null' sql command, solving the other problem), but obviusly if it's possible...=)
Sorry for grammatical mistakes.
View 9 Replies
View Related
Oct 1, 2007
Hi All,
I have created a dynamic SQL STATEMENT , but the result of the execution of that statement matters to me. within stored procedure.
Note: If run the statement using EXEC() command, the result gets displayed on the SQL Editor.
But I DO NOT KNOW HOW to Capture that value.
Any idea how to capture as I would like capture the result and stored it in a table.
Thank you.
--Israr
View 4 Replies
View Related
Feb 13, 2008
Hi,
Our report is working fine with data loaded from a stored procedure (#1) that contains a fairly simple Select statement. We need the same report to work with a dataset loaded from a stored procedure (#2) that uses 'Exec sp_executesql @queryString'. Unfortunately, attempts to call the latter cause an error in the report. From everything that I've read, there should be no difference between datasets created using either method. Any ideas what could be getting in the way of the latter?
I have doublechecked that the dynamic query is returning a valid dataset and that all the columns are in the same format as sp #1. The designer shows the dataset and the report with the data loaded, but the live system produces an error.
Any help is much appreciated.
Debbie
View 4 Replies
View Related
Feb 8, 2008
Hi
I've used a temporary table in the stored procedure
I've created it as DECLARE @Temp TABLE (id INT)
in select clause I've used this temp table through the joins..
But I've also used a varchar variable to store my criteria...
which I'm building in the stored procedure
so inorder to use it in the where clause I used
EXEC ('SELECT ....................
FROM @Temp T
LEFT OUTER JOIN ...
LEFT OUTER JOIN ...
WHERE ' + @Criteria )
Unfortunately this is not working.
Giving the errror
Must declare the variable '@TempT'.
I had to use Temporary table using #, which I feel is really waste of memory...
Is there a way by which I can use my Table variable in the EXEC statement.
Thanks In advance
View 4 Replies
View Related
Jul 22, 2015
I use new query to execute my store procedure but didnt return any value is that any error for my sql statement??
USE [Pharmacy_posicnet]
GO
/****** Object: StoredProcedure [dbo].[usp_sysconf] Script Date: 22/07/2015 4:01:38 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[usp_sysconf]
[Code] ....
View 6 Replies
View Related
Jul 23, 2005
Hello,Is it possible to EXEC stored procedure from a query?I want to execute stored procedure for every line of SELECT resulttable.I guess it's possible with cursors, but maybe it's possible to make iteasier.Give an example, please.Thank you in advance.Hubert
View 2 Replies
View Related
Apr 26, 2008
I have a temporary table with multiple records and a Stored Procedure requiring a value. In a Stored Procedure, I want to loop through records in the table and use the value from each record read as input to another Stored Procedure. How do I do this?
View 7 Replies
View Related
May 14, 2008
Hello,
I have a stored procedure where I run an insert statement. I want to knwo if it is possible to do it using a variable for the table name (either in-line or with an EXEC statement without building a string first and executing that string. See examples of what I am talking about in both cases below:
I want to be able to do this (with or without the EXEC) :
------------------------------------------------------------------------------------
DECLARE @NewTableNameOut as varchar(100)
Set @NewTableNameOut = 'TableToInsertInto'
EXEC(
Insert Into @NewTableNameOut
Select * From tableToSelectFrom
)
------------------------------------------------------------------------------------
I can not do the above because it says I need to declare/set the @NewTableNameOut variable (assuming it is only looking at this for the specific insert statement and not at the variable I set earlier in the stored procedure.
I can do it like this by creating a string with the variable built into the string and then executing the string but I want to know if I can do it like I have listed above.
------------------------------------------------------------------------------------
DECLARE @NewTableNameOut as varchar(100)
Set @NewTableNameOut = 'TableToInsertInto'
EXEC(
'Insert Into ' + @NewTableNameOut + ' ' +
'Select * From tableToSelectFrom'
)
------------------------------------------------------------------------------------
It is not an issue for my simple example above but I have some rather large queries that I am building and I want to run as described above without having to build it into a string.
Is this possible at all?
If you need more info please let me know.
View 1 Replies
View Related
Apr 29, 2004
Hi there
i have a stored procedure like this:
CREATE PROCEDURE SP_Main
AS
SET NOCOUNT ON
BEGIN TRANSACTION
exec SP_Sub_One output
exec SP_Sub_Two output
IF @@ERROR = 0
BEGIN
-- Success. Commit the transaction.
Commit Tran
END
ELSE
Rollback Tran
return
GO
now the problem is, when i execute the stored procedure's inside the main stored procedure, and these sub sp's has an error on it, the main stored procedure doesnt rollback the transation.
now i can put the "begin transation" in the two sub stored procedure's. The problem is
what if the first "SP_Sub_One" executed successfully, and there was error in "SP_Sub_Two"
now the "SP_Sub_One" has been executed and i cant rollback it... the error occured in the
"SP_Sub_Two" stored procedure ... so it wont run ... so there will be error in the data
how can i make a mian "BEGIN TRANSACTION" , that even it include the execution of stored procedure in it.
Thanks in advance
Mahmoud Manasrah
View 1 Replies
View Related
Jan 17, 2008
Hi all,
In my SQL Server Management Studio Express (SSMSE), I executed the following sql code suuccessfully:
--insertNewRocord.sql--
USE shcDB
GO
CREATE PROC sp_insertNewRecord @procPersonID int,
@procFirstName nvarchar(20),
@procLastName nvarchar(20),
@procAddress nvarchar(50),
@procCity nvarchar(20),
@procState nvarchar(20),
@procZipCode nvarchar(20),
@procEmail nvarchar(50)
AS INSERT INTO MyFriends
VALUES (@procPersonID, @procFirstName, @procLastName, @procAddress,
@procCity, @procState, @procZipCode, @procEmail)
GO
EXEC sp_insertNewRecord 7, 'Peter', 'Wang', '678 Old St', 'Detroit',
'Michigon', '67899', 'PeterWang@yahoo.com'
GO
=======================================================================
Now, I want to insert a new record into the dbo.Friends table of my shcDB by executing the following T-SQL and Visual Basic 2005 codes that are programmed in a VB2005 Express project "CallshcDBspWithAdoNet":
--Form1.vb--
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Public Class Form1
Public Sub InsertNewFriend()
Dim connectionString As String = "Integrated Security-SSPI;Persist Security Info=False;" + _
"Initial Catalog=shcDB;Data Source=.SQLEXPRESS"
Dim connection As SqlConnection = New SqlConnection(connectionString)
connection.Open()
Try
Dim command As SqlCommand = New SqlCommand("sp_InsertNewRecord", connection)
command.CommandType = CommandType.StoredProcedure
EXEC sp_insertNewRecord 6, 'Craig', 'Utley', '5577 Baltimore Ave',
'Ellicott City', 'MD', '21045', 'CraigUtley@yahoo.com'
Console.WriteLine("Row inserted: " + _
command.ExecuteNonQuery().ToString)
Catch ex As Exception
Console.WriteLine(ex.Message)
Throw
Finally
connection.Close()
End Try
End Sub
End Class
===========================================================
I ran the above project in VB 2005 Express and I got the following 5 errors:
1. Name 'EXEC' is not declared (in Line 16 of Form1.vb)
2. Method arguments must be enclosed in parentheses (in Line 16 of Form1.vb)
3. Name 'sd-insertNewRecord' is not declared. (in Line 16 of Form1.vb)
4.Comma, ')', or a valid expression continuation expected (in Line 16 of Form1.vb)
5. Expression expected (in Line 16 of Form1.vb)
============================================================
I know that "EXEC sp_insertNewRecord 6, 'Craig', 'Utley', '5577 Baltimore Ave',
'Ellicott City', 'MD', '21045', 'CraigUtley@yahoo.com' "in Line 16 of Form1.vb is grossly in error.
But I am new in doing the programming of T-SQL in VB 2005 Express and I am not able to change it.
Please help and advise me how to correct these problems.
Thanks in advance,
Scott Chang
View 22 Replies
View Related
Oct 31, 2007
I have two SQL Server 2000 (one is localhost, one is remote with VPN IP 192.168.5.4).
I can select * from [192.168.5.4].db.dbo.test but I can't exec [192.168.5.4].db..spAdd in localhost.
These select and sp is OK for 1 or 2 week without any problem,but it didn't work one day.
Can some one explain why?
View 5 Replies
View Related
Feb 29, 2000
When I use EXEC in a stored procedure ( after building complex option logic) it produces an returns an error of 'Access denied' on the underlying tables.
All objects are dbo owned and execute permission has been given to all users.
Can ant one help?
Rob
View 1 Replies
View Related
Jul 22, 2003
When using a SP for getting a recordset is there any issues with using exec like in: rs.open "exec spWhatever"...
Should I use rs.open "spWhatever" or does it really matter performance wise on the SQL server?
Thanks
View 4 Replies
View Related
Nov 1, 2007
I am trying to create a awkward sp, just need to know if i can do this somehow, here i piece of the code...
create procedure IM_SP_TrPreProc /*@TableName Varchar(255),*/ @SystemFileName Varchar(255)
---------------------------------------------
--Param1 = Tablename
--Param2 = Systemfilename
---------------------------------------------
as
declare @TableName Varchar(255);--Just For Testing---DELETE!!
declare @Filename varchar(255); --Store Distinct filename
declare @DSNo Varchar(255);-- Use 'set' to execute Var TableName
declare @SumUnits Varchar(255); --Use 'set' to calculate sum of units
declare @SumValue Varchar(255);
Set @TableName = 'TrDs01' -- Testing Only--DELETE!!
------------------------Set Statements using @TableName Var------------------------------------------
Set @DSNo = 'select distinct DataSupplierNo from ' + @TableName
Set @SumUnits = 'select sum(Units) from ' + @TableName
Set @SumValue = 'Select sum(Value) from ' + @TableName
------------------------------------------------------------------------------------------------------
Insert into TransactionMaster([FileName],DataSupplierNo,ImportFileRecordID,FileLoadDate,
UnitsSum,ValueSum,RecordCount)
Select(@Filename),(exec(DSNo)), ................
Just the Bold and underlined bit "exec(DSNo)"..... is this doable in some way? can i use exec to retrieve the value to insert to data supplier. As far as i know i have to do it like this because im using a variable as the table name...
View 2 Replies
View Related
Apr 28, 2006
I need help understanding the syntax of the "exec sql" statement.
i am looking at code that build an sql string such as
sql="exec SOMETHING Session("id")"
or something like that.
then, there is
conn.execute(sql)
My question is the "SOMETHING" in the sql statement...is what? I know it is user defined (object or variable or such), but what exactly is it? i look through the rest of the code and don;'t see SOMETHING defined elsewhere.
i am not sure if i am asking the question right. i don't understand what the SOMETHING is doing, or why it is there.
in particular, the code i am examining is
sql="exec SurveyDelete "&"'" & Session("StudentID") & " ' "
conn.execute(sql)
i understand the this statement will delete a record, but how does it handle "SurveyDelete", how does it know what the is when it is not defined anywhere else in the code?
View 2 Replies
View Related
Feb 17, 2006
Hi,
Can I execute my MDX statement as in the exhibit format!
DECLARE @test VARCHAR(MAX)
SET @test = 'WITH test AS (SELECT * FROM merchants)'
EXEC(@test)
SELECT * FROM test
If I don't use that dynamice sql statement, everything work fine.
Thanks,
Myo
View 1 Replies
View Related
Mar 28, 2006
Hi,
I have a written a SP to do indexdefrag to all user table indexes in a databases...I had to use dynamic sql so I can reuse this code for any DB...
declare @strsql varchar(500)
set @strsql = ' dbcc indexdefrag('+'''DBName'''+',554556545,3)'
exec (@strsql)
When I execute the above script, I immeaditely see the results in the query analyser like below:
Pages Scanned Pages Moved Pages Removed
------------- ----------- -------------
3 0 0
Looks like the indexdefrag did not happen since the logical fragmentation is still a high number like 30%.....
Just wondering whats goin on..
Thanks.
Ranga
View 3 Replies
View Related
