|
Check my reply to you on C# forum.
I Love T-SQL
|
|
|
|
|
I have a nasty little job where I have a CSV table with 50+ cols which will grow (1-2 per year)
The last 44+ cols are in sets of 3, where AttibuteName changes but the date fields do not.
AttibuteName, Start_Date, End_Date
Therefore the columns are not unique and their number is not stable.
At this point SSIS seems to have a serious issue, however this could be resolved if the import method created cols based on the cols in the source file. I'm quite happy to have the labels in the 1st data row and all data to be varchar as I can deal with this in TSQL but dynamically mapping the new columns is the issue.
Ideally I would like to drop the table(no problem)
Bulk copy into a new table based on the source file
Rename the destination file (no problem)
Process the datavia stored proc (no problem)
I have written an app which does exactly what I want but it is a manual thing and I really want to automate this. Having told the boss that writing ETL apps for each module is wrong, I need to clean this one up.
TIA
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Customer can enrol several operation.
Each operation can be enroled by many Customer.
Customer own Operations and not own operations are displaied.
And When Customer will use a operation ,check it that has enroled.
how to design the tables to display these relation ?
I want to know the best desgin.
|
|
|
|
|
Hi
This is nothing ADO.NET but SQL... (not quite sure if this is right forum). I am new to Sybase and today using sqsh (http://www.sqsh.org/[^]) not sure how to "clear screen".
Anyone familiar with this?
Thanks!!
devy
|
|
|
|
|
May be The title Of the Question Sounds Strange But I Will Explain What I mean ...
I'm Analysing Web Application And I am Very confused
The Problem Is That I didn't use T-sql in Complex Scenario so I don't Know If the T-Sql
Has the Ability To manipulate Date Like .Net Languages In Easy Way ??
Of course I Know That There are loops, conditions , interact With Variables , Builtin Methods
And Etc......
But Is it Easy To Develop , Update .........
Thanks  !
You have To Search About The Truth Of Your Life
Why Are you Here In Life ?
|
|
|
|
|
I've seen the best of both worlds. Stored procedures so complex, thousands of lines long and splattered with dynamic sql's and temp tables and numerous loops. And also BLL's having thousands of procedures. In the end, I guess the call is yours, if you're the sole developer. If you're in a team, you might want to leverage your team's skillset.
Developing Business Logic in T-SQL is not for the faint hearted, and is usually lengthier and more complex than a similar implementation in .NET. And T-SQL is pretty limited when it comes to inbuilt functions and constructs. Nowhere near to the .NET libraries, that's for sure. A traditionalist would put his BLL's in the application and use the backend just for DML's.
In the end, it's your call. If your Business Logic is very complex, stick to .NET, else if it's just a few checks and conditions, T-SQL would do just fine.
Alternately if your database server is much more powerful than your web server, you also have the option of using CLR Stored Procedures.
SG
Cause is effect concealed. Effect is cause revealed.
|
|
|
|
|
Please don't cross post. I guess this is where it belongs, as your question had nothing to do with VB.
SQL is really easy. Much more so than VB. But, it can get complex, it depends on how complex you need to get, to get what you need. Buy a book.
Christian Graus
Please read this if you don't understand the answer I've given you
"also I don't think "TranslateOneToTwoBillion OneHundredAndFortySevenMillion FourHundredAndEightyThreeThousand SixHundredAndFortySeven()" is a very good choice for a function name" - SpacixOne ( offering help to someone who really needed it ) ( spaces added for the benefit of people running at < 1280x1024 )
|
|
|
|
|
hi all
in my table i had a colum contain dattime data the formating date
like this "13/12/2007 09:16:41 "
when i make a select statement by where clause and between condition
as
select * from MessageAccepted where AcceptedDateTime between '22/10/2007'and '23/10/2007'
SQL gives me this error
Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
is there is a way to convert the datetime datatype and how???
or how can i solve this problem
please i need your help
thanks for all
Kareem Elhosseny
|
|
|
|
|
Convert on DateTime datatype as:
select * from MessageAccepted where AcceptedDateTime between cast('22/10/2007' as DateTime) and Cast('23/10/2007' as DateTime)
I Love T-SQL
|
|
|
|
|
thank's for your effort but i use it before and SQL gives me the same error
Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
Kareem Elhosseny
|
|
|
|
|
Post your data how they are stored in table, and is datatype of column DateTime or varchar?
I Love T-SQL
modified on Sunday, April 20, 2008 4:07 AM
|
|
|
|
|
Dear Blue_Boy
the datatype in my table is Datetime and the data stored as
this format
23/10/2007 01:48:57 PM
Kareem Elhosseny
|
|
|
|
|
this should works for you...
<br />
select convert(varchar(15),AcceptedDateTime,103) from table1 <br />
where convert(varchar(15),AcceptedDateTime,103) >='20/10/2007' and convert(varchar(15),AcceptedDateTime,103)<='21/10/2007'
I Love T-SQL
modified on Sunday, April 20, 2008 7:41 AM
|
|
|
|
|
thank's alot it works but please could u tell me why we don't use Between
& what's the error when we use it & what is the error Causer?
thank's again for your help,time,and your attention
Kareem Elhosseny
|
|
|
|
|
I guess it should works and with between clause.
You are welcome anytime, my pleasure to help you...
select convert(varchar(15),AcceptedDateTime,103) from table1 <br />
where convert(varchar(15),AcceptedDateTime,103) between '20/10/2007' and '21/10/2007'
I Love T-SQL
|
|
|
|
|
thank's God it workes by Between
select * from MessageAccepted where convert(varchar(10),AcceptedDateTime,103) between '22/10/2007'and '23/10/2007'
but i have a Question
if i made the query as above it return 3 record
and when i change in convert method just only the varchar size to 8 as example its return to me 2 record
can u tell me the reasion please
Kareem Elhosseny
|
|
|
|
|
Why you need to change varchar length? Can't be length 15 or you need length 8?
I Love T-SQL
|
|
|
|
|
no i just try but it work without datatype size because its an optional parametr in convert method
here is my account kareem_elhosseny@hotmail.com
you can add me in your contact list
i hope to be friends to exchange experiance
thank's alot my brother
Kareem Elhosseny
|
|
|
|
|
Yes, you can let it without specify size of varchar, but it is more usable if you specify size.
My add is s_voca@hotmail.com
I Love T-SQL
|
|
|
|
|
I would strongly recommend that you use the following convert statement for your dates when doing comparisons:
convert(char(8),getdate(),112))
as it will return your dates in ISO format which means that comparisons will be valid every time.
Continuous effort - not strength or intelligence - is the key to unlocking our potential.(Winston Churchill)
|
|
|
|
|
We have an application where we do not want to close the cursors after a commit.
I am testing the app with SQL Server 2005 SP2 on Windows XP Professional with ODBC and SQLNativeClient
I have done the following
- The server option "Close cursor on COMMIT" is not enabled
- The database option 'cursor close on commit' is not enabled
- In the connection code of my VC++ 2005 app I wrote
SQLSetConnectAttr(*phDbConnect, SQL_COPT_SS_MARS_ENABLED, (SQLPOINTER)SQL_MARS_ENABLED_YES, SQL_IS_UINTEGER);
SQLSetConnectAttr(*phDbConnect, SQL_COPT_SS_PRESERVE_CURSORS, (SQLPOINTER)SQL_PC_ON , NULL);
SQLSetConnectAttr(*phDbConnect, SQL_AUTOCOMMIT , (SQLPOINTER)SQL_AUTOCOMMIT_OFF, NULL);
In spite of these configurations SQL Server closes the cursors when a commit is done. What should I do?
I also tried the following ExecuteSql bevor executing a query:
ExecuteSql("SET CURSOR_CLOSE_ON_COMMIT OFF");
The reason, why I need open cursors after a commit is very simple:
What I do is
1) getting all rows from table y
SELECT xxx FROM y
2) fetching over each row from y
while (FETCH)
3) updating table z inside the fetch of table y
UPDATE z SET xxxx WHERE....
4) committing the changes made to table z
COMMIT
5) while-end
On 4) SQL Server closes also the cursor for the SELECT statement from 1)  ...
Thank you very much
Mat
|
|
|
|
|
Right off the bat it seems like a rather complex way of updating some rows (cursors are usually a bad way of doing such things, but sometimes needed), but that aside....
is there any need to wrap it in transaction and thus committing after each update of Z?
And if you want it in an transaction - wouldn't it be better to only committing after you're done updating all rows of z?
---------------------------
127.0.0.1 - Sweet 127.0.0.1
|
|
|
|
|
Hello!
Is there any way to check if Referential Integrity is implemented in Stored-Procedures?
Not a single relationship is defined on DB tables!!!
Regards,
Adeel
Do rate the reply, if it helps or even if it doesnot, because it helps the members to know, what solved the issue. Thanks.
|
|
|
|
|
What do you want to check?
Referential integrity is on table level, so I'm unsure what you are trying to accomplish?
---------------------------
127.0.0.1 - Sweet 127.0.0.1
|
|
|
|
|
In normal circumstances, Referencial Integrity (RI) is used on table-level, by defining relationships FKs and PKs....but in special circumstances RI can be implemented at Stored-Procedure (SP) level.
Example:
In proper LDM:
table1.col1 = PK
table2.col1 = PK
table2.col2 = FK
Relationship created and its done!
In this special case:
If table1 and table2 are to linked, in SPs where-ever table1.col1 is inserted or changed or updated, table2.col2 is also changed!
The problem of this approach is, we have to keep all the relationships intact manually and there is no record of which table is related to which table!!!
So the problem is, if we have such system implementation and we dont know what relationships exist (offcourse we didnt implement the system) how can we find it out?
Can we do that finding the update on each table in each SP code?
Regards,
Adeel
Do rate the reply, if it helps or even if it doesnot, because it helps the members to know, what solved the issue. Thanks.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
