|
Did you reinstall OS and SQL Server?
|
|
|
|
|
No, all this was intact on the hard drive, we simply replaced the motherboard and fired it up.
|
|
|
|
|
Make sure your SQL Agent is running and that there is a job for it. There is an icon bottom right or go Administrative tools>Services>SQL Server Agent
|
|
|
|
|
|
Hi all,
i have three tables:
table 1 has two colomns: name and id
table 2 has two columns: countrycode and id(id in this table is the forgin key from table one)
table 3 has two columns: countryname and countrycode(countrycode is the forggin key from table two)
now i have to make a query to select the name from table one and the countryname that belongs to that name from table three  .
can anyone help me to make this query???
Thanx.
|
|
|
|
|
SELECT t1.name, t3.countryname
FROM [table 1] as t1
INNER JOIN [table 2] as t2 ON t2.id = t1.id
INNER JOIN [table 3] as t3 ON t3.countrycode = t2.countrycode
|
|
|
|
|
Hi i got a DataBase design on Visio and i need to use this DB on MySql server can any one tell me how can i export from Visio or from Ms Sql server thanks
Mohammad Al Hoss
|
|
|
|
|
Hi
Please help me
I have to create a SP.
The secenario is that,
A application calls the SP with a parameter(login), and a string of datas ( Acctid level1 level2; Acctid level1 level2; .......)
UserID AcctID Level1 level2
test testee N Y
eg:
the string of value from application will be as
(SVDB, N, Y; VGBY, Y, B; SCFV, Y, S; SRVD, Y, N; .....)
The SP have to get the fist set of data i.e SVDB, N, Y , check if SVDB exists in the table for that login, if yes update else insert.
Finally do acheck and delete the Acctid not available in above list
Im using SQL 7 server. Please help me in doing this
Thanks in advance
|
|
|
|
|
See below...
DECLARE @s NVARCHAR(4000);
SET @s = '(SVDB, N, Y; VGBY, Y, B; SCFV, Y, S; SRVD, Y, N; SRVD, Y, N; )';
;
SET @s = REPLACE(REPLACE(REPLACE( @s ,' ','') ,'(','') ,')','');
CREATE TABLE #S (pk INT IDENTITY(1,1), line NVARCHAR(100));
CREATE TABLE #T (col1 NVARCHAR(4), col2 NVARCHAR(1), col3 NVARCHAR(1));
DECLARE @p1 INT;
DECLARE @p2 INT;
SET @p1 = 1;
SET @p2 = CHARINDEX(';' , @s);
WHILE @p1 < @p2
BEGIN
INSERT INTO #S (line) SELECT SUBSTRING(@s, @p1, @p2-@p1);
SET @s = SUBSTRING(@s, @p2+1, LEN(@s));
SET @p1 = 1;
SET @p2 = CHARINDEX(';' , @s);
END
DECLARE @k INT;SET @k = 1;
DECLARE @max INT;
SELECT @max = MAX(pk) FROM #S;
WHILE @k < @max
BEGIN
SELECT @s =
'INSERT INTO #T (col1 , col2 , col3) SELECT '''
+ REPLACE( line , ',', ''',''') + '''' FROM #S WHERE pk = @k;
EXEC(@s);
SET @k = @k + 1;
END
DROP TABLE #S;
SELECT * FROM #T;
;
DROP TABLE #T;
Tell me please if this works.
Good luck! 
modified on Thursday, July 10, 2008 1:38 PM
|
|
|
|
|
Hi,
we can use IN clause to check condition in list of data
eg: Select * from table where fieldname IN('value1','value2');
but I have to use pattern matching together with this
can I use LIKE clause?
Can Anybody help ?
Thanks in Advance,
Soni
|
|
|
|
|
Are you hard-coding the values in the IN clause, or is list of values getting selected from another table?
|
|
|
|
|
Hi.
I have a stored procedure, and it's working fine.
One of the input parameters is XML (@p_xml) and is processed like this:
DECLARE @docHandle int
EXEC sp_xml_preparedocument @docHandle OUTPUT, @p_xml
SELECT * INTO #raw_table
FROM
OPENXML(@docHandle, '//NODE', 2)
The problem is when the XML is really big (~20 MB with ~35000 nodes). Then the OPENXML statement uses 7-8 minutes to finish.
Is there any way to speed up OPENXML, or is there any other workaround ?
I have tried both select into and insert into and both temp table and table variable. All with the same result.
Could someone help me ?
Kjetil
|
|
|
|
|
I doubt it. Its not the fast thing around. BTW, with the code shown, you do realise you are locking most of the system tables in your tempdb database for the 7-8 minutes it takes to run. A select into locks system tables for the duration of its run, this could seriously impact other users.
Sorry I can't help further.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Hi.
I actually use insert into, but I was to lazy to paste the table declaration in my post
Kjetil
|
|
|
|
|
I'm getting the following error: SQL Code 8630. Select error: Internal Processor Error: The query processor encountered an unexpected error during execution.
The query runs fine in Query Analyzer and in the PowerBuilder development environment but not in the compiled application.
Any ideas of the cause of this type of error? 
|
|
|
|
|
|
hi
thanks for your idea but my problem didnt solve
it takes when i use a select into instrauction with c#
is there any other ways?
|
|
|
|
|
Dear All,
My application stores two date values as Decimal in the SQL server 2005, one for the 'DayTransDate' like (2008/08/09) which is stored in the Data base as a Decimal= 20080709, and the other is for 'DayTransTime' like (12:08:44) which is stored in the Data base as a Decimal= 120844.
Now, I need to read these values and compare them with some date...
I wrote a query to convert with some help from my friend, but the query did not work well because of a syntax error (Incorrect syntax near ',') which I could not figure it out. The following is the query:
SELECT * FROM MyTable
WHERE convert(datetime, cast(DayTransDate AS char(8))
+ ' ' + STUFF(STUFF(CAST(DayTransTime AS char(6), 5, 0, ':'), 3, 0, ':')))
> DATEADD(minute, -30, getdate());
First: Is the previous query have the correct functions to convert the Decimal values into date, If yes, where is the syntax error??
Second: If you know any other way to convert, please tell me how to convert from Decimal into date.
With my Best Regards to you...
Kind Regards
OBarahmeh
|
|
|
|
|
Why are your dates saved like this? Even if you don't want to use the date time types (for which is see little reason not to) - dates in sql server will cast directly to a decimal value which could then be stored.
And you're missing a closing bracket after your second cast.
|
|
|
|
|
obarahmeh wrote: First: Is the previous query have the correct functions to convert the Decimal values into date, If yes, where is the syntax error??
Obviously not, else there wouldn't be a syntax error
Your STUFF is wrong.
stuff(stuff(CAST(DayTransTime AS char(8)),5,0,':'),3,0,':')
should do it.
It does beg the question as to why you are storing dates and times as decimal when there is a perfectly good datetime data type.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Ashfield wrote: Your STUFF is wrong.
stuff(stuff(CAST(DayTransTime AS char(8)),5,0,':'),3,0,':')
should do it.
I used your query, but it did not work, and the following is my new query with its error:
SELECT * FROM MyTable
WHERE convert(datetime, cast(DayTransDate AS char(8))
+ ' ' + STUFF(STUFF(CAST(DayTransTime AS char(6)), 5, 0, ':'), 3, 0, ':'))
> DATEADD(minute, -30, getdate());
The error: The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
Do you have an idea to solve the previous error??
About your question of why do I use Decimal instead of datetime... I am connecting my application to read the data from another source where they are using Decimal data types in all cases instead of Date. So I just have no idea about the reason  
Kind Regards
OBarahmeh
|
|
|
|
|
obarahmeh wrote: I used your query, but it did not work
Thats because you did not make all the changes - you still have 'AS char(6)', it should be 'AS char(8)'
If it works when you copy if fully will you increase my score for my previous answer 
Bob
Ashfield Consultants Ltd
modified on Thursday, July 10, 2008 7:36 AM
|
|
|
|
|
|
I have just been running this
CREATE TABLE #MyTable ( DayTransDate Decimal(8,0) , DayTransTime Decimal(6,0) );
INSERT INTO #MyTable (DayTransDate, DayTransTime) SELECT 20080709, 120844 ;
INSERT INTO #MyTable (DayTransDate, DayTransTime) SELECT 20090709, 120844 ;
INSERT INTO #MyTable (DayTransDate, DayTransTime) SELECT 20080710, 150844 ;
INSERT INTO #MyTable (DayTransDate, DayTransTime) SELECT 20080710, 200844 ;
SELECT *,DATEADD(minute, -30, getdate()) as comparedate FROM #MyTable
WHERE convert(datetime, cast(DayTransDate AS char(8))
+ ' ' + stuff(stuff(CAST(DayTransTime AS char(8)),5,0,':'),3,0,':'))
> DATEADD(minute, -30, getdate());
which gives me
DayTransDate DayTransTime comparedate
20090709 120844 2008-07-10 19:49:16.030
20080710 200844 2008-07-10 19:49:16.030
exactly as expected, so what doesn't work?
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Play with this:
*/
CREATE TABLE #MyTable (
DayTransDate Decimal(8,0) ,
DayTransTime Decimal(6,0)
);
INSERT INTO #MyTable (DayTransDate, DayTransTime) SELECT 20080709, 120844 ;
INSERT INTO #MyTable (DayTransDate, DayTransTime) SELECT 20090709, 120844 ;
SELECT *
, DayTransDate * 1000000 + DayTransTime
, convert(datetime, cast(DayTransDate AS char(8))
+ ' '
+ STUFF(STUFF(CAST(DayTransTime AS char(6)), 5, 0, ':'), 3, 0, ':'))
, CONVERT(NVARCHAR, DATEADD(minute, -30, GETDATE()), 112) +
REPLACE(CONVERT(NVARCHAR, DATEADD(minute, -30, GETDATE()), 108) , ':', '')
, REPLACE(REPLACE(REPLACE(
CONVERT(NVARCHAR(19), DATEADD(minute, -30, GETDATE()), 120)
, '-', '') , ':', '') , ' ', '')
FROM #MyTable
WHERE
DayTransDate * 1000000+DayTransTime
>
REPLACE(REPLACE(REPLACE(
CONVERT(NVARCHAR(19), DATEADD(minute, -30, GETDATE()), 120)
, '-', '') , ':', '') , ' ', ''
)
;
SELECT * FROM #MyTable
WHERE
CONVERT(
NVARCHAR(19),
convert(datetime,
cast(DayTransDate AS char(8))
+ ' '
+ STUFF(STUFF(CAST(DayTransTime AS char(6)), 5, 0, ':'), 3, 0, ':')
)
, 120)
>
CONVERT(NVARCHAR(19), DATEADD(minute, -30, GETDATE()) , 120)
;
SELECT * FROM #MyTable
WHERE
DayTransDate * 1000000 + DayTransTime
>
CONVERT(NVARCHAR,
DATEADD(minute, -30, GETDATE()), 112
)
+
REPLACE(
CONVERT(NVARCHAR, DATEADD(minute, -30, GETDATE()), 108)
, ':', ''
)
;
SELECT * FROM #MyTable
WHERE
DayTransDate * 1000000 + DayTransTime
>
REPLACE(REPLACE(REPLACE(
CONVERT(NVARCHAR(19), DATEADD(minute, -30, GETDATE()), 120)
, '-', '') , ':', '') , ' ', ''
)
;
DROP TABLE #MyTable
/* Any of the following should work the same way for your case: */
SELECT * FROM MyTable
WHERE
CONVERT(
NVARCHAR(19)
, convert(
datetime,
cast(
DayTransDate AS char(8))
+ ' '
+ STUFF(STUFF(CAST(DayTransTime AS char(6)), 5, 0, ':'), 3, 0, ':')) ,
120
)
>
CONVERT(NVARCHAR(19), DATEADD(minute, -30, GETDATE()) , 120)
;
SELECT * FROM MyTable
WHERE DayTransDate * 1000000 + DayTransTime
> CONVERT(
NVARCHAR,
DATEADD(minute, -30, GETDATE()),
112
)
+ REPLACE(CONVERT(NVARCHAR, DATEADD(minute, -30, GETDATE()), 108) , ':', '')
;
SELECT * FROM MyTable
WHERE
DayTransDate * 1000000 + DayTransTime
>
REPLACE(REPLACE(REPLACE(
CONVERT(NVARCHAR(19), DATEADD(minute, -30, GETDATE()), 120)
, '-', '') , ':', '') , ' ', ''
)
;
modified on Thursday, July 10, 2008 9:00 AM
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
