|
Good Day all.
I have the Following Query
SELECT T.ID, T.CYCLETEMPLATES
FROM TBL_TERM T
This Brings back something like this
ID CycleTemplate
==============================================================
5 | 1010101010101000100010101010100010000000000000000000
6 | 1000000000000000000000000000000010000000000000000000
Now i have another table Defined like this
CREATE TABLE DBO.REC_TERM_CYCLE
(
ID INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
TERM INT NULL,
CYCLE VARCHAR(10) NULL
)
Let me explain the Logic of a Cycle template,
1010101010101000100010101010100010000000000000000000
The "1"'s represent the position of the template. if i can return this, it will bring me
1,3,5,7,9,11,12,16 ................
So i want to run an Insert statement on the table REC_TERM_CYCLE like this
INSERT INTO DBO.REC_TERM_CYCLE
VALUES(ID,cycles)
Now the Cycles are the individual number retrieved from that long string. but now this must go to a table to retrieve the Cycles Template long string and add the id and the position of the cycle
i have the below code
SELECT T.ID, T.CYCLETEMPLATES
FROM TBL_TERM T
Declare @Len int
SET @Len = LEN(T.CYCLETEMPLATES)
Declare @Counter int
SET @Counter = 1
WHILE @Counter < = @Len
BEGIN
if (SUBSTRING(T.CYCLETEMPLATES,@Counter,1)='1')
INSERT INTO DBO.REC_TERM_CYCLE
VALUES(T.ID,@Counter)
SET @Counter = @Counter + 1
END
PRINT 'INSERTED' + T.ID '' + @Counter ''
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.tiyaneProperties.co.za
vuyiswa@its.co.za
www.ITS.co.za
modified on Wednesday, April 1, 2009 6:07 AM
|
|
|
|
|
I got a Solution, and the Solution was that i have to create a Function like this
CREATE FUNCTION [dbo].[f_GenerateRows]
(@NumRows INT)
RETURNS
@Tbl TABLE (pkID INT PRIMARY KEY NOT NULL)
AS
BEGIN
DECLARE @i INT;
-- SELECT * FROM dbo.f_GenerateRows((SELECT 10))
SET @I = 1
WHILE @I <= @NumRows BEGIN
INSERT INTO @Tbl VALUES(@I)
SET @I = @I + 1
END
RETURN
END
After that i have to do my insert like this in another SP
ALTER PROC sp_Create_Staff_Cycle_Clash
AS
--CHECK IF THE TABLE EXISTS
IF NOT exists(select * from sys.databases where name = 'REC_TERM_CYCLE')
BEGIN
EXEC CREATE_TABLE_REC_TERM_CYCLE
END
ELSE
BEGIN
TRUNCATE TABLE DBO.REC_TERM_CYCLE
DECLARE @ROWS int
SET @ROWS = (SELECT COUNT(*) FROM tbl_term)
INSERT INTO REC_TERM_CYCLE (term, cycle)
SELECT t.ID, i.pkID
FROM TBL_TERM AS t
CROSS JOIN dbo.f_GenerateRows(@ROWS) AS i
WHERE SUBSTRING(t.CYCLETEMPLATES, i.pkID, 1) = '1'
ORDER BY t.ID, i.pkID
END
now this Works fine, but i wanted to check First if the table exists before i work on it, now if the table exists and i run this sp i get the Following error
Msg 2714, Level 16, State 6, Procedure CREATE_TABLE_REC_TERM_CYCLE, Line 4
There is already an object named 'REC_TERM_CYCLE' in the database.
Please help
Thank you
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.tiyaneProperties.co.za
vuyiswa@its.co.za
www.ITS.co.za
|
|
|
|
|
Hi,
I have created a database, and I want to populate it with some demo data so that I can test my queries for optimisation. I want a program that creates me something where I can insert data, lots of data. Anything out there that is free?
Thanks
|
|
|
|
|
Have you ever considered mastering the arcane art of Googling.
I Googled 'test data generator', and got hundreds of hits.
Go on, give it a go! You know you want to.
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.”
|
|
|
|
|
SELECT TO_TIMESTAMP ('10-Sep-02 14:10:10.123000', 'DD-Mon-RR HH24:MI:SS.FF')
FROM DUAL;
|
|
|
|
|
Which result return your query?
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
actaully in sql serve it is telling that to_timestamp does not exit ..so i want wat is the alternate of this particular function in sql server
|
|
|
|
|
Your query is in Oracle and it to convert it in T-SQL then it should be near to select convert(varchar,cast('10-Sep-02 14:10:10.123000' as DateTime),120)
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
hi mr iam getting this error wen i executed in teh swl server
Conversion failed when converting datetime from character string.
|
|
|
|
|
Try using the ISO format[^] for date/time values; cast('2002-09-10T14:10:10.123' as DateTime)
Good luck
I are troll
|
|
|
|
|
Hi
I'am looking for good tool that can convert from Oracle to sql server and from sql server to oracle.
I found some, but there was many errors during the conversion
thank's in advance
|
|
|
|
|
Hey everyone,
How would you create a simple sequence that starts with the maximum value of a table column
CREATE SEQUENCE SEQ_TEST
MINVALUE 1
START WITH (SELECT MAX(ID) FROM TEST)
INCREMENT BY 1
CACHE 20
ORDER
/
START WITH (SELECT MAX(ID) FROM TEST)
*
ERROR at line 3:
ORA-01722: invalid number
May be by creating a temporary variable to store that value and use it after the START WITH .. If so, please show me how or kindly suggest another way round
Many thanks guys!
All generalizations are wrong, including this one!
(\ /)
(O.o)
(><)
|
|
|
|
|
Worths to check this link.[^],I guess you have to convert result SELECT MAX(ID) FROM TEST into number.
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
Hey mate,
Thank you for standing up for me until now, I just found the complete answer and thought to let you know..
DECLARE
N NUMBER(10);
BEGIN
SELECT MAX(ID)+1 INTO N FROM TEST;
-- CREATING SEQUENCE
Execute immediate ('DROP SEQUENCE SEQ_TEST');
Execute immediate ('CREATE SEQUENCE SEQ_TEST
MINVALUE 1
START WITH '||N||'
INCREMENT BY 1
CACHE 20
ORDER');
END;
/
CREATE OR REPLACE TRIGGER TRG_TEST_AUTONUMBER
BEFORE INSERT ON TEST
FOR EACH ROW
WHEN (NEW.ID IS NULL)
BEGIN
SELECT SEQ_TEST.NEXTVAL
INTO :NEW.ID
FROM DUAL;
END;
/
INSERT INTO TEST(NAME,YEAR)
SELECT NAME,2009 FROM TEST WHERE YEAR = 2008
/
Thanks again my friend!
All generalizations are wrong, including this one!
(\ /)
(O.o)
(><)
|
|
|
|
|
Thanks,I learned still some new techique in Oracle from your solution
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
I had the same kind of problem recently.
This is how I solved it:
DECLARE
ID NUMBER;
BEGIN
SELECT MAX(ID) INTO ID FROM TEST;
EXECUTE IMMEDIATE 'CREATE SEQUENCE SEQ_TEST
MINVALUE 1
START WITH ' || ID || '
INCREMENT BY 1
CACHE 20
ORDER';
END;
/
"Lots of programmers have had sex - some have even had it with members of the same species." - Pete O'Hanlon
|
|
|
|
|
That does it! Thanks mate!
All generalizations are wrong, including this one!
(\ /)
(O.o)
(><)
|
|
|
|
|
You're welcome.
"Lots of programmers have had sex - some have even had it with members of the same species." - Pete O'Hanlon
|
|
|
|
|
Hi,
I'm going to deploy a Report Service Project, created in VS2008.
I've set these info:
OverwriteDataSources : False
TargetDataSourceFolder : \
TargetReportFolder : RSProjectTest (it's my project name)
TargetServerUrl : http://localhost/Reportserver
when I want to deploy 'Reporting Services Login' dialog appears. I've entered my sql login and my windows login, but none of them was accepted.
What should I do? what is this login ?
Best wishes
|
|
|
|
|
Hi
I installed Sql Server 2005 ManagemenStudio in my local machine to create sample tables in my local database. When I open the Sql Server its keep on asking me to connect to the server, can't I create local database and create tables and stored procedure there.
Can any one help me please.
Thanks in advance,
|
|
|
|
|
You should be able to connect to the localhost with windows authentication mode. Once you connect you can create databases and such. Doe sit give you an error message when you try to connect to the localhost?
|
|
|
|
|
You are totally missing the point.
In order to create any SQLServer database, whether local or remote, you have to be connected to an SQLServer server.
When you create the database you can set its location in the dialogue that Management Studio presents.
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.”
|
|
|
|
|
Hello All,
I want to access different database(MySQL,Oracle,SqlServer etc) using ODBC dsn less connection with my application by providing the connection and database schema details at run time . so is there any specific connection string pattern which is accepted by all database providers. I mean to say
for MySQL connection string is like
Driver ={MySql Driver};Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword;Port=3306;
for oracle we have
Driver=(Oracle in XEClient);dbq=111.21.31.99:1521/XE;Uid=myUsername;Pwd=myPassword;
so it replaced Server with dbq .. so is there any standard string parameters which could be used irrespective to database. and only SQL based databases I need to connect.
I hope you understand what I want to say
Thanks
Param
|
|
|
|
|
I have been off work for some days now and since I came back this morning, I keep getting this error when I start my SQL Server 2005... this is the error:
"An Exception occurred while executing a Transact-SQL statement or batch
(Microsoft.SqlServer.ConnectionInfo)"
Additional Information:
Database 'msdb' cannot be opened. It has been marked SUSPECT by recovery. See the SQL Server errorlog for more information. (Microsoft SQL Server, Error: 926)
What can I do to rectify it because it keeps giving similar error each time I try to update anything in my database
|
|
|
|
|
tolucole wrote: See the SQL Server errorlog for more information.
And what did the the error log say?
|
|
|
|