Stored Procedure Using A Declared Variable In Insert Query (inline Or Using EXEC)

Can't TABLE Variable Be Used In EXEC Statement In Stored Procedure
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 Replies !

---

Passing A SSIS Global Variable To A Declared Variable In A Query In SQL Task
I have a SQL Task that updates running totals on a record inserted using a Data Flow Task. The package runs without error, but the actual row does not calculate the running totals. I suspect that the inserted record is not committed until the package completes and the SQL Task is seeing the previous record as the current. Here is the code in the SQL Task:
 
DECLARE @DV INT;
SET @DV = (SELECT MAX(DateValue) FROM tblTG);
DECLARE @PV INT;
SET @PV = @DV - 1;
 
I've not been successful in passing a SSIS global variable to a declared parameter, but is it possible to do this:
 
DECLARE @DV INT;
SET @DV = ?;
DECLARE @PV INT;
SET @PV = @DV - 1;

 
I have almost 50 references to these parameters in the query so a substitution would be helpful.
 
Dan
 

View Replies !

---

Is It Possible To Use Twice Declared. Variable Names- KILL And After Declared. Variable
is it possible to use twice declared. Variable names-
declared. Variable  and after KILL
and use the same declared. Variable 
like

DECLARE

@StartDate datetime
 
KILL @StartDate datetime (remove from memory)
use after with the same name

i have 2  big stored PROCEDURE
i need to put one after one
and psss only 1 Variable name to the second stored PROCEDURE
like this i don't get this error
 

The variable name '@Start_Date' has already been declared. Variable names must be unique within a query batch or stored procedure.

Msg 134, Level 15, State 1, Line 146

The variable name '@End_Date' has already been declared. Variable names must be unique within a query batch or stored procedure.
i use like
KILL @endDate  ??
KILL @StartDate   ??

 
TNX

View Replies !

---

Using Declared Variable As Passphrase Slows Query
 

I have two tables - gift_cards and history - each related by a field called "card_number".  This field is encrypted in the history table but not in the gift_cards table.  Let's say the passphrase is 'mypassphrase'.  The following query takes about 1 second to execute with a fairly large amount of data in both tables:
 

SELECT max([history].[date_of_wash]) AS LastUse

FROM gift_cards AS gc LEFT JOIN history

ON gc.card_number=CAST(DecryptByPassPhrase('mypassphrase', HISTORY.CARD_NUMBER) AS VARCHAR(50))

GROUP BY gc.card_number

 
When I use a declared variable to contain the passphrase, the same query takes over 40 seconds.  For example,
 

declare @vchPassphrase as nvarchar(20)

select @vchPassphrase = 'mypassphrase'

SELECT max([history].[date_of_wash]) AS LastUse

FROM gift_cards AS gc LEFT JOIN history

ON gc.card_number=CAST(DecryptByPassPhrase(@vchPassphrase, HISTORY.CARD_NUMBER) AS VARCHAR(50))

GROUP BY gc.card_number

 
This query is part of a stored procedure and, for security reasons, I can't embed the passphrase in it.  Can anyone explain the discrepancy between execution times and suggest a way to make the second query execute faster?
 
Thanks,
SJonesy

View Replies !

---

Stored Procedure Using A Declared Cursor
I need to write a stored procedure using T-SQL to declare a cursor for containing id(staff_no), names and specialism of all doctors that have specialism, The contents of the cursor then are to be displayed using a loop and print statement to give a formatted display of the output of each record within the cursor.

The doctors table has the following columns with specialism allowing NULL values

doctor
(
staff_no CHAR(3),
doctor_name CHAR(12),
position CHAR(15),
specialism CHAR(15),
PRIMARY KEY(staff_no)
)

Any help would be greatly appreciated.

View Replies !

---

Converting An Inline Sql To A Stored Procedure
Hi i have the following method in my page, it works fine and everything, but i am trying to modify it so that it can take a stored procedure;
 protected void Button1_Click(object sender, EventArgs e)
{SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["streamConnectionString"].ConnectionString);String sql = "SELECT userID, userName FROM users WHERE userName LIKE " + "'" + userName.Text + "%' OR organisation= " + "'" + OrganisationList.SelectedValue + "'";
conn.Open();
SqlCommand comm = new SqlCommand(sql, conn);SqlDataReader reader = comm.ExecuteReader(CommandBehavior.CloseConnection);
DataList1.DataSource = reader;
DataList1.DataBind();
conn.Close();
}
 
My question is what should my stored procedure be, and also how will i now structure the above method.

View Replies !

---

Changing A Stored Procedure To Inline Sql
Hi i have the following stored procedure, i am trying to convert it to inline sql, however i got stuck,  because how would i output a value e.g "@Access_Right_ID",  this is the stored procedure below
CREATE PROCEDURE dhoc_AccessRight_Insert  @AccessLevel char(50), @Access_Right_ID int OUT,
 @Tstamp timestamp out
 ASSELECT * FROM tblAccess_Right
WHERE AccessLevel = @AccessLevel IF @@ROWCOUNT <>0   BEGIN         RAISERROR('This record is already in the database',16,1)         RETURN 14    END
ELSE   BEGIN SET NOCOUNT OFF;             INSERT INTO tblAccess_Right (   AccessLevel ) VALUES (@AccessLevel);            SET @Access_Right_ID = @@Identity            SET @Tstamp = (SELECT Tstamp FROM tblAccess_Right WHERE Access_Right_ID=@Access_Right_ID)
      --Make sure this has saved, if not return 10 as this is unexpected error     IF @@rowcount = 0 BEGIN                    RAISERROR('This record has not been inserted at this time, it might have been inserted by another user, please try again',16,1)        RETURN 10 END     ELSE          BEGIN  IF @@error <>0 BEGIN    RETURN @@error            END     ENDEND
GO

View Replies !

---

How To Create A Stored Procedure Of This Code (inline Sql In Aspx) ?
I have some SQL code as inline SQL (bad habit, I know). Now I want to convert this to an sproc, but I'm pretty much out of ideas here. The code looks like this:
string SQL = "SELECT * FROM MyDBTable WHERE 1=1";
if (txtMyField1.Text != "")
{
SQL = SQL + " AND MyField1 = @MyField";
}
if (txtMyField2.Text != "")
{
SQL = SQL + " AND MyField2 LIKE '%'+ @MyField2 + '%'";
}
if (txtMyField3.Text != "")
{
SQL = SQL + " AND MyField3 LIKE '%' + @MyField3 + '%'";
}
I have an search page built on ASP.NET 2.0. Based on what the user has entered to the form fields, the SQL in constructed on the fly. Since this is now inside codebehind file (aspx.cs), I want to get rid of it and move it to an sproc. But the question is how ? Some simple SQL clauses are easy to convert to an sproc but this is causing me lots of issues.

View Replies !

---

Can A Stored Procedure Called From An Inline Table-Valued Function
Hi,

I'm trying to call a Stored Procedure from a Inline Table-Valued Function. Is it possible? If so can someone please tell me how? And also I would like to call this function from a view. Can it be possible? Any help is highly appreciated. Thanks

View Replies !

---

Variable Declaration In A Stored Procedure Query
I have the following lines embedded in a stored procedure

---
----
----
select @querystr = "select @lkinpos = pos_lkin_pos from "+@database+" where pos_clnt_id = "+str(@clntid)+" and pos_isin ="+"'"+ @isinno+"'"

print @querystr
exec(@querystr)
---
---
---

When i execute the above stored procedure, it returns an error as follows:

Server: Msg 137, Level 15, State 1, Line 1
Must declare the variable '@lkinpos'.

When i see the print @querystr statement, it returns the query str as follows:

select @lkinpos = pos_lkin_pos from nsdldpm..pos_mstr where pos_clnt_id = 10000045 and pos_isin ='ine227a01011'

This when executed independently, gives me the required output.

Can anybody solve this for me?
Thanks in advance

View Replies !

---

How To Get Query Second Column Value/other Rows While Assigning To A Variable In A Stored Procedure?
Hi,
I'm try to get the query second column value when it is assinged to a varchar variable.
Ex:In SP below is statement I wrote
SET @Values  =  (SELECT COL1,COL2 FROM TABLE)
Question 1:  How to access the COL2 value from @Value2?
Question 2:  How to access the other row values if above returns more than a row?
Please send me the solution as soon as possible.
Thanks-Vikash/Bala

View Replies !

---

Variable Names Must Be Unique Within A Query Batch Or Stored Procedure.
hi ,

 i got this error ... i have tried various scenarios.. nothing works for me... can any one gimme the proper guidance...

 

The variable name '@CCSID' has already been declared. Variable names must be unique within a query batch or stored procedure.

thanx in advance

raj

View Replies !

---

Insert Query With Stored Procedure
Hi,is it possible to create an "INSERT INTO ..... "Select from storedprocedure" Query?I want to create an temporary table. In this table I want to enter the data,which I can get from an stored procedure.But in the FROM-clause a stored procedure is not allowed?

View Replies !

---

Stored Procedure To Run Query And Then Insert
I have a query that select rows from the employees,salary_head and salary_group tables
this is the query
SELECT dbo.salary_head.salary_group_id, dbo.salary_group.salary_group, dbo.salary_head.amount, dbo.grade_level.[level],
dbo.employees.employ_name, dbo.employees.work_id, dbo.employees.company_id, dbo.employees.designation, dbo.salary_head.level_id,
dbo.employees.terminate, dbo.employees.banks_id, dbo.employees.bank_account_no
FROM dbo.employees INNER JOIN
dbo.salary_head INNER JOIN
dbo.salary_group ON dbo.salary_head.salary_group_id = dbo.salary_group.salary_group_id ON
dbo.employees.level_id = dbo.salary_head.level_id INNER JOIN
dbo.grade_level ON dbo.employees.level_id = dbo.grade_level.level_id
i also have a table called payrollers1 with the following fields
payroll_id int auto
payperiod_id int
employee_id
level_id
designation_id
banks_id
bankaccount_no
salarygroup_id
Amount
I am trying to write a stored procedure that will run the above query and then insert the values of the employee_id,level_id,designation_id,salary_group_i d,amount rows into the payroller table.
As for the payperiod_id i want the Stored procedure to look up the max payperiod value.
I am totally new to stored procedure and do not know how to write this code.
Can somebody help me with this code.

View Replies !

---

Help With TSQL Stored Procedure - Error-Exec Point-Procedure Code
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 Replies !

---

STORED PROCEDURE INSERT QUERY ADVICE
I have 3 tables:

1) users: users information
2) products: products available
3) purchases: has relationships to both the users and products tables.

How can I write a stored procedure that inserts to the Purchases table and
populates to the other two tables. I was thinking that the records have to be
populated first on the "users" and "products" table then
when my INSERT is done on the "purchases" table it will refer to the
other two tables. I tried query builder but it wouldn’t allow me to insert from
one single INSERT statement to one table. Any advice that on how to approach
this? Thanks you!

View Replies !

---

Set Value For Variable In A Declared Cursor
Hi,

I have a problem on setting the value for the variable in a declared cursor. Below is my example, I have declared the cursor c1 once at the top in a stored procedure and open it many times in a loop by setting the variable @str_var to different values. It seems the variable cannot be set after the cursor declared. Please advise how can I solve this issue.

------------------------------------------------------------------------
DECLARE @str_var VARCHAR(10)
DECLARE @field_val VARCHAR(10)

DECLARE c1 CURSOR LOCAL FOR
SELECT field1 FROM tableA WHERE field1 = @str_var


WHILE (Sometime TRUE)
BEGIN

....

SET @str_var = 'set to some values, eg. ABC123, XYZ123'

OPEN c1

FETCH c1 INTO @field_val

WHILE (@@fetch_status != -1)
BEGIN

PRINT @field_val
...

FETCH c1 INTO @field_val
END

CLOSE c1

END

DEALLOCATE c1

----------------------------------------------------------------------

Thanks a lots,
Vincent

View Replies !

---

Cursor Declared With Variable In Where Clause
When I execute next query on sqlserver 6.5 nested in stored procedure I can see that 'open testCursor' selected rows using new value of @var. When I execute query on sqlserver 7.0 I can see that 'open testCursor' selected rows using value of @var before 'declare ... cursor'. Is there any way to force sqlserver 7.0 to proccess cursor like it did it before.

select @var = oldValue

declare testCursor cursor
for select someColumns
from someTable
where someColumn = @var

select @var = newValue

open testCursor

fetch next from testCursor into @someColumns

Thank's in advance.

Mirko.

View Replies !

---

Problem Returning A Declared Variable
when I run this sproc all I get out of it is "the commands completed successfully" and doesn't return the value. If anyone can point out where the error is I would really appreciate it. Thanks


Code:


Create Procedure LookupLeagueIdByUserName(@userName as varchar(40) = '') as
begin
if (@userName = '')
raiserror('LookupLeagueIdByUserName: Missing parameters', 16,1)
else
begin
Declare @leagueId int
Set @leagueId = -1

--Check if the username belong to a player
Select @leagueId = leagueId From Users u
inner join players p on p.userId = u.userId
inner join teams t on p.teamId = t.teamId
where u.userName = @userName

if (@leagueId > 0)
begin
return @leagueId
end
else
begin
--Check if the username belong to a teamUser
Select @leagueId = leagueId From Users u
inner join teamUsers tu on tu.userId = u.userId
inner join teams t on tu.teamId = t.teamId
where u.userName = @userName

if (@leagueId > 0)
begin
return @leagueId
end
else
begin
--Check if the username belong to a leagueUser
Select @leagueId = leagueId From Users u
inner join leagueUsers lu on lu.userId = u.userId
where u.userName = @userName

if (@leagueId > 0)
begin
return @leagueId
end
else
begin
--username is not in db or is an admin user
return -1
end
end
end
end
end
return
-- when I run this I get no results returned
LookupLeagueIdByUserName 'chris'

View Replies !

---

Exec Stored Procedure
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 Replies !

---

Exec Stored Procedure
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 Replies !

---

Exec Stored Procedure
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 Replies !

---

How To Exec A Stored Procedure
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 Replies !

---

Exec Twp Stored Procedure In A Main Stored Procedure
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 Replies !

---

Inline Variable Assignment
I have to write a query for printing multiple barcodes, depending on the quantity of items that came in the store, based on the order number.



DECLARE @num INT
SELECT BarCodes.BarCode, BarCodes.ArticleID, ArticlesTrafic.DocumentID, ArticlesTrafic.TrafficQuantity
FROM BarCodes INNER JOIN
Articles ON BarCodes.ArticleID = Articles.ArticleID INNER JOIN
getAutoNumberTable(@num) ON @num=ArticlesTrafic.TrafficQuantity
WHERE (ArticlesTrafic.DocumentID = @Param2)



The thing i would like to do, is somehow assign a value to @num and pass it to the getAutoNumberTable stored procedure, which generates a table of consequtive numbers, so that each record is displayed multiple times. Is it even possible to do it without using temp tables and loops?

View Replies !

---

Obtaining Collation Length Of Declared Variable Within SP
Morning All,Can I have some help with this one please, I am having to make a fixed length text file based on information from the DBDeclare @EDIString varchar(MAX)Declare @RecordType varchar(2)Declare @RegistrationMark varchar(7)Declare @Model_Chassis varchar(11)Declare @LocationCode Varchar(4)Declare @MovementDate varchar(8)Declare @IMSAccountCode varchar(5)Declare @MovementType varchar(8)Declare @NotUsed1 Varchar(28)Declare @NotUsed2 varchar(7)Select @RecordType = RecordType, @RegistrationMark = RegistrationMark, @Model_Chassis = Model_And_Chassis, @LocationCode = LocationCode, @MovementDate = MovementDate, @IMSAccountCode = IMSAccountCode, @Movementtype = MovementTypeCode from Fiat_OutBoundOnce I have selected the information from the DB I need to ensure that each field is the correct length.  I therefore want to pass the variable and the length of the variable into a function to return the correct length.So if location Code = 'AB'  this needs to be four characters long so want to pass it into a function and return 'AB  'As I need to do this for 70+ variables is there an easy way to obtain the length of the collation for the variable?regardsTom

View Replies !

---

Select Declared Variable With Case Statements
I am trying to gather counts for table imports made for files from friday - sunday and create a variable that will be the body of an email.
I'd like to use either a case statement or a while statement since the query is the same but the values must be collected for each day (friday, saturday and sunday) and will all be included in the same email.

I have declared all variables appropriately but I will leave that section of the code out.


Select @ifiledate = iFileDate from tblTelemark where
iFileDate = CASE
WHEN iFileDate = REPLACE(CONVERT(VARCHAR(10), GETDATE()-3, 101), '/','') THEN

Select @countfri1 = Count(*) from tbl1
Select @countfri2 = Count(*) from tbl2
Select @countfri3 = Count(*) from tbl3
Select @countfri4 = Count(*) from tbl4


WHEN iFileDate = REPLACE(CONVERT(VARCHAR(10), GETDATE()-2, 101), '/','') THEN
Select @countsat1 = Count(*) from tbl1
Select @countsat2 = Count(*) from tbl2
Select @countsat3 = Count(*) from tbl3
Select @countsat4 = Count(*) from tbl4

WHEN iFileDate = REPLACE(CONVERT(VARCHAR(10), GETDATE()-1, 101), '/','') THEN
Select @countsun1 = Count(*) from tbl1
Select @countsun2 = Count(*) from tbl2
Select @countsun3 = Count(*) from tbl3
Select @countsun4 = Count(*) from tbl4


END

Is there a way to do what this that works???

View Replies !

---

How To Call A Variable Declared In Parent Package
 

I have declared a variable XYZ in Parent package
Similarly I have declared ABC in Child package and have done the configuration.
I have assigned some value to XYZ
How to get the value in Child Package.

View Replies !

---

Stored Procedure Exec An SQL Statement
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 Replies !

---

Timeout While Exec Stored Procedure
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 Replies !

---

OPENROWSET, EXEC And Stored Procedure
How can I dynamically run code that execute remote sp? I execure remote queries, but now I've to remotely run the undocumented 'dbcc showfilestats' !!

declare @SQL varchar(2000)
SELECT @SQL = "SELECT a.* FROM OPENROWSET('SQLOLEDB','Server=[Server Name];Trusted_Connection=yes', 'exec sp_who') AS a"
exec (@sql)

When you run it, sql answer

Server: Msg 207, Level 16, State 3, Line 2
Invalid column name 'SELECT a.* FROM OPENROWSET('SQLOLEDB','Server=DSIALBA;Trusted_Conn ection=yes', 'exec sp_who') AS a'.

but the code is correct...


Where it the mistake? :-((

Thans a lot!

][arco

View Replies !

---

Exec Stored Procedure Permission
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 Replies !

---

SSIS: Problem Mapping Global Variables To Stored Procedure. Can't Pass One Variable To Sp And Return Another Variable From Sp.
I'm new to SSIS, but have been programming in SQL and ASP.Net for several years. In Visual Studio 2005 Team Edition I've created an SSIS that imports data from a flat file into the database.  The original process worked, but did not check the creation date of the import file. I've been asked to add logic that will check that date and verify that it's more recent than a value stored in the database before the import process executes.
 
Here are the task steps.


[Execute SQL Task] - Run a stored procedure that checks to see if the import is running. If so, stop execution. Otherwise, proceed to the next step.

[Execute SQL Task] - Log an entry to a table indicating that the import has started.

[Script Task] - Get the create date for the current flat file via the reference provided in the file connection manager. Assign that date to a global value (FileCreateDate) and pass it to the next step.  This works.

[Execute SQL Task] - Compare this file date with the last file create date in the database. This is where the process breaks.  This step depends on 2 variables defined at a global level. The first is FileCreateDate, which gets set in step 3.  The second is a global variable named IsNewFile. That variable needs to be set in this step based on what the stored procedure this step calls finds out on the database.  Precedence constraints direct behavior to the next proper node according to the TRUE/FALSE setting of IsNewFile.


If IsNewFile is FALSE, direct the process to a step that enters a log entry to a table and conclude execution of the SSIS.

If IsNewFile is TRUE, proceed with the import.  There are 5 other subsequent steps that follow this decision, but since those work they are not relevant to this post.
Here is the stored procedure that Step 4 is calling.  You can see that I experimented with using and not using the OUTPUT option.  I really don't care if it returns the value as an OUTPUT or as a field in a recordset.  All I care about is getting that value back from the stored procedure so this node in the decision tree can point the flow in the correct direction.
 

CREATE PROCEDURE [dbo].[p_CheckImportFileCreateDate]

/*

The SSIS package passes the FileCreateDate parameter to this procedure, which then compares that parameter with the date saved in tbl_ImportFileCreateDate.

If the date is newer (or if there is no date), it updates the field in that table and returns a TRUE IsNewFile bit value in a recordset.

Otherwise it returns a FALSE value in the IsNewFile column.

Example:

exec p_CheckImportFileCreateDate 'GL Account Import', '2/27/2008 9:24 AM', 0

*/

@ProcessName varchar(50)

, @FileCreateDate datetime

, @IsNewFile bit OUTPUT

AS

SET NOCOUNT ON

--DECLARE @IsNewFile bit

DECLARE @CreateDateInTable datetime

SELECT @CreateDateInTable = FileCreateDate FROM tbl_ImportFileCreateDate WHERE ProcessName = @ProcessName

IF EXISTS (SELECT ProcessName FROM tbl_ImportFileCreateDate WHERE ProcessName = @ProcessName)

BEGIN

-- The process exists in tbl_ImportFileCreateDate. Compare the create dates.

IF (@FileCreateDate > @CreateDateInTable)

BEGIN

-- This is a newer file date. Update the table and set @IsNewFile to TRUE.

UPDATE tbl_ImportFileCreateDate

SET FileCreateDate = @FileCreateDate

WHERE ProcessName = @ProcessName

SET @IsNewFile = 1

END

ELSE

BEGIN

-- The file date is the same or older.

SET @IsNewFile = 0

END

END

ELSE

BEGIN

-- This is a new process for tbl_ImportFileCreateDate. Add a record to that table and set @IsNewFile to TRUE.

INSERT INTO tbl_ImportFileCreateDate (ProcessName, FileCreateDate)

VALUES (@ProcessName, @FileCreateDate)

SET @IsNewFile = 1

END

SELECT @IsNewFile
 
The relevant Global Variables in the package are defined as follows:
Name : Scope : Date Type : Value
FileCreateDate : (Package Name) : DateType : 1/1/2000
IsNewFile : (Package Name) : Boolean : False
 
Setting the properties in the "Execute SQL Task Editor" has been the difficult part of this.  Here are the settings.
 
General
Name = Compare Last File Create Date
Description = Compares the create date of the current file with a value in tbl_ImportFileCreateDate.
TimeOut = 0
CodePage = 1252
ResultSet = None
ConnectionType = OLE DB
Connection = MyServerDataBase
SQLSourceType = Direct input
IsQueryStoredProcedure = False
BypassPrepare = True
 
I tried several SQL statements, suspecting it's a syntax issue. All of these failed, but with different error messages. These are the 2 most recent attempts based on posts I was able to locate.
SQLStatement = exec ? = dbo.p_CheckImportFileCreateDate 'GL Account Import', ?, ? output
SQLStatement = exec p_CheckImportFileCreateDate 'GL Account Import', ?, ? output
 
Parameter Mapping
Variable Name = User::FileCreateDate, Direction = Input, DataType = DATE, Parameter Name = 0, Parameter Size = -1
Variable Name = User::IsNewFile, Direction = Output, DataType = BYTE, Parameter Name = 1, Parameter Size = -1
 
Result Set is empty.
Expressions is empty.
 
When I run this in debug mode with this SQL statement ...
exec ? = dbo.p_CheckImportFileCreateDate 'GL Account Import', ?, ? output
... the following error message appears.
 
SSIS package "MyPackage.dtsx" starting.
Information: 0x4004300A at Import data from flat file to tbl_GLImport, DTS.Pipeline: Validation phase is beginning.
 
Error: 0xC002F210 at Compare Last File Create Date, Execute SQL Task: Executing the query "exec ? = dbo.p_CheckImportFileCreateDate 'GL Account Import', ?, ? output" failed with the following error: "No value given for one or more required parameters.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
 
Task failed: Compare Last File Create Date
 
Warning: 0x80019002 at GLImport: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED.  The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

SSIS package "MyPackage.dtsx" finished: Failure.

When the above is run tbl_ImportFileCreateDate does not get updated, so it's failing at some point when calling the procedure.
 
When I run this in debug mode with this SQL statement ...
exec p_CheckImportFileCreateDate 'GL Account Import', ?, ? output
... the tbl_ImportFileCreateDate table gets updated.  So I know that data piece is working, but then it fails with the following message.
 
SSIS package "MyPackage.dtsx" starting.
Information: 0x4004300A at Import data from flat file to tbl_GLImport, DTS.Pipeline: Validation phase is beginning.

Error: 0xC001F009 at GLImport: The type of the value being assigned to variable "User::IsNewFile" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.

Error: 0xC002F210 at Compare Last File Create Date, Execute SQL Task: Executing the query "exec p_CheckImportFileCreateDate 'GL Account Import', ?, ? output" failed with the following error: "The type of the value being assigned to variable "User::IsNewFile" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.
". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: Compare Last File Create Date

Warning: 0x80019002 at GLImport: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED.  The Execution method succeeded, but the number of errors raised (3) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

SSIS package "MyPackage.dtsx" finished: Failure.
 
The IsNewFile global variable is scoped at the package level and has a Boolean data type, and the Output parameter in the stored procedure is defined as a Bit.  So what gives?
 
The "Possible Failure Reasons" message is so generic that it's been useless to me.  And I've been unable to find any examples online that explain how to do what I'm attempting. This would seem to be a very common task.  My suspicion is that one or more of the settings in that Execute SQL Task node is bad.  Or that there is some cryptic, undocumented reason that this is failing.
 
Thanks for your help.
 

View Replies !

---

Exec A Stored Procedure And Not Wait For Response
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 Replies !

---

Error Executing Stored Procedure - EXEC(&#34;any String&#34;)
I'am getting an error when running a stored procedure that issues an EXEC command. The message says that a variable has not been declared when it has.
The error message states "Must declare the variable '@gid_amt'."

Below is code from the procedure:

DECLARE @gid_amt char(18)

BEGIN
SELECT @select_stmt = "SELECT @gid_amt=SUM(" + @elm_nm + ")" +
" FROM TGDMKT_SEC_ASSET a " +
" JOIN TGDMKT_SEC_FACT b " +
" ON a.GID_ASSET_ID = b.GID_ASSET_ID " +
" AND a.ACCTG_TIME_ID = b.ACCTG_TIME_ID " +
" WHERE a.ACCTG_TIME_ID = 18 " +
" AND a.ASSET_CTGY_CD = 'FM' " +
" AND a.ST_CD = 'N' " +
" AND a.SCHD_D_CD = " + @schd_d_cd +
" AND a.SCHD_D_KIND_CD = " + @schd_d_kind_cd +
" AND b.HYP_MGMT_ID IN ( SELECT c.HYP_MGMT_ID " +
"FROM TGDHYP_MGMT_ORG c " +
" WHERE c.MLNM_CORP_CD = " + @mlnm_corp_cd +
" AND c.MLNM_PROD_LN_CD = " + @mlnm_prod_ln_cd +
" AND c.CO_CD <> '^^^^' " +
" AND c.SUBSEG_CD <> '^^')"


EXEC (@select_stmt)

END

If have tried everthing to resolve the problem. If anyone knows of a way to resolve the problem, please let me know.

Thanks,
Eric

View Replies !

---

Report Error Using Stored Procedure With Exec Sp_executesql
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 Replies !

---

How To Capture The ResultSet Of EXEC Command In Stored Procedure
 

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 Replies !

---

EXEC Stored Procedure For Every Line Of SELECT Result Table - How?
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 Replies !

---

Update Statement Using Declared Variable Produces Unexpected Results On SQL Server 2005
We are getting unexpected results from the following update statement when it is executed on SQL Server 2005.
 

The strange thing is that we get duplicated values for QM_UID (although when run under SQL Server 2000 we don't get duplicated values)


Can anyone explain why this happens or suggest another way of populating this column without using a cursor or adding a temporary autoincrement column and copying the values over?


declare @NextID int;

set @NextID = 1;

update tmp set QM_UID=@NextID, @NextID = @NextID + 1;

select QM_UID, count(*) from tmp group by QM_UID having count(*) > 1 order by QM_UID


QM_UID count(*)

25 2
26 3
27 4
28 4
29 4
30 4
31 4
32 4
33 4
34 4
35 5
36 4
37 4
38 4
39 4
40 3

...

 

--- Script to replicate problem

 

-- NB: The number of rows that must be added to tmp before this problem will occur is machine dependant

--        100000 rows is sufficient on one of our servers but another (faster) server doesn't show the error

--        at 100000 rows but does at 1000000 rows.

 

-- Create a table

CREATE TABLE tmp (
    [QM_ADD_OP] [char](6) DEFAULT '' NOT NULL,
    [QM_ADD_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL,
    [QM_ADD_TIME] [int] DEFAULT -1 NOT NULL,
    [QM_EDIT_OP] [char](6) DEFAULT '' NOT NULL,
    [QM_EDIT_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL,
    [QM_EDIT_TIME] [int] DEFAULT -1 NOT NULL,
    [QM_LOCK_OP] [char](6) DEFAULT '' NOT NULL,
    [QM_QUOTE_JOB] [smallint] DEFAULT 0 NOT NULL,
    [QM_QUOTE_NUM] [char](12) DEFAULT '' NOT NULL,
    [QM_JOB_NUM] [char](12) DEFAULT '' NOT NULL,
    [QM_PRJ_NUM] [char](12) DEFAULT '' NOT NULL,
    [QM_NUMBER] [char](12) DEFAULT '' NOT NULL,
    [QM_REV_NUM] [char](6) DEFAULT '' NOT NULL,
    [QM_REV_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL,
    [QM_REV_TIME] [int] DEFAULT -1 NOT NULL,
    [QM_REV_OPR] [char](6) DEFAULT '' NOT NULL,
    [QM_STYLE_CODE] [char](4) DEFAULT '' NOT NULL,
    [QM_REP_JOB_NUM] [char](12) DEFAULT '' NOT NULL,
    [QM_REP_COLUMN] [smallint] DEFAULT 0 NOT NULL,
    [QM_REP_PART] [char](6) DEFAULT '' NOT NULL,
    [QM_REP_MODEL] [smallint] DEFAULT 0 NOT NULL,
    [QM_REP_TYPE] [smallint] DEFAULT 0 NOT NULL,
    [QM_MODEL_QUOTE] [char](12) DEFAULT '' NOT NULL,
    [QM_RUN_NUM] [int] DEFAULT 0 NOT NULL,
    [QM_SOURCE_QUOTE] [char](12) DEFAULT '' NOT NULL,
    [QM_SOURCE_VAR] [smallint] DEFAULT 0 NOT NULL,
    [QM_SOURCE_QTY] [char](12) DEFAULT '' NOT NULL,
    [QM_SOURCE_PART] [char](6) DEFAULT '' NOT NULL,
    [QM_SOURCE_MODEL] [smallint] DEFAULT 0 NOT NULL,
    [QM_ORIG_QUOTE] [char](12) DEFAULT '' NOT NULL,
    [QM_ORIG_VAR] [smallint] DEFAULT 0 NOT NULL,
    [QM_ORIG_QTY] [char](12) DEFAULT '' NOT NULL,
    [QM_ORIG_PART] [char](6) DEFAULT '' NOT NULL,
    [QM_COPY_JOB] [char](12) DEFAULT '' NOT NULL,
    [QM_COPY_COLUMN] [smallint] DEFAULT 0 NOT NULL,
    [QM_COPY_J_PART] [char](6) DEFAULT '' NOT NULL,
    [QM_COPY_QUOTE] [char](12) DEFAULT '' NOT NULL,
    [QM_COPY_VAR] [smallint] DEFAULT 0 NOT NULL,
    [QM_COPY_QTY] [char](12) DEFAULT '' NOT NULL,
    [QM_COPY_Q_PART] [char](6) DEFAULT '' NOT NULL,
    [QM_JOINT_STATUS] [smallint] DEFAULT 0 NOT NULL,
    [QM_QUOTE_STATUS] [smallint] DEFAULT 0 NOT NULL,
    [QM_JOB_STATUS] [smallint] DEFAULT 0 NOT NULL,
    [QM_LIVE_STATUS] [smallint] DEFAULT 0 NOT NULL,
    [QM_USER_STATUS] [smallint] DEFAULT 0 NOT NULL,
    [QM_DEL_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL,
    [QM_IS_CONVERTED] [smallint] DEFAULT 0 NOT NULL,
    [QM_PRINTED] [smallint] DEFAULT 0 NOT NULL,
    [QM_COPY_RATES] [smallint] DEFAULT 0 NOT NULL,
    [QM_IMPORT_UPDATE] [smallint] DEFAULT 0 NOT NULL,
    [QM_CRED_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL,
    [QM_CRED_TIME] [int] DEFAULT -1 NOT NULL,
    [QM_CRED_AMT] numeric(26,8) DEFAULT 0 NOT NULL,
    [QM_CRED_OP] [char](6) DEFAULT '' NOT NULL,
    [QM_HELD] [smallint] DEFAULT 0 NOT NULL,
    [QM_PROOF] [char](12) DEFAULT '' NOT NULL,
    [QM_DELIV_METHOD] [char](12) DEFAULT '' NOT NULL,
    [QM_ART_METHOD] [char](12) DEFAULT '' NOT NULL,
    [QM_DES_TYPE] [smallint] DEFAULT 0 NOT NULL,
    [QM_REC_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL,
    [QM_REC_TIME] [int] DEFAULT -1 NOT NULL,
    [QM_OWN_OP] [char](6) DEFAULT '' NOT NULL,
    [QM_RESP_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL,
    [QM_RESP_TIME] [int] DEFAULT -1 NOT NULL,
    [QM_RESP_OP] [char](6) DEFAULT '' NOT NULL,
    [QM_RESP_OP_1] [char](6) DEFAULT '' NOT NULL,
    [QM_RESP_OP_2] [char](6) DEFAULT '' NOT NULL,
    [QM_RESP_OP_3] [char](6) DEFAULT '' NOT NULL,
    [QM_RESP_OP_4] [char](6) DEFAULT '' NOT NULL,
    [QM_RESP_OP_5] [char](6) DEFAULT '' NOT NULL,
    [QM_RECONTACT] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL,
    [QM_REQ_FLAG] [smallint] DEFAULT 0 NOT NULL,
    [QM_ORIG_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL,
    [QM_ORIG_TIME] [int] DEFAULT -1 NOT NULL,
    [QM_PREF_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL,
    [QM_PREF_TIME] [int] DEFAULT -1 NOT NULL,
    [QM_LATE_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL,
    [QM_LATE_TIME] [int] DEFAULT -1 NOT NULL,
    [QM_TITLE] [char](72) DEFAULT '' NOT NULL,
    [QM_DELIV_CODE] [char](12) DEFAULT '' NOT NULL,
    [QM_CLT_SPEC] [char](12) DEFAULT '' NOT NULL,
    [QM_TAX_REF] [char](22) DEFAULT '' NOT NULL,
    [QM_CONTACT] [char](36) DEFAULT '' NOT NULL,
    [QM_PHONE] [char](22) DEFAULT '' NOT NULL,
    [QM_FAX] [char](22) DEFAULT '' NOT NULL,
    [QM_ORDER] [char](20) DEFAULT '' NOT NULL,
    [QM_ORDER_CFM] [smallint] DEFAULT 0 NOT NULL,
    [QM_ORDER_REL] [char](6) DEFAULT '' NOT NULL,
    [QM_REP] [char](12) DEFAULT '' NOT NULL,
    [QM_REP_1] [char](12) DEFAULT '' NOT NULL,
    [QM_REP_2] [char](12) DEFAULT '' NOT NULL,
    [QM_REP_3] [char](12) DEFAULT '' NOT NULL,
    [QM_REP_4] [char](12) DEFAULT '' NOT NULL,
    [QM_REP_5] [char](12) DEFAULT '' NOT NULL,
    [QM_COORDINATOR] [char](12) DEFAULT '' NOT NULL,
    [QM_PRIORITY] [smallint] DEFAULT 0 NOT NULL,
    [QM_TYPE_CODE] [char](12) DEFAULT '' NOT NULL,
    [QM_GRADE] [smallint] DEFAULT 0 NOT NULL,
    [QM_FIN_SIZE_CODE] [char](12) DEFAULT '' NOT NULL,
    [QM_FIN_WID] numeric(26,8) DEFAULT 0 NOT NULL,
    [QM_FIN_LEN] numeric(26,8) DEFAULT 0 NOT NULL,
    [QM_FIN_DEP] numeric(26,8) DEFAULT 0 NOT NULL,
    [QM_FIN_GUSS] numeric(26,8) DEFAULT 0 NOT NULL,
    [QM_FIN_GSM] numeric(26,8) DEFAULT 0 NOT NULL,
    [QM_FIN_UNIT] [char](12) DEFAULT '' NOT NULL,
    [QM_ORIENT] [smallint] DEFAULT 0 NOT NULL,
    [QM_PROD_CODE] [char](22) DEFAULT '' NOT NULL,
    [QM_FIN_GOOD] [char](22) DEFAULT '' NOT NULL,
    [QM_CUST_CODE] [char](12) DEFAULT '' NOT NULL,
    [QM_CUST_CODE_1] [char](12) DEFAULT '' NOT NULL,
    [QM_CUST_CODE_2] [char](12) DEFAULT '' NOT NULL,
    [QM_CUST_PROS] [smallint] DEFAULT 0 NOT NULL,
    [QM_REQD_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL,
    [QM_REQD_TIME] [int] DEFAULT -1 NOT NULL,
    [QM_FOLIO] [char](12) DEFAULT '' NOT NULL,
    [QM_FOLIO_1] [char](12) DEFAULT '' NOT NULL,
    [QM_FOLIO_2] [char](12) DEFAULT '' NOT NULL,
    [QM_PACK_QTY] numeric(26,8) DEFAULT 0 NOT NULL,
    [QM_USAGE] numeric(26,8) DEFAULT 0 NOT NULL,
    [QM_REORDER] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL,
    [QM_EACH_WGT] numeric(26,8) DEFAULT 0 NOT NULL,
    [QM_WGT_UNIT] [char](12) DEFAULT '' NOT NULL,
    [QM_RFQ_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL,
    [QM_RFQ_TIME] [int] DEFAULT -1 NOT NULL,
    [QM_RFQ_OPR] [char](6) DEFAULT '' NOT NULL,
    [QM_SALES_TYPE] [smallint] DEFAULT 0 NOT NULL,
    [QM_SALES_SRC] [char](12) DEFAULT '' NOT NULL,
    [QM_SALES_RSN] [char](12) DEFAULT '' NOT NULL,
    [QM_PROFILE] [char](12) DEFAULT '' NOT NULL,
    [QM_JOB_QTY] numeric(26,8) DEFAULT 0 NOT NULL,
    [QM_PREV_QTY] numeric(26,8) DEFAULT 0 NOT NULL,
    [QM_JOB_UNIT] [char](12) DEFAULT '' NOT NULL,
    [QM_PO_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL,
    [QM_PO_TIME] [int] DEFAULT -1 NOT NULL,
    [QM_PO_OP] [char](6) DEFAULT '' NOT NULL,
    [QM_DLY_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL,
    [QM_DLY_TIME] [int] DEFAULT -1 NOT NULL,
    [QM_QTY_DESP] numeric(26,8) DEFAULT 0 NOT NULL,
    [QM_TOTAL_DLY] [int] DEFAULT 0 NOT NULL,
    [QM_SCHED_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL,
    [QM_SCHED_TIME] [int] DEFAULT -1 NOT NULL,
    [QM_CLOSE_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL,
    [QM_CLOSE_TIME] [int] DEFAULT -1 NOT NULL,
    [QM_INV_NUM] [char](12) DEFAULT '' NOT NULL,
    [QM_PACK_NUM] [char](12) DEFAULT '' NOT NULL,
    [QM_DOWN_LOAD] [smallint] DEFAULT 0 NOT NULL,
    [QM_TRACK_CODE] [char](4) DEFAULT '' NOT NULL,
    [QM_TAX_TYPE] [smallint] DEFAULT 0 NOT NULL,
    [QM_TAX_CODE] [char](6) DEFAULT '' NOT NULL,
    [QM_CURR] [char](6) DEFAULT '' NOT NULL,
    [QM_EXCH_RATE] numeric(18,8) DEFAULT 0 NOT NULL,
    [QM_UNIT_QTY] numeric(26,8) DEFAULT 0 NOT NULL,
    [QM_UNIT_FLAG] [smallint] DEFAULT 0 NOT NULL,
    [QM_RUNON_QTY] numeric(26,8) DEFAULT 0 NOT NULL,
    [QM_SPEC_QTY] numeric(26,8) DEFAULT 0 NOT NULL,
    [QM_CHARGEABLE] [smallint] DEFAULT 0 NOT NULL,
    [QM_NC_REASON] [char](22) DEFAULT '' NOT NULL,
    [QM_CUST_MKUP] numeric(18,8) DEFAULT 0 NOT NULL,
    [QM_JOB_MKUP] numeric(18,8) DEFAULT 0 NOT NULL,
    [QM_BROKERAGE] numeric(18,8) DEFAULT 0 NOT NULL,
    [QM_CUST_DISC] numeric(18,8) DEFAULT 0 NOT NULL,
    [QM_INVOKED_BTNS] [int] DEFAULT 0 NOT NULL,
    [QM_IMPORTED] [smallint] DEFAULT 0 NOT NULL,
    [QM_IMPORT_RECALC] [smallint] DEFAULT 0 NOT NULL,
    [QM_IMPORT_CONVERT] [smallint] DEFAULT 0 NOT NULL,
    [QM_BRANCH] [char](6) DEFAULT '' NOT NULL,
    [QM_CODE] [char](36) DEFAULT '' NOT NULL,
    [QM_TEMPLATE] [smallint] DEFAULT 0 NOT NULL,
    [QM_REPEAT_PERIOD] [int] DEFAULT 0 NOT NULL,
    [QM_REOPEN_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL,
    [QM_CAT_OPTION] [char](16) DEFAULT '' NOT NULL,
    [QM_UNIT_ID] [char](10) DEFAULT '' NOT NULL,
    [QM_PROD_BRANCH] [char](6) DEFAULT '' NOT NULL,
    [QM_UID] [int] DEFAULT 0 NOT NULL,
    [QM_AVAIL_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL,
    [QM_AVAIL_TIME] [int] DEFAULT -1 NOT NULL
) ON [PRIMARY]


GO

-- Create an index on the table

CREATE unique INDEX [QM_NUMBER_ORDER] ON tmp([QM_QUOTE_JOB], [QM_NUMBER]) ON [PRIMARY]

GO

 

-- Populate the table

declare @Counter as int

SET NOCOUNT ON

set @Counter = 1

while @Counter < 100000

begin

insert into tmp (QM_ADD_TIME, QM_NUMBER) values (1,@Counter);

set @Counter = @Counter + 1

end

GO

-- Update QM_UID to a sequential value

declare @NextID int;

set @NextID = 1;

update tmp set QM_UID=@NextID, @NextID = @NextID + 1;

 

-- Find rows with a duplicate QM_UID (there should be no duplicate)

select QM_UID, count(*) from tmp group by QM_UID having count(*) > 1 order by QM_UID

--drop table tmp

 

-- output from select @@VERSION

-- Microsoft SQL Server 2005 - 9.00.3054.00 (Intel X86)   Mar 23 2007 16:28:52   Copyright (c) 1988-2005 Microsoft Corporation  Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)

View Replies !

---

In Stored Procedure How To Loop Through Rows In Table And Pass Parameter To EXEC SP
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 Replies !

---

Using Declared Variables In SQL INSERT Statement.
 
I am new to scripting in general and I've run into an issue when attempting to write a VB variable to a database table in SQL Express.  I am trying to record the value of the variable to the db, but it does not appear that the value is being passed to SQL.  If I hard code the values in the SQL statement it works fine.  Can someone explain what I'm doing wrong accomplish this?  My code is below.  Thanks in advance. 
file.aspx
<asp:SqlDataSource ID="SqlDataSource" runat="server"
ConnectionString="<%$ ConnectionStrings:SqlConnectionString %>"
SelectCommand="SELECT * FROM [Table]"
InsertCommand="INSERT INTO [Table] (field1, field2) VALUES (& variable1 &, & variable2 &);" >
</asp:SqlDataSource>
file.aspx.vb
Protected Sub Button_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button.Click
Dim variable1 As String = FileUpload.FileName
Dim variable2 As String = Date.Now
Dim path As String = Server.MapPath("~/directory/)
If FileUpload.HasFile = True Then
Try
SqlDataSource.Insert()
FileUpload.PostedFile.SaveAs(path & _
FileUpload.FileName)
End Try
 
End If
 
End Sub

View Replies !

---

Using Table Name Stored In A Scalar Variable In Stored Procedure Problem
Hello to all!

I have a table name stored in a scalar variable (input parameter of my stored procedure). I need to run SQL statement: SELECT COUNT (*) FROM MyTable and store the result of my query in a scalar variable:

For example:

declare @countRows int

set @countRows = (select count(*) from MyTable)

The problem is that the name of MyTable is stored in the input variable of my stored procedure and of corse this does not work:

declare @countRows int

set @countRows = (select count(*) from @myTableName)

I also tried this:

declare @sqlQuery varchar(100)

set @sqlQuery = 'select count(*) from ' + @myTableName

set  @countRows = exec(@sqlQuery)

But it looks like function exec() does not return any value...

Any idea how to solve this problem?

Thanx,

Ziga

View Replies !

---

Variable Insert To SQL Server Insert Satement Setting Values For The @variable INSIDE Sql
ok, I am on Day 2 of being brain dead.I have a database with a table with 2 varchar(25) columns I have a btton click event that gets the value of the userName,  and a text box.I NEED to insert a new row in a sql database, with the 2 variables.Ive used a sqldatasource object, and tried to midify the insert parameters, tried to set it at the button click event, and NOTHING is working. Anyone have a good source for sql 101/ASP.Net/Braindead where I can find this out, or better yet, give me an example.  this is what I got <%@ Page Language="C#" %><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><script runat="server">     protected void runit_Click(object sender, EventArgs e)    {       //SqlDataSource ID = "InsertExtraInfo".Insert();      //SqlDataSource1.Insert();    }      protected void Button1_Click1(object sender, EventArgs e)    {        SqlDataSource newsql;                newsql.InsertParameters.Add("@name", "Dan");        newsql.InsertParameters.Add("@color", "rose");        String t_c = "purple";        string tempname = Page.User.Identity.Name;        Label1.Text = tempname;        Label2.Text = t_c;        newsql.Insert();    }</script><html xmlns="http://www.w3.org/1999/xhtml" ><head runat="server">    <title>mini update</title></head><body>    <form id="form1" runat="server">        &nbsp;name<asp:TextBox ID="name" runat="server" OnTextChanged="TextBox2_TextChanged"></asp:TextBox><br />        color        <asp:TextBox ID="color" runat="server"></asp:TextBox><br />        <br />        <asp:Button ID="Button1" runat="server" OnClick="Button1_Click1" Text="Button" />        &nbsp;<br />        set lable =&gt;<asp:Label ID="Label1" runat="server" Text="Label" Width="135px" Visible="False"></asp:Label><br />        Lable 2 =&gt;        <asp:Label ID="Label2" runat="server" Text="Label"></asp:Label><br />        Usernmae=&gt;<asp:LoginName ID="LoginName1" runat="server" />        <br />        <br />        <br />        <br />        <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConflictDetection="CompareAllValues"            ConnectionString="<%$ ConnectionStrings:newstring %>" DeleteCommand="DELETE FROM [favcolor] WHERE [name] = @original_name AND [color] = @original_color"            InsertCommand="INSERT INTO [favcolor] ([name], [color]) VALUES (@name, @color)"            OldValuesParameterFormatString="original_{0}" SelectCommand="SELECT [name], [color] FROM [favcolor]"            UpdateCommand="UPDATE [favcolor] SET [color] = @color WHERE [name] = @original_name AND [color] = @original_color">            <DeleteParameters>                <asp:Parameter Name="original_name" Type="String" />                <asp:Parameter Name="original_color" Type="String" />            </DeleteParameters>            <UpdateParameters>                <asp:Parameter Name="color" Type="String" />                <asp:Parameter Name="original_name" Type="String" />                <asp:Parameter Name="original_color" Type="String" />            </UpdateParameters>            <InsertParameters>        <asp:InsertParameter("@name", "Dan", Type="String" />        <asp:InsertParameter("@color", "rose") Type="String"/>                                       </InsertParameters>        </asp:SqlDataSource>        &nbsp;        <asp:GridView ID="GridView1" runat="server" AllowPaging="True" AllowSorting="True"            AutoGenerateColumns="False" DataKeyNames="name" DataSourceID="SqlDataSource1">            <Columns>                <asp:CommandField ShowDeleteButton="True" ShowEditButton="True" ShowSelectButton="True" />                                <asp:BoundField DataField="color" HeaderText="color" SortExpression="color" />                <asp:BoundField DataField="name" HeaderText="name" ReadOnly="True" SortExpression="name" />            </Columns>        </asp:GridView>           </form></body></html>  

View Replies !

---

T-SQL And Visual Basic 2005 Codes That Execute A User-Defined Stored Procedure In Management Studio:How To Declare EXEC &&amp; Sp?
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 Replies !

---

Job To Exec Stored Proc To Exec DTS Package
We want to limit the access of our developers to production, yet allow them to run DTS pakages without having access to the package itself. One idea was to create a job that executes a stored procedure that executes the DTS package.
Here's the developer's tale of woe:

When I originall wrote the application I instantiated a DTS Package object in Visual Basic using the SQL-DMO library and executed it directly from the code. This was not desirable because it required SA access on the server.
I've tried the following steps as a member of an NT Global Group. This global group should have data reader/writer access to the database. This login is not SA on the server

Executing master..xp_cmdshell 'dtsrun /Sausroadsgate /E /Nroads_cd_import'
For this option, I was a public member of master and granted execute access to xp_cmdshell.
The error I received when I tried this option with normal permissions is below:
"Msg 50001, Level 1, State 50001
xpsql.cpp Error 997 from GetProxyAccount on line 472"
When I added account to the SA role, it worked correctly.

Next I created a Job. The job had a single step, which was to run a CmdExec task. The command line for the CmdExec task was: "dtsrun /Sausroadsgate /E /Nroads_cd_import". In a stored procedure to be called by the client application I added the following statement:
msdb.dbo.sp_start_job @job_name = 'roads_cd_import'
The error I received when I tried this option with normal permissions is below:
"Server: Msg 14262, Level 16, Procedure sp_verify_job_identifiers, Line 61
The specified @job_name ('roads_cd_import') does not exist."
When I added the account to the SA role, it worked correctly.

Any help is appreciated

View Replies !

---

BULK Insert From A Text File Name Stored Into A Variable
Hello I need to write a proc to load data from txt files I receive into a table. It works fine when I specify
bulk insert.... from 'myfilename.txt'
BUT my filename will always change and I store it into a variable @filename

When I try to run the bulk insert instruction ... from @filename it doesn't work..
do you know why?

Thank you in advance

View Replies !

---

Using An Exec Query To Insert Pdf, .doc File Into Table From A Dir Path Which Is A Field In Another Table
I have the following query in sql 2005:
 

PROCEDURE [dbo].[uspInsert_Blob] (

@fName varchar(60),

@fType char(5),

@fID numeric(18, 0),

@bID char(3),

@fPath nvarchar(60)

)

 

as

DECLARE @QUERY VARCHAR(2000)

SET @QUERY = "INSERT INTO tblDocTable(FileName, FileType, ImportExportID, BuildingID, Document)

SELECT '"+@fName+"' AS FileName, '"+@fType+"' AS FileType, " + cast(@fID as nvarchar(18)) + " as ImportExportID, '"+@bID+"' AS BuildingID, * FROM OPENROWSET( BULK '" +@fPath+"' ,SINGLE_BLOB)

AS Document"

EXEC (@QUERY)
 
This puts some values including a pdf or .doc file into a table, tblDocTable.
 
Is it possible to change this so that I can get the values from a table rather than as parameters.  The Query would be in the form of: insert into tblDocTable (a, b, c, d) select a,b,c,d from tblimportExport.
 
tblImportExport has the path for the document (DocPath)  so I would subsitute that field, ie. DocPath, for the @fPath variable.
 
Otherwise I can see only doing a Fetch next from tblIportExport where I would put every field into a variable and then run this exec query on these. Thus looping thru every row in tblImportExport.
 
Any ideas how to do this?

View Replies !

---