Use Of Variable In Identity Function !

Pass Variable To Identity Function
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 Replies !   View Related

---

Compare The Value Of A Variable With Previous Variable From A Function ,reset The Counter When Val Changes
I am in the middle of taking course 2073B €“ Programming a Microsoft SQL Server 2000 Database. I noticed that in Module9: Implementing User-Defined Functions exercise 2, page 25; step 2 is not returning the correct answer.
 
Select employeeid,name,title,mgremployeeid from dbo.fn_findreports(2)
 
It returns manager id for both 2 and 5 and I think it should just return the results only for manager id 2.  The query results for step 1 is correct but not for step 2.
 
Somewhere in the code I think it should compare the inemployeeid with the previous inemployeeid, and then add a counter. If the two inemployeeid are not the same then reset the counter.  Then maybe add an if statement or a case statement.  Can you help with the logic?  Thanks!
 
Here is the code of the function in the book:
 
/*
**       fn_FindReports.sql
**
**  This multi-statement table-valued user-defined
**  function takes an EmplyeeID number as its parameter
**  and provides information about all employees who
**  report to that person.
*/
USE ClassNorthwind
GO
/*
**  As a multi-statement table-valued user-defined
**  function it starts with the function name,
**  input parameter definition and defines the output
**  table.
*/
CREATE FUNCTION fn_FindReports (@InEmployeeID char(5))
RETURNS @reports TABLE
  (EmployeeID char(5) PRIMARY KEY,
  Name nvarchar(40) NOT NULL,
  Title nvarchar(30),
  MgrEmployeeID int,
  processed tinyint default 0)
-- Returns a result set that lists all the employees who
-- report to a given employee directly or indirectly
AS
BEGIN
 DECLARE @RowsAdded int
 -- Initialize @reports with direct reports of the given employee
 INSERT @reports
  SELECT EmployeeID, Name = FirstName + ' ' + LastName, Title, ReportsTo, 0
  FROM EMPLOYEES
  WHERE ReportsTo = @InEmployeeID
 SET @RowsAdded = @@rowcount
 -- While new employees were added in the previous iteration
 WHILE @RowsAdded > 0
 BEGIN
  -- Mark all employee records whose direct reports are going to be
  -- found in this iteration
  UPDATE @reports
  SET processed = 1
  WHERE processed = 0
  
  -- Insert employees who report to employees marked 1
  INSERT @reports
   SELECT e.EmployeeID, Name = FirstName + ' ' + LastName , e.Title, e.ReportsTo, 0
   FROM employees e, @reports r
   WHERE  e.ReportsTo = r.EmployeeID
   AND r.processed = 1
  SET @RowsAdded = @@rowcount
  -- Mark all employee records whose direct reports have been
  -- found in this iteration
  UPDATE @reports
  SET processed = 2
  WHERE processed = 1
 END
RETURN -- Provides the value of @reports as the result
END
GO
 

View Replies !   View Related

---

How To Assign The @@IDENTITY To A Variable
Hi,

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

Thanks,

View Replies !   View Related

---

Return @@identity For Another Function
What I'm trying to do is provide a solution where users can upload an image and a description, to a database, so I'm trying to insert the title and description then return the @@identity for the image upload function which will name the image like this
image_23.jpg (23 being the @@identity) resize it and save it to specified directory

I cant seem to get the identity to return to my script.
This is my SP

CREATE PROCEDURE SP_Insertad
(
@catid int,
@subcatid int,
@areaid int,
@uid int,
@adtitle varchar(255),
@addescription varchar(1000)
)

AS
Insert Into Tbl_ad
(ad_title, ad_description,ad_area,ad_ui_id,ad_active,ad_date,ad_ct_id,ad_sc_id,ad_location)
VALUES
(@adtitle,@addescription,@areaid, @uid, 0,convert(varchar, GETUTCDATE(), 101), @catid, @subcatid, 1)

select @@identity
return
GO


I tested in query analyser, and it works fine, so It must be my code. this is my function

Sub Insert_pic(sender as object, e as eventargs)

Dim catid = Request.form("ddcats")
Dim subcatid = Request.form("subcatrad")
Dim adtitle = Request.Form("txttitle")
Dim AdDescription = Request.form("txtdescription")
Dim uid = getUID(Context.User.Identity.Name)
Dim areaid = Request.form("ddarea")
SQLConnect = new SqlConnection(ConfigurationSettings.Appsettings("mydB"))

SQLCommander = New SQLCommand("SP_INSERTad", SQLConnect)

SQLCommander.Commandtype = Commandtype.StoredProcedure

SQLCommander.Parameters.add("@adtitle", adtitle)
SQLCommander.Parameters.add("@addescription", addescription)
SQLCommander.Parameters.add("@catid", catid)
SQLCommander.Parameters.add("@subcatid", subcatid)
SQLCommander.Parameters.add("@uid", uid)
SQLCommander.Parameters.add("@areaid", areaid)

'// this section not working right, it wont write return id

Dim paramreturn as SQLParameter
paramreturn = SQLCommander.Parameters.Add("ReturnValue", SQLDBType.Int)
ParamReturn.Direction = ParameterDirection.ReturnValue

response.write(SQLCommander.Parameters("ReturnValue").Value)

SQLConnect.open()
SQLCommander.ExecuteNonQuery()
SQLConnect.close()

End sub

Can anybody see anything I missing? I appreciate any imput

View Replies !   View Related

---

Reset Identity Value On A Table Variable
Hi,

Is anyone familiar with a way to reset the identity value on a table variable...

the DBCC command doesnt seem to work on a table variable...

I try this:
dbcc checkident ('@block, RESEED, 1)

I get this msg, rightfully so..

Could not find a table or object named '@block'. Check sysobjects.

My block table has an identity column called "blockid". Is there anyway I can update this column after the inserts have been done? Or does anyone know of any other way I can use the identity capability on a table variable?

Thanks much :)

View Replies !   View Related

---

Using A Variable To Assign An IDENTITY SEED
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 Replies !   View Related

---

IDENTITY - Using A Local Variable Value For A Seed Value
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 Replies !   View Related

---

Store Current Identity In A Variable
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 Replies !   View Related

---

Restarting The Identity Column On Table Variable
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 Replies !   View Related

---

Has SELECT INTO With IDENTITY() Function Changed The Way #'s Are Assigned?
We have SELECT INTO queries that use the IDENTITY function to assign a sequential row number to a result set based upon a sort order. This has been helpful in SQL Server 2000 for median determination. It appears, however, that in SQL Server 2005, the row numbers are not assigned sequentially, or maybe they are assigned before the sort order is applied.
 
Can anyone verify whether the IDENTITY function has changed behavior between 2000 and 2005? We would prefer not to have to make changes to existing queries. Thank you.

 
(BTW, the workaround we found so far is to put the initial SELECT...ORDER BY in a subquery, but then we had to include a phrase like TOP 10000000 to pass syntax check. Is there a better way?)
 

View Replies !   View Related

---

Function With A Tablename As Variable
Dear all,

Can someone help me with the following function? I would like to use a table name as a variable.

Thanks in advance!

CREATE FUNCTION FAC_user.Overzicht_DTe (@tabel1 as nvarchar, @proces as nvarchar, @categorie as nvarchar)
RETURNS numeric AS
BEGIN
declare @aantal numeric

if @proces = 'Inhuizen'
begin
if @categorie = 'open_op_tijd'
begin
SET @aantal =(SELECT Count(@tabel1 + '.Contractnummer')
FROM @tabel1, Rapportageweek
WHERE@tabel1.Verwerkingsdatum is null
AND @tabel1.UiterlijkeVerwDatum >= Rapportageweek.Rapportagedatum
AND @tabel1.ItemType = 'ZVHG'
AND @tabel1.ItemType = 'ZVHN'
AND @tabel1.ItemType = 'ZVIG'
AND @tabel1.ItemType = 'ZVIN'
GROUP BY@tabel1.Maand, @tabel1.Jaar)
end

if @categorie = 'open_te_laat'
begin
SET @aantal =(SELECT Count(@tabel1 + '.Contractnummer')
FROM @tabel1, Rapportageweek
WHERE@tabel1.Verwerkingsdatum is null
AND @tabel1.UiterlijkeVerwDatum < Rapportageweek.Rapportagedatum
AND @tabel1.ItemType = 'ZVHG'
AND @tabel1.ItemType = 'ZVHN'
AND @tabel1.ItemType = 'ZVIG'
AND @tabel1.ItemType = 'ZVIN'
GROUP BY@tabel1.Maand, @tabel1.Jaar)
end

end

return @aantal

END

View Replies !   View Related

---

How Specify The Variable As Parameter For A Function
I could successfully modify the package level variable using a script component (Control Flow Level) and execute the data flow task after this script component. The OLE DB Command has one parameter for which I'm using one of the user variable. Here's the SQL statement.

SELECT     Year_Key, Year_Name, Year_Short_Name, Year_Number, Year_Start_Date, Year_End_Date
FROM         d_Time_School_Year
WHERE     (Year_Key = ?)

This works fine. But I want to pass the year_key to a function which accepts a parameter. The SQL should be like this

SELECT     Year_Key, Year_Name, Year_Short_Name, Year_Number, Year_Start_Date, Year_End_Date
FROM        fn_TimeDimension (?)


But SSIS doesn't like this. When I click on parameters command button I get and error like this

"Parameters cannot be extracted from the SQL command. The provider might not help.........

Syntax error, Permission Violation,  or the non-specific error(Microsoft SQL native Client)"

Any clue how to utilize the variables in a SQL which gets data from a function instead of a table?

Thanks

Jemini Joseph

View Replies !   View Related

---

Can A Sql 2005 Function Return More Than A Variable
Hi,I have a sql 2005 function who return a distance from 2 zipcodes. This function is called from a Stored procedure like this :SELECT *, dbo.fn_GetDistance (...) AS DistanceIn this function, i have a Latitude and i want this Latitude to be also returned.It is possible or a function can return only one variable?If it is possible, what's the syntax of it?Thanks in advance

View Replies !   View Related

---

Can't Get Left() Function To Work With A Variable.
I have not been able to find the answer as to why the LEFT() function doesn't see the variable as being a variable.
I originally thought it did not accept a variable as the first parameter, however the definition says it can be a variable.
Anyone knows why this isn't working?
This is how I have the code:

SELECT LEFT(@tpatdata, CHARINDEX('^', Alert1) -1)

the variable @tpatdata is the column name (tablename.Alert1), iif I rewrite it like this:

SELECT LEFT(tablename.Alert1, CHARINDEX('^', Alert1) -1) it works.

View Replies !   View Related

---

Help: About Charindex Function Doesn't Work With Variable
Hello to all,
I hope that somebody can help me.
I have written a sql query to search Partner.  I have a wtcomValidRelationships Table. There are two Fields (IDMember(type: Int) and RelationshipIDs(type: varchar(1000)) in this table.
Example: 3418 has 3422 RelationshipID and 3422 has 4088 RelationshipID, if i want to check if there is a relationship between 3418 and 4088. 
declare @IDM int;
declare @IDO char(100);
set @IDM = 3418;
set @IDO = '4088';
select B.IDMember
from wtcomValidRelationships as A, wtcomValidRelationships as B
where A.IDMember = @IDM and charindex(cast(B.IDMember as char(100)),A.RelationshipIDS) > 0
and charindex(@IDO,B.RelationshipIDs) > 0
Using this query i get nothing.
I try to use constant in charindex and i get result.
declare @IDM int;
declare @IDO char(100);
set @IDM = 3418;
set @IDO = '4088';
select B.IDMember
from wtcomValidRelationships as A, wtcomValidRelationships as B
where A.IDMember = @IDM and charindex('3422',A.RelationshipIDS) > 0
and charindex('4088',B.RelationshipIDs) > 0
So i think that charindex doesn't work with variable. But I must use variable. Can someone help me? What should i do ?
Thanks
Best Regards
Pinsha

View Replies !   View Related

---

Using A Date Function To Declare A Variable Used In A SQL Query
Hi all can you help me, I know that I am doing some thing wrong. What I need to do is set a variable to the current date so I can use it in a SQL query to an access database. This is what I have so far
<script runat="server"">
Sub Page_Load
dim --all the variables for my sql connections--
dim ff1 As Date
then my sql connection and queries
sql="SELECT FullRate, " & ff1 &" FROM table1 WHERE hotelnumber = " & hotel
This works is I set ff1 as a string and specify the string (my column headings are set as dates in my table)
dim ff1 As string
ff1="30/01/2006"
but I need ff1 to be the current date and is I use ff1 As date it returns time and date
Is there any way to set ff1 to the current date in this format "dd/mm/yyyy"
 
Any help is greatly appreciated

View Replies !   View Related

---

Can We Use A Variable As An Argument In A User Defined Function
Hi,
I have written a user defined function to convert a date from an OLD time zone to NEW time Zone. The three parameters are (date, OLDTz,NEWTZ).

=====================================
declare
@v_tz varchar(10) ,
@v_da integer,
@v_ss varchar(100)
select @v_da=7
select @v_tz='GMT'
select @v_ss='soumilpatel'
select substring('soumilpatel',2,3)
select substring(@v_ss,2,3)
select getdate(), dateadd(day, 7, getdate()), dateadd(day, @v_da, getdate())
select top 2 timestampwhenset, dbo.tc(timestampwhenset,'GMT','PST') from s_monitorevents
select top 2 timestampwhenset, dbo.tc(timestampwhenset,'GMT', @v_tz ) from s_monitorevents
go
=====================================

In real life the value of @v_tz is coming from a table. How do I go about resolving the value of the variable before I run the function. I was able to use the variable substitution successfully in a numeric function as well as in a string/character function.

Your input is very much appreciated.
-soumil

View Replies !   View Related

---

Using Table Variable As Input To Function Or Procedure
Is there any way to use table variable as input to a function or stored procedure?

View Replies !   View Related

---

Passing Variable To Table Function In Join
Hello, thanks in advance for reading this. I am having difficulty trying to get a statement to work.

There is a MAIN table:
ItemNo int identity(1,0),
ItemType tinyint

There is a WETPAINT table:
ItemNo int,
Color varchar(20)

There is a DRYPAINT table:
ItemNo int,
Color varchar(20)

Now, what I want to do is JOIN the MAIN table to either the WETPAINT table or the DRYPAINT table depending on the value of MAIN.ItemType

So I created a table function called getTable:

CREATE FUNCTION [dbo].[gettable]
(
@ItemType int = 1
)
RETURNS
@thistable TABLE
(
Color varchar(20)

)
AS
BEGIN
if @ItemType = 1
insert into @thistable (color) select color from WETPAINT
if @ItemType = 2
insert into @thistable (color) select color from DRYPAINT
RETURN
END

This is all fine and dandy if I iterate through the MAIN table one row at a time, but how can I JOIN the tables, like:

SELECT MAIN.ItemNo, a.Color
FROM MAIN
INNER JOIN gettable(Main.ItemNo) as a
ON a.ItemNo = MAIN.ItemNo

Obviously, there is more than one field in the DRYPAINT and WETPAINT tables, and there is a need to have both tables instead of combining them into one.

Any help in how to create a table alias by passing a value from the select statement would be greatly appreciated! Thanks again.

PS -- I am trying to create a view with this, so I can't use variables and iterate through the MAIN table one row at a time.

View Replies !   View Related

---

Passing Variable To String Compare In Function
I have created a function with:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[fn_concat_boxes](@item varchar, @week int)
RETURNS VARCHAR(100)
AS
BEGIN

DECLARE @Output varchar(100)

SELECT @Output = COALESCE(@Output + '/', '') +
CAST(quantity AS varchar(5))
FROM flexing_stock_transactions
WHERE item = @item AND week = @week
GROUP BY quantity
ORDER BY quantity

RETURN @Output


END

how can I pass the variable @item correctly for the string comparison

WHERE item = @item AND week = @week

to work correctly please?

WHERE item = '@item' AND week = @week

won't work and

WHERE item = @item AND week = @week

won't work.

View Replies !   View Related

---

Make A Select From A Clr Function To A Table Variable
Hi,
is it somehow possible to make a Select-Statement from a Clr-Funktion to a table variable??
 
Regards
Pamelia

View Replies !   View Related

---

Assigning Datepart Function To A Datetime Variable?
I am getting wrong output when assigning a datepart function to a variable.  I should get 2006 but instead I get an output 1905.

Below  is the code and output.  Any help will be greatly appreciated.  Thanks

 

DECLARE @FiscalStartCurrYear datetime

SET @FiscalStartCurrentYear = DATEPART(year, GETDATE())

select @FiscalStartCurrYear

 

Output

-----------

1905-06-30 00:00:00.0000

 

View Replies !   View Related

---

Problem Passing A Variable Into A Table-valued Function
Hi,

i am encountering a problem in a stored procedure when a pass a variable value into a table-valued function. The table-valued function is named getCurrentDriver and has 1 attribute: car-ID.

The syntax is as follows:

select car.id, car.licenceNumber, car.brand, car.model,
(select driverName from getCurrentDriver(car.id)) as driverName
from car

When I try to compile I get following error on the line of the function:
Incorrect syntax near '.'

The database version is SQL Server 2000 SP3.

What am I doing wrong? Is there a workaround for this error?

View Replies !   View Related

---

Passing Table Variable To Stored Proc / Function
Hi all,
Is it possible to pass a table variable to a Stored proc or a function?
If it is can you give me the sentax.

TIA,

View Replies !   View Related

---

&&"Must Declare The Scalar Variable&&" In Table-valued Function
Hi, I'm having trouble with this multi-statement table-valued function:

ALTER FUNCTION MakeArDetail
(
    -- Add the parameters for the function here
    @dateStart DATETIME,
    @dateEnd DATETIME
)
RETURNS @arDetail TABLE 
(   
     Insurer VARCHAR(50),
     NABP INT DEFAULT 0,
     Claim MONEY DEFAULT 0,
     Payment MONEY DEFAULT 0,
     NumRx CHAR(7),
     PatientName VARCHAR(50),
     Paid030 MONEY DEFAULT 0,
     Paid3160 MONEY DEFAULT 0,
     Paid6190 MONEY DEFAULT 0,
     Paid91120 MONEY DEFAULT 0,
     Paid121 MONEY DEFAULT 0
)
AS
BEGIN
DECLARE @arTemp TABLE
(   
     Insurer VARCHAR(50),
     NABP INT DEFAULT 0,
     Claim MONEY DEFAULT 0,
     Payment MONEY DEFAULT 0,
     NumRx CHAR(7),
     PatientName VARCHAR(50),
     Paid030 MONEY DEFAULT 0,
     Paid3160 MONEY DEFAULT 0,
     Paid6190 MONEY DEFAULT 0,
     Paid91120 MONEY DEFAULT 0,
     Paid121 MONEY DEFAULT 0
)

INSERT INTO @arTemp
   SELECT DISTINCT Insurer,NABP,0,0,NumRx,Patient,0,0,0,0,0 FROM Pims;
UPDATE @arTemp SET Claim =
  (SELECT SUM(Pims.AmtReq)
       FROM Pims
       WHERE Pims.Insurer = @arTemp.Insurer AND
             Pims.NABP = @arTemp.NABP AND
             Pims.NumRx = @arTemp.NumRx
  );

INSERT INTO @arDetail SELECT * FROM @arTemp
RETURN
END
GO

I get
Msg 137, Level 15, State 2, Procedure MakeArDetail, Line 43
Must declare the scalar variable "@arTemp".

I don't understand why SQL thinks @arTemp is a scalar variable which has to be declared.
If I don't include the UPDATE command the thing works.

View Replies !   View Related

---

Indexes On Table Variable Of Table Valued Function
Hi there,

Can someone tell me if it is possible to add an index to a Table variable that is declare as part of a table valued function ? I've tried the following but I can't get it to work.

ALTER FUNCTION dbo.fnSearch_GetJobsByOccurrence
(
@param1 int,
@param2 int
)
RETURNS @Result TABLE (resultcol1 int, resultcol2 int)
AS
BEGIN

CREATE INDEX resultcol2_ind ON @Result

-- do some other stuff

RETURN
END

View Replies !   View Related

---

&&"Static Variable&&" In Function
Hi!

     I have a function that uses a constant value on its calculations. This value is defined on a table. I don't want to query this table everytime I call the function (I call it on a loop from my Java code). Is there anything like a static variable I could use?

Thank you!

 

View Replies !   View Related

---

Table-valued Function Run Once For Each Row In A Table Variable.
I have a stored produre.  Inside this stored procedure I have table variable with one column.  Once the table variable is populated with rows, I would like to pass each value in the table, into a table-valued function.  The table-valued function may return any number of rows.  I would like all the rows the TVF returns to be returned from the stored procedure as a single result set.  I would also like to do this without defining a table variable to hold the results of the table-value function.
 



Code Snippet

declare @IdTable table
(
 EmployeeId nvarchar( 16 ) not null
)
insert into @IdTable
select EmployeeNumber from Employees
 
/*
I need to run this query for every EmployeeId value in @IdTable and return the results from the stored proc as a single result set.
*/
select * from fn_GetEmployeeById( EmployeeId )

 
 



 
Any help is very much appreciated.
Andrew
 

View Replies !   View Related

---

Table-valued Function Into A @table Variable
In my stored procedure i have a multi-valued varchar(max) parameter and I wrote a table-valued function that takes the varchar(max) and return a table back to the stored procedure where i inserted into a @table. Just wondering is there a better and faster way of doing this?




ALTER   PROCEDURE [dbo].[rpt]

(


@CourtIDs as nvarchar(MAX) -- @CourtIDs = '1231,3432,1234,3421'

)
AS


--split CourtIDs into a table
DECLARE @tbCourtIDs     table(CourtID int NOT NULL PRIMARY KEY)
INSERT INTO @tbCourtIDs
select * from dbo.Split(@CourtIDs, ',')

View Replies !   View Related

---

Insert Row In Table With Identity Field, And Get New Identity Back
I want to insert a new record into a table with an Identity field and return the new Identify field value back to the data stream (for later insertion as a foreign key in another table). 

What is the most direct way to do this in SSIS?

 

TIA,

 

barkingdog

 

P.S. Or should I pass the identity value back in a variable and not make it part of the data stream?

 

View Replies !   View Related

---

Identity...I Need To Get The Last (or Highest Number In Identity Column)...
Ok,I just need to know how to get the last record inserted by the highestIDENTITY number. Even if the computer was rebooted and it was twoweeks ago. (Does not have to do with the session).Any help is appreciated.Thanks,Trint

View Replies !   View Related

---

Identity Crisis With Identity Fields! -- Help Needed.
Hey SWYNKers,

This is probably a simple problem, but I could really use the help.


I've got a simple membership table that uses an Identity field to produce a unique ID for each Member. It only happens rarely, but sometimes I end up with Members with the exact same ID. How can this happen? I understand that one shouldn't depend on SQL Identity fields to produce an "ordered" or incremented number, but each ID field, if Identity, should be unique. Thats the whole point of an Identity field right?!

Anyway, does anybody know why this happens or how to prevent it, or workarounds?

Oh SQL engineers and those with Know-how, I beseech thee to help me.

Thanks,

Fenderson Farseid
NOT an engineer since 1975

View Replies !   View Related

---

Problem In Using Sqlbulkcopy To Insert Data From Datatable(no Identity Column) Into Sql Server Table Having Identity Column
Hi,
I am having problem in bulk update of a sql server table haning identity column from a datatable( has no identity column) using sqlbulkcopy. I tried several approaches, but it does not show any error nor is the table getting updated. But the identity value seems to getting increased every time.
thanks.
varun

View Replies !   View Related

---

Last GASP On &&"Insert Row In Table With Identity Field, And Get New Identity Back &&" ?
While I have learned a lot from this thread I am still basically confused about the issues involved.

.I wanted to INSERT a record in a parent table, get the Identity back and use it in a child table. Seems simple.

To my knowledge, mine would be the only process running that would update these tables. I was told that there is no guarantee, because the OLEDB provider could write the second destination row before the first, that the proper parent-child relationship would be generated as expected. It was recommended that I create my own variable in memory to hold the Identity value and use that in my SSIS package.

1. A simple example SSIS .dts example illustrating the approach of using a variable for identity would be helpful.

2. Suppose I actually had two processes updating these tables, running at the same time. Then it seems the "variable" method will also have its problems. Is there a final solution other than locking the tables involved prior to updating them or doing something crazy like using a GUID for the primary key!

3. We have done the type of parent-child inserts I originally described from t-sql for years without any apparent problems. (Maybe we were just lucky.)  Is the entire issue simply a t-sql one or does SSIS add a layer of complexity beyond t-sql that needs to be addressed?

 

TIA,

 

Barkingdog

View Replies !   View Related

---

SSIS Script Task Alters Package Variable, But Variable Does Not Change.
I'm working on an SSIS package that uses a vb.net script to grab some XML from a webservice (I'd explain why I'm not using a web service task here, but I'd just get angry), and I wish to then assign the XML string to a package variable which then gets sent along to a DataFlow Task that contains an XML Source that points at said variable. when I copy the XML string into the variable value in the script, if do a quickwatch on the variable (as in Dts.Variable("MyXML").value) it looks as though the new value has been copied to the variable, but when I step out of that task and look at the package explorer the variable is its original value.

I think the problem is that the dataflow XML source has a lock on the variable and so the script task isn't affecting it. Does anyone have any experience with this kind of problem, or know a workaround?

View Replies !   View Related

---

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 !   View Related

---

SSIS Error Reading XML Loaded Into Variable With XML Source Using XML File From Variable.
Hi
 
I am getting the following error when trying to extract data using XML source "

Error: 0xC02090D0 at Data Flow Task - Load XML data to database, XML Source - Load XML data from variable [15893]: The component "XML Source - Load XML data from variable" (15893) was unable to read the XML data."

 
What I have done is read XML data from file and stripped out DTD contents using XSLT transformation in an XML task.  The XML file is loaded into a string variable called XMLProduct.  Next I have a XML task to validate the variable contents against the XSD, which works.  Then I am trying to load the data using XML Source within a Data Flow task, which is when the error occurs.  If before I enter the Data Flow and use a script task to read the content of  XMLProduct variable and save that to a file then point XML Source task to the file it works ok.
 
I need to iterate through a whole bunch of XML files so reading it into a variable would be the preferred option if it worked of course.
 
Any help would be hugely appreciated.
 
Edit --- I have saved the output from the XSLT transformation XML Task to file and not a variable then read that in using the XML source task and it worked.  It would be nice to get the variable method working though as it would save having to create another file.  It is a work around at the moment though.

View Replies !   View Related

---

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 !   View Related

---

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

View Replies !   View Related

---

Debug Error - Object Variable Or With Block Variable Not Set -
I keep getting this debug error, see my code below, I have gone thru it time and time agian and do not see where the problem is.  I have checked and have no  NULL values that I'm trying to write back.
~~~~~~~~~~~
Error:
System.NullReferenceException was unhandled by user code  Message="Object variable or With block variable not set."  Source="Microsoft.VisualBasic"
~~~~~~~~~~~~
My Code
Dim DBConn As SqlConnection
Dim DBAdd As New SqlCommand
Dim strConnect As String = ConfigurationManager.ConnectionStrings("ProtoCostConnectionString").ConnectionString
DBConn = New SqlConnection(strConnect)
DBAdd.CommandText = "INSERT INTO D12_MIS (" _
& "CSJ, EST_DATE, RECORD_LOCK_FLAG, EST_CREATE_BY_NAME, EST_REVIEW_BY_NAME, m2_1, m2_2_date, m2_3_date, m2_4_date, m2_5, m3_1a, m3_1b, m3_2a, m3_2b, m3_3a, m3_3b" _
& ") values (" _
& "'" & Replace(vbCSJ.Text, "'", "''") _
& "', " _
& "'" & Replace(tmp1Date, "'", "''") _
& "', " _
& "'" & Replace(tmpRecordLock, "'", "''") _
& "', " _
& "'" & Replace(CheckedCreator, "'", "''") _
& "', " _
& "'" & Replace(CheckedReviewer, "'", "''") _
& "', " _
& "'" & Replace(vb2_1, "'", "''") _
& "', " _
& "'" & Replace(tmp2Date, "'", "''") _
& "', " _
& "'" & Replace(tmp3Date, "'", "''") _
& "', " _
& "'" & Replace(tmp4Date, "'", "''") _
& "', " _
& "'" & Replace(vb2_5, "'", "''") _
& "', " _
& "'" & Replace(vb3_1a, "'", "''") _
& "', " _
& "'" & Replace(vb3_1b, "'", "''") _
& "', " _
& "'" & Replace(vb3_2a, "'", "''") _
& "', " _
& "'" & Replace(vb3_2b, "'", "''") _
& "', " _
& "'" & Replace(vb3_3a, "'", "''") _
& "', " _
& "'" & Replace(vb3_3b, "'", "''") _
& "')"
DBAdd.Connection = DBConn
DBAdd.Connection.Open()
DBAdd.ExecuteNonQuery()
DBAdd.Connection.Close()

View Replies !   View Related

---

Retrieving Result Set From Dynamically Called Stored Procedure Or Function In A Function
Is there any way I can retrieve the result set of a Stored Procedurein a function.ALTER FUNCTION dbo.fn_GroupDeviceLink(@groupID numeric)RETURNS @groupDeviceLink TABLE (GroupID numeric, DeviceID numeric)ASBEGINDeclare @command nvarchar(255)SELECT @command = Condition// @command is an SQL string or stored procedue nameFROM DeviceGroupWHERE GroupID = @groupIDINSERT @groupDeviceLinkEXEC @commandRETURNENDIs there any way i can do anything like this. @command is a variableholding the name of a stored produre. I need to run that storedprocure and return the values in such a way that they can be used in aSELECT StatementMy goal is SELECT * FROM Device INNER JOINdbo.fn_GroupDeviceLink(@groupID) ON ....this fn_GroupDeviceLink should run the proper stored procedure andreturn the values. What i also want to do is play with that result setof the specific stored procedure before i return it. Is this possible?If not, what is the work arround?ThanksMark

View Replies !   View Related

---

How To Match The Date Variable And Character Variable
Hello,
I run the DTS to copy data from Progress to SQL Server. How can match/convert the date variables to check.
The field p-date as format 'mm-dd-year' . It has the value of 10/09/2001.
The field s-date as varchar format. It has the value 2001-10-09.
How can use the where condition ( Select ...... WHERE p-date = s-date.)

Thanks

View Replies !   View Related

---

Variable Indirection: Choosing Variable At Runtime
Hello!
I'm using SQL Server 2000.
I have a variable which contains the name of another variable scoped in my stored procedure. I need to get the value of that other variable, namely:
 
DECLARE @operation VARCHAR(3)
DECLARE @parameterValue VARCHAR(50)

SELECT @operation='DIS'
 
CREATE table #myTable(value VARCHAR(20))
INSERT into #myTable values('@operation')
 
SELECT top 1 @parameterValue = value from #myTable
-- Now @parameterValue is assigned the string '@operation'
-- Here I need some way to retrieve the value of the @operation variable I declared before (in fact
-- another process writes into the table I retrieved the value from), in this case 'DIS'
 
DROP TABLE #myTable

 

I've tried several ways, but didn't succeed yet!  
Please tell me there's a way to solve my problem!!!
 
Thank you very much in advance!

View Replies !   View Related

---

Help Convert MS Access Function To MS SQL User Defined Function
I have this function in access I need to be able to use in ms sql.  Having problems trying to get it to work.  The function gets rid of the leading zeros if the field being past dosn't have any non number characters.For example:TrimZero("000000001023") > "1023"TrimZero("E1025") > "E1025"TrimZero("000000021021") > "21021"TrimZero("R5545") > "R5545"Here is the function that works in access:Public Function TrimZero(strField As Variant) As String   Dim strReturn As String   If IsNull(strField) = True Then      strReturn = ""   Else      strReturn = strField      Do While Left(strReturn, 1) = "0"         strReturn = Mid(strReturn, 2)      Loop   End If  TrimZero = strReturnEnd Function

View Replies !   View Related

---

In-Line Table-Valued Function: How To Get The Result Out From The Function?
Hi all,
 
I executed the following sql script successfuuly:
 
shcInLineTableFN.sql:

USE pubs

GO

CREATE FUNCTION dbo.AuthorsForState(@cState char(2))

RETURNS TABLE

AS

RETURN (SELECT * FROM Authors WHERE state = @cState)

GO
 
And the "dbo.AuthorsForState" is in the Table-valued Functions, Programmabilty, pubs Database.
 
I tried to get the result out of the "dbo.AuthorsForState" by executing the following sql script:
 
shcInlineTableFNresult.sql:

USE pubs

GO

SELECT * FROM shcInLineTableFN

GO

 
I got the following error message:

Msg 208, Level 16, State 1, Line 1

Invalid object name 'shcInLineTableFN'.

 
Please help and advise me how to fix the syntax

"SELECT * FROM shcInLineTableFN"
and get the right table shown in the output.
 
Thanks in advance,
Scott Chang

View Replies !   View Related

---

A Function Smilar To DECODE Function In Oracle
I need to know how can i incoporate the functionality of DECODE function like the one in ORACLE in mSSQL..
please if anyone can help me out...


ali

View Replies !   View Related

---

Using RAND Function In User Defined Function?
Got some errors on this one...

Is Rand function cannot be used in the User Defined function?
Thanks.

View Replies !   View Related

---

I Want A Function Like IfNull Function To Use In Expression Builder
Hi,

I wonder if there a function that i can use in the expression builder that return a value (e.g o)  if the input value is null ( Like ifnull(colum1,0)  )

 

i hope to have the answer because i need it so much.

 

Maylo

View Replies !   View Related

---

Object Variable Or With Block Variable Not Set
While I was processing the cubes, error "Object Variable Or With Block Variable Not Set" prompt out,
what does it mean ?

Please help !!!

View Replies !   View Related

---

Set A System Variable To User Variable
How can I inside a DFT set a System variable, for example "TaskName" to an own created User Variable?

 

The reason is that I need to use this variable later in the Control Flow.

 

Regards

 

Riccardo 

View Replies !   View Related

---