|
Hi everybody!
I have my own kind of server that for each client keeps an oledb connection opened. It also uses a thread pool for serving the clients. Now let's say the connection between my server and SQL server breaks for whatever reason. While it's down if my server attempts to do anything with the database it obviously fails. Then network issue gets solved.
If my server attempts to continue manipulate the same oledb connection - it still fails. If I reconnect it fails anyway as long as there are those other olddb connections for those other clients of mine. Only when all oledb connections are closed I can start connecting successfully to the SQL server. It seems that in multithreaded/same exe session environment all oledb connections must be closed and reopened in order to recover. Is that true? Or is there a better way to force oledb to revive connections?
Please help!
Thanks a lot in advance.
|
|
|
|
|
Hi guys,
I am having a weird issue, I am trying the following and for some reason varchar(50) value is set to null when I encrypt or decrypt it. If I change it from varchar to nvarchar it works. Our table in the database is already set to varchar to store the encrypted information and can't change it due to licensing issues. Please help, what am I doing wrong?
OPEN SYMMETRIC KEY DSBEncryptionKey DECRYPTION BY CERTIFICATE EncryptDsb
DECLARE @encryptedNum varchar(50) -- varchar doesn't work here, nvarchar works
SELECT @encryptedNum=EncryptByKey(Key_GUID('DSBEncryptionKey '), '111000614');
select CONVERT(varchar(50),decryptbykeyautocert(cert_id('EncryptDsb'), NULL, @encryptedNum))
FYI, my symetric key uses AES_256 algorithm.
Please help!
Sameer
|
|
|
|
|
I have a query that is joining tables and calculating the SUM.
It seems to be working, however; the "SUM" is double what it should be.
Any ideas:
Select, spendaccount, spend.fromcode, spend.keyword,
spend.kwtype, SUM (Rev.trans)
Trans, SUM (rev.newaccounts)
newaccounts, SUM (rev.gross) Gross, SUM (rev.refunds)
Refunds, SUM (rev.net) Net, spend.clicks, spend.spend
From spend, Rev wherespend.fromcode=
rev.fromcode
group by spend.fromcode
Regards,
Hulicat
|
|
|
|
|
Hi,
Which SUM you are talking about?
SUM (Rev.trans)?
SUM (rev.newaccounts)?
SUM (rev.gross)?
SUM (rev.refunds)?
SUM (rev.net) Net?
Niladri Biswas
|
|
|
|
|
Hello and thanks for the reply; I am not sure on the approach but here is what I am trying to do:
Spend table-> spend (SUM'd), clicks, kytype account
Rev table-> transactions, newaccounts, gross, refunds, net (SUM'd),
From spend, Rev wherespend.fromcode=
rev.fromcode
----all in one output--------
Regards,
Hulicat
|
|
|
|
|
I am assuming certain things.
I have created both the tables and has inserted certain values. Please look into it whether you also have the same values or not!
tblSpend
COLUMN NAME DATATYPE
----------- -------
SPENDACCOUNT int
FROMCODE int
KEYWORD varchar(50)
KWTYPE int
CLICKS int
SPEND int
Values in tblSpend are
101 1 KeyWord1 1 1 10
102 2 KeyWord2 1 10 11
103 3 KeyWord3 2 21 15
104 4 KeyWord4 3 22 16
105 5 KeyWord5 5 11 11
106 6 KeyWord6 2 33 12
107 7 KeyWord7 1 111 11
108 8 KeyWord8 2 22 2
109 9 KeyWord9 1 1 1
110 10 KeyWord10 1 2 3
tblRev
COLUMN NAME DATATYPE
----------- -------
NEWACCOUNTS int
GROSS int
REFUNDS int
NET int
TRANS int
FROMCODE int
Values in tblRev are
1 10 20 10 10 1
2 20 30 10 20 2
3 30 40 15 25 3
4 40 50 45 5 4
5 50 100 50 50 5
6 60 400 200 200 6
7 70 100 100 0 7
8 70 100 50 50 7
6 111 300 120 180 6
The Query is
SELECT
S.SPENDACCOUNT, S.KEYWORD,
S.KWTYPE,S.CLICKS,S.SPEND,
D.FROMCODE,D.TRANS,D.NEWACCOUNTS,
D.GROSS,D.REFUNDS,D.NET
FROM (
SELECT
T.FROMCODE,
SUM (R.TRANS) TRANS,
SUM (R.NEWACCOUNTS) NEWACCOUNTS,
SUM (R.GROSS) GROSS,
SUM (R.REFUNDS) REFUNDS,
SUM (R.NET) NET
FROM TBLSPEND T, TBLREV R
WHERE
T.FROMCODE= R.FROMCODE
GROUP BY T.FROMCODE
) D, TBLSPEND S
WHERE S.FROMCODE = D.FROMCODE
OUTPUT:
101 KeyWord1 1 1 10 1 1 10 20 10 10
102 KeyWord2 1 10 11 2 2 20 30 10 20
103 KeyWord3 2 21 15 3 3 30 40 15 25
104 KeyWord4 3 22 16 4 4 40 50 45 5
105 KeyWord5 5 11 11 5 5 50 100 50 50
106 KeyWord6 2 33 12 6 12 171 700 320 380
107 KeyWord7 1 111 11 7 15 140 200 150 50
Hope this helps
Niladri Biswas
modified on Tuesday, June 30, 2009 4:26 AM
|
|
|
|
|
Hello Niladri_Biswas, thanks for your help.
Actually I know what the problem is I am just can't figure out the query.
Example of data a problem.
Fromcode Spend.spend rev.gross
ABC 10 70
ABC 20 50
ABC 10 20
Results look like the following:
rev.gross sum'd + spend.spend
Fromcode spend.spend rev.gross
abc 140 (expected 90)
abc 160 (expected 100)
abc etc
I want the total for each column based on the fromcode but not joined sums if that makes sense.
Thanks and Regards
Regards,
Hulicat
|
|
|
|
|
If you want to solve a problem like this; sometimes you can figure out what is going on by running a similar query to the one you do have but instead of using SUM use COUNT. This is just a debugging method, not the solution.
I suspect if you did that with your original query all of the lines that you saw double would come out with a count of 2 or higher.
Maybe you should try something more like this for your query:
select
spend.account,
spend.fromcode,
spend.keyword,
spend.kwtype,
(select sum(rev.trans) from rev where rev.fromcode=spend.fromcode) trans,
(select sum(rev.newaccounts) from rev where rev.fromcode=spend.fromcode) newaccounts,
(select sum(rev.gross) from rev where rev.fromcode=spend.fromcode) gross,
(select sum(rev.refunds) from rev where rev.fromcode=spend.fromcode) refunds,
(select sum(rev.net) from rev where rev.fromcode=spend.fromcode) net,
spend.clicks,
spend.spend
from spend
order by spend.fromcode
I don't have mysql running so I can't test it myself.
_____________________________
When life hands you marmots, make marmalade.
|
|
|
|
|
Thanks "smcnulty2000" and "Niladri_Biswas" your help and input was greatly appreciated.
Regards
Regards,
Hulicat
|
|
|
|
|
I am still trying to get it to work, this one didnot return any results
SELECT S.SPENDACCOUNT, S.KEYWORD, S.KWTYPE,S.CLICKS,S.SPEND, D.FROMCODE,D.TRANS,D.NEWACCOUNTS, D.GROSS,D.REFUNDS,D.NET FROM ( SELECT T.FROMCODE, SUM (R.TRANS) TRANS, SUM (R.NEWACCOUNTS) NEWACCOUNTS, SUM (R.GROSS) GROSS, SUM (R.REFUNDS) REFUNDS, SUM (R.NET) NET FROM TBLSPEND T, TBLREV R WHERE T.FROMCODE= R.FROMCODE GROUP BY T.FROMCODE ) D, TBLSPEND WHERE S.FROMCODE = D.FROMCODE
This one "hung" the database:
Thanks...for the help I think I am close
select spend.account,spend.fromcode,spend.keyword,spend.kwtype, (select sum(rev.trans) from rev where rev.fromcode=spend.fromcode) trans, (select sum(rev.newaccounts) from rev where rev.fromcode=spend.fromcode) newaccounts, (select sum(rev.gross) from rev where rev.fromcode=spend.fromcode) gross,(select sum(rev.refunds) from rev where rev.fromcode=spend.fromcode) refunds,(select sum(rev.net) from rev where rev.fromcode=spend.fromcode) net, spend.clicks, spend.spendfrom spend order by spend.fromcode
Regards,
Hulicat
|
|
|
|
|
Hello,
I have to Update a Csv file into Sql Server DataBase, the file contains 200 000 records.
So how can i do to optimize the DbTransactions ?! because 200 000 is too big and i want not overload the Database Server.
Solution1: create a global DbTransaction with IsolationLevel=RaadUncommited
Solution2: create a DbTransaction foreach Record with IsolationLevel=RaadUncommited
Solution3: create a DbTransaction for a group of Records with IsolationLevel=RaadUncommited
Already Thanks.
|
|
|
|
|
What do you mean by "update" csv into database? You update records in place, or insert new ones?
Look at BULK INSERT at MSDN. It is very fast, you can insert all in temp table and then update records, if that is your goal.
|
|
|
|
|
thank you for you answer.
I update a record.
I know the BULK INSERT but the CSV file have to be in the same machine of Sql Server Data base!
|
|
|
|
|
|
i am using ms sql server 2005. i used full text search (fts) facility. now i want to know that does fts cares about sequence of a phrase. for example
("select * from news AS N INNER JOIN containstable(news,datacontent,'local' AND 'company') AS A ON N.[id]=A.[KEY] ORDER BY A.RANK DESC",connec)
this returns results that includes local and company but anywhere in the document. but i want it like "my local company is very big." so i mean the word "company" must come after "local" (at least 1 time) to have a result. how can i do this. i tried it as 'local company' but an error occured...
|
|
|
|
|
Hi All
i have a view in sql2005.
this view links to several other views and those views may link to other views.
how do i get a completed list of dependancies?
i have tried to use this but only gives me the 1st level
EXEC sp_MSdependencies N'viewname', null, 131527
thanks in advance
Anoop
|
|
|
|
|
Try this
EXEC sp_MSdependencies N'viewname', null, 266751
Hope this helps
Niladri Biswas
modified on Tuesday, June 30, 2009 6:08 AM
|
|
|
|
|
Hi,
nope this returns no data...
i am using SQL2005 and have a view that is dependant on several other views.
any other suggestions are welcomed...
thank you
Anoop
|
|
|
|
|
Good Day All
i have the Following Example String
10,11,9
This is a Varchar Field in SQl, and i want this Sort the numbers inside this String to be like this
9,10,11
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.somee.com
http://www.vuyiswamaseko.tiyaneProperties.co.za
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/
|
|
|
|
|
use need something like
DROP FUNCTION IF EXISTS sortString;
DELIMITER |
CREATE FUNCTION sortString(inString TEXT) RETURNS TEXT
BEGIN
DECLARE delim CHAR(1) DEFAULT ','; -- delimiter
DECLARE strings INT DEFAULT 0; -- number of substrings
DECLARE forward INT DEFAULT 1; -- index for traverse forward thru substrings
DECLARE backward INT; -- index for traverse backward thru substrings, position in calc. substrings
DECLARE remain TEXT; -- work area for calc. no of substrings
-- swap areas TEXT for string compare, INT for numeric compare
DECLARE swap1 TEXT; -- left substring to swap
DECLARE swap2 TEXT; -- right substring to swap
SET remain = inString;
SET backward = LOCATE(delim, remain);
WHILE backward != 0 DO
SET strings = strings + 1;
SET backward = LOCATE(delim, remain);
SET remain = SUBSTRING(remain, backward+1);
END WHILE;
IF strings < 2 THEN RETURN inString; END IF;
REPEAT
SET backward = strings;
REPEAT
SET swap1 = SUBSTRING_INDEX(SUBSTRING_INDEX(inString,delim,backward-1),delim,-1);
SET swap2 = SUBSTRING_INDEX(SUBSTRING_INDEX(inString,delim,backward),delim,-1);
IF swap1 > swap2 THEN
SET inString = TRIM(BOTH delim FROM CONCAT_WS(delim
,SUBSTRING_INDEX(inString,delim,backward-2)
,swap2,swap1
,SUBSTRING_INDEX(inString,delim,(backward-strings))));
END IF;
SET backward = backward - 1;
UNTIL backward < 2 END REPEAT;
SET forward = forward +1;
UNTIL forward + 1 > strings
END REPEAT;
RETURN inString;
END |
DELIMITER ;
-- example call:
SET @Xstr = "The,quick,brown,fox,jumped,over,the,lazy,dog";
SET @Ystr = "9,8,7,6,5,4,3,2,1";
SET @Zstr = "7,8,9,6,5,2,1,4,3";
SET @str = "3,2,3,4,2,3";
SELECT sortString(@str) AS s1,sortString(@Xstr) AS s2,sortString(@Ystr) AS s3,sortString(@Zstr) AS s4;
|
|
|
|
|
Your Query is Full of
>
They will not Compile in SQL
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.somee.com
http://www.vuyiswamaseko.tiyaneProperties.co.za
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/
|
|
|
|
|
the sample is of MySQL, you need to write the code for MS SQL
|
|
|
|
|
I got a Solution
create FUNCTION [dbo].[fnSplit](
@sInputList VARCHAR(8000) -- List of delimited items
, @sDelimiter VARCHAR(8000) = ',' -- delimiter that separates items
) RETURNS @List TABLE (item int)
BEGIN
DECLARE @sItem VARCHAR(8000)
WHILE CHARINDEX(@sDelimiter,@sInputList,0) <> 0
BEGIN
SELECT
@sItem=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@sDelimiter,@sInputList,0)-1))),
@sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@sDelimiter,@sInputList,0)+LEN(@sDelimiter),LEN(@sInputList))))
IF LEN(@sItem) > 0
INSERT INTO @List SELECT @sItem
END
IF LEN(@sInputList) > 0
INSERT INTO @List SELECT @sInputList -- Put the last item in
RETURN
END
go
create FUNCTION GetSortedString
(
@inputString varchar(max)
)
RETURNS varchar(max)
AS
BEGIN
declare @outputString varchar(max)
set @outputString = ''
select @outputString = ltrim(rtrim(str(item))) + ',' + @outputString from dbo.fnSplit(@inputString,',') order by item desc
if SUBSTRING(@outputString,len(@outputString),1) = ','
begin
set @outputString = SUBSTRING(@outputString,1,len(@outputString)-1)
end
return @outputString
END
GO
and Tested it like this
select dbo.GetSortedString('2,1,4,5,3,6')
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.somee.com
http://www.vuyiswamaseko.tiyaneProperties.co.za
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/
|
|
|
|
|
Hi,
The steps are:
1) Use a table valued split function to split the comma delimited strings
2) Then sort the table values
3) Loop thru each values of the table and make a customised delimited
string .
The above steps are depicted below
STEP 1:
The split function goes like this
-- Created by NILADRI BISWAS
ALTER FUNCTION [dbo].[fnSplit]
(@oldstring as varchar(100),@delimeter as varchar(1))
RETURNS @mytab table(counter int,stringval varchar(100))
AS
Begin
Declare @newstring as varchar(100)
Declare @pos as int
Declare @i as int
Declare @c as int
set @newstring = '';
set @i = 1
set @c = 0
set @pos = CHARINDEX(@delimeter, @oldstring)
WHILE (@i != 0)
Begin
set @c = @c +1
insert into @mytab(counter,stringval) values(@c,@newstring + Substring(@oldstring,0, @pos))
set @oldstring = Substring(@oldstring,@pos+1,len(@oldstring))
set @pos = CHARINDEX(@delimeter, @oldstring)
set @i = @pos;
if (@i = 0)
Begin
set @i = 0;
set @c = @c +1
insert into @mytab(counter,stringval) values(@c,@newstring + @oldstring)
End
End
return
End
Create a stored proc like this
-- Created by NILADRI BISWAS
ALTER PROCEDURE dbo.SortedList
-- Add the parameters for the stored procedure here
(@DelimitedValues AS VARCHAR(50))
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @OUTPUT VARCHAR(30)
DECLARE @TEMPOUTPUT VARCHAR(30)
SET @OUTPUT = ''
SET @TEMPOUTPUT = ''
DECLARE @MYCURSOR CURSOR
SET @MYCURSOR = CURSOR FOR
STEP 2:
SELECT STRINGVAL FROM dbo.fnSplit(@DelimitedValues,',')
ORDER BY RIGHT(REPLICATE('0', 11) + stringval, 10)
STEP 3:
OPEN @MYCURSOR
FETCH NEXT
FROM @MYCURSOR INTO @TEMPOUTPUT
WHILE @@FETCH_STATUS = 0
BEGIN
SET @OUTPUT = @OUTPUT + @TEMPOUTPUT + ','
FETCH NEXT
FROM @MYCURSOR INTO @TEMPOUTPUT
END
CLOSE @MYCURSOR
DEALLOCATE @MYCURSOR
SET @OUTPUT = SUBSTRING(@OUTPUT,0,LEN(@OUTPUT))
PRINT @OUTPUT
END
Hope this helps
Niladri Biswas
modified on Monday, June 29, 2009 11:52 AM
|
|
|
|
|
Thank its Works like wonders
Resolved
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.somee.com
http://www.vuyiswamaseko.tiyaneProperties.co.za
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/
|
|
|
|
|