|
If you want to advertise with CP then go through the usual channel.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Good Day All
i have the Following sp
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_Timetable_View] @selectionType varchar(30), -- either Venues, Staff, Subjects, Curricula etc...
@selectedItems ntext, @selectedTerms ntext
AS
*/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[tempdb].[dbo].[#temp]'))
drop table [#temp]
--Creation of Temp1
SELECT MTN.ID,S.DESCR,ISNULL(MTN.CYCLETEMPLATE,C.CYCLES) AS CYCLETEMPLATE,MTN.ACTV AS [ACTV]
into #temp
FROM TBL_STAFF S
INNER JOIN MTM_ACTV_STAFF MTN ON
S.ID = MTN.STAFF
LEFT OUTER JOIN MTM_ACTV_STAFF_CYCLE C
ON C.IDL = MTN.ID
--STEP 3 HERE WE ARE CREATING A TEMP TABLE
--CHECK IF THE TABLE EXISTS FIRST IF IT DOES DROP IT
--HERE WE ARE CREATING A FIELD CYCLEIDlIST THAT IS EMPTY THAT WILL BE POPULATED LATER
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[tempdb].[dbo].[#TempSummary]'))
drop table [#TempSummary]
SELECT DESCR, dbo.GetSortedString(Cast(NULL AS varchar(8000))) AS CycleIdList,ACTV
INTO #TempSummary
FROM #temp (NOLOCK)
GROUP BY DESCR,ACTV
ORDER BY DESCR,ACTV
--CHECK THE TABLE CONTENTETS
--SELECT * FROM TempSummary
--WE ONLY SAW NAMES THAT ARE NOT DUPLICATED AND NULLS IN THE OTHER FIELD
--HERE WE UPDATE THE TEMPSUMMARY TABLE AND DO SOME CONCATINATING AND THIS IS VERY FAST,
--STEP 4
UPDATE #TempSummary
SET CycleIdList = dbo.Concat(#TempSummary.Descr,actv)
--SELECT * FROM TempSummary
--LETS CHECK THE TEMP SUMMARY TABLE
--select * from TempSummary
--IT CONTAINS ALL THE DATA THAT WE WANT :)
--Select DISTINCT Descr AS [Staff],dbo.DistinctList(Cycles,Descr) As [Cycles]
--into Temp2 From temp
Declare @xmldoc int
EXEC sp_xml_preparedocument @xmldoc OUTPUT, @selectedItems --Create an internal representation of the XML document
-- turn the xml into a table with characteristics of the given table
SELECT *
INTO #selectedItems
FROM OPENXML ( @xmldoc , '/Root/Tags' , 2)
WITH (
[TagID] int
)
EXEC sp_xml_removedocument @xmldoc
create table #selectedActvs (
id int
)
-- From the list of selected items of whichever type, find the list of activities these relate to; i.e. which activities are envolved in the selection
if (@SelectionType = 'Venues') begin
-- show the timetable for the selected activities
Insert into #selectedActvs
(id)
select distinct Actv [Id]
from sol_actv_venu
where venu in ( select TagID from #selectedItems )
end
if (@SelectionType = 'Staff') begin
Insert into #selectedActvs
(id)
select distinct Actv [Id]
from sol_actv_staff
where staff in ( select TagID from #selectedItems )
end
if (@SelectionType = 'Subjects') begin
Insert into #selectedActvs
(id)
select distinct Actv [Id]
from sol_actv_time
inner join tbl_actv a on a.id = sol_actv_time.Actv
inner join tbl_cntc ct on ct.id = a.cntcID
where modlID in ( select TagID from #selectedItems )
end
if (@SelectionType = 'SubjectContainer') begin
Insert into #selectedActvs
(id)
select distinct Actv [Id]
from sol_actv_time
inner join tbl_actv a on a.id = sol_actv_time.Actv
inner join tbl_cntc ct on ct.id = a.cntcID
inner join tbl_modl m on m.id=ct.modlid
inner join mtm_modl_container mc on mc.modl=m.id
where mc.container in ( select TagID from #selectedItems )
end
if (@SelectionType = 'Curricula') begin
Insert into #selectedActvs
(id)
select distinct sa.Actv [Id]
from sol_actv_time sa
inner join mtm_subj_strm_actv ss on ss.actv = sa.actv
inner join mtm_curr_strm cs on cs.subjstrmid = ss.strm
inner join tbl_curr_strm c on c.id = cs.currstrmid
where c.curr in ( select TagID from #selectedItems )
end
if (@SelectionType = 'Curriculum Streams') begin
Insert into #selectedActvs
(id)
select distinct sa.Actv [Id]
from sol_actv_time sa
inner join mtm_subj_strm_actv ss on ss.actv = sa.actv
inner join mtm_curr_strm cs on cs.subjstrmid = ss.strm
inner join tbl_curr_strm c on c.id = cs.currstrmid
where c.id in ( select TagID from #selectedItems )
end
EXEC sp_xml_preparedocument @xmldoc OUTPUT, @selectedTerms --Create an internal representation of the XML document
-- turn the xml into a table with characteristics of the given table
SELECT *
INTO #selectedTerms
FROM OPENXML ( @xmldoc , '/Root/Tags' , 2)
WITH (
[TagID] int
)
EXEC sp_xml_removedocument @xmldoc
select distinct tt.Dy, tt.Sess, m.descr as [Code], m.LongName as[Description], ctyp.Abrev, ctyp.Descr as [Type], ct.Number,
a.GrpName, a.GrpNumber, a.Duration,
CASE WHEN MAV.stud IS NULL THEN a.Students
ELSE MAV.STUD END as [Students],
v.descr as [Venue], v.Capacity,
s.descr as [Staff], m.id as [ModlID], t.Descr as [Term]
, ISNULL(temp.CYCLEIDLIST,t.Descr) as [StaffTerm]
, dates.descr [Date],a.Length as [Length]
from sol_actv_time tt
inner join tbl_clmn dates on dates.id=tt.dy
inner join tbl_actv a on a.id = tt.actv
inner join #selectedActvs SelectedActvs on SelectedActvs.ID = a.id -- filter the list of activies to be shown by the activities which fall in the selection
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 --This will become t.id = actv.term when terms move to activities
inner join #selectedTerms selTerms on selTerms.TagID = a.term
left outer join sol_actv_venu ttVenu on ttVenu.actv = a.id
left outer join SOL_ACTV_VENU mav on mav.venu=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 mtm_actv_staff mas on mas.actv=ttStaff.actv and mas.staff=ttStaff.Staff
left outer join tbl_term staffTerm on staffTerm.id=mas.cycletemplate
left outer join tbl_staff s on s.id = ttStaff.Staff
left outer join [#TempSummary] temp on temp.descr = s.descr
order by tt.dy, tt.sess
This sp is fine and working Perfectly and i have a UDF defined like this
ALTER FUNCTION [dbo].[Concat] (@Name varchar(50),@Actv int)
RETURNS varchar(max)
WITH EXECUTE AS CALLER
AS
BEGIN
Declare @s varchar(max)
SET @s = ''
SELECT @s = @s + IsNull(',' + Cast(Cycletemplate AS varchar), '')
FROM #Temp (NOLOCK)
WHERE #Temp.Descr = @Name And Temp.Actv = @Actv
GROUP BY Cycletemplate
ORDER BY Cycletemplate
IF (@S IS NOT NULL) AND (@S <> '') SET @S = SubString(@s, 2, Len(@S)-1)
RETURN @S
END
now the UDF is used in the Bolded part of the Sp
i know i cant use a #temp table in a UDF , i want to access a temp table created in the Sp i want to use it in the UDF. i can use table Variables, but is there a Global table variable ? that i can use, that is created in the sp but used in the UDF. I need your Advice.
Thanks
Vuyiswa Maseko,
Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code.
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.com
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/
|
|
|
|
|
You can use global temp tables ##TableName, this may work. Caveat, I have never attempted this so I'm not sure.
You need to be careful with these, if you have multiple users processing then you will get a race condition.
You can also use XML variables to pass table data between a proc and a function, I'm not sure how you would return the XML to the proc but it could always be your output! You should be able to return an XML variable as well.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I tried the Global temps but the UDF does not want to associate itself with anything thathas to do with # and i tried table Variables and the Query perfomance degraded. and again i tried Common Table Express and the same thing, the Perfomance
Vuyiswa Maseko,
Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code.
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.com
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/
|
|
|
|
|
Vuyiswa Maseko wrote: SELECT @s = @s + IsNull(',' + Cast(Cycletemplate AS varchar), '')
FROM #Temp (NOLOCK)
The #Temp table would be empty there? How about using a real table, instead of a temporary one?
I are Troll
|
|
|
|
|
Hi guys, is there a way to retrieve, for a given record, the names of all the columns where the values of those columns for that record match a given condition?
ID A B C D E F G
A1 1 0 1 1 0 1 0
Like if I want to retrieve, for record "A1", all the column names where the value = 1, it would return "A, C, D, F, G".
Can this be done in SQL directly?
Thanks!!
modified on Friday, January 15, 2010 8:56 PM
|
|
|
|
|
There is no easy way, you will need to write some code of your own. Gotchas that spring to mind are datatype conversion and accuracy when comparing numbers - floats for example are only approximate. Its not difficult, but does need a bit of planning and a lot of testing.
Bob
Ashfield Consultants Ltd
Proud to be a Code Project MVP
|
|
|
|
|
Not the nicest looking solution, but it does the trick;
SELECT ID,
CASE WHEN A=1 THEN 'A'
ELSE ''
END +
CASE WHEN B=1 THEN 'B'
ELSE ''
END +
CASE WHEN C=1 THEN 'C'
ELSE ''
END +
CASE WHEN D=1 THEN 'D'
ELSE ''
END +
CASE WHEN E=1 THEN 'E'
ELSE ''
END
FROM [TestTable]
Good luck
I are Troll
|
|
|
|
|
Thank you Eddy, unfortunately I've got 90 columns for conditional check, so I'll have to improvise. However, I could see myself using this code potentially in the future with tables that have fewer columns.
Many thanks again.
|
|
|
|
|
IMO if you need this, you really should rethink your table design. It probably should have been fewer (two or three) columns and more rows, something like ID, name, value
|
|
|
|
|
Unfortunately so far I couldn't think of a better table design without making it even more complicated.
However, I am going to take an alternate route by putting them into an array first, and check for the conditions from there in a loop. This is currently the best I can come up with.
|
|
|
|
|
Go with Luc's suggestion rethink your data structure, this one will drive you nuts. Try this
CREATE TABLE [dbo].[Attr](
[AttrID] [int] IDENTITY(1,1) NOT NULL,
[CustomerID] int,
[AttrType] [varchar](255) NULL,
[AttrValue] [varchar](255) NULL,
[Modified] [datetime] NULL,
[ModifiedBy] [varchar](50) NULL)
Where AttrType = your column Name and AttrValue = 1/0.
CustomerID is the foreign key to the entity you are applying the attributes to.
You would only have 4 rows representing the ACDF colums (0 deemed to not exist)
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I tried to store the picture present in the MY Picture location of my system
but unable to do so
Qurey
CREATE TABLE StockGifs (StockGifID int NOT NULL, Gif varbinary(max) NOT NULL)
INSERT StockGifs (StockGifID, Gif)
SELECT 1, BulkColumn
FROM OPENROWSET (BULK 'C:\Documents and Settings\All Users\Documents\My Pictures\Sample Pictures\Blue hills',SINGLE_BLOB) AS x
Error Message :Msg 4860, Level 16, State 1, Line 1
Cannot bulk load. The file "C:\Documents and Settings\All Users\Documents\My Pictures\Sample Pictures\Blue hills" does not exist.
Let me know what is the change need to be done
If the location alone Can be stored then how to convert a jpeg file to binary file.
Thanks in Advance,
Praveen Arokiam
|
|
|
|
|
You need to use full pathname of the file, which includes the extension.
Jpeg files are binary files. No need to convert.
|
|
|
|
|
Is the picture on your pc or the server? When you run this SQL it executes on the server hosting sql server, so directories are located on there, not your PC
Bob
Ashfield Consultants Ltd
Proud to be a Code Project MVP
|
|
|
|
|
|
I created a .mdf file of MS-Access which contains some tables.
Now i want data from two or more table in single table.
So i created queries in .mdf file with in Ms-Access. And querying these from my application.
Suppose Details is query name in .mdf file, then from application
i am using
"Select [nName], [nDate] from [Details]" .
Now i have some question on this:
a) If i use above way i.e. (create query on access then use this virtual table in application) It increases file size.
here question is- Will this going to slow application.
b) If i don't create query and from application with help of joins and sub-queries get data in DataTable object then use this DataTable.
But here question is- this DataTable will grow in size by time. this will also create load on application. what if this DataTable be in MB size?
So, Is creating Query(virtual-table / view) in .mdf file is good ?
Or Directly get data in DataTable object, then use this data ?
I am using C#.
any help/comment !!
|
|
|
|
|
Hum Dum wrote: So, Is creating Query(virtual-table / view) in .mdf file is good ?
Yes, in a real relational database (SQL Server, Oracle, MySQL etc) this is called a view and has no cost in disk space. This is probably NOT true for Access as Access is a POS, however using queries/views is still the correct way to go.
You need to evaluate your use of Access as your datastore and make sure it is valid. If more than one user is to access the data then use SQL Server.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hum Dum wrote: I created a .mdf file of MS-Access which contains some tables.
.mdf is the default extension for SQL Server... you sure you didn't use SQL Server? And if you used Access, renaming it .mdf won't make it SQL Server...
Saving a query in Access will not increase the filesize dramatically, it's not like it's making a copy of the actual data tables...
Access is going to be fine as a database engine if you have up to 10 users that are located on the same local network, and not using wireless network connections.
For database size, Anything up to a couple of hundred meg in Access will still give you reasonable performance (depending on your data structures of course)
|
|
|
|
|
_Damian S_ wrote: you sure you didn't use SQL Server?
Yup i am sure on that ,
Only here i mistype .mdb to .mdf.
I have some more question
a) A .mdb file we can store anywhere we want. Like this can we store .mdf(sql server DB file).
b) How can i transfer .mdb file to .mdf along data inside. Or more like moving to sql server. Is there any feature in SQL server to import and convert.
c)At present i am storing mdb file path in my connection string by OpenFileDialog(built in C#.net). Like this Can i search for .mdf file?
and using this file on different pc(LAN network) with my application.
In sql server where can we find our DB file( i hadn't explore much on myself)
regards
|
|
|
|
|
Yes, you can store your mdb file wherever you like. So long as it can be accessed by your front end software (on the same LAN, as I said earlier).
Yes, you can transfer Access data to SQL Server. You can either use the "upsizing wizard", or simply create a blank SQL Server database and import from Access. (Note - this doesn't work with SQL Express - but you can create SQL Express tables and link to Access, then use queries to get your data across).
Yes, you can do similar for SQL Server. Check out www.connectionstrings.com for comprehensive details on how to connect to SQL Server. I wouldn't worry about letting the user search for the .mdf file though - that's not how it works. They simply connect to the SQL Server and your userid/password/login that is set up handles the rest.
In SQL Server, your default location for data files is under MSSQL\Data, and depending on what version you have installed it may be directly under C:\, or in c:\program files etc... have a look around for it. Of course, you could simply open SQL Server and look at the file details of a created database.
|
|
|
|
|
_Damian S_ wrote: I wouldn't worry about letting the user search for the .mdf file though - that's not how it works. They simply connect to the SQL Server and your userid/password/login that is set up handles the rest.
Well for file search i am doing only for first time. If user installing application for first time then it asks for mdb file then i create a text file on local pc, then from next time onward i simply read this file for getting DB file path.
Now Can SQL express do the work(As its freeware i hope so , SQL server is not). If i install Sql Express on one machine then create DB. After this can i use this DB from LAN ?
By providing userid/pass as u said and it handle rest?
regards
|
|
|
|
|
With SQL all you are doing is providing the Servername and Database name to connect. This is the same no matter where you connect from. Directly connecting to the underlying files is something that is only of use in on-the-fly demos and not to be used for serious applications.
SQL Express will work fine for this, though it has some limitations See this Microsoft article for details[^]. The two main issues will be a reduced functionality Management Studio GUI and the 4GB data limit.
|
|
|
|
|
Hi,
I am working with an SSIS package(2005), which dumps records from a csv file into TABLE1. After making some alterations to thses records, it is gain inserted into another TABLE2 in the DB.
One of the columns is a date column. TABLE2 has all the datatypes fixed and it has a col with datatype "smalldatetime". TABLE1 is created each time the package is run. So at first i made the TABLE1.Rdate col datatype datetime but when i try converting this col to smalldatetime for inserting into TABLE2 it gives an error "The conversion of a datetime data type to a smalldatetime data type resulted in an out-of-range datetime value."
I tried changing the date col datatype to VARCHAR but again at the time of converting this col to smalldatetime for inserting into TABLE2 it gives an error "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.". The date in the TABLE1.Rdate col is in the format '2009-10-13'.
Can anyone help me with this Please?
Thanks & regards,
Payal
|
|
|
|
|
I always just chuck everything into a varchar field when using SSIS and let the down stream stored proc do all the transforms. I can deal with problems better in a stored proc than insinde an SSIS package.
Look in BOL for CONVERT and use one of the modifiers, 111 - Japan looks like the most likely
CONVERT(DATETIME, varcharfield,111)
Never underestimate the power of human stupidity
RAH
|
|
|
|
|