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
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 RelatedHi 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!
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
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 RelatedI 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
I've this query
SELECT
t1.ID, t1.Date, t1.Time,
t1.VALUE
FROM RESULT WHERE t1.Date>=CONVERT(VARCHAR(10),DATEADD(d,-7,GETDATE()),101) AND
t1.Date<CONVERT(VARCHAR(10), GETDATE(), 101)
Let's say, current date is 8 AUG 2005 and current time is 2045
So, i will get
ID | Date (this is datetime) | Time (this is integer) | Value
--------------------------------------------------
204 | 8/1/2005| 2359 | 90
205 | 8/1/2005| 2250 | 99
206 | 8/1/2005| 1950 | 88
...
...
207 | 8/7/2005| 1845 | 77
208 | 8/7/2005| 2255 | 77
209 | 8/7/2005| 2140 | 77
Can someone can show me to filter data between
t1.Date>=CONVERT(VARCHAR(10),DATEADD(d,-7,GETDATE()),101) AND TIME>=CurrentTime
t1.Date<CONVERT(VARCHAR(10), GETDATE(), 101) AND TIME<=CurrentTime
If current date is 8 AUG 2005 and current time is 2045, so the result shown as follow
ID | Date (this is datetime) | Time (this is integer) | Value
--------------------------------------------------
204 | 8/1/2005| 2359 | 90
205 | 8/1/2005| 2250 | 99
...
...
207 | 8/7/2005| 1845 | 77
I only have this query,
SELECT
t1.ID, t1.Date, t1.Time,
t1.VALUE
FROM RESULT WHERE t1.Date>=CONVERT(VARCHAR(10),DATEADD(d,-7,GETDATE()),101) AND
t1.Date<CONVERT(VARCHAR(10), GETDATE(), 101)
lack of idea to put the TIME condition.
Plz help me..
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
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?
I have table of three column first column is an ID column. However at creation of the table i have not set this column to auto increment. Then i have copied 50 rows in another table to this table then set the ID column values to zero.
Now I have changed the ID column to auto increment seed=1 increment=1 but the problem is i couldn't figure out how to update this ID column with zero value set to each row with this auto increment values so the ID column would have values from 1-50. Is there a away to do this?
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 2 Replies View RelatedHi,
I'm making some sort of application where people can add their resume.
They also need something to add places where they have worked or currently are working.
I have a form where they can add this and i add this experience using the following stored procedure:
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[sp_Insert_Experience]
@ExperienceId uniqueidentifier,
@ExperienceEmployee nvarchar(100),
@ExperienceFrom datetime,
@ExperienceUntil datetime,
@ExperienceQualifications nvarchar(250),
@ExperienceTechnologies nvarchar(250),
@ExperienceTasks nvarchar(250)
as
insert into Experiences
values(@ExperienceId,@ExperienceEmployee,@ExperienceFrom,@ExperienceUntil,@ExperienceQualifications,
@ExperienceTechnologies,@ExperienceTasks);
It must be possible to add the place where they currently are working. Then the ExperienceUntil has to be something like still going on. Then I decided that the user then had to set the current date.
But what I want is the following, I want that the ExperienceUntil keeps updating automatically, like everytime i get that record, it has to have current date.
Is this possible ?
But if the ExperienceUntil isn't the current date , it just had to take the supplied parameter date.
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
I'm working with a third-party database (SQL Server 2005) and the problem here is the following:
- There are a bunch of ETL processes that needs to insert rows on a table (let's call this table T) and at the same time, an ERP (owner of T) is up and running (reading, updating and inserting on T).
- The PK of T is an Integer.
Today all ETL processes uses (select max(ID) + 1 from T) to insert new rows, so just picture the scenario. It is a mess! Everyday they get duplicate key error when 2 or more concurrent processes are trying to insert a row (with the max) at the same time.
Considering that I can't change the PK, what is the best approach to solve this problem?
To sum up:
* I need to have processes in parallel inserting on T
* I can't change anything on T
* The PK is NOT an Identity
when i alter non identity column to identity column using this Query alter table testid alter column test int identity(1,1) then i got this error message Msg 156, Level 15, State 1, Line 3 Incorrect syntax near the keyword 'identity'.
View 2 Replies View Relatedi know there is a function to count number of rows in a group, but is there anyway of numbering each row?
So that I can use =Last(ReportIems("norow") to find the last row on the page????
Thanks
I have following query
....
while ( select fieldID from table where age <> 2)
begin
how I can get current value of "fieldID" for future calculations or operations?
like
insert into othertable values (fieldID, .....)
end
Hi everyone,
Currenly, I'm getting the current year in my code, and passing this to my stored procedure.
I'm sure there's a way just to get the current year (ie '2004') just by using SQL.
I was just about to post this question here when I did a bit more digging around and found my solution:
DATEPART(yyyy, GETDATE())
This will get the year.
Hope this helps someone searching for the answer!
I am trying to append the current row ID to a string I am trying to
insert via a sproc. I have retrieved the @@Identity and I am passing it
into a class with a parameter and calling it using:
Listings.UpdateDB AddNewListing = new Listings.UpdateDB();
AddNewListing.InsertListing (Bathrooms.Text, Bedrooms.Text,
Description.Text, Features.ToString(), Address.Text, Price.Text, FN);
I would like to add the current row ID to "FN" like:
Listings.UpdateDB AddNewListing = new Listings.UpdateDB();
AddNewListing.InsertListing(Bathrooms.Text, Bedrooms.Text,
Description.Text, Features.ToString(), Address.Text, Price.Text, FN +
ID);
Thanks in advance,
Justin.
My backup fails in the night, because of a non logged operation being executed at the same instant. I am interested in finding out the statement that is causing the problem. What would be the best way to do that?
View 2 Replies View RelatedI have registered a server on to my client machine successfully,but I am not finding the current activity item in the management folder on EM.How do I see the current activity on the server.Do I need to be SA for that server?My machine has NT workstation with SQL server 7.0 on it.Any one to help?
View 1 Replies View RelatedWhen viewing Current Activity, nothing shows up.
If i run sp_who, the return is normal.
If I run sp_who2, the following message is returned. (it also is returned randomly while using function in EM such as Backup/Restore.)
Msg 268, Level 16, State 1
You cannot run SELECT INTO in this database. The DBO would have to run sp_dboption to enable this option.
What is it??? It's buggin me......
Dano
p.s. I remember a similar problem in the past, it had to do with turning truncate on checkpoint in one of the system databases msdb/master/tempdb or something like that. I turned off all Trunc. and Select/Bulk options on these...
Hi,
Is there any command that gives the current database name.
Regards
Kishore
Today when I opened current activity window on my SQL6.5 server, I could not able to see any activities listed. What will be the problem?
But sp_who works fine.
Srini
l want to run my procedure every month,but it must look at the 1st and the last day of the month.So that when l use my where clause l'll
say where 1st and last day between @startdate and @enddate. How do l do that?
collections_hexagon '2002-05-12','2002-05-12'
Alter Procedure Collections_Hexagon
(
@Startdate datetime,
@Enddate datetime
set @Startdate = 1stdayof currentMonth
set @Startdate = lastdayof currentMonth
)
How to get current database name using stored procedure?
View 2 Replies View RelatedHi,
I want to know a sql query that would show the current database name.
I have tried looking into the online books but couldn't find the right query.I hope u can do better.This is urgent.Thanxs. -- Vijay
Hello,
I have a column in one of my tables called CURRENT. However, when I try to do a select on this column I get an error, I assume because CURRENT is also a SQL term.
Is there any way I can rename my CURRENT column (maybe using an alias?) within my SQL script? I am unable to change the column name within the database.
Thanks,
Jon
How can I filter Current Weeek Date? For example today week dates starts on April 27 and ends on May 3. Thanks
View 20 Replies View RelatedHello All,
Can someone help me with this query?
I have the following table and data structure
Table1: Orders
Field1: OrderID
Table2: OrderStatus
Field1: OrderStatusID
FIeld2: OrderID
Field3: StatusID
Field4: InsertDate
Sample Data:
Table1:
11
12
Table2:
1,11,1,6/1/2008
2,12,1,6/1/2008
3,11,2,6/2/2008
4,12,2,6/2/2008
5,11,3,6/3/2008
6,11,4,6/4/2008
Wanted Results:
11,4,6/4/2008
12,2,6/2/2008
I am trying to get the current row number using this:
SELECT ROW_NUMBER() OVER (ORDER BY QUANTITY) FROM RDR1 WHERE Row = CurrentRow
However i got error message says row is an invalid column name.
SELECT c.cust_fullname AS Customer, c.cust_membership_id AS Account#, SUM(t.c_amount) AS ChargeTotal
FROM Transactions AS t
INNER JOIN Customers AS c ON t.s_ref_num = c.cust_id
where s_credit_tran_type = 'House Account' and b_cancel = 0
GROUP BY c.cust_fullname, c.cust_membership_id
I need this simple query to run automatically every night at 11:30pm, everyday, but only for the Current Day's business.My file will output to .CVS format for import into Quickbooks. I'd love to have it import directly INTO Quickbook's, but I am not at that level yet..
Is there a system word for the current date in a stored procedure or do I have to pass it in as a parameter ?
ie
BK_DateRequired <= Today