Store Current Identity In A Variable

Apr 30, 2007

Hi there, I should prefix this message by saying, I am brand new to SQL so please be gentle

What I am doing seems pretty standard and I'd like some advice on the best way to proceed.

I am simply trying to add some customer information to my database. For this I have set up two tables. One for the Customer name, title, etc and then a related table for the address which is referenced in the previous table by a AddressID.

In the address table the AddressID uses an Identity. So what I'd like to do (which may not be the best way, if so tell me) is write a stored procedure to accept all of the customer information then INSERT a new entry into the Addresses table, then use DBCC to retrieve the current IDENTITY value and then add the remaining customer info into the Customers table using the IDENTITY value retrieved for the AddressID.

Now is that a total bass-ackwards way of doing this?

Thanks for the help!

View 3 Replies


ADVERTISEMENT

Scripting: Dumb Q.. How Can I Store A DTS Variable Inside A Script Variable?

Nov 1, 2005

Hi

View 7 Replies View Related

Get Current Identity(Procedure)

Jul 20, 2005

Hi everyone,How can I retrieve the current identity used in a procedure from atrigger.What I am trying to do is:The procedure takes a @username (nvarchar) as a parameter, and theprocedure identifies the @username's identity and tries to delete theidentity's row. An Instead Of trigger fires, and updates the specificrow.But how can the trigger identify the targeted row?Which built-in function can I use?Thanksgardmt

View 3 Replies View Related

Identity Current Qeustion

Apr 16, 2007

my proc doesnt workselect ident_current("table1") as img
select ident_current("table2")as art

insert into table3(ImageID,ArticalCode)
values (img,art) how come?







the error i get is:

The name "img" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted

View 4 Replies View Related

HttpContext.Current.User.Identity.Name Blank ?

Mar 2, 2007

I have implemented the forms authentication method using  MSDN web site http://msdn2.microsoft.com/en-us/library/ms160724.aspx

and now i want to modify that code

so when i used

Dim UserName as string =HttpContext.Current.User.Identity.Name

from the vb.net code behind

it gives me ss as blank value ?

but the same code i used from the normal web site its gives me the

"SER1Administrator" value for that string(current user)

so my problem is why is that code not working for the report server sample project ,which microsoft provided ?

do i have to change any configuration settings ?

please help me .......i am in trouble ?

(what i really want is i need to get the username from the active page directory? wheather this username exist from the active page directory.)

 

regards

sujithf

 

 

View 1 Replies View Related

HttpContext.Current.User.Identity.Name Form SQLDataSource

Apr 21, 2007

Hi,How do I get hold of the HttpContext.Current.User.Identity.Name from within an sqldatasource? I want to create a WHERE clause that says WHERE UserName = @UserName and the @UserName parameter be filled with the current user name. I would be able to do this in code, but I don't know how to get at this information from within a datasource. At a guess I tried to use the Cookie option from the drop down list, and use the name of the FormsAuthentication Cookie, but this didn't work. Is there a way I can do this? If so how? I don't want to resort to using a hidden field or anything like that if I can help it. Many thanks, Steve 

View 2 Replies View Related

How Can I Get The Name Of The Current Database In Variable PROCEDURE

May 22, 2008

i need to know the name of the current database in variable
like "Northwind"
so i can use it inside PROCEDURE

tnx

View 5 Replies View Related

How To Insert A Identity Key Through Store Procudure?

Apr 25, 2007

I want to insert 3 values to sql database through store procudure
 
--- a store procudure is like this--
 ALTER PROCEDURE dbo.FreeExperience
@identity ,
@name nvarchar(20),@gender int,
AS         begin           Insert into list(cid,name,gender)                       values (@identity,@name,@gender)
        end RETURN
-----------------------------------------------------------
the cid is a identity value . it will +1 automatically when user insert a new data..
if i ingore this column in store procudure it will cause error (becuase this column is not allow null)
please help. thanks

View 11 Replies View Related

How To Store ExecuteScalar Value Into Variable ?

Oct 16, 2006

I having a strange problem, my code is as below: cmd.connection = cnncmd.commandtext = "select count(*) from member" dim i as integeri = cInt(cmd.executescalar) However, what the result tat i get is "&H0" ! When I use the same query in sqlquery, it did show out the result as "11".I have no idea abt wat is goin on, can anyone gv me some guide ?  Thanks.

View 2 Replies View Related

Using A Variable To Store A Table

Aug 9, 2007

Hi,

I have a question. I have a standard query that retrieves the list of users from a database. The query uses "exist" statement and inside it I have another smaller query. The smaller query which used withing exist statement is called numerous times during the query execution. I am wondering if I can speed things up by storing result of a smaller query in a table rather than asking DB to run it again and again. The query takes over a minute to run and I am trying to find a way of speeding it up.

Thanks a lot

Serge

View 2 Replies View Related

Store The Output In To A Variable

Mar 26, 2008

This is my stored procedure .It is working fine .I want to capture the output in a print statement .Can anyone help me please .




alter proc r (@id INT)

as

BEGIN

DECLARE @input VARCHAR(800)

DECLARE @c_input INT

DECLARE @i_Input INT

DECLARE @input_left VARCHAR(800)

DECLARE @delimiter CHAR(1)

select @delimiter = ','

DECLARE @in VARCHAR(800)

DECLARE @list VARCHAR(800)

declare @list2 VARCHAR(800)



SET @input = 'AWCA,GCS,IHP,Aetna'

select @c_input = (select dbo.Fx_CharCount(@delimiter,@input))

set @c_input = @c_input + 1

while @c_input > 0

BEGIN

select @i_input = charindex(@Delimiter,@input)

if @i_input != 0

BEGIN

select @input_left = left(@input, @i_input - 1)

END

else

select @input_left = @input

select @in = '''' + @input_left + ''''

select @list = ISNULL(@list + ',', '') + @in

select @input = right(@input ,(len(@input) - @i_input))

SET @c_input = @c_input -1

if @c_input = 0 or @input = @input_left

break

end

Print @list

EXECUTE ('SELECT Label FROM repricingsystemtype WHERE Label Not IN (' + @list + ')')

END



View 5 Replies View Related

Store Column Value In A Variable

Oct 1, 2007

Hi,

I am transferring data from msaccess to sql2005 using foreach loop. I have a table called Students and a column in it called State.

I want to store the value of the State in a variable in the foreach loop. I am using Dataflowtask to transfer rows from access to sql. what component can i use inside dft to store the value of State in a variable.

Plz its urgent

thanku.

View 5 Replies View Related

How To Store The Results Of A Query In A Variable.

Jun 25, 2007

Can anyone tell me or point me in the direction of how I can store select query results to a variable in VB.NET? Im using the SqlDataSource control with dropdowns and textboxes for searching. I want to store the search results in a variable on the button click event.

View 2 Replies View Related

Store FOR AXL AUTO In A Column Or Variable

Jun 22, 2004

Here is my objective:

As part of auditing of our application, I want to write the values of all column in one row out to an audit table both before and after an update to see what the changes were.

I know I can SELECT * FROM tableName FOR XML AUTO to generate a single XML 'column', but I cannot figure out how to capture that output and store it.

According to MS, you cannot use FOR XML in a subselect, or fill a TEXT variable with the results of such (which, IMHO really diminishes the usefulness).

I want to stream out the results of one entire row and store that in ONE column of an audit table. XML seems like the easiest way if not for this limitation.

A better way?

Thanks.

View 5 Replies View Related

How To Store Image Datatype Value To A Variable?

Aug 29, 2005

I have image type col.I'm trying to do the following,DECLARE @Data varbinary(16)SET @Data = (select imageCol from Table1 where id=3)As image datatype returns varbinary value, so I want to store image col value to a varbinary variable(or any other type variable, eg., varchar). But getting following error,========================================Server: Msg 279, Level 16, State 3, Line 2The text, ntext, and image data types are invalid in this subquery or aggregate expression.========================================Is there anyway to store image datatype value to a variable?Cheers.

View 2 Replies View Related

Variable To Store String For Where Clause

Mar 26, 2001

I want to declare a variable that will serve as my where clause. The variable will be passed in from our website. Does anyone have any information or can guide me in the right direction to do this?

example:

@variable varchar(1000)

SELECT *
FROM table
WHERE @variable

(The @variable would reflect the where clause string)


Thanks!

View 1 Replies View Related

Can Store A List Of Values In A Variable?

Apr 16, 2014

I'm looking for a way to store a list of values in a variable. The query user will need to input a list of file numbers, and my query will need to perform a couple operations on that same list of values, which is why it seems a variable would be most appropriate.

I can't obtain the list of values from the database as they will have to be entered by the user. I'm imagining storing these in a table variable.... User just copies/pastes the list of values somewhere into the query code and executes as usual.

View 7 Replies View Related

Store SQL Task Result As Variable

Mar 18, 2008



Help - I am having a moment!

I am building a simple package that looks at the results of a sql query, prior to running the next step.

Basically Outline and settings


SQL task


Result Set: Full result set

SQL Source: Direct Input

SQL Statement: Select Count (*) FROM X

Parameter Mapping


Variable Name: User::C

Direction: Return Value

Data Type: Numeric

Parameter Name: NewParameterName

Result Set = NewResultName: User::C

Precedence Constraint


Evaluation Operation: Expression and Constraint

Value: Success

Expression: @C > 0 ( I originally tried User::C and received an error message)

Execute SQL Server Agent Job


If step one results greater than 1, then execute the SQL agent job (SRS Report)
Using this setup I receive an error message stating "

TITLE: Package Validation Error
------------------------------
Package Validation Error
------------------------------
ADDITIONAL INFORMATION:
Error at Package: The expression "@C > 0" must evaluate to True or False. Change the expression to evaluate to a Boolean value.
Error at Package: There was an error in the precedence constraint between "Execute SQL Task" and "Execute SQL Server Agent Job Task".
(Microsoft.DataTransformationServices.VsIntegration)


Any thought or suggestions would be greatly appreciated.

r/ Anthony

View 7 Replies View Related

Store Connection String In Variable

May 10, 2007

Hi,



Does anyone know a way to store the ConnectionString of an OLEDB connection in a variable? I want to see exactly what values are in there because the step is failing with an error at that point. Here is what I'm looking for:



Data Source=;Initial Catalog=;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;





Thanks,

Phil

View 5 Replies View Related

How To Assign The @@IDENTITY To A Variable

Feb 24, 2005

Hi,

HOW can I assign the value of @@IDENTITY to the any variable in SQL SERVER .

Thanks,

View 1 Replies View Related

Use Of Variable In Identity Function !

Jun 4, 2008

declare @number int

set @number = 100

SELECT emp_id AS emp_num,
fname AS first,
minit AS middle,
lname AS last,
IDENTITY(int, @number, 1) AS job_num,
job_lvl AS job_level,
pub_id,
hire_date
INTO employees
FROM employee


Is there any way i can use variable inside the identity function like in the above example?.

Is there any other alternative?

Thanks in advance

View 6 Replies View Related

Store Count Result In A Local Variable

Feb 10, 2000

Hi All,
I need to store the row count from two different servers (one 6.5 and one 7.0)
to compare by doing this:(in T-SQL)

declare @kount1 int, @kount2 int
exec master..xp_cmdshell 'isql -Sserver65 -d dbA -T -Q "select count(*) from tableA",no_output
exec master..xp_cmdshell 'isql -Sserver70 -d dbA -T -Q "select count(*) from tableA",no_output

How can I save the rowcount in @kount1, @kount2 respectively for comparison?
Appreciate your feedback.
David Nguyen

View 4 Replies View Related

Duplicate Record Count And Store In Variable

Jul 16, 2014

I have duplicate records in table.I need to count duplicate records based upon Account number and count will be stored in a variable. I need to check whether count > 0 or not in stored procedure.I have used below query.

SELECT @_Stat_Count= count(*),L1.AcctNo,L1.ReceivedFileID from Legacy L1,Legacy L2,ReceivedFiles where L1.ReceivedFileID = ReceivedFiles.ReceivedFileID
and L1.AcctNo=L2.AcctNo group by L1.AcctNo,L1.ReceivedFileID having Count(*)> 0
IF (@_Stat_Count >0)
BEGIN
SELECT @Status = status_cd from status-table where status_id = 10
END

View 5 Replies View Related

Store Result Of Stored Procedure Into A Variable

Oct 16, 2007

I wrote a stored procedure that finds a number. I want to store the number it finds into a variable so i can use it within another procedure.
I hope i'm being clear.
Any help will be appreciated.
Here is an example of how i am finding my number
Employee is the name of my table and Number is the name of my column.

SELECT Max(Number)
FROM Employee

View 5 Replies View Related

IDENTITY - Using A Local Variable Value For A Seed Value

Mar 22, 1999

I'm trying to have an identity column seed value specified with a local variable value as follows, however it
doesn't allow me to do it (Says cannot use a variable name for a seed value).
Any ideas or suggestions?

DECLARE @idvalue int

SELECT @idvalue = max(accountid) + 1
FROM account

CREATE TABLE accounttemp
(Accountid int IDENTITY(@idvalue,1),
name char(10),
address char(10))

View 1 Replies View Related

Using A Variable To Assign An IDENTITY SEED

Feb 16, 2004

I'm using a stored procedure to create a table in sql 2000. One of the columns is an identity column. I need to set the seed to a max(number) from a column in another table, this column is not an identity column and can't be changed into one. I've been trying to set the seed by passing a variable. I continue to get errors so either I've got the syntax wrong or it's not possible to set the seed via a variable. Any words of wisdom would be appreciated.

View 5 Replies View Related

Pass Variable To Identity Function

Aug 11, 2006

is it possible to pass a variable to an identity funtion

example

declare @max_note int

select @max_note = max(key ) from notes

select m_key = identity( int, @max_note, 1),
name

into #prod_note

from prod_note

View 3 Replies View Related

Is It Possible To Store The Result Of A Dynamic Query In A Local Variable

Dec 19, 2000

I have a dynamic query which returns me a result and I want to capture that value to make further use of it in the same code. Is that possible??
exec ('select col_nm from table_name'). i want the result of this query to be captured.
DP

View 4 Replies View Related

How To Store Column's Value From A Flat File Into A Package Variable?

Oct 7, 2007

I have a few flat files that will be retrieved from some SFTP server. One of the flat file will act as a terminal file where it will specify the total number of records expected in each other the flat file.

Data in the terminal.txt
FileName TotalRecords
File1 1000
File2 1500
File3 2000

So, before transforming the data from the flat file sources into the target destination, i wish to do a row count checking for each of the flat file source to make sure that the number of records in the flat file source is tally with the number of records specify in the terminal.txt file. I'm able to get the number of records in each of the flat file by using the RowCount component but don't know how to get the data out from the terminal.txt file in order to make a rowcount comparison.

Can any1 help me on this? Or is there any other way we can do to make sure that the flat file source is alright before proceeding with the data transformation task?

Thanks!

View 3 Replies View Related

Restarting The Identity Column On Table Variable

Jun 2, 2006

In a cursor, I declare a table variable like so:
DECLARE @TempTable TABLE(RowID INT IDENTITY, valueID int)
I then insert into that table from another table.  The purpose is to get a list that looks like this after the insert:
RowID     valueID1               348972               345223               94822
etc....
However, the next time through my loop (cursor) I want to restart my RowID identity property, because the next batch of valueID's should then again have a RowID starting from 1.
I tried
delete from @TempTable  DBCC CHECKIDENT(@TempTable , RESEED, 0)
but I get 'Must declare the variable @TempTable table' error.
Is there a way to destroy and recreate that @TempTable  variable?
 
 

View 4 Replies View Related

Import Data Where Current Data Has Unique Identity

Aug 10, 2000

I have data for online catalogue in SQL 7.0. The web grogrammer asked me to add a unique key for reference. I used int datatype with identity seed of 1 and increment of 1. This works fine BUT when I try to import new data I get an error because the csv file has no column and therefore no value for the unique field which will not allow null by definition.

How can I maintain a unique field to act as primary key in my data when
I want to add (and delete) data that doesn't have this field.

I tried adding the uniqueidentifier field but this gives error message.

The only work round is to delete the unigue field altogether and then add the new data and afterwards create a new unique field. At 600000 + lines of data, this is time and memory consuming

Any help appreciated,Thanks, Keith

View 1 Replies View Related

How To Write A SELECT Statement And Store The Result In A Session Variable

Nov 6, 2007

I'm sure this is a very simple piece of code, but I'm having trouble understanding how to do this.
First I have a database with three columns


ContactID

View 1 Replies View Related

How To Store Multiple Result In A Variable On Sql Server Stored Procedure

Feb 29, 2008

 name               age            weightaaa                    23                50bbb                    23                60ccc                     22               70ddd                    24                20  eee                     22               30i need the output that calculate the sum of weight group by name input : age limit ex: 22 - 23 output  : age           total weight  23               11022                100  this output must stored in a sql declared variable for some other further process . 

View 7 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved