|
in SQL Analyzer, I was able to connect and login under SQL auth. I also able to login using Window Auth. but my query comes back empty. In another code, I used
<asp:sqldatasource id="SqlDataSource1" runat="server" connectionstring="<%$ ConnectionStrings:dbReMUSConnectionString %>"
="" selectcommand="SELECT .................... ">
on the same db and I had no problem.
I also using vs 2005 and the db is a sql 2000 db
Thanks
|
|
|
|
|
where the rest of my asp tag goes? Do I need to put something around them?
|
|
|
|
|
hi
how should i retrieve View's SQL statement in SQL Server.
i am using OleDb and i want it to be compatible with all the data Systems.
for example i can retrieve the Query statements from an Access database (with GetOleDbSchemaGuid() ) but i am not able to do it with SQL Server (it dowsnt support retrieving views that way).
is there another way or a specific SQL statement to do so??
thanks in advance
-- modified at 6:20 Monday 26th February, 2007
|
|
|
|
|
I used profiler to create a trace of all the queries from SSMS to the database and came up with this:
<br />
SELECT ISNULL(smv.definition, ssmv.definition) AS [Definition]<br />
FROM sys.all_views AS v<br />
LEFT OUTER JOIN sys.sql_modules AS smv ON smv.object_id = v.object_id<br />
LEFT OUTER JOIN sys.system_sql_modules AS ssmv ON ssmv.object_id = v.object_id<br />
WHERE (v.type = 'V')and(v.name=N'{my_view_name}' and SCHEMA_NAME(v.schema_id)=N'dbo')<br />
Just replace {my_view_name} with the name of the view you're interested in. Also, if the view is using a schema other than 'dbo' make sure to change that as well.
|
|
|
|
|
Hello everybody,
I have a Attendance table, which has columns Date. ShiftInTime, ShiftOutTime, EventTime, Login_Type.
Now for every EventCode= "I" or "O" there are two records. That means for a day say 07/10/2006 as Workdate there are two records like,
EmpID Date EventTime EventCode
1 07/10/2006 9:00 I
1 07/10/2006 17:15 O
NOW.. I want a report in Table or Crystal report which will have a single record for a single Workdate..as folllows
EmpID WorkDate ShiftInTime ShiftOutTime InTime OutTime
1 07/10/2006 09:00 17:00 9:00 17:15
Can you please help me out..I'm usng a table in ASp
Praveen.K
System Engineer
Graviton Technologies Pvt.Ltd
|
|
|
|
|
select * from table t1 inner join table t2
on t1.date = t2.date
Regards,
Sylvester G
Senior Software Engineer
Xoriant Solutions
sylvester_g_m@yahoo.com
|
|
|
|
|
Sir,
U'r suggestion is not enough...because i am using onlu only table ,,,my quear gave me theattendance report like i shown above...but Can i simply make the inner join with the same table?
i need the out put as follows
----------------------------------
ID |Name |InTime |OutTime|
1 |xxxxxxxxxxx| 09:05 | 06:01
2 |sdsssdddddd| 06:15 | 06:15
-----------------------------------
Like that..But at present they are displaying as i explainned in my first question.
Praveen.K
System Engineer
Graviton Technologies Pvt.Ltd
|
|
|
|
|
Try this:
SELECT main.EmpId, main.Date,
(SELECT i.EventTime FROM Attendance i
WHERE i.EmpID = main.EmpId AND
i.Date = main.Date AND i.Login_Type = 'I') AS ShiftInTime,
(SELECT o.EventTime FROM Attendance o
WHERE o.EmpID = main.EmpID AND
o.Date = main.Date AND o.Login_Type = 'O') AS ShiftOutTime
FROM Attendance main
the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
thanks for u'r advide,
But I am using only one table namedv History with following tables
EmpId,EmpName,ShiftIn,ShiftOut,EventTime,LoginType From Event Time I am extracting the values based on the LoginType,,Sir u got me...That is only one table is there....(History)....One doubt regarding that u specified the main,i,o as defrent tables or not?
Praveen.K
System Engineer
Graviton Technologies Pvt.Ltd
|
|
|
|
|
i, main and o are not different tables. They are all table aliases. If you look at the syntax carefully, you will see that this is a way to use items from the same table in sub-items. Change the references from Attendance to History, but leave the table aliases alone.
the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Sir,
I tried but i am getting the error message as "At most one record can be returned by this sub query" ?
This is the code thai i tried
SELECT main.UserName, <br />
(SELECT i.EventTime FROM History i <br />
WHERE i.FuncCode="0") AS InTime, <br />
(SELECT o.EventTime FROM History o <br />
WHERE o.FuncCode="10") AS OutTime <br />
FROM History main
Any chance of mistake in this code?
Praveen.K
System Engineer
Graviton Technologies Pvt.Ltd
|
|
|
|
|
That'll teach me to do it away from SQL Server. Here you go:
SELECT
h.EmpID,
h.[Date],
h.EventTime AS ShiftInTime,
h2.EventTime AS ShiftOutTime
FROM
History h
LEFT OUTER JOIN
History h2
ON
h.EmpID = h2.EmpID AND
h.[Date] = h2.[Date] AND
h2.EventCode = 'O'
WHERE
h.EventCode = 'I'
the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Sir,
Some changes i made from u'r query ..any way u'r concept worked here,,,Thanks for u'r assistance sir
This is the code i used.....
SELECT h.UserName, h.EventDate, h.EventTime AS InTime, h2.EventTime AS OutTime, h.Dept<br />
FROM History AS h LEFT JOIN History AS h2 ON (h.UserName = h2.UserName) AND (h.EventDate = h2.EventDate)<br />
WHERE (((h.EventDate)=#2/24/2006#) AND ([h.FuncCode]="0") AND ([h2.FuncCode]="10") AND ((h.Dept)="Engg"));
Thanks and Regards
Praveen.K
System Engineer
Graviton Technologies Pvt.Ltd
|
|
|
|
|
i have 3 columns in my table the room,timeStart,timeEnd.
For example i have inserted this data
room: room1
timeStart: 2007-03-02 8:00 AM
timeEnd: 2007-03-02 3:00 PM
Here's the data that will not be accepted if try to insert
room: room1
timeStart: 2007-03-02 9:00 AM
timeEnd: 2007-03-02 4:00 PM
because the timestart is between the timeStart and the timeEnd. Room1 will only be occupied after 3:00 PM.
Can you help me with this?.
Thank you very Much
Bernie
|
|
|
|
|
Hmm. You and this guy Clickety[^] seem to be trying to solve the same problem. Coincidence? Homework?
the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
please help me getting a portable data access layer code which independent of language
thanks in advance
Tony
|
|
|
|
|
tonyalex wrote: please help me getting a portable data access layer code which independent of language
Have you looked at NHibernate? It will help you build a language independent DAL.
|
|
|
|
|
Tony
DevExpress Persistent Objects is pretty good. I've used it quite a bit now and I have to say that I like it.
the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Sorry,
What i meant is the DAL Source Code that is given by the microsoft for all its .net language.........
|
|
|
|
|
tonyalex wrote: What i meant is the DAL Source Code that is given by the microsoft for all its .net language.........
What DAL source code? Microsoft does not supply DAL source code.
|
|
|
|
|
yes, microsoft is providing code for the data access layer for the our custom application,think some thing like SQLHELPER
|
|
|
|
|
tonyalex wrote: yes, microsoft is providing code for the data access layer for the our custom application,think some thing like SQLHELPER
That is part of the Enterprise Library. It is not a DAL, it is designed to help you create a DAL more easily.
|
|
|
|
|
Can anybody please tell me in SQL Server how should I get the records with their row numbers in a table. Like we use Rownum in Oracle. Thank you.
Regards,
Mohd. Abdul Aleem,
S/W Engineer
Akebono Soft Technologies
aleem_abdul@akebonosoft.com.
|
|
|
|
|
indian143 wrote: Can anybody please tell me in SQL Server how should I get the records with their row numbers in a table.
You need SQL Server 2005. However, you should realise that row numbers are a fiction. Oracle and SQL Server 2005 supply a number based on the order that the rows are returned and have little (only incidental) or no bearing on how the data is actually stored.
Anyway, the answer is ROW_NUMBER[^]
|
|
|
|
|
Could you please tell me that dont I have any other option to get it SQL Server 2000. Please. Thank you.
Regarsd,
S/W Engineer
Akebono Soft Technologies
aleem_abdul@akebonosoft.com.
|
|
|
|