How do I access the value of a stored proc return param in c# using executeNonQuery?

I've got a stored proc to insert a record and return the id of the record inserted in an output param.
How do I access this value in my code after the proc is executed?

param = comm.CreateParameter();
param.ParameterName ="@.MemberID";
param.Direction =ParameterDirection.Output;
param.DbType =DbType.Int32;
comm.Parameters.Add(param);

try
{
rowsAffected =GenericDataAccess.ExecuteNonQuery(comm);
}
catch
{
rowsAffected = -1;
}

you should simply be able to access the parameters Value property.

param.Value

How could I use row as columns?

create table t2
(
umc varchar(20),
outdate datetime,
outnumber int
)
insert t2 values (1,'2005-2-5',1)
insert t2 values (2,'2005-2-5',1)
insert t2 values (2,'2005-2-6',1)
insert t2 values (3,'2005-2-5',2)
insert t2 values (3,'2005-2-6',1)
insert t2 values (4,'2005-2-7',1)
I hope the result to be
(2005-2-5,2005-2-6,2005-2-7 is column name now)
2005-2-5 2005-2-6 2005-2-7
1 1 0 0
2 1 1 0
3 2 1 0
4 0 0 1
Can I just compose it with SELECT statement?First you have to select distinct dates into a cursor,
than select from the table left outer join each date where date from the
table = date of the column.
"XXY" <xxy02021@.NOSPAM.163.com> wrote in message
news:eUkwCXNEFHA.2508@.TK2MSFTNGP09.phx.gbl...
> create table t2
> (
> umc varchar(20),
> outdate datetime,
> outnumber int
> )
> insert t2 values (1,'2005-2-5',1)
> insert t2 values (2,'2005-2-5',1)
> insert t2 values (2,'2005-2-6',1)
> insert t2 values (3,'2005-2-5',2)
> insert t2 values (3,'2005-2-6',1)
> insert t2 values (4,'2005-2-7',1)
> I hope the result to be
> (2005-2-5,2005-2-6,2005-2-7 is column name now)
> 2005-2-5 2005-2-6 2005-2-7
> 1 1 0 0
> 2 1 1 0
> 3 2 1 0
> 4 0 0 1
> Can I just compose it with SELECT statement?
>|||http://aspfaq.com/show.asp?id=2462
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"XXY" <xxy02021@.NOSPAM.163.com> wrote in message
news:eUkwCXNEFHA.2508@.TK2MSFTNGP09.phx.gbl...
> create table t2
> (
> umc varchar(20),
> outdate datetime,
> outnumber int
> )
> insert t2 values (1,'2005-2-5',1)
> insert t2 values (2,'2005-2-5',1)
> insert t2 values (2,'2005-2-6',1)
> insert t2 values (3,'2005-2-5',2)
> insert t2 values (3,'2005-2-6',1)
> insert t2 values (4,'2005-2-7',1)
> I hope the result to be
> (2005-2-5,2005-2-6,2005-2-7 is column name now)
> 2005-2-5 2005-2-6 2005-2-7
> 1 1 0 0
> 2 1 1 0
> 3 2 1 0
> 4 0 0 1
> Can I just compose it with SELECT statement?
>|||SELECT umc,
SUM(CASE WHEN DATEDIFF(DAY,@.dt,outdate)=0 THEN outnumber ELSE 0 END),
SUM(CASE WHEN DATEDIFF(DAY,@.dt,outdate)=1 THEN outnumber ELSE 0 END),
SUM(CASE WHEN DATEDIFF(DAY,@.dt,outdate)=2 THEN outnumber ELSE 0 END)
FROM T2
WHERE outdate >= @.dt
AND outdate < DATEADD(DAY,3,@.dt)
GROUP BY umc
Column names in a query are fixed so dynamic SQL would be required to
change the names based on the data. That shouldn't really be a problem
though. It should be easy enough to display different column names in
your client application.
David Portas
SQL Server MVP
--|||Nadim,
Thanks so much and that's what I want, however, is it possible for you to
show me some sample codes based on my DDL?
yours, XXY
"Nadim Wakim" <nadimlb@.cyberia.net.lb>
:uOjnD2NEFHA.1392@.tk2msftngp13.phx.gbl...
> First you have to select distinct dates into a cursor,
> than select from the table left outer join each date where date from the
> table = date of the column.
>
> "XXY" <xxy02021@.NOSPAM.163.com> wrote in message
> news:eUkwCXNEFHA.2508@.TK2MSFTNGP09.phx.gbl...
>|||Hi David and Roji,
I do appreciated your articles and sample codes, however when the outdate
ranges much(it might be any day in a year in my table), I am afraid it is
not a good idea using datediff. Don't you think so ?
yours, XXY
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org>
:1108198808.232843.84220@.g14g2000cwa.googlegroups.com...
> SELECT umc,
> SUM(CASE WHEN DATEDIFF(DAY,@.dt,outdate)=0 THEN outnumber ELSE 0 END),
> SUM(CASE WHEN DATEDIFF(DAY,@.dt,outdate)=1 THEN outnumber ELSE 0 END),
> SUM(CASE WHEN DATEDIFF(DAY,@.dt,outdate)=2 THEN outnumber ELSE 0 END)
> FROM T2
> WHERE outdate >= @.dt
> AND outdate < DATEADD(DAY,3,@.dt)
> GROUP BY umc
> Column names in a query are fixed so dynamic SQL would be required to
> change the names based on the data. That shouldn't really be a problem
> though. It should be easy enough to display different column names in
> your client application.
> --
> David Portas
> SQL Server MVP
> --
>|||Roji, Thanks so much!!!
I read http://www.sqlteam.com/item.asp?ItemID=2955 and got the right answer
from
exec crosstab 'select umc from t2 group by
umc','sum(outnumber)','outdate','t2'
You are the MAN!!
"XXY" <xxy02021@.NOSPAM.163.com> д?
:eUkwCXNEFHA.2508@.TK2MSFTNGP09.phx.gbl...
> create table t2
> (
> umc varchar(20),
> outdate datetime,
> outnumber int
> )
> insert t2 values (1,'2005-2-5',1)
> insert t2 values (2,'2005-2-5',1)
> insert t2 values (2,'2005-2-6',1)
> insert t2 values (3,'2005-2-5',2)
> insert t2 values (3,'2005-2-6',1)
> insert t2 values (4,'2005-2-7',1)
> I hope the result to be
> (2005-2-5,2005-2-6,2005-2-7 is column name now)
> 2005-2-5 2005-2-6 2005-2-7
> 1 1 0 0
> 2 1 1 0
> 3 2 1 0
> 4 0 0 1
> Can I just compose it with SELECT statement?
>|||I'm reminded of the Di-Tech commercials:( :)
www.rac4sql.net
"XXY" <xxy02021@.NOSPAM.163.com> wrote in message
news:%23kdz4APEFHA.2608@.TK2MSFTNGP10.phx.gbl...
> Roji, Thanks so much!!!
> I read http://www.sqlteam.com/item.asp?ItemID=2955 and got the right
> answer
> from
> exec crosstab 'select umc from t2 group by
> umc','sum(outnumber)','outdate','t2'
> You are the MAN!!
>
> "XXY" <xxy02021@.NOSPAM.163.com> д?
> :eUkwCXNEFHA.2508@.TK2MSFTNGP09.phx.gbl...
>|||I don't see a problem. The date range selection is in the WHERE clause
and is sargable. The cost of DATEDIFF should be relatively light but if
performance is a concern then you should test it out with your typical
data-set.
David Portas
SQL Server MVP
--sql

how could I store images in a database?!!

hi everybody,can anybody help me out by telling me how could I store images in a database, and how to insert new images at run time to a database and thanks

If you have C# .NET (Express, and probably other versions too), it comes with a "Movie Collection Starter Kit" example which has a button that loads an image into the database. Just go to File->New Project, and select the "Movie Collection Starter Kit" template. Open ListDetail.cs, and find the ImportImage method.

How come this dont work?

Ok I am new to this command, BULK INSERT, I cant seem to get this to work or
error out for me.
Heres what I have;
BULK INSERT TBL_Master_DoNotCallList_Loader
FROM 'E:\DE_210652006.txt'
WITH
(
FIELDTERMINATOR = '~'
)
I am running this thru QA logged in as the Admin.
The file is in the location and the date looks like so, in Notepad.
302,2066707~302,2074122~302,2076346~302,2077647~302,2094465~
~ is the field and row terminator.
When I run above cmd I get it telling me it ran successfully.
But theres no data in table and it runs instantly.
Any help would be appreciated.
Thanks
Deasun
--
Deasun
Home Site: www.tirnaog.com
Check out: The Code Vault in my forums section.What is the definition of the table. Also, you didn't specify a row separator. Perhaps the table has
only one columns and you really want ~ as the row terminator?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Deasun" <Deasun@.discussions.microsoft.com> wrote in message
news:BCA35F53-B895-4F6A-9435-8AA493BA3338@.microsoft.com...
> Ok I am new to this command, BULK INSERT, I cant seem to get this to work or
> error out for me.
> Heres what I have;
> BULK INSERT TBL_Master_DoNotCallList_Loader
> FROM 'E:\DE_210652006.txt'
> WITH
> (
> FIELDTERMINATOR = '~'
> )
> I am running this thru QA logged in as the Admin.
> The file is in the location and the date looks like so, in Notepad.
> 302,2066707~302,2074122~302,2076346~302,2077647~302,2094465~
> ~ is the field and row terminator.
> When I run above cmd I get it telling me it ran successfully.
> But theres no data in table and it runs instantly.
> Any help would be appreciated.
> Thanks
> Deasun
> --
> Deasun
> Home Site: www.tirnaog.com
> Check out: The Code Vault in my forums section.|||"Deasun" wrote:
> Heres what I have;
> BULK INSERT TBL_Master_DoNotCallList_Loader
> FROM 'E:\DE_210652006.txt'
> WITH
> (
> FIELDTERMINATOR = '~'
> )
That should be:
BULK INSERT TBL_Master_DoNotCallList_Loader
FROM 'E:\DE_210652006.txt'
WITH
(
ROWTERMINATOR = '~'
)
ROWTERMINATOR <> FIELDTERMINATOR. You might need to specify both, but you
get the idea.
Maury|||Thanks to you both for the reply.
I had specified both row and field terminators at one point neither worked.
We now think it has something to do with the SQL server itself.
We have tried the cmd on another server and it worked fine. :)
Thanks again.
--
Deasun
Home Site: www.tirnaog.com
Check out: The Code Vault in my forums section.
"Maury Markowitz" wrote:
> "Deasun" wrote:
> > Heres what I have;
> > BULK INSERT TBL_Master_DoNotCallList_Loader
> > FROM 'E:\DE_210652006.txt'
> > WITH
> > (
> > FIELDTERMINATOR = '~'
> > )
> That should be:
> BULK INSERT TBL_Master_DoNotCallList_Loader
> FROM 'E:\DE_210652006.txt'
> WITH
> (
> ROWTERMINATOR = '~'
> )
> ROWTERMINATOR <> FIELDTERMINATOR. You might need to specify both, but you
> get the idea.
> Maury
>sql

How come my database do not have a record id?

this is my first time used M.Access, so i not very sure is it Access do not have a record id that it will auto generate itself one when i insert a record? if i cannot find.. what is the way that i can enable it?How do i extract the record id of my access records in java?

How can you use a variable tablename and retrieve the output from the Insert?

We are trying to create a unique key from a table with indentity set in the table. We will have a number of these tables. Therefore, we will be creating a stored procedure and passing the table as a parameter. In this example we are setting the table.

When we run the the script, the output clause from the insert should give us a unique number from the given table in the temporary table. This example stores the output in a temporary table @.tTemp.

How can you use a variable table name and retrieve the output from the Insert?

declare @.tTestTable varchar (20)

set @.tTestTable = 'mis.test_sequence'

--DECLARE @.tTestTable TABLE ( sqVal [int] IDENTITY(1,1) NOT NULL, add_date datetime)

declare @.testsql varchar (4000), @.testseq int

DECLARE @.tTemp table (mainpk int)

set @.testsql = 'DECLARE @.tTemp table (mainpk int) INSERT ' + @.tTestTable + ' OUTPUT INSERTED.sqVal into @.tTemp VALUES (getdate() ) SELECT @.testseq=mainpk FROM @.tTemp'

select @.testsql

EXECUTE sp_executesql @.testsql, N'@.testseq int output,@.tTemp table (mainpk int),@.tTemp table (mainpk int) ',@.tTemp,@.tTemp,@.testseq output,@.tTemp

SELECT * FROM @.tTemp

Please help

Thanks Tim.

Why not to create an sp per each table, instead trying to come with a general one?

Code Snippet

use tempdb

go

create table #t (c1 int not null identity, c2 datetime)

declare @.tTestTable varchar (20)

set @.tTestTable = '#t'

declare @.testsql nvarchar (4000), @.testseq int

set @.testsql = 'INSERT into' + quotename(@.tTestTable) + '(c2) values(getdate()); set @.testseq = scope_identity()'

select @.testsql

EXECUTE sp_executesql @.testsql, N'@.testseq int output',@.testseq output

SELECT @.testseq

drop table #t

The Curse and Blessings of Dynamic SQL

http://www.sommarskog.se/dynamic_sql.html

AMB|||

Thankyou hunchback,

Your Code Snippet helped me solve my problem.

Tim.

Here's my final code.

USE [TestDB]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

Create proc GetNext (@.sequenceName varchar(40) , @.nextVal int output)

as

begin

declare @.sqlStmt nvarchar (4000)

declare @.tTemp table (mainpk int)

set nocount on

-- This option works using the OUTPUT clause.

set @.sqlStmt = 'DECLARE @.tTemp table (mainpk int) Insert ' + @.sequenceName + ' OUTPUT INSERTED.sqVal into @.tTemp DEFAULT VALUES SELECT @.nextVal=mainpk FROM @.tTemp'

execute sp_executesql @.sqlStmt, N'@.nextVal int output',@.nextVal output

set nocount off

end

How can you tell what the primary key of a new row will be?

I need to insert a row into a table in SQL Server 2000. The primary
key for the row is an identity type, so it auto-numbers for me without
needing to put in the value in the insert statement.

My problem, is that after i insert a row, i need to insert another row
in a different table that references the first row. To do that i need
to know the primary key for the original row.

How can i tell what the primary key was? In Oracle, you would check
the sequence before the original insert. Is there a similar feature
in SQL Server? And how would you use it?

(I'm using C# ADO)

- PaulOn 7 Jan 2004 17:26:13 -0800, prempel@.paradata.com (Paul) wrote:

>I need to insert a row into a table in SQL Server 2000. The primary
>key for the row is an identity type, so it auto-numbers for me without
>needing to put in the value in the insert statement.
>My problem, is that after i insert a row, i need to insert another row
>in a different table that references the first row. To do that i need
>to know the primary key for the original row.
>How can i tell what the primary key was? In Oracle, you would check
>the sequence before the original insert. Is there a similar feature
>in SQL Server? And how would you use it?
>(I'm using C# ADO)
>- Paul

In SQL Server, you check the identity value immediately following the INSERT.
The old way to do this was to check the @.@.IDENTITY variable, but that's
unrelibale if a trigger also inserts a row into another table, so the new,
preferred way to do it is the SCOPE_IDENTITY function.|||Paul wrote:

> I need to insert a row into a table in SQL Server 2000. The primary
> key for the row is an identity type, so it auto-numbers for me without
> needing to put in the value in the insert statement.
> My problem, is that after i insert a row, i need to insert another row
> in a different table that references the first row. To do that i need
> to know the primary key for the original row.
> How can i tell what the primary key was? In Oracle, you would check
> the sequence before the original insert. Is there a similar feature
> in SQL Server? And how would you use it?
> (I'm using C# ADO)
> - Paul

I'll leave it to someone that knows more about SQL Server than I to
answer your question. But what you suggest for Oracle doesn't work in
Oracle. Well unless you are in a single-user environment. The solution
in Oracle would be to use the RETURNING clause of the INSERT statement
as in:

DECLARE
x emp.empno%TYPE;
BEGIN
INSERT INTO emp
(empno, ename)
VALUES
(seq_emp.NEXTVAL, 'Morgan')
RETURNING empno
INTO x;

dbms_output.put_line(x);
END;
/

--
Daniel Morgan
http://www.outreach.washington.edu/...oad/oad_crs.asp
http://www.outreach.washington.edu/...aoa/aoa_crs.asp
damorgan@.x.washington.edu
(replace 'x' with a 'u' to reply)

How can permissions of multiple tables be changed at once?

A user database has a lot tables. How can I change all the tables to have
the same permissions (e.g. SELECT, INSERT, etc.) at once rather than manually
do it by going through table by table?
Thanks in advance for any help,
Bing
You really can't. You could write some scripts to do the work, but by the
time you finished doing that, you would probably have spent more time than
doing it by hand.
You should create roles however and add your users to those roles and then
apply permissions to the roles.
There are already a few roles pre-created for you that can help you with
this. For example, db_datareader and db_datawriter.
That should give you your basic INSERT, SELECT etc. permissions on the bulk
of your database objects.
HTH
Rick Sawtell
MCT, MCSD, MCDBA
"bing" <bing@.discussions.microsoft.com> wrote in message
news:908EE4BF-CF98-46CD-A543-36634FA87DE7@.microsoft.com...
> A user database has a lot tables. How can I change all the tables to have
> the same permissions (e.g. SELECT, INSERT, etc.) at once rather than
manually
> do it by going through table by table?
> Thanks in advance for any help,
> Bing
|||Below is a sample script you can customize and run to apply mass
permissions.
SET NOCOUNT ON
DECLARE @.GrantStatement nvarchar(4000)
DECLARE GrantStatements CURSOR
LOCAL FAST_FORWARD READ_ONLY FOR
SELECT
N'GRANT SELECT ON ' +
QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME) +
N' TO MyRole'
FROM INFORMATION_SCHEMA.TABLES
WHERE
OBJECTPROPERTY(
OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)),
'IsMSShipped') = 0 AND
TABLE_TYPE = 'BASE TABLE'
OPEN GrantStatements
WHILE 1 = 1
BEGIN
FETCH NEXT FROM GrantStatements
INTO @.GrantStatement
IF @.@.FETCH_STATUS = -1 BREAK
BEGIN
RAISERROR (@.GrantStatement, 0, 1) WITH NOWAIT
EXECUTE sp_ExecuteSQL @.GrantStatement
END
END
CLOSE GrantStatements
DEALLOCATE GrantStatements
Hope this helps.
Dan Guzman
SQL Server MVP
"bing" <bing@.discussions.microsoft.com> wrote in message
news:908EE4BF-CF98-46CD-A543-36634FA87DE7@.microsoft.com...
>A user database has a lot tables. How can I change all the tables to have
> the same permissions (e.g. SELECT, INSERT, etc.) at once rather than
> manually
> do it by going through table by table?
> Thanks in advance for any help,
> Bing
|||Thanks all who replied. We do use roles to manage users and permissions.
I'll customize and try the script Dan kindly provided.
Bing
"Dan Guzman" wrote:

> Below is a sample script you can customize and run to apply mass
> permissions.
>
> SET NOCOUNT ON
> DECLARE @.GrantStatement nvarchar(4000)
> DECLARE GrantStatements CURSOR
> LOCAL FAST_FORWARD READ_ONLY FOR
> SELECT
> N'GRANT SELECT ON ' +
> QUOTENAME(TABLE_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME) +
> N' TO MyRole'
> FROM INFORMATION_SCHEMA.TABLES
> WHERE
> OBJECTPROPERTY(
> OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME)),
> 'IsMSShipped') = 0 AND
> TABLE_TYPE = 'BASE TABLE'
> OPEN GrantStatements
> WHILE 1 = 1
> BEGIN
> FETCH NEXT FROM GrantStatements
> INTO @.GrantStatement
> IF @.@.FETCH_STATUS = -1 BREAK
> BEGIN
> RAISERROR (@.GrantStatement, 0, 1) WITH NOWAIT
> EXECUTE sp_ExecuteSQL @.GrantStatement
> END
> END
> CLOSE GrantStatements
> DEALLOCATE GrantStatements
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "bing" <bing@.discussions.microsoft.com> wrote in message
> news:908EE4BF-CF98-46CD-A543-36634FA87DE7@.microsoft.com...
>
>

How Can Insert A Data In To This Table?

SALAM SIR,
CREATE TABLE Airlines_Master
( Aircode CHAR(2),
Airlines_name VARCHAR(15))
THIS IS MADE A TABLE IN SQLSERVER 2000.BUT HOW CAN INSERT DATA INTO THIS TABLE?Lookup and read the section on SELECT and INSERT statements in Books Online.|||Lookup and read the section on SELECT and INSERT statements in Books Online.
Well,Batman this post is too much.People asking questions on insert and select ....there sould be some limit..:shocked:
Joydeep|||Well,Batman this post is too much.People asking questions on insert and select ....there sould be some limit..:shocked:
JoydeepEvery time I create something "foolproof", mother nature goes out and creates a better fool. Following that same line of logic, there is no "lower limit" on how basic a question can be... Someone, somewhere, will ask anything you can imagine, and probably several things that you can't imagine too! It is just the nature of the beast, so sit back and enjoy, don't get your knickers in a twist!

-PatP

How can I use the Variables in SQL Execute SQL task?

I define a package variables "varOutTable" and "varFromTable".

and I insert a SQL Execute SQL Task into Control Flow

my sql command is

"Select * into @.[User::varOutTable] from @.[User::varFromTable]"

but the task failed,

it seems that sql task can't get the varOutTable and varFromTable

How can I use the Variables in SQL Execute SQL task?

thanks!!

Use an expression to build your SQL statement. This is:

Open SQL Task editor|||

thank for your reply.

you are right,

it runs great now.

|||Will this work for a select count(*)?|||

agentf1 wrote:

Will this work for a select count(*)?

Why not? A select count(*) would work just like a select * does.... So yes, it would work.|||Just make sure you add an alias to the column (eg. Count(*) as MyCount) and the result set uses the same name (MyCount)....|||I am doing this, my SQL looks like Select count(*) as recordcount from table_name. My variable is set up in ResultSet on the task and is an object.|||Are there any examples of doing a select count(*) into a variable? For some reason when displaying my variable it looks like it contains -1.|||

agentf1 wrote:

Are there any examples of doing a select count(*) into a variable? For some reason when displaying my variable it looks like it contains -1.

That's because your variable is of OBJECT type. If you're returning one row, one value, you can use an integer data type (in this case).|||I am, thanks. I will try this.|||When changed to int32 I get the following error message.

Error: 0xC001F009 at D3OLNAC3: The type of the value being assigned to variable "User::spmessage" 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 Execute SQL Task, Execute SQL Task: Executing the query "select count(*) as recordcount from loan_auj" failed with the following error: "The type of the value being assigned to variable "User::spmessage" 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: Execute SQL Task
Warning: 0x80019002 at D3OLNAC3: 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 "D3OLNAC3.dtsx" finished: Failure.|||

That should work. Make sure ResultSet property is set to single row; the result set page you have a resultsetname RecordCount that points to a SSIS variable and that variable is Int32.

If all this is fine; then post all the details of you Execute SQL task to see other potential causes of error

|||That was it, I needed to change result set to single row. Thanks.

How can I use the Variables in SQL Execute SQL task?

I define a package variables "varOutTable" and "varFromTable".

and I insert a SQL Execute SQL Task into Control Flow

my sql command is

"Select * into @.[User::varOutTable] from @.[User::varFromTable]"

but the task failed,

it seems that sql task can't get the varOutTable and varFromTable

How can I use the Variables in SQL Execute SQL task?

thanks!!

Use an expression to build your SQL statement. This is:

Open SQL Task editor|||

thank for your reply.

you are right,

it runs great now.

|||Will this work for a select count(*)?|||

agentf1 wrote:

Will this work for a select count(*)?

Why not? A select count(*) would work just like a select * does.... So yes, it would work.|||Just make sure you add an alias to the column (eg. Count(*) as MyCount) and the result set uses the same name (MyCount)....|||I am doing this, my SQL looks like Select count(*) as recordcount from table_name. My variable is set up in ResultSet on the task and is an object.|||Are there any examples of doing a select count(*) into a variable? For some reason when displaying my variable it looks like it contains -1.|||

agentf1 wrote:

Are there any examples of doing a select count(*) into a variable? For some reason when displaying my variable it looks like it contains -1.

That's because your variable is of OBJECT type. If you're returning one row, one value, you can use an integer data type (in this case).|||I am, thanks. I will try this.|||When changed to int32 I get the following error message.

Error: 0xC001F009 at D3OLNAC3: The type of the value being assigned to variable "User::spmessage" 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 Execute SQL Task, Execute SQL Task: Executing the query "select count(*) as recordcount from loan_auj" failed with the following error: "The type of the value being assigned to variable "User::spmessage" 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: Execute SQL Task
Warning: 0x80019002 at D3OLNAC3: 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 "D3OLNAC3.dtsx" finished: Failure.|||

That should work. Make sure ResultSet property is set to single row; the result set page you have a resultsetname RecordCount that points to a SSIS variable and that variable is Int32.

If all this is fine; then post all the details of you Execute SQL task to see other potential causes of error

|||That was it, I needed to change result set to single row. Thanks.

How can I update relationship tables?

<----Ihave 2 tables are: 'customers (parent)' and 'open_ac (child)'

<---Ihave tried to insert and update data into sql database by using textboxes(don't use datagrid)

<---Mytables details are below

<---thistable uses for keeping user data

customersfields:

Columnname type length Description

cu_id int 4 Primary key Identifiers

cu_fname nvarchar 20 allow null first name

cu_lname nvarchar 40 allow null last name

cu_nat nvarchar 20 allownull nationality

cu_add nvarchar 40 allow null address

cu_wplace nvarchar 40 allownull workplace

cu_tel nvarchar 10 allownull telephone

cu_fax nvarchar 10 allow null fax

cu_email nvarchar 10 allownull email

<--theopen_ac uses for keeping register date/time of customers

open_acfields:

Columnname type length Description

cu_id int 4 link key

op_date date/time 8 register date

<----mycode

ImportsSystem.Data.SqlClient

Public Class cus_reg
Inherits System.Web.UI.Page
Dim DS As DataSet
Dim iRec As Integer 'Current Record
Dim m_Error As String = ""

Public Property MyError() As String
Get
Returnm_Error
End Get
Set(ByVal Value As String)
m_Error =Value
IfTrim(Value) = "" Then
Label3.Visible = False
Else
Label3.Text = Value
Label3.Visible = True
End If
End Set
End Property

Private Sub Page_Load(ByVal sender As System.Object,ByVal e As System.EventArgs) Handles MyBase.Load
If Not Page.IsPostBack Then
Dim C1 AsNew MISSQL
'DS =C1.GetDataset("select * from customers;select * from open_ac;select * fromaccounts")
DS =C1.GetDataset("select * from customers;select * from open_ac")

Session("data") = DS
iRec = 0
Viewstate("iRec") = iRec
Me.MyDataBind()

Dim Dtr AsDataRow = DS.Tables(0).NewRow
DS.Tables(0).Rows.Add(Dtr)
iRec =DS.Tables(0).Rows.Count - 1
viewstate("iRec") = iRec
Me.Label2.Text = DateTime.Now
Me.MyDataBind()
Else
DS =Session("data")
iRec =ViewState("iRec")
End If
Me.MyError = ""
End Sub

Public Function BindField(ByVal FieldName As String) AsString
Dim DT As DataTable = DS.Tables(0)
Return DT.Rows(iRec)(FieldName)& ""
End Function
Public Sub MyDataBind()
Label1.Text = "Record : "& iRec + 1 & " of " & DS.Tables(0).Rows.Count
txtcu_id.DataBind()
txtcu_fname.DataBind()
txtcu_lname.DataBind()
txtcu_add.DataBind()
txtcu_occ.DataBind()
txtcu_wplace.DataBind()
txtcu_nat.DataBind()
txtcu_tel.DataBind()
txtcu_fax.DataBind()
txtcu_email.DataBind()
End Sub

Here isupdate code

Private Sub bUpdate_Click(ByVal sender As System.Object,ByVal e As System.EventArgs) Handles bUpdate.Click
Dim DT As DataTable = DS.Tables(0)
Dim DR As DataRow = DT.Rows(iRec)
'Dim DR1 As DataRow = DT1.Rows(iRec)

If DR.RowState = DataRowState.AddedThen
Iftxtcu_id.Text.Trim = "" Then
Me.MyError = "please enter your id"
Exit Sub
End If
DR("cu_id")= txtcu_id.Text
End If

If txtcu_fname.Text.Trim ="" Then
Me.MyError ="please enter your name"
Exit Sub
Else
DR("cu_fname") = txtcu_fname.Text
End If

If txtcu_lname.Text.Trim ="" Then
Me.MyError ="please enter your last name"
Exit Sub
Else
DR("cu_lname") = txtcu_lname.Text
End If

If txtcu_add.Text.Trim ="" Then
Me.MyError ="please enter your address"
Exit Sub
Else
DR("cu_add") = txtcu_add.Text
End If

If txtcu_occ.Text.Trim ="" Then
Me.MyError ="please enter your occupation"
Exit Sub
Else
DR("cu_occ") = txtcu_occ.Text
End If

If txtcu_wplace.Text.Trim ="" Then
Me.MyError ="please enter your workplace"
Exit Sub
Else
DR("cu_wplace") = txtcu_wplace.Text
End If

If txtcu_nat.Text.Trim ="" Then
Me.MyError ="Please enter your nationality"
Exit Sub
Else
DR("cu_nat") = txtcu_nat.Text
End If

If txtcu_tel.Text.Trim ="" Then
DR("cu_tel") = DBNull.Value
Else
DR("cu_tel") = txtcu_tel.Text
End If

If txtcu_tel.Text.Trim ="" Then
DR("cu_fax") = DBNull.Value
Else
DR("cu_fax") = txtcu_fax.Text
End If

If txtcu_email.Text.Trim ="" Then
DR("cu_email") = DBNull.Value
Else
DR("cu_email") = txtcu_email.Text
End If

Dim Strsql As String
If DR.RowState = DataRowState.AddedThen
Strsql ="insert into customers (cu_id,cu_fname,cu_lname,cu_add,cu_occ,cu_wplace,cu_nat,cu_tel,cu_fax,cu_email)values (@.P1,@.P2,@.P3,@.P4,@.P5,@.P6,@.P7,@.P8,@.P9,@.P10)"
Else
Strsql ="update customers setcu_fname=@.P2,cu_lname=@.P3,cu_add=@.P4,cu_occ=@.P5,cu_wplace=@.P6,cu_nat=@.P7,cu_tel=@.P8,cu_fax=@.P9,cu_email=@.P10where cu_id =@.P1"
End If
Dim C1 As New MISSQL
Dim cmd As SqlCommand =C1.CreateCommand(Strsql)
C1.CreateParam(cmd,"ITTTTTTTTT")
cmd.Parameters("@.P1").Value = DR("cu_id")
cmd.Parameters("@.P2").Value= DR("cu_fname")
cmd.Parameters("@.P3").Value = DR("cu_lname")
cmd.Parameters("@.P4").Value = DR("cu_add")
cmd.Parameters("@.P5").Value = DR("cu_occ")
cmd.Parameters("@.P6").Value = DR("cu_wplace")
cmd.Parameters("@.P7").Value= DR("cu_nat")
cmd.Parameters("@.P8").Value = DR("cu_tel")
cmd.Parameters("@.P9").Value = DR("cu_fax")
cmd.Parameters("@.P10").Value = DR("cu_email")

Dim Y As Integer = C1.Execute(cmd)
If Y > 0 Then
DR.AcceptChanges()
Else
Me.MyError ="Can not register"
End If

<---code above in this sub it can update only customers tables and when I tried to coding below

<----it alerts can not update

Dim DT1 As DataTable = DS.Tables(1)
Dim DR1 As DataRow = DT1.Rows(iRec)
If DR1.RowState = DataRowState.AddedThen
Iftxtcu_id.Text.Trim = "" Then
Me.MyError = "Please enter id"
Exit Sub
End If
DR1("cu_id")= txtcu_id.Text
End If
If Label2.Text.Trim = ""Then
DR1("op_date") = Label2.Text
End If

Dim StrSql1 As String
If DR1.RowState =DataRowState.Deleted Then
StrSql1 ="insert into open_ac (cu_id,op_date) values (@.P13,@.P14)"
Else
StrSql1 ="update open_ac set op_date=@.P14 where cu_id=@.P13"
End If
Dim C2 As New MISSQL
Dim cmd2 As SqlCommand =C2.CreateCommand(StrSql1)
C2.CreateParam(cmd2, "ID")
cmd2.Parameters("@.P1").Value = DR1("cu_id")
cmd2.Parameters("@.P2").Value = DR1("op_date")

Dim Y1 As Integer = C2.Execute(cmd2)
If Y1 > 0 Then
DR1.AcceptChanges()
Else
Me.MyError ="Can not register"
End If
End Sub
End Class

<--thisis class I use for connecting to database and call parameters...

MISSQLclass

ImportsSystem.Data.SqlClient
Public Class MISSQL
Dim PV As String ="Server=web_proj;uid=sa;pwd=sqldb;"
Dim m_Database As String = "c1_itc"
Public Strcon As String
Public Sub New()
Strcon = PV &"database=" & m_Database
End Sub
Public Sub New(ByVal DBName As String)
m_Database = DBName
Strcon = PV &"database=" & m_Database
End Sub
Public Property Database() As String
Get
Returnm_Database
End Get
Set(ByVal Value As String)
m_Database =Value
Strcon = PV& "database=" & m_Database
End Set
End Property

Public Function GetDataset(ByVal Strsql As String, _
Optional ByVal DatasetName As String= "Dataset1", _
Optional ByVal TableName As String ="Table") As DataSet
Dim DA As New SqlDataAdapter(Strsql,Strcon)
Dim DS As New DataSet(DatasetName)
Try
DA.Fill(DS,TableName)
Catch x1 As Exception
Err.Raise(60002, , x1.Message)
End Try
Return DS
End Function

Public Function GetDataTable(ByVal Strsql As String, _
Optional ByVal TableName AsString = "Table") As DataTable
Dim DA As New SqlDataAdapter(Strsql,Strcon)
Dim DT As New DataTable(TableName)
Try
DA.Fill(DT)
Catch x1 As Exception
Err.Raise(60002, , x1.Message)
End Try
Return DT
End Function

Public Function CreateCommand(ByVal Strsql As String) AsSqlCommand
Dim cmd As New SqlCommand(Strsql)
Return cmd
End Function

Public Function Execute(ByVal Strsql As String) AsInteger
Dim cmd As New SqlCommand(Strsql)
Dim X As Integer = Me.Execute(cmd)
Return X
End Function

Public Function Execute(ByRef Cmd As SqlCommand) AsInteger
Dim Cn As New SqlConnection(Strcon)
Cmd.Connection = Cn
Dim X As Integer
Try
Cn.Open()
X =Cmd.ExecuteNonQuery()
Catch
X = -1
Finally
Cn.Close()
End Try
Return X
End Function

Public Sub CreateParam(ByRef Cmd As SqlCommand, ByValStrType As String)
'T:Text, M:Memo, Y:Currency,D:Datetime, I:Integer, S:Single, B:Boolean, P: Picture
Dim i As Integer
Dim j As String
For i = 1 To Len(StrType)
j =UCase(Mid(StrType, i, 1))
Dim P1 AsNew SqlParameter
P1.ParameterName = "@.P" & i
Select Casej
Case "T"
P1.SqlDbType = SqlDbType.NVarChar
Case "M"
P1.SqlDbType = SqlDbType.Text
Case "Y"
P1.SqlDbType = SqlDbType.Money
Case "D"
P1.SqlDbType = SqlDbType.DateTime
Case "I"
P1.SqlDbType = SqlDbType.Int
Case "S"
P1.SqlDbType = SqlDbType.Decimal
Case "B"
P1.SqlDbType = SqlDbType.Bit
Case "P"
P1.SqlDbType = SqlDbType.Image
End Select
Cmd.Parameters.Add(P1)
Next
End Sub
End Class

<---Thank you in advance

<---and Thank you very much for all help

Hi,

Try the following code :(one part of your code)

Dim DT1As DataTable = DS.Tables(1)
Dim DR1As DataRow = DT1.Rows(iRec)
If DR1.RowState = DataRowState.AddedThen
If txtcu_id.Text.Trim =""Then
Me.MyError ="Please enter id"
Exit Sub
End If
DR1("cu_id") = txtcu_id.Text
End If

////If Label2.Text.Trim ="" Then
//// DR1("op_date") = Label2.Text
////End If

If Label2.Text.Trim <>""Then
DR1("op_date") = Label2.Text
End If

Dim StrSql1As String
////If DR1.RowState = DataRowState.Deleted Then
If DR1.RowState = DataRowState.AddedThen
StrSql1 ="insert into open_ac (cu_id,op_date) values (@.P13,@.P14)"
Else
StrSql1 ="update open_ac set op_date=@.P14 where cu_id=@.P13"
End If
Dim C2As New MISSQL
Dim cmd2As SqlCommand = C2.CreateCommand(StrSql1)
C2.CreateParam(cmd2,"ID")
cmd2.Parameters("@.P1").Value = DR1("cu_id")
cmd2.Parameters("@.P2").Value = DR1("op_date")

Dim Y1As Integer = C2.Execute(cmd2)
If Y1 > 0Then
DR1.AcceptChanges()
Else
Me.MyError ="Can not register"
End If

Thanks.

|||

So thank you very much for help for coding.

I tried to follow your code below.

>Dim DT1As DataTable = DS.Tables(1)
> Dim DR1As DataRow = DT1.Rows(iRec)
> If DR1.RowState = DataRowState.AddedThen
> If txtcu_id.Text.Trim =""Then
> Me.MyError ="Please enter id"
> Exit Sub
> End If
> DR1("cu_id") = txtcu_id.Text
> End If////If Label2.Text.Trim ="" Then
> //// DR1("op_date") = Label2.Text
> ////End If

> If Label2.Text.Trim <>""Then
> DR1("op_date") = Label2.Text
> End If

> Dim StrSql1As String
> ////If DR1.RowState = DataRowState.Deleted Then
> If DR1.RowState = DataRowState.AddedThen
> StrSql1 ="insert into open_ac (cu_id,op_date) values (@.P13,@.P14)"
> Else
> StrSql1 ="update open_ac set op_date=@.P14 where cu_id=@.P13"
> End If
> Dim C2As New MISSQL
> Dim cmd2As SqlCommand = C2.CreateCommand(StrSql1)
> C2.CreateParam(cmd2,"ID")
> cmd2.Parameters("@.P1").Value = DR1("cu_id")
> cmd2.Parameters("@.P2").Value = DR1("op_date")

> Dim Y1As Integer = C2.Execute(cmd2)
> If Y1 > 0Then
> DR1.AcceptChanges()
> Else
> Me.MyError ="Can not register"
> End If

But still show "Can not register" I don't know why?

and I try another way by follow your code and editing something below this can work. But I don't know what I did wrong above

Dim DT1 As DataTable = DS.Tables(1)
Dim DR1 As DataRow = DT1.Rows(iRec)
If DR1.RowState = DataRowState.Added Then
DR1("cu_id") = txtcu_id.Text
End If

'////If Label2.Text.Trim = "" Then
' //// DR1("op_date") = Label2.Text
' ////End If

If Label2.Text.Trim <> "" Then
DR1("op_date") = Label2.Text
End If

Dim StrSql1 As String
'////If DR1.RowState = DataRowState.Deleted Then
If DR1.RowState = DataRowState.Added Then
StrSql1 = "insert into open_ac (cu_id,op_date) values (@.P_C1,@.P_C2)"
Else
StrSql1 = "update open_ac set op_date=@.P_C2 where cu_id=@.P_C1"
End If
Dim C2 As New MISSQL
Dim cmd_child As SqlCommand = C2.CreateCommand(StrSql1)
C2.CreateParam_child(cmd_child, "TD")
cmd_child.Parameters("@.P_C1").Value = DR1("cu_id")
cmd_child.Parameters("@.P_C2").Value = DR1("op_date")
Dim Y1 As Integer = C2.Execute(cmd_child)
If Y1 > 0 Then
DR1.AcceptChanges()
End If
End Sub

Thank you very very much for your help

How can i treate Huge DB size in sql server 2000

Hi all,

I have DB in operation its MDF size reached 8.38 GB and the system that work on the queries of insert timeout and the operation failed and many problems happens ...

actually the reason of the huge size of the DB is just one table that contain image field which we store word files in it in each row in the table .........

so how can i solve this problem without affecting the structure of the DB ..... coz we don't wanna to make code changes in the application that use this DB

thanks

if it 8.38 GB.. this can not be considered as a "Huge" DB from SQL Server point of view ... SQL Server handles much much larger databases... this is basically... architecture flaw... when u store word file in database it is bound to have some performance issue... its again if you have sufficient Hardware resources u can do that... you could have store the path of word file instead of file itself... anyhow, if u don't want to change the architecture... you can move the table which stores word file to a different Filegroup/disk ... or archive the unwanted data… add more hardware… etc.. etc…

Madhu

|||

I hear about the posibility of partioning the huge size table into physical partitions with out affect the logical structure so the performance of accessing the table will be faster ......

so if this process can be done on sql server 2000 on already exist DB how can it be done ...

i need an article about doing that .........

|||

If you are refering to partition of tables its supported in SQL 2005. This article might help you.

http://msdn2.microsoft.com/en-us/library/ms190787.aspx

Thanks,

|||

What about making partionning in sql server 2000?

|||

there are many article available... google it...

i think this will give some hints

http://www.microsoft.com/technet/prodtechnol/sql/2000/reskit/part10/c3861.mspx?mfr=true

regards

Madhu

|||You can create filegroups in SQL Server 2000. 8+GB is not a huge database as I have been handling databases more than 100GB and SQL Server is still functioning well. If your application is the one timing out, you have to revisit your application design and your codes as well as your database structure.|||When was the last time the database has been checked for consistency and redinexed for stats update?|||

Let me honest with that never happen ......

so .... is there any suggestions?!

|||

use DBCC DBREINDEX to reindex all the tables and use sp_updatestats to update all the table statistics and see the performance

also use sp_spaceused @.updateusage = 'TRUE' to get the space used by the data...

Madhu