Hi All
I am new to sqlserver.I got the doubt when i want to perform a task.Need yours advice
In a table i am having a column email with mailids. most of the rows are with the value email@email.com.
My requirement is i want to change the value email@email.com as email1@email.com,email2@email.com,.....like that
with autoincrement.Here primarykey attribute name is id
Waiting for valuable suggestions
Baba
Dear Friends,
I have two tables........emp and salgrade
I need a procedure to update emp table with the given % hikes.The grades we should take from salgrade table
if the grade =1, then the hike will be 40%
if the grade =2, then the hike will be 30%
if the grade =3, then the hike will be 20%
if the grade =4, then the hike will be 10%
so whenever i execute this procedure, the table should be automatically updated.
thank you verymuch.
Vinod
SP's are still not my forte, but getting there!I am trying to hit two tables in this one and return the values but keep getting the error code = 1 built into the sp and can't see why.CREATE PROCEDURE dbo.retrieveScores @studentId VARCHAR(20), @courseId VARCHAR(20), @lessonLocation VARCHAR(20) OUTPUT, @lessonStatus VARCHAR(20) OUTPUT, @lessonScoreRaw VARCHAR(10) OUTPUT, @lessonScoreMin VARCHAR(10) OUTPUT, @lessonScoreMax VARCHAR(10) OUTPUT, @objId VARCHAR(20) OUTPUT, @objStatus VARCHAR(20) OUTPUT, @objScoreRaw VARCHAR(10) OUTPUT, @objScoreMin VARCHAR(10) OUTPUT, @objScoreMax VARCHAR(10) OUTPUT ASBEGIN DECLARE @errCode INT
DECLARE c_courseScores CURSOR FOR SELECT lessonLocation, lessonStatus, lessonScoreRaw, lessonScoreMin, lessonScoreMax FROM courseScores WHERE studentId = @studentId AND courseId = @courseId FOR READ ONLY
OPEN c_courseScores FETCH c_courseScores INTO @lessonLocation, @lessonStatus, @lessonScoreRaw, @lessonScoreMin, @lessonScoreMax CLOSE c_courseScores DEALLOCATE c_courseScores
DECLARE c_objScores CURSOR FOR SELECT objId, objStatus, objScoreRaw, objScoreMin, objScoreMax FROM objScores WHERE studentId = @studentId AND objId = @objId AND courseId = @courseId FOR READ ONLY
OPEN c_objScores FETCH c_objScores INTO @objId, @objStatus, @objScoreRaw, @objScoreMin, @objScoreMax CLOSE c_objScores DEALLOCATE c_objScores
IF( @@FETCH_STATUS <> 0 ) BEGIN SET @errCode = 1 GOTO HANDLE_APPERR END
SET @errCode = 0 RETURN @errCode
HANDLE_APPERR: IF( CURSOR_STATUS( 'local', 'c_courseScores' ) >= 0 ) BEGIN CLOSE c_courseScores DEALLOCATE c_courseScores END
SET @errCode = 1 RETURN @errCodeENDGOSuggestions?Thanks all,Zath
userid logintime logouttime subject question answer correct status
1000 9/2/2007 12:10:10 chemistry 0
1000 chemistry 1 T 1
1000 chemistry 2 F 0
1000 chemistry 3 a 1
1000 chemistry 4 a 0
1000 chemistry 5 b 1
1000 9/2/2007 12:20:30 chemistry 1
3000 9/2/2007 12:40:00 Math 0
3000 Math 1 c 1
3000 Math 2 a 1
3000 Math 3 T 0
3000 Math 4 F 1
3000 Math 5 b 0
3000 9/2/2007 12:50:25 Math 1
2000 9/2/2007 12:10:10 Biology 0
2000 Biology 1 T 1
2000 Biology 2 F 0
2000 Biology 3 a 1
Logintime means time person started the test.
Logouttime means time person stopped the test.
Correct 1 means correct answer
Correct 0 means wrong answer
Status 0 means test is started.
Status 1 means test is stopped. (Some time people don't stop test due to network failure. So that record got no status 1 or logouttime).
i need to find total score of correct answer.
Based on below table, i need to find who passed and who failed.
Subject Total_Question Main_Question Pass_Score
Math 5 3,5 3
Biology 5 4 4
Chemistry 5 1,2 5
i need to write a stored procedure to do calculation.....so i want to find the below result......
userid logintime logouttime Duration Subject Score Question_Missed Result
1000 9/2/2007 12:10:10 9/2/2007 12:20:30 10:20 Chemistry 2 2 Fail
3000 9/2/2007 12:40:00 9/2/2007 12:50:25 10:25 Math 3 3,5 Pass
2000 9/2/2007 12:10:10 Biology 2 4 Fail
How can i combine both table and get this result?...Please help me. I need help.
Hey all,
Can anyone tell me why this stored procedure doesn't work?
ALTER PROCEDURE [dbo].[RemoveLocation]
@Id int,
@Name varchar
AS
DELETE FROM Classifieds_Terminals
WHERE [Id] = @Id and [Name] = @Name
I try exeuting it like this:
USE [CLASSIFIEDSDB2.MDF]
GO
DECLARE @return_value int
EXEC @return_value = [dbo].[RemoveLocation]
@Id = 18,
@Name = N'Terminal A'
SELECT 'Return Value' = @return_value
GO
It returns 0 and nothing happens....???
Here is the table:
id | Name
18 Terminal A18 Terminal B18 Terminal C
Hi,
I have a stored procedure which returns a dataset after some intense calculations. It actually calculates the daily work hours (wages) for each employee in a month. I am using a temporary table where for each employee, a day's calculations are stored as a row. So if e.g. there are 2 employees, a total of 2 x 31 = 62 rows are saved in the temporary table. In the end i flip the temporary table horizontally (each employee row than displays daily calculations for upto 31 days possibly) to produce a monthly report. The problem is that as the data has grown alot, it takes a very very long time (more than a minute) to calculate these values. The actual time is consumed in flipping the table horizontally. User can select any montly from Jan-Dec.
Can any expert given me some advice on redefining this stored procedure (to optimize for speed) where i can calculate monthly wedges (each day included in the report) for each employee (over 400). In short, we can look at all daily calculations (31 days possibly) in one row for each employee.
Regards....
How do most people handle database searches for things like a product database? What I mean by that is your typical product table may look like:
ProductIDProductTitleProductDescriptionProductCategoryIDEtc...
Assuming you have a Full Text Catalog set up on the ProductDescription field, would you use Dynamic Sql or a Stored Procedure?
I'm wrestling with this because I haven't found a good way to either parse a parameter in SQL to make a stored procedure work with the same flexibility as Dynamic TSQL, and I would prefer not to have any direct access to the table from the application.
My other option (which to me isn't a great one) is to have a fairly large number of optional "keyword" parameters in the stored procedure and then parse them on the application side...this is less than favorable in that in theory someone could pass more keywords than I've allotted in my stored procedure.
How would you approach this challenge?
Thanks in advance.
Ryan
If I have a stored pocedure that just does one simple update command, do I need a commit after the update even if it is not part of a transaction. Or does it commit automatically? Thanks!
View 2 Replies View RelatedHello everyone. I've attached a copy of my recently created stored procedure but now I want to expound on it by creating synatx within it that will allow me to create a 'weighting' type of scenario based on pre-determined weight/ranking classifications (example: a selection of skill '1' would grant the user 2 points toward their ranking, a selection of skill '2' might grant the user 4 pts., etc.) In the end, the users would be tallied and sorted based on the highest ranking (in pts) to the lowest.
The business I'm in is that we develop a web site interface for recruiters and potential job seekers to post resumes, develop a career plan and rank their current work status against the open market.
In short, does anyone out there know how I can implement a "ranking" type system into the syntax provided below?
I've considered the CASE statement but was not clear on how it would work.
Any suggestions would be great.
Claude
cjohnson@staffmentor.net
CREATE PROCEDURE spListMatch
@job_id uniqueidentifier
AS
declare @jobcity varchar(50)
declare @jobposition uniqueidentifier
declare @jobrelocate bit
declare @jobtravel uniqueidentifier
declare @jobyears int
declare @jobIndustry uniqueidentifier
declare @Jobstate varchar(2)
declare @candcity varchar(50)
declare @candposition uniqueidentifier
declare @candrelocate bit
declare @candtravel uniqueidentifier
declare @candstate varchar(2)
declare @candindustry uniqueidentifier
declare @candyears int
declare @holdid uniqueidentifier
declare @candidateid uniqueidentifier
declare @displayid int
declare @ks1 varchar(50)
declare @ks2 varchar(50)
declare @ks3 varchar(50)
declare @ks4 varchar(50)
declare @ks5 varchar(50)
declare @match int
declare @key_skill_desc varchar(50)
declare @strongest int
declare @candIndustrydesc varchar(50)
declare @candPositiondesc varchar(50)
declare @candTraveldesc varchar(50)
declare @prefcity varchar(50)
declare @prefstate varchar(2)
declare @citymatch int
declare @icount numeric
declare @totcount numeric
declare @debug int
select @debug = 1
set nocount on
select @jobcity = city, @jobposition = position_id, @jobrelocate = relocate_assist, @jobtravel = travel_id, @jobstate = state, @jobyears = position_yrs from t_job_order where job_id = @job_id
select @totcount = count(*) from t_job_vstat where job_id = @job_id
select @totcount = @totcount + 3
DECLARE Cand_Cursor CURSOR FOR
select candidate_id, key_skill_desc, strongest from t_cand_vstat, t_key_skill where t_cand_vstat.key_skill_id in (select key_skill_id from t_job_vstat where job_id = @job_id) and
t_cand_vstat.key_skill_id = t_key_skill.key_skill_id
order by candidate_id
CREATE TABLE #ReturnTemp (
candidateid uniqueidentifier NOT NULL,
displayid int,
city varchar(50),
state varchar(2),
Industry varchar(50),
travel varchar(50),
position varchar(50),
hitcount smallint,
tpercent numeric,
ks1 varchar(50),
ks2 varchar(50),
ks3 varchar(50),
ks4 varchar(50),
ks5 varchar(50)
)
OPEN Cand_Cursor
declare @candidate_id uniqueidentifier
FETCH NEXT FROM Cand_Cursor into @candidate_id, @key_skill_desc, @strongest
select @holdid = @candidate_id
WHILE @@FETCH_STATUS = 0
BEGIN
if @candidate_id <> @holdid
begin
select @icount = @icount + 1
if @match = 1
update #ReturnTemp set hitcount = @icount, tpercent = (@icount/@totcount * 100), ks1 = @ks1, ks2 = @ks2, ks3 = @ks3, ks4 = @ks4, ks5 = @ks5 where candidateid = @holdid
select @match = 1
select @ks1 = ""
select @ks2 = ""
select @ks3 = ""
select @ks4 = ""
select @ks5 = ""
select @holdid = @candidate_id
select @icount = 1
select @candrelocate = relocate, @candtravel = travel_id from t_cand_pref where candidate_id = @candidate_id
select @candcity = city, @candstate = state, @displayid = display_id from t_candidate1 where candidate_id = @candidate_id
select @candposition = position_id, @candyears = position_yrs, @candindustry = cat_sub_cat_id from t_cand_seek where candidate_id = @candidate_id
if @candposition = @jobposition select @icount = @icount + 10
if @candyears = @jobyears select @icount = @icount + 8
if @candtravel = @jobtravel
begin
select @icount = @icount + 2
end
else if @jobtravel <> '91C858C8-4A46-4FD8-9B73-87FEE00F799E'
begin
if @candtravel = '91C858C8-4A46-4FD8-9B73-87FEE00F799E'
begin
select @match = 0
end
else
begin
select @icount = @icount + 1
end
end
DECLARE City_Cursor CURSOR FOR
select distinct city, state from t_cand_pref_city_state C, t_city_state S where
c.city_state = s.city_state and C.candidate_id = @candidate_id
OPEN City_Cursor
FETCH NEXT FROM City_Cursor into @prefcity, @prefstate
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM City_cursor
into @prefcity, @prefstate
select @citymatch = 0
if ((@prefcity = @jobcity) and (@prefstate = @jobstate))
begin
--do nothing
select @citymatch = 1
select @icount = @icount + 1
end
END
CLOSE City_Cursor
DEALLOCATE City_Cursor
if @citymatch = 0
select @match = 0
if @candindustry <> @jobindustry
select @match = 0
if @strongest = 1
begin
if @ks1 = ""
select @ks1 = @key_skill_desc
else if @ks2 = ""
select @ks2 = @key_skill_desc
else if @ks3 = ""
select @ks3 = @key_skill_desc
else if @ks4 = ""
select @ks4 = @key_skill_desc
else if @ks5 = ""
select @ks5 = @key_skill_desc
end
if @match = 1
begin
select @candIndustrydesc = cat_sub_desc from t_cat_sub_cat where cat_sub_cat_id = @candIndustry
select @candPositiondesc = position_desc from t_position where position_id = @candPosition
select @candTraveldesc = travel_desc from t_travel where travel_id = @candtravel
INSERT INTO #ReturnTemp(Candidateid,
displayid,
city,
state,
Industry,
travel,
position,
hitcount)
values (@candidate_id,
@displayid,
@candcity,
@candstate,
@candIndustrydesc,
@candtraveldesc,
@candpositiondesc,
@icount)
end
end
else
begin
if @strongest = 1
begin
if @ks1 = ""
select @ks1 = @key_skill_desc
else if @ks2 = ""
select @ks2 = @key_skill_desc
else if @ks3 = ""
select @ks3 = @key_skill_desc
else if @ks4 = ""
select @ks4 = @key_skill_desc
else if @ks5 = ""
select @ks5 = @key_skill_desc
end
select @icount = @icount + 1
end
--look at other stuff
FETCH NEXT FROM Cand_cursor
into @candidate_id, @key_skill_desc, @strongest
END
CLOSE Cand_Cursor
DEALLOCATE Cand_Cursor
select * from #ReturnTemp
Hello,
I'm new to SQL. I wrote this Stored Procedure but it does not work. When I'm executing it I get these errors:
Server: Msg 170, Level 15, State 1, Procedure Test, Line 12
Line 12: Incorrect syntax near '='.
Server: Msg 170, Level 15, State 1, Procedure Test, Line 15
Line 15: Incorrect syntax near ')'.
Server: Msg 170, Level 15, State 1, Procedure Test, Line 19
Line 19: Incorrect syntax near '='.
Server: Msg 170, Level 15, State 1, Procedure Test, Line 24
Line 24: Incorrect syntax near '='
Can anyone please help me with that?
Thank you in advance.
CREATE PROCEDURE Test As
BEGIN TRANSACTION
Select PVDM_DOCS_1_5.DOCINDEX1, TableTest.DOCINDEX2, TableTest.DOCINDEX3, TableTest.DOCINDEX4
From PVDM_DOCS_1_5, TableTest
WHERE TableTest.DOCINDEX1 = PVDM_DOCS_1_5.DOCINDEX1
AND (TableTest.DOCINDEX2 Is NULL or TableTest.DOCINDEX2 ='' OR TableTEst.DOCINDEX3 Is NULL or TableTest.DOCINDEX3 ='' or TableTest.DOCINDEX4 is NULL or TableTEst.DOCINDEX4 = '');
IF TableTest.DOCINDEX2 is NULL or TableTest.DOCINDEX2 = ''
UPDATE TableTest.DOCINDEX2 = DOCINDEX2
WHERE (DOCINDEX1 IN
(SELECT DOCINDEX1
FROM PVDM_DOCS_1_5))
END IF
If TableTest.DOCINDEX3 is NULL or TableTest.DOCINDEX3 = ''
UPDATE TableTest.DOCINDEX3 = PVDM_DOCS_1_5.DOCINDEX3
WHERE (DOCINDEX1 IN
(SELECT DOCINDEX1
FROM PVDM_DOCS_1_5))
END IF
If TableTest.DOCINDEX4 is NULL or TableTest.DOCINDEX4 = ''
UPDATE TableTest.DOCINDEX4 = PVDM_DOCS_1_5.DOCINDEX4
WHERE (DOCINDEX1 IN
(SELECT DOCINDEX1
FROM PVDM_DOCS_1_5))
END IF;
DELETE PVDM_DOCS_1_5 WHERE DOCINDEX1 = DOCINDEX1
IF (@@ERROR <> 0) GOTO on_error
COMMIT TRANSACTION
-- return 0 to signal success
RETURN (0)
on_error:
ROLLBACK TRANSACTION
-- return 1 to signal failure
RETURN (1)
GO
While trying to run an .exe file which analyzes the log files from IIS 5.0, for a web-site, I get this error...
Run-time error '5'. Invalid procedure call or argument
Is it possible this error to be generated from a stored procedure ???
:confused:
Thanks !
Hello, I have the following stored procedure and the following aspx page. I am trying to connect this aspx page to the stored procedure using the SqlDataSource. When the user enters a branch number in textbox1, the autonumber generated by the database is returned in textbox2. I am not quite sure what to do to get this to execute. Can someone provide me assistance? Will I need to use some vb.net code behind?
Stored ProcedureCREATE PROCEDURE InsertNearMiss @Branch Int, @Identity int OUT ASINSERT INTO NearMiss (Branch)VALUES (@Branch)
SET @Identity = SCOPE_IDENTITY()
GO
ASPX Page
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:NearMissConnectionString %>" InsertCommand="InsertRecord" InsertCommandType="StoredProcedure" SelectCommand="InsertRecord" SelectCommandType="StoredProcedure"> <SelectParameters> <asp:ControlParameter ControlID="TextBox1" Name="Branch" PropertyName="Text" Type="Int32" /> <asp:ControlParameter ControlID="TextBox2" Direction="InputOutput" Name="Identity" PropertyName="Text" Type="Int32" /> </SelectParameters> <InsertParameters> <asp:Parameter Name="Branch" Type="Int32" /> <asp:Parameter Direction="InputOutput" Name="Identity" Type="Int32" /> </InsertParameters> </asp:SqlDataSource> <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
<asp:TextBox ID="TextBox2" runat="server"></asp:TextBox>
Details :
Reporting Services 2000, SQL 2000 database, Visual Studio . Net 2003
In Report Design view
In "Data" tab, I can see records for column 'sRCName' returned from the stored procedure(usp_GetRouteCodeData) after clicking '!' icon. When I moved to "Preview" tab, I am getting below error message.
"The value expression for the textbox €˜sRCName€™ refers to the field €˜sRCName€™. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope."
Observation : there is no value returned from the dataset on 'Fields' panel. The SP is accessing a table called tblRCM.
If I go to the Data--> Dataset --> Query, change the "Command Type" from 'Stored Procedure' to 'Text' and entered
select * from tblRCM at Query string area, the report is running fine.
Issue: This issue only happens at my laptop, my team member can create the same report using the same stored procedure without any error. The database is sitting on a server.
In the troubleshooting process, I tried to create a simple report by calling a stored procedure(CustOrderHist) from NorthWind DB in my local SQL server, I am able to see the data/value in 'Fields' panel and sucessfully view the data in 'Preview' tab.
Looks like the issue only happen on my machine, for a report that using stored procedure to access a DB sitting on a server.
I hope to hear from anyone who have encountered the similiar issue before, or, have any clue to resolve the issue.
Thanks.
Im new to stored procedure, but here's what I want, if someone can get me started and provide this as an example it would be VERY welcome!I have the following, a SP with parameter IsMale. This parameter may be empty. If it's not empty I want to add some text to my selection query: AND IsMale=paramvalueHere's the SP so far:ALTER PROCEDURE [dbo].[spFindUsersAdvanced] --declare parameters here@IsMale bitASBEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; SELECT DISTINCT BirthDate,IsMale, FROM aspnet_Users INNER JOIN tblUserData ON aspnet_Users.UserId = tblUserData.UserID WHERE tblUserData.Username<>''IF @IsMale THENBEGIN AND IsMale='True'ENDELSEBEGIN AND IsMale='False'ENDEND IFEND1. How do I check if the parameter is empty?2. How do I add the text to my selection query?
View 3 Replies View RelatedHi all,I am trying to write a stored procedure, which has two insert statements.the first insert statement, is simple which inserts data into a table and returns the primary key for the new row added. using this primary key, i am writing another insert statement, which passes a list of elements which would be then entered into another table, with the primary key of the first table.Is this possible to do in a single stored procedure? I have implemented this using two different sp, but am wondering if it can be done other way?thanks for your help!
View 10 Replies View RelatedHi,
For many to many relationship, what is the minimum number of tables required.
Hi plz read below is my scenarioI have two diff tables which stores passwords (don't ask why b'coz it was there).Now to make data in both table consistance what I need to do?plz provide optimized solution.Thanks in advance.bye bye.happy coding!!!
View 1 Replies View RelatedExperts,
I've few questions regarding SQL Server. Appreciated, If any one out there help me with it.
I would like to do T-SQL Programing and would like to know some good book. I'll mostly be doing Stored procedures,Triggers and views programming. Any advice?
On my sql server I've 10 logins. But, I cannot view these logins from the Enterprise GUI. But, When i run a query against the syslogin table, those all 10 logins shows up. Why is that?
any help would be greatly appreciated.
Access 97.................
I'm trying to construct the following SQL statement but am missing something somewhere.
I get a syntax error with the 1st FROM highlighted.
If i construct a query out of the bit that works and then construct a query on that query then i can get it to work no problem but how to mimic that in SQL ? I can't simply view the SQL of the queries because the query names replace the SQL statements.
I'm trying to SUM the 5 volumes that get returned by the TOP 5 SQL statement. The VotingUniverse_TotalVolumeByType query returns the TotalVolume which will enable me to divide the SUMmed 5 volumes by the TotalVolume to obtain a %age.
SELECT
Type,
Sum(Volume) AS Volume,
TotalVolume
FROM
>>>>>>>>>>this following bit works............
(SELECT TOP 5
VotingUniverse_TopFirmsVolumeByType.Type,
VotingUniverse_TopFirmsVolumeByType.Volume
FROM
VotingUniverse_TopFirmsVolumeByType
WHERE
(((VotingUniverse_TopFirmsVolumeByType.Volume)
In (SELECT TOP 5 VotingUniverse_TopFirmsVolumeByType.Volume
FROM
VotingUniverse_TopFirmsVolumeByType
ORDER BY Volume DESC)))
ORDER BY
VotingUniverse_TopFirmsVolumeByType.Volume;)
>>>>>>>>>>>end of bit that works
INNER JOIN
VotingUniverse_TotalVolumeByType
ON
Type = VotingUniverse_TotalVolumeByType.Type
GROUP BY
Type,
TotalVolume;
hope this makes sense to someone anyway
thanks
====
Paul
I have a xxx.exe running on SQL 2000 server as a sql job every hour. For some reason this job sometime just hung in the middle and never finish. But
if I stop it and rerun it, it will be down within 10 seconds. I am wondering
if we can set up something so that the job aborts automatically if it ran more than 10 minutes.
Hi everyone.
We are running Sql 6.5 sp5 on a Pentium II 350 Mhz with 512 Mb. RAM, 1 ScSi 4 Gb hd, one 8Gb ScSi hd and 2 IDE hd (13,6 Gb and 8Gb) computer running windows NT 4.0 sp 6 without any problems.
Now have bought a new computer. The new one is a Pentium III 733 Mhz 512 Mb Ram with 2 ScSi ultra wide 2 18Gb each and one 40 Gb Ide Hd. We have installed windows NT 4.0 sp 6 and Sql 6.5 sp 5. We have restored our database to make some tests to it. The database is 7 Gb big.
The results of our test are that the old computer is between 20% and 30% faster than the new one when doing sql instructions and using the database even knowing that the hd´s of the new computers are 4 times faster than the old ones.
Would you please give us any help or clue that would make the new computer go faster?
We know that upgrading to sql 7 will work but we need first to make this computer work better with the 6.5.
Thank you.
Hello,
When I try to run my asp file it gives me this error. :mad:
Technical Information (for support personnel)
Error Type:
Microsoft OLE DB Provider for SQL Server (0x80040E07)
Syntax error converting datetime from character string.
/conOpen_inc.asp, line 10
Browser Type:
Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; .NET CLR 1.1.4322)
Page:
GET /Default.asp
Anyone can tell me whats happening ? thank you
I often use DTS to move databases between the servers. When you choose "Transform" data and “Column Mappings and Transformation” window opens, the default option is “Append rows to destination table”.
Question: Is there a way to change the default option to “Delete rows in destination table”, so I wouldn’t have to go thru the transformation of dozens of tables every time I move databases around.
P.S. I know that I can save DTS package with my settings and use it later. The problem is that every time it might be different database or objects might change. So, it is not a solution for me.
Thanks
Dear All,
I have a client requirement. We are integrating two applications. So the problem is that, whenever some updates (meaning to say "create" , "delete" , "update") happens in the SQL DB Tables, i needed to track that data and send it to a different system.
What I have thought abt is that if SQL could generate a file with all the values deleted/updated/created in the DB, then I could take those values and do the needful. Please help me..
Thanks,
Sanjo
Hello guys,
I'm new to the forum and to MS SQL 2K.
I'm trying to a merge similar rows in a table into a single row and put them in a new table.
Example:-
This is my input table
TableA
ID A B C
------------------------
1 jk kl bj
2 sd we op
3 io po kl
1 ui gh ew
2 kl re op
1 qw kj nn
My output table should look like this
TableB
ID A1 B1 C1 A2 B2 C2 A3 B3 C3
-----------------------------------------------------
1 jk kl bj ui gh ew qw kj nn
2 sd we op kl re op
3 io po kl
Please help me on how to create my output.
Thanks in advance,
Sid.
Hi,
I have a table structure like this
TableName: Common
Columns
PartnerId: int
NativeId: int
FirstName: nvarchar(50)
LastName:nvarchar(50)
1)I should get the records with a minimum native id for a particular PartnerId,
2) if duplicates exists in the above condition i should select top 1 (first record)
How can i do it??
TIA,
sudheer
Hi,
I have been SQL developer for past 2 yrs, want to get into Administration(DBA) can any one suggest me some good articles on net and good books for this.
TIA,
sudheer.
Hi experts,
I am upgrading my database from SQL Server 2000, SP4 to SQL server 2005, SP2.
1) Is it required to recreated my user defined functions? I mean drop them and recreate them again?
2) Do I need to recreate the views and Stored procedure?
3) Do we have a checklist from Micorosoft for upgradation?
Thanks in advance
Regards
Sachin
Don't sit back because of failure. It will come back to check if you still available. -- Binu
Hey all, I need some help to build some data bases and maybe some morestuff. Please reply only if you can do it volunteerly or very cheap please;but there is incentives in time..TIA
View 4 Replies View RelatedI have a tableCreate Table Payments {paymentid int,customerid int,amount int,date datetime}What I want is the sum of the amounts of the last payments of all customers.Now the last payment of a customer is not necessarily the one with thehighest paymentid for that customer BUT it is the one with the highestpaymentid on the MOST RECENT date. We dont keep the time part just the dateso if there are more than 1 payments of a customer on a date ( and there aremany such cases ) only then the paymentid decides which is the last payment.Further the last payment may be the last as of today but I may want to findthe sum of all the last payments upto say March 1, 2003or any date. My own solution is too slow even it is correct.SELECT SUM( AMOUNT )FROM PAYMENTS AS P1WHERE PAYMENTID =( SELECT MAX( PAYMENTID ) FROM PAYMENTS AS P2 WHERE P1.CUSTOMERID =P2.CUSTOMERID AND DATE =( SELECT MAX(DATE) FROM PAYMENS AS P3 WHERE P3.CUSTOMERID = P2.CUSTOMERIDAND DATE < #9/8/03# ))What would be the most efficient solution to this.Both in SQL Server and in Access 2000thx in advance
View 3 Replies View RelatedI am using the following T-SQL to pull records from a given table that have a start time between 6:59:59PM and 7:00:00AM. However, it's not working. The SQL I'm using is listed here. What am I doing wrong?
select RecNum, convert(varchar(8), StartTime, 8) as Start_Time from TableA
where convert(varchar(8), StartTime, 8) between '18:59:59' and '07:00:00'
By the way, there are a multitude of records that I'm looking for in the table.
hi friends
I need some Help with VSA in SSIS . Whenever i open VSA for scripting , it shows the following validation errors.
Warning 1 The dependency 'EnvDTE' could not be found.
Warning 3 The dependency 'Microsoft.SqlServer.DTSRuntimeWrap' could not be found.
Warning 5 The dependency 'Microsoft.SqlServer.msxml6_interop' could not be found.
Warning 4 The dependency 'Microsoft.SqlServer.PipelineHost' could not be found.
Warning 6 The dependency 'Microsoft.SqlServer.SqlTDiagM' could not be found.
Warning 2 The dependency 'Microsoft.SqlServer.VSAHosting' could not be found.
Warning 7 The dependency 'Microsoft.SqlServer.VSAHostingDT' could not be found.
I am able to locate these dependencies in the c: programfilesmicrosoftsqlserver90SDKAssemblies.
What is the solution for this type of problem ?
thanks and regards
MAxx
Hello,
When I try to open the ReportBuilder.application file
It gives me a error message
contact vendor
PLATFORM VERSION INFO
Windows : 6.0.6001.65536 (Win32NT)
Common Language Runtime : 2.0.50727.1434
System.Deployment.dll : 2.0.50727.1434 (REDBITS.050727-1400)
mscorwks.dll : 2.0.50727.1434 (REDBITS.050727-1400)
dfdll.dll : 2.0.50727.1434 (REDBITS.050727-1400)
dfshim.dll : 2.0.50727.1434 (REDBITS.050727-1400)
SOURCES
Deployment url : file:///C:/Users/pmohan/Desktop/ReportBuilder(2).application
IDENTITIES
Deployment Identity : ReportBuilder.application, Version=9.0.3042.0, Culture=neutral, PublicKeyToken=c3bce3770c238a49, processorArchitecture=msil
APPLICATION SUMMARY
* Online only application.
* Trust url parameter is set.
ERROR SUMMARY
Below is a summary of the errors, details of these errors are listed later in the log.
* Activation of C:UserspmohanDesktopReportBuilder(2).application resulted in exception. Following failure messages were detected:
+ Downloading file:///C:/Users/pmohan/Desktop/ReportBuilder.exe.manifest did not succeed.
+ Could not find file 'C:UserspmohanDesktopReportBuilder.exe.manifest'.
+ Could not find file 'C:UserspmohanDesktopReportBuilder.exe.manifest'.
+ Could not find file 'C:UserspmohanDesktopReportBuilder.exe.manifest'.
COMPONENT STORE TRANSACTION FAILURE SUMMARY
No transaction error was detected.
WARNINGS
There were no warnings during this operation.
OPERATION PROGRESS STATUS
* [4/3/2008 3:25:51 PM] : Activation of C:UserspmohanDesktopReportBuilder(2).application has started.
* [4/3/2008 3:25:51 PM] : Processing of deployment manifest has successfully completed.
* [4/3/2008 3:25:51 PM] : Installation of the application has started.
ERROR DETAILS
Following errors were detected during this operation.
* [4/3/2008 3:25:51 PM] System.Deployment.Application.DeploymentDownloadException (Unknown subtype)
- Downloading file:///C:/Users/pmohan/Desktop/ReportBuilder.exe.manifest did not succeed.
- Source: System.Deployment
- Stack trace:
at System.Deployment.Application.SystemNetDownloader.DownloadSingleFile(DownloadQueueItem next)
at System.Deployment.Application.SystemNetDownloader.DownloadAllFiles()
at System.Deployment.Application.FileDownloader.Download(SubscriptionState subState)
at System.Deployment.Application.DownloadManager.DownloadManifestAsRawFile(Uri& sourceUri, String targetPath, IDownloadNotification notification, DownloadOptions options, ServerInformation& serverInformation)
at System.Deployment.Application.DownloadManager.DownloadApplicationManifest(AssemblyManifest deploymentManifest, String targetDir, Uri deploymentUri, IDownloadNotification notification, DownloadOptions options, Uri& appSourceUri, String& appManifestPath)
at System.Deployment.Application.ApplicationActivator.DownloadApplication(SubscriptionState subState, ActivationDescription actDesc, Int64 transactionId, TempDirectory& downloadTemp)
at System.Deployment.Application.ApplicationActivator.InstallApplication(SubscriptionState& subState, ActivationDescription actDesc)
at System.Deployment.Application.ApplicationActivator.PerformDeploymentActivation(Uri activationUri, Boolean isShortcut, String textualSubId, String deploymentProviderUrlFromExtension)
at System.Deployment.Application.ApplicationActivator.ActivateDeploymentWorker(Object state)
--- Inner Exception ---
System.Net.WebException
- Could not find file 'C:UserspmohanDesktopReportBuilder.exe.manifest'.
- Source: System
- Stack trace:
at System.Net.FileWebRequest.EndGetResponse(IAsyncResult asyncResult)
at System.Net.FileWebRequest.GetResponse()
at System.Deployment.Application.SystemNetDownloader.DownloadSingleFile(DownloadQueueItem next)
--- Inner Exception ---
System.Net.WebException
- Could not find file 'C:UserspmohanDesktopReportBuilder.exe.manifest'.
- Source: System
- Stack trace:
at System.Net.FileWebResponse..ctor(FileWebRequest request, Uri uri, FileAccess access, Boolean asyncHint)
at System.Net.FileWebRequest.GetResponseCallback(Object state)
--- Inner Exception ---
System.IO.FileNotFoundException
- Could not find file 'C:UserspmohanDesktopReportBuilder.exe.manifest'.
- Source: mscorlib
- Stack trace:
at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath)
at System.IO.FileStream.Init(String path, FileMode mode, FileAccess access, Int32 rights, Boolean useRights, FileShare share, Int32 bufferSize, FileOptions options, SECURITY_ATTRIBUTES secAttrs, String msgPath, Boolean bFromProxy)
at System.IO.FileStream..ctor(String path, FileMode mode, FileAccess access, FileShare share, Int32 bufferSize, Boolean useAsync)
at System.Net.FileWebStream..ctor(FileWebRequest request, String path, FileMode mode, FileAccess access, FileShare sharing, Int32 length, Boolean async)
at System.Net.FileWebResponse..ctor(FileWebRequest request, Uri uri, FileAccess access, Boolean asyncHint)
COMPONENT STORE TRANSACTION DETAILS
No transaction information is available.