Hi Everybody,
I am facing another probs. I have created a trigger for table Tab1 for
perticular column col1 for checking value ranges.But at time for using
insert statement it is working fine but when I am importing data from
flat file through DTS the trigger is not working.How to solve this.
Regards
Arijit Chatterjee
this is my first time using sql server, so i guess i'm def doing something wrong here..
my connection:
Code:
objConn = "Driver={SQL Server};Server=SHEMPCWEBSERVER;Database=aoms;Trusted_Con nection=yes;"
Set conn=Server.CreateObject("ADODB.Connection")
conn.Open objConn
Set rsLogin = Server.CreateObject("ADODB.recordset")
'Select the data from the database using the submitted data.
rsLogin.Open "SELECT * FROM tblUsers", conn
Response.Write rsLogin("fldUsrsName")
my error:
Code:
Microsoft OLE DB Provider for ODBC Drivers error '80040e4d'
[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'SHEMPCIUSR_XBOARDS-VRORER9'.
/aoms/testdb.asp, line 6
can anyone see what i'm doing wrong?
Thanks
Shem
Hello...
I am stuck with update statement...
I am having 3 tables abc,def and xyz...
i want to update column1 of abc with column2 of def values (here the probs is i dont have any common fields between them)
i am having common values between def(column10) and xyz(column10_new)
so i am using inner join something like below....
but this looks like wrong to me...bcoz when i do this (only select):
select column2 FROM def inner join xyz on def. column10 = xyz. column10_new
i am getting different as compared to the update below....
UPDATE abc
SET column1 = column2 FROM def inner join xyz
on def. column10 = xyz. column10_new
can anyone suggest how to solve this update???
Thanks
Hi Group,I am trying to display the multiplication through this way----------------------select 1163436036*100----------------------Getting the error============================Server: Msg 8115, Level 16, State 2, Line 1Arithmetic overflow error converting expression to data type int.============================For that reason I was tried to convert that to nvarchar------------------------select convert(numeric(36,2),1163436036*100)------------------------But still getting the error=============================Server: Msg 8115, Level 16, State 2, Line 1Arithmetic overflow error converting expression to data type int.=============================Please help me to solve it out..Thanks and RegardsArijit Chatterjee
View 3 Replies View RelatedI keep getting errors while trying to install SQL 2000 enterprise edition.....
Internal Error: Contact MS Tech Support
Help !!!
Hi All,
I have this sql syntax which displays the records within the xml but instead of displaying 4 records (3 records relating to the last question ID) but instead resulting in only two records picking only the first options 'Unhelpful'.
Definitely doing something wrong here, please advise!
DECLARE @doc xml
SET @doc =
'<DivisionName>
<QuestInfo Custref="18759" SubDate="2006-01-01T00:00:00"
Polref="30018759" AgentID="4189" ClaimRef="14024-5647-890"/>
<DVName>Ho</DVName>
<DvcodeNo>1</DvcodeNo>
<ClaimGroup>
<CustSurveyNo>4</CustSurveyNo>
<ClaimGroupType>Water</ClaimGroupType>
<Questions>
<QuestionID>45</QuestionID>
<Answer>
<AnswerID>43</AnswerID>
<Ansoption />
</Answer>
</Questions>
<Questions>
<QuestionID>34</QuestionID>
<Answer>
<AnswerID>13</AnswerID>
<Ansoption>
<Options>Unhelpful</Options>
</Ansoption>
</Answer>
</Questions>
</ClaimGroup>
</DivisionName>'
DECLARE @docHandle int
EXEC sp_xml_preparedocument @docHandle OUTPUT, @doc
SELECT *
FROM
OPENXML(@docHandle, '/DivisionName/ClaimGroup/Questions/Answer/
Ansoption', 2)
WITH
(DVName varchar (20) '../../../../DVName',
DvcodeNo int '../../../../DvcodeNo',
CustSurveyNo int '../../../CustSurveyNo',
ClaimGroupType varchar (20) '../../../ClaimGroupType',
QuestionID int '../../QuestionID',
AnswerID int '../AnswerID',
Ansoption varchar (30)'Options')
EXEC sp_xml_removedocument @docHandle
Hi folks,
I got a strange Problem with this statement:
select * from [db1].[dbo].[table1] AS db1
INNER JOIN [db2].[dbo].[table1] AS db2
ON (db1.Text = db2.Text)
Text fields are both nvarchar(50)
I think this should work, but it doesnt?
I got a SQL Server Error 446
I know, text compares are not that good, but it exists no other way....
Thanks for any advice!!!
I have production server 2000. The server gets disconnected sometime by itself and sometime it is working fine.Sometimes it even doesn't get restarted. Is there any problem with service packs and some performance issues.
Can you SQL guru give me best suggestion and how should i proceed.
when i update i want only date portion tp be displayed from datetime datatype...
create table temp11 (datecolumn datetime)
insert into temp11 values (getdate ())
insert into temp11 values (getdate ())
insert into temp11 values (getdate ())
insert into temp11 values (getdate ())
now when i am running this query,i am getting what i want...
select convert (varchar, datecolumn,111) from temp11
but when i am tyring to update in the temp11 table using the below query...
update temp11
set datecolumn = convert (varchar, datecolumn,111)
i am getting date and time as well like...
2008-01-14 00:00:00.000
2008-01-14 00:00:00.000
2008-01-14 00:00:00.000
2008-01-14 00:00:00.000
i only want the date portion in my updated new table.....
any suggestions plzzzzzzzzz
is it poss thru any sql query???or shud be done in the front end only???
Hi All,
I've designed a simple sales report , first grouped by Transport type and then by item.
Example
Category Item Sales
DHL A 2000
DHL C 1500
DHL E -1700
AmEx A 200
Amex E 20
-------------------------------
when click on sales under group DHL it shouild sort -say descending-
so result should be
DHL A 2000
DHL C 1500
DHL E -1700
instead I get results
DHL A 2000
DHL E -1700
DHL C 1500
this is just an example but this is the sort of results I get when I try to use interactive sorting.
Any Ideas?
thanks
Sonny
You notice after installing SQL Server 2005 that there is a warning message in the SQL Server log that SQL Server cannot lock pages in memory. What must you do to allow SQL Server to lock memory pages in memory?
After installing SQL Server, your customer complains that transactions are slow. You notice that there is excessive I/O in the TempDb. You want to move TempDb to another set of disks, so you have the sytem administrator configure two more logical disks on the server. What must you do to configure the SQL Server TempDb on this new set of disks?
You are instructed to install SQL Server in an Active / Active cluster environment. What two network related pieces of information must you have prior to performing the installation?
Has anyone faced problems in calling one stored procedure from within another stored procedure ? I am considering using nested SPs, and would appreciate any inputs on it.
Thks,
SC
Hi All,
It would be great if anybody could help me how to write a T-SQL script to generate a report in spreadSheet from a table in SQLserver 2000 database.
And this report should be generated as per the following timings(Sheduled timings only).<script></script>
As well it should be able to send a mail regarding the availability of the report to user groups.
Incase if the generation of report fails then we need to send a mail reg the failure.
Any HELP is greatly appreciated!!!
Thanks in Advance!!!
Regards,
Sashi
Hi:
Am trying to import XLS data into SQL 2005 SP2 thro a SSIS Data Flow task. My Excel Connection string has IMEX=1, ImportMixedTypes is set to Text and the typeguessrows is set to 0.
Import works fine for cells of Format Text, but when I have a large number (in a general Format cell) it gets converted into scientific notation(e.g. 3.234175e+7) in the table.
What am I doing wrong?
TIA
Kar
Hi. I am trying to figure out the code for sorting a manual (non-identity) number column in my table. the purpose is to
show the user's pictures in perfect order (1,2,3,4,5,6...).
The Jist of my problem... When a user first inserts six pictures, he gets:
|1|
|2|
|3|
|4|
|5|
|6|
All is good. But, say he deletes picture |3|. Now the list order looks like this:
|1|
|2|
<- |3| is removed
|4|
|5|
|6|
And, then he inserts two more pictures, now he his this:
|1|
|2|
|4|
|5|
|6|
|7| <- |7| & |8| are added
|8|
What i want to acheive is a "reshuffling" of the number order every time a picture is removed. So, when |3| is removed, |4| becomes |3|, |5| becomes |4| and so on. There should never be a gap in the order.
I am new to stored procedures, and have been trying to figure this out. Below is my guesswork:
Code:
ALTER PROCEDURE dbo.sp_NewPersonalPic
(
@photo_name VARCHAR(50) = NULL,
@photo_location VARCHAR(100) = NULL,
@photo_size VARCHAR(50) = NULL,
@user_name VARCHAR(50) = NULL,
@photo_caption VARCHAR(150) = NULL,
@photo_default BIT = NULL,
@photo_private BIT = NULL,
@photo_number INTEGER = NULL,
@photo_date DATETIME = NULL
)
AS
BEGIN
SELECT @photo_date = CONVERT(DATETIME,convert(char(26), getdate(), 109))
END
BEGIN
SET @photo_number = 1
SELECT
@photo_number = (
SELECT COUNT(*)
FROM dbo.PersonalPhotos b
WHERE
a.photo_date < b.photo_date
)
FROM
dbo.PersonalPhotos a
ORDER BY
a.photo_date
END
BEGIN
My thinking is that it would be a safe bet to use the "photo_date" column as a litmus for my "photo_number" column (ie, the most recent record inserted by the user will always be at a later date than the previously inserted record). So:
photo_number photo_date
|1| 2006-06-26 21:43:36.653
|2| 2006-06-26 21:43:50.000
|3| 2006-06-26 21:45:25.217
|4| 2006-06-26 21:45:33.763
|5| 2006-06-26 22:39:42.670
|6| 2006-06-26 22:39:49.200
If |3| is removed above, the numbers are reordered based on the time of entry sequence.
Any suggestions on how to acheive this in my stored procedure? Currenly, i get the correct order, but it goes crazy when i delete and add.
Thanks and sorry for the verbose post.