|
You will need to set up the SQL user with adequate permissions on your database. That's it.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Mycroft Holmes wrote: You will need to set up the SQL user with adequate permissions on your database. That's it.
ok I m trying; any good resources you know except msdn?
shankbond
|
|
|
|
|
Hi,
Actually when you remotely perform any SQL request (using OPENROWSET , BLOB type data) SQL server
need the AuC first. That you have the permission to perform this operation.
Md. Marufuzzaman
|
|
|
|
|
Hey,
I have this table:
JAN FEB MAR APR
--- --- --- ---
10 10 10 10
Suppose I'm in March now, how would I total the values of the two previous months (ie. JAN and FEB) to have 20
Please help.
All generalizations are wrong, including this one!
(\ /)
(O.o)
(><)
|
|
|
|
|
Well, if that was what I wanted to do, I wouldn't start from there.
Has this design been forced on you, or are you free to choose your own?
Henry Minute
Do not read medical books! You could die of a misprint. - Mark Twain
Girl: (staring) "Why do you need an icy cucumber?"
“I want to report a fraud. The government is lying to us all.”
|
|
|
|
|
It is forced on me, here's the big picture:
Jan Feb Mar Apr
--- --- --- ---
Budget 10 10 10 10
Actual 10 5 10 0
... 0 0 0 0
... 0 0 0 0
... 0 0 0 0
All generalizations are wrong, including this one!
(\ /)
(O.o)
(><)
|
|
|
|
|
Sorry to be so long in replying.
I'm with Luc here, unless somebody else comes up with a viable solution I'd be investigating PIVOT. I have never used it, so I can't be of much help.
Good Luck!
Henry Minute
Do not read medical books! You could die of a misprint. - Mark Twain
Girl: (staring) "Why do you need an icy cucumber?"
“I want to report a fraud. The government is lying to us all.”
|
|
|
|
|
Thanks Henry, Yes, it can be solved using pivot tables or the decode function but my problem is far more complicated to be solved by either of them alone!
Thanks for your time mate!
All generalizations are wrong, including this one!
(\ /)
(O.o)
(><)
|
|
|
|
|
Is that a SQL table or a flatfile table such as Excel?
"My interest is in the future because I'm going to spend the rest of my life there." - Charles F. Kettering
|
|
|
|
|
Hey Jerry,
It's a SQL Table, but dont worry, I've already found another way round, although it's not professional but it solves my problem. It was for a crystal report file and I figured out I can simply create a typed dataset with the exact names and data types of those of the generated query, generate the datatable fields from the invoking form using ado and set the datasource of the report to the dataset I just created.
Thank you for your time Jerry!
All generalizations are wrong, including this one!
(\ /)
(O.o)
(><)
|
|
|
|
|
Hi,
the natural way a datatable grows is by adding rows, not columns. So a more natural design would have only two columns/fields, say "month" and "total", hence it would look like:
MONTH TOTAL
200901 10
200902 10
200903 10
200904 10
and now it becomes very easy to:
- add another month
- select some months and perform calculations on their data
Luc Pattyn [Forum Guidelines] [My Articles]
DISCLAIMER: this message may have been modified by others; it may no longer reflect what I intended, and may contain bad advice; use at your own risk and with extreme care.
|
|
|
|
|
Hey Luc,
Thanks but I'm kinda forced to this table structure:
Jan Feb Mar Apr
--- --- --- ---
Budget 10 10 10 10
Actual 10 5 10 0
... 0 0 0 0
... 0 0 0 0
... 0 0 0 0
Is there any way to do it with this structure??
All generalizations are wrong, including this one!
(\ /)
(O.o)
(><)
|
|
|
|
|
Muammar© wrote: Is there any way to do it with this structure??
Anything is possible, however I have never chosen such hard way of doing things, so I don't know any details.
FWIW: there is a PIVOT command that rotates a table.
Luc Pattyn [Forum Guidelines] [My Articles]
DISCLAIMER: this message may have been modified by others; it may no longer reflect what I intended, and may contain bad advice; use at your own risk and with extreme care.
|
|
|
|
|
Thanks Luc, Yes, it can be solved using pivot tables or the decode function but my problem is far more complicated to be solved by either of them alone!
Thanks for your time mate!
All generalizations are wrong, including this one!
(\ /)
(O.o)
(><)
|
|
|
|
|
Absolutely. That's why I said, I wouldn't start from there. With people being forced to use designs like that, it's little wonder that some of the questions asked in the forums seem to be a bit daft.
Henry Minute
Do not read medical books! You could die of a misprint. - Mark Twain
Girl: (staring) "Why do you need an icy cucumber?"
“I want to report a fraud. The government is lying to us all.”
|
|
|
|
|
The answer is pretty simple
SELECT (SUM(JAN) + SUM(FEB)) LAST_2_MONTHS_RESULT FROM TBLMONTH
This query will work even for if the column values are null.
e.g.
JAN FEB MARCH APRIL
--- --- ----- -----
10 10 10 10
NULL 20 11 12
20 40 1 1
30 NULL 5 6
NULL NULL NULL NULL
The answer for such a case is
LAST_2_MONTHS_RESULT
-------------------
130
Hope it helps
Niladri Biswas
|
|
|
|
|
Thanks mate!
That was quite an answer but still it doesnt answer my question, all you did is add the previous two months literally, anyways, I've already worked that out using a stupid syntax from C# that has nothing to do with SQL. Thanks anyways mate!
All generalizations are wrong, including this one!
(\ /)
(O.o)
(><)
|
|
|
|
|
Hi,
Does this solution solved your problem. If so, please close the issue by marking the
question's answer as a "good answer".
If not then please response so that we can come to know where is the precise problem and
will help with all posible efforts.
Because if you get the right solution and is not marking it as good answer, then it is not
getting close and is listing in the Unanswered questions section , so it is becoming
difficult to filter out which are the true unresolved issues.
Please co-operate.
Niladri Biswas
|
|
|
|
|
Thanks again, but I already said your earlier replay doesnt answer my question and it would be misleading to mark it as a good answer.
All generalizations are wrong, including this one!
(\ /)
(O.o)
(><)
|
|
|
|
|
Hi,
I'm in need of a stored procedure which takes 5 input values and then stores them in 'Table A'. This is simple enough.
However i also need to get data from a 2nd table 'Table B', return 5 random records(1 column only) and then input these records into 'Table A' along with the other data mentioned above. (Making a single record / row of 10 bits of information)
Hopefully this makes sense.
I know how to create the select statement for the above, but how do i then capture this data within the stored procedure and use it in the insert statement?
Thanks!
|
|
|
|
|
Your question is not very clear, because
a) You never specified if the data in TableA has been inserted already and
then you want to update the 6th column of TableA [ As you
specified that the first 5 fields of TableA will have values supplied from
the Stored Proc which indicates that the 6th column will have values from
TableB ] with the values from TableB.
b) The Table Structure of both the tables are absent.
c) What will be the ultimate output [ Any rough sketch will make the
picture more clear]
d) Whether Table2's column will have predefined value or not. If not
whether the values needs to be inserted at runtime in TableB and then it
has to be fetched and next to be inserted in TableA?
e) Any dependency is lying between the two tables(e.g. Referential
Integrity Constraint)
However, I am assuming that TableA has the following structure
TableA
Col1 Col2 Col3 Col4 Col5 Col6
All are Varchar(50) types.
N.B.~ Col6 will have the value from TableB
TableB
RandomCol
Again Varchar(50)
And my Stored Proc is as under:
ALTER PROCEDURE InsertRecordsIntoTableA
-- Add the parameters for the stored procedure here
(
@VarCol1 AS VARCHAR(50),
@VarCol2 AS VARCHAR(50),
@VarCol3 AS VARCHAR(50),
@VarCol4 AS VARCHAR(50),
@VarCol5 AS VARCHAR(50)
)
AS
BEGIN
-- VARIABLE DECLARATION
DECLARE @RANDOMCOLUMNVALUES VARCHAR(50)
-- STEP 1: DECLARE A CURSOR
DECLARE MYRANDOMCURSOR CURSOR FOR
SELECT RANDOMCOL
FROM TABLEB
-- STEP 2: OPEN THE CURSOR
OPEN MYRANDOMCURSOR
FETCH MYRANDOMCURSOR INTO @RANDOMCOLUMNVALUES
-- STEP 3: START THE LOGIC
WHILE @@Fetch_Status = 0
BEGIN
-- STEP 4: INSERT THE COMBINED RECORDS INTO TABLE A
INSERT INTO TABLEA(COL1,COL2,COL3,COL4,COL5,COL6)
VALUES(@VarCol1,@VarCol2,@VarCol3,@VarCol4,@VarCol5,@RANDOMCOLUMNVALUES)
-- STEP 5: GET THE NEXT RECORD
FETCH MYRANDOMCURSOR INTO @RANDOMCOLUMNVALUES
END
--STEP 6: CLOSE THE CURSOR
CLOSE MYRANDOMCURSOR
--STEP 6: DEALLOCATE THE CURSOR
DEALLOCATE MYRANDOMCURSOR
END
Hope this answers you question.
If not, please tell your problem precisely with necessary
description
It can even be solved without using CURSOR and with a while loop
Please be specific about your question so that others can understand
properly.
Niladri Biswas
|
|
|
|
|
Good Day all i have the Following Query
select distinct convert(varchar(10), (convert(smalldatetime, c.descr)), 120) [Date], tt.Sess [Session], m.descr [Subject], ctyp.Abrev [PaperType], ct.Number [PaperNumber], a.GrpName [Campus], a.Duration, CASE WHEN MAV.STUDENTS IS NULL THEN a.Students ELSE MAV.STUDENTS END [Students], v.descr [Venue], v.Capacity, s.descr [Staff]
-- into #Timetable
from sol_actv_time tt
inner join tbl_clmn c on c.id=tt.Dy
inner join tbl_actv a on a.id = tt.actv
inner join tbl_cntc ct on ct.id = a.cntcid
inner join tbl_cntc_typ ctyp on ctyp.id = ct.cntctyp
inner join tbl_modl m on m.id = ct.modlid
inner join tbl_term t on t.id = a.term
left outer join sol_actv_venu ttVenu on ttVenu.actv = a.id
left outer join mtm_actv_venue mav on mav.venue=ttVenu.Venu and mav.actv=a.id
left outer join tbl_venue v on v.id = ttVenu.Venu
left outer join sol_actv_staff ttStaff on ttStaff.Actv = a.id
left outer join tbl_staff s on s.id = ttStaff.Staff
select activity.* from #Timetable activity for xml auto, elements
I get the Following Error
Msg 295, Level 16, State 3, Line 2
Conversion failed when converting character string to smalldatetime data type.
Thanks
Vuyiswa Maseko,
Few companies that installed computers to reduce the employment of clerks have realized their expectations.... They now need more and more expensive clerks even though they call them "Developers" or "Programmers."
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.somee.com
http://www.vuyiswamaseko.tiyaneProperties.co.za
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/
|
|
|
|
|
I don't have sql server in front of me so I can't test unfortunately.
What is the data type of c.descr?
|
|
|
|
|
Its a String but its in a Date Format.
Thanks i have resolved this by accepting the data as data from my presentation layer.
Thanks
Vuyiswa Maseko,
Few companies that installed computers to reduce the employment of clerks have realized their expectations.... They now need more and more expensive clerks even though they call them "Developers" or "Programmers."
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.somee.com
http://www.vuyiswamaseko.tiyaneProperties.co.za
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/
|
|
|
|
|
Hi All
I'm not a SQL guy, so I'm stuggling a bit. I hope you guys can help me.
I have an SSIS package which copies tables from one server to another. However, the source tables will, from time to time, get new fields added to it. My understanding of SSIS is that when you are copying a table, you either need the destination table to already exist, or you need to create the table via a predetermined table build script. Is there a way of copying the source table to the destination regardless of what fields are in it?
(we could just change the SSIS package to reflect the changes, but it can take over a week for our DBA's to swap it out for us)
Any help would be greatly appreciated.
Thanks
oooo, the Jedi's will feel this one....
|
|
|
|