I define one of my columns as FLOAT. Some of the values are negative and positive with precision 8 or 12. When I run updates against that column and then check the data all values are 0.
Any idea why?? I appreciate it.
Thanks,
David
hi guyz i am trying to trap the duplicate records . cannot trap the below dupes any idea. i also used ltrim and rtrim it still thinking it is a duplicate but it is not. what is the best way doing it.
select count(*) , muzenbr from muzealbums_1 where muzenbr = '1000082' group by MuzeNbr, PNOTES, CNOTES, CAT3, Performer having count(*) > 1
MuzeNbr nvarchar no 14 yes (n/a) (n/a) SQL_Latin1_General_CP1_CI_AS
PNOTES nvarchar no -1 yes (n/a) (n/a) SQL_Latin1_General_CP1_CI_AS
CNOTES nvarchar no -1 yes (n/a) (n/a) SQL_Latin1_General_CP1_CI_AS
CAT3 nvarchar no -1 yes (n/a) (n/a) SQL_Latin1_General_CP1_CI_AS
Performer nvarchar no 510 yes (n/a) (n/a) SQL_Latin1_General_CP1_CI_AS
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
Hello,
I'm writing a program which runs SQL express installer
Code Snippet
public bool InstalSQLExpress()
{
//creating new process for setup
Process setupProcess = new Process();
//start information for setup process
ProcessStartInfo startInfo = new ProcessStartInfo();
//filename location
startInfo.FileName = sqlExpressSetupFileLocation;
//commandline arguments ("/qb" - only basic UI during the setup, "/qn" - setup with no UI)
//startInfo.Arguments = BuildCommandLine(); // <- make it work
// startInfo.Arguments = "/qb INSTANCENAME=SQLEXPRESS ADDLOCAL=All SECURITYMODE=SQL" +
// "SQLACCOUNT=konrad SQLPASSWORD=****** " +
// "AGTACCOUNT=konrad AGTPASSWORD=******" +
// "ASACCOUNT=konrad ASPASSWORD=******" +
// "SAPWD=*****";
startInfo.Arguments = BuildCommandLine();
//we're not using system shell to process execution
startInfo.UseShellExecute = false;
//adding start info
setupProcess.StartInfo = startInfo;
setupProcess.EnableRaisingEvents = true;
setupProcess.OutputDataReceived += new DataReceivedEventHandler(setupProcess_OutputDataReceived);
setupProcess.ErrorDataReceived += new DataReceivedEventHandler(setupProcess_ErrorDataReceived);
setupProcess.Exited += new EventHandler(setupProcess_Exited);
//running the process
return (setupProcess.Start());
}
private string BuildCommandLine()
{
string strCommandLine = null;
MessageBox.Show("SQL");
if (this.sqlSecurityMode)
{
strCommandLine = "/qb";
strCommandLine += " INSTANCENAME=SQLEXPRESS";
strCommandLine += " ADDLOCAL=All";
strCommandLine += " SECURITYMODE=SQL";
strCommandLine += " SQLACCOUNT=" + sqlAccount;
strCommandLine += " SQLPASSWORD=" + sqlPassword;
strCommandLine += " AGTACCOUNT=" + sqlAccount;
strCommandLine += " AGTPASSWORD=" + sqlPassword;
strCommandLine += " ASACCOUNT=" + sqlAccount;
strCommandLine += " ASPASSWORD=" + sqlPassword;
strCommandLine += " SAPWD=" + SysadminPassword;
return strCommandLine;
}
else
{
return strCommandLine;
}
}
When I specify command with literal value it works fine but when I try to use function or variable installer prompt error message:
SQL Server Setup could not validate the service accounts. Either the service accounts have not been provided for all of the services being installed, or the specified username or password is incorrect. For each service, specify a valid username, password, and domain, or specify a built-in system account.
I don't know why it's wrong and be gracefull for help
Hello all,The followinq qurey returns sometimes values of null to some of this columns, byK,byT,byD. the column F wil not contains any nulls, and 0 will be populated in it at any case of .Now, the problem is that when sorting out F the sort will not work when there is null parameters in byK because teh fact that a 0 values is greater then NULL value, and the sort of F will not take in considiration. So I guess the question is how can I sort NULL values and 0 values to be the same weight in the sort by command? SELECT A.gym_id as gym,s_id, week, gym_name, boxer, league, sum(points)
points,sum(byK)as byK, sum(byT) as byT,sum(byPoints) as byPoints ,
sum(byD) as byD, count(C.gym) as F
FROM A inner JOIN B ON A.gym_id = B.gym_id
left JOIN C ON A.gym_id = C.gym
WHERE (B.l_id = ?text
group by A.gym_id
order by points DESC,byK DESC,byT DESC, byPoints DESC, byD DESC,F ASC
I have an XML file that my XML Source component is accessing. I have noticed that is possible to set a column in the external metadata collection to a certain datatype and the matching output column to a different datatype and this doesn't not generate a warning like it does with other source components (e.g. Flat File Source Adapter).
Try it. Set a column in your external metadata to have a datatype of DT_WSTR. Set the matching output column to DT_UI8. You will NOT get a validation error. I think you should.
This behaviour was noticed on RTM (i.e. no service pack installed) by the way.
I think this is a bug. Anyone agree?
-Jamie
Hello,
I tried to use bookmarks in OLEDB consumer templates and I have problems with using MoveToBookmark. This method sets cursor position correctly on bookmarked row, but it seems to me it doesn't set the right cursor position in DB. When I call MovePrev after MoveToBookmark, I will not get previous row of the bookmarked row.
Simplified Example - I get bookmark of the first row, then move to the 3th row. Call MoveToBookmark (current row is no the first row). But when I call MovePrev I'will not get DB_S_ENDOFROWSET but the second row:
MoveFirst()
bk = GetBookmark()
MoveNext()
MoveNext() // Now I'm on third row
MoveToBookmark(bk)
GetData() - Now I get data from 1st row
MovePrev()
GetData() - Now I get data from the second row!!
All calls to OLEDB returs S_OK.
Does somebody now, whats wrong?
Thanks
I can't take full credit for this. I want to share this with Jeff Moden who did the important research for this calculation here.
All I did was just adapting some old code according to the mantissa finding Jeff made and optimized it a little
Some test codeDECLARE@SomeNumber FLOAT,
@BinFloat BINARY(8)
SELECT@SomeNumber = -185.6125,
@BinFloat = CAST(@SomeNumber AS BINARY(8))
SELECT@SomeNumber AS [Original],
CAST(@SomeNumber AS BINARY(8)) AS [Binary],
dbo.fnBinaryFloat2Float(CAST(@SomeNumber AS BINARY(8))) AS [Converted],
@SomeNumber - dbo.fnBinaryFloat2Float(CAST(@SomeNumber AS BINARY(8))) AS [Error]
And here is the code for the function.CREATE FUNCTION dbo.fnBinaryFloat2Float
(
@BinaryFloat BINARY(8)
)
RETURNS FLOAT
AS
BEGIN
DECLARE@Part TINYINT,
@PartValue TINYINT,
@Mask TINYINT,
@Mantissa FLOAT,
@Exponent SMALLINT,
@Bit TINYINT,
@Ln2 FLOAT,
@BigValue BIGINT
SELECT@Part = 1,
@Mantissa = 1,
@Bit = 1,
@Ln2 = LOG(2),
@BigValue = CAST(@BinaryFloat AS BIGINT),
@Exponent = (@BigValue & 0x7ff0000000000000) / EXP(52 * @Ln2)
WHILE @Part <= 8
BEGIN
SELECT@Part = @Part + 1,
@PartValue = CAST(SUBSTRING(@BinaryFloat, @Part, 1) AS TINYINT),
@Mask =CASE WHEN @Part = 2 THEN 8 ELSE 128 END
WHILE @Mask > 0
BEGIN
IF @PartValue & @Mask > 0
SET @Mantissa = @Mantissa + EXP(-@Bit * @Ln2)
SELECT@Bit = @Bit + 1,
@Mask = @Mask / 2
END
END
RETURNSIGN(@BigValue) * @Mantissa * POWER(CAST(2 AS FLOAT), @Exponent - 1023)
END
Thanks again Jeff!
Peter Larsson
Helsingborg, Sweden
how do i use "sp_attach_db" properly so that i can make a new database with only the .mdf file?
View 2 Replies View RelatedI have writen a small program in a Query window that runs fine if I hilight and run small chuncks. (I have listed the statements with out the clauses so it is more easly viewed here.)
The problem is if I simply execute the Query window I get massive errors that don’t make sence. I am guessing I need some GO and BEGIN/END statements? But I don’t know where I should use them.
I would really appreciate a few pointers so I can just run the Query window.
drop table SourceFile
drop table ReferenceFile
SELECT TOP
INTO SourceFile
FROM
SELECT TOP
INTO ReferenceFile
FROM
-- test data
INSERT ReferenceFile
(AddressCleanedPK, New_First_Name, New_Last_Name, New_Address, Phone, Zip)
values ()
INSERT SourceFile
(AddressCleanedPK, New_First_Name, New_Last_Name, New_Address, Phone, Zip)
values (22)
-- test data
drop table MATCHTEST
SELECT TOP 1 AddressCleanedPK, New_First_Name, New_Last_Name, New_Address, Phone, Zip
INTO MATCHTEST
FROM AddressCleaned_npidata_20050523_20071112
DELETE MATCHTEST
ALTER TABLE MATCHTEST
ADD REF_ML VARCHAR(2)
INSERT MATCHTEST ()
SELECT
FROM ReferenceFile r
inner join SourceFile s
on s.New_Last_Name = r.New_Last_Name
select * from MATCHTEST
Hi,
How do i deploy IS package properly? can i just copy the .dtsx and assign that in an sql jobs?
thanks a lot
cherriesh
Hi,
I'm very new in SSIS. I've created 3 packages in the project. sometimes when i modified the project and save/save-all it, when i tried to build (isn't this used to deploy?), I am being asked by this:
Package 1 has been modified outside the source editor. Do you want to reload it?
When I press on yes, all my modifications were not save. If i answered no, the build process stops. i dunno if this is because the build process is already finished or it was terminated because i chose 'NO'. When i tried to rebuild it again, it will ask me the same question.
What is the proper way to save and build the project? When it says ' do you want to reload it?', does it mean reloading the old copy before modification?
Thanks!
cherrie
Hello, i have something like this, i want the annoucements (status = 0) to be on top, then topics with (status = 1) below, then the rest of the topics.
So i tried:
SELECT forum_topics.id, forum_topics.status, forum_topics.username AS starter, forum_topics.subject, forum_topics.closed, forum_topics.answerpostid, forum_topics.views, forum_topics.answers, forum_topics.lastanswer, forum_topics.lastanswerid, forum_topics.created AS started, forum_answer.username, forum_answer.answer, forum_answer.created FROM forum_topics LEFT OUTER JOIN forum_answer ON forum_answer.id = forum_topics.lastanswerid WHERE (boardid = @ID OR boardid = 0) ORDER BY (status) ASC, (created) ASC
Problem is that they are not sorted diffrently, when i change the (created) ASC to (created) DESC i get the same result and the rows are not sorted, they only get sorted by status so i have status=0 at the top, then status=1 then the rest. How do i get them to be sorted first by status ASC then by created ASC/DESC?
Patrick
I installed MDAC 2.6 SP1 on my 2000 machine in order to get SQL server 7.0 to work properly on my machine. I'm trying to get installed on server which is on NT 4.0 SP6 so that when I save a DTS package on my 2000 machine and run it on the server it doesn't get the error client access ODBC driver not capable. I intalled MDAC 2.6 SP1 on the server but it doesn't look like its updating it. The install goes fine. I reboot but when I go into the ODBC connections and look at the driver for the client acceess it still has version 6.00.00.05 while my 2000 machine says 6.00.07.00
Any ideas on why its not updating. Can I just take the DWBODBC.DLL from my machine and copy it to the server?
We have SQL Server 7.0 running on an NT 4.0 sp5 box. Whenever we stop the SQL Server Service, we get the message that SQL Agent will also be stopped. If we do this, all the services seem to stop fine, but when we try to start the SQL Server Agent Service back up, it won't let us. In order for it to restart, we have to reboot the server so that it starts 'automatically'. Does anyone have any ideas as to why this is?
View 1 Replies View RelatedI have the following trigger that will insert the correctly if none of the items in the #tempKCS exist in the destination table. However if one of the items in the #tempKCS table do exist in the Sales table then the insert does not execute for the other itmes in the #temoKCS list that are not already listed in the Sales Table. Below is the trigger. Tell me where I am making my mistake. Thanks
CREATE TRIGGER [Insert_KCSales] on [dbo].[Reclines]
FOR INSERT, UPDATE
AS
SELECT KitItemSum.Date, Kits.KitItemNo AS ItemNo, (Kits.Quantity * KitItemSum.QtySold) As QtySold, ((Kits.[Percentage]/100) * KitItemSum.AmtSold) AS AmtSold, KitItemSum.Division, 0 AS QtyReturned, 0 AS AmtReturned Into #TempKCS
FROM (SELECT Summary.Recdate AS [Date], Summary.ItemNo, Sum(Summary.Qty) AS QtySold, SUM((Summary.Amount-Summary.DiscAmt)) AS AmtSold, Summary.Division
FROM (SELECT DISTINCT R.Recno, R.Recdate,I.RecLineNo, I.ItemNo, I.Qty, I.Amount, I.DiscAmt, I.Division FROM Inserted I
INNER JOIN Kits K ON I.ItemNo = K.MasterItemNo INNER JOIN Receipt R ON I.RecNo = R.RecNo INNER JOIN Items ON I.ItemNo = Items.ItemNo) AS Summary
GROUP BY Summary.RecDate, Summary.ItemNo, Summary.Division) AS KitItemSum INNER JOIN Kits ON KitItemSum.ItemNo = Kits.MasterItemNo;
INSERT INTO Sales ([Date], ItemNo, Division, QtySold, AmtSold, Qtyreturned, AmtReturned)
SELECT [Date], ItemNo, Division, QtySold, Amtsold, Qtyreturned, Amtreturned
FROM #TempKCS
WHERE NOT EXISTS (SELECT S.[Date], S.[ItemNo] FROM Sales S INNER JOIN #TempKCS KCS on S.ItemNo = KCS.ItemNo AND S.[Date] = KCS.[Date]);
Hello friends, I have developed an ASP.NET 2.0 application. The problem is that the IIS server returns the next error:
SQL Server does not allow remote connections
Sometimes returns:
SQL Server does not exists.
My question is if there is any known bug with ASP.NET and SQL Server. My sql server have activated the Remote Connections (its working properly on other web page).
Its a problem of the number of connections??
Its a problem ofbad configuration??
Its a IIS problem??
Please bring me some help.
Hi,
We installed SQL Server 2005 on windows 2000 with IIS 5. After installation, I can see report manager using http://localhost/reports. But I can only the top part. There's no My Reports or anything, just "Home / My subscriptions / Help".
Is there anything wrong and how to fix it?
Any help would be appreciated.
Thanks
Hi i given my query here. in that joings betweens tables is correct, but i am getting the following error
ora-00933 sql command not properly ended
can we have exists function inside if condition??
select distinct loc.authorization from account acct, location loc, iom_order iom
where loc.location_id = acct.location_id and acct.account_id = iom.account_id and iom.iom_order_id =140
if(loc.authorization is null and exists(
select distinct line.INTRALATA_PIC_FREEZE ,line.INTERLATA_PIC_FREEZE
from line line, iom_order iom, activity act
where ((line.INTRALATA_PIC_FREEZE='F' or line.INTERLATA_PIC_FREEZE='F')and line.line_id = act.component_id and act.component = 23 and act.iom_order_id = 140)
union all
select distinct trunk.INTRALATA_PIC_FREEZE ,trunk.INTERLATA_PIC_FREEZE
from trunk trunk, activity act
where ( (trunk.INTRALATA_PIC_FREEZE='F' or trunk.INTERLATA_PIC_FREEZE='F')andtrunk.trunk_id = act.component_id and act.component = 25 and act.iom_order_id = 140)
select distinct rcf.INTRALATA_PIC_FREEZE ,rcf.INTERLATA_PIC_FREEZE
from remote_call_fwd rcf, activity act
where((rcf.INTRALATA_PIC_FREEZE='F' or rcf.INTERLATA_PIC_FREEZE='F')and rcf.remote_call_fwd_id = act.component_id and act.component = 24 and act.iom_order_id = 140)
select distinct tsg.INTRALATA_PIC_FREEZE ,tsg.INTERLATA_PIC_FREEZE
from nodal_tsg tsg, activity act
where ((tsg..INTRALATA_PIC_FREEZE='F' or tsg.INTERLATA_PIC_FREEZE='F')and tsg.nodal_tsg_id = act.component_id and act.component = 5 and act.iom_order_id = 140))) Then
select distinct loc.authorization from order2misc o2m, location loc, iom_order iom where loc.location_id =
o2m.serv_loc_id and iom.iom_order_id =140
else
select distinct loc.authorization from account acct, location loc, iom_order iom
where loc.location_id = acct.location_id and acct.account_id = iom.account_id and iom.iom_order_id =140
end if;
Hi,Currently, I am on SQL2000 SP3 and Windows 2003.I am trying to restore the full database backup and transaction logbackup on the development server. The full database restore is not aproblem, but the transaction log restore happened during the day is.Everytime when I try to compare the transaction in the database(select the last update date from one of my table as a benchmark, thenbackup the transaction log for restore) to the development db with therestored transaction log, I got none of the transaction from the logbackup.What did I do wrong? Is my concept about using the transaction logrestore wrong?Thanks in advance.
View 4 Replies View RelatedI have about 7 tables I need to join, but am having a lot ofdifficulty with the joins, that I need some help on.I'll provide the details of four tables to illustrate the scenario.I have one table called Product, which contains a complete list ofproducts.I have another table called Date, which contains a complete list ofdates.I have a table called sales, which contains Product, date and Qty.I have a table called Purchases which contains product, date and qty.I want to link my Purchases and sales tables to my Product and datetables.On each row I want product from the Product table, Date from the datetable, Sales Qty from the sales table and Purchases Qty from thePurchases table.I've been messing around with full outer, inner joins etc, but can'tget it right.All help greatfull.Regards,Ciarán
View 5 Replies View RelatedHi,
I have 2 servers running in a clustered mode(active/active). In which we installed SP2 on peak production time but somehow it resulted in failure i.e it installed partially and sql server is unstable as of now. I'd like to know if it would be fine to restart the server without full installation of SP2 and what could be the implications of a server restart in such a scenario.
Pls help urgent!!!!!!!
Hi,
I am exporting a table to a fixed width flat file using "Ragged Right" format. Though i specify carriage return for the last column, the text file is not taking it up.
I am doing the following for connection
cmFlatFile.properties("Format").SetValue(cmFlatFile,"RaggedRight")
cmFlatFile.properties("RowDelimiter").SetValue(cmFlatFile,vbCrLf)
and while adding the column i am setting up the width as follows
vColumns = flatFileDestination.InputCollection(0).GetVirutalInput().VirtualInputColumnCollection
For cols = 0 to vColumns.Count -1
Dim col As IDTSConnectionManagerFlatFileColumn90 = ff.Columns.Add()
if cols = vColumns.Count - 1 Then
col.ColumnDelimiter = vbCrLf
end if
cols.ColumnType = "FixedWidth"
cols.Columnwidth = 20
......
Next cols
Still the rows are exported next to each other just like when we use FixedWidth.
Hi,
I've my SSIS Packages, having multiple Configuration stored in SQL Server Table named [SSIS Configurations].
And it's devloped on BHUDEV Server ON Devlopmentdb Database. So durring development Default Cofigurations are set for BHUDEV Server.
Problem is that when I install my Package on SQL Server on different Server for example JOHN, and now my database is ProductionDB and [SSIS Configurations] table will be on ProductionDB database. And I've made required changes in [SSIS Configurations] table. So when I run the Package even then it picks configuration information from BHUDEV.Developmentdb.dbo.[SSIS Configurations] table. That's my problem.
But If when during execution time, I change the connection information then it works fine.
Please Help!
Bhudev
Hi,
Long story, Ihad SQL 2000 happily installed and running for a long time. Attempted to load SQL 2005 alongside, but in retrospect see that I used the same install directory as SQL 2000. Found that the SQL 2000 databases still ran, in that I was able to run applications that rely on them. Enterprise manager ans Query Analyzer are still in the start/program list, but when I try to open them they say that the DSN isn't there, and the odbc driver is missing. Also if I try to configure the system SQL DSN in ODBC datasources I get the same message. I guess the SQL 2005 install has scrambled the ODBC setup.Tried deinstalling SQL 2005, but the problem persists. Tried reloading mdac-typ, same problem. Tried reinstalling SQL2000, same problem. Tried fully deinstalling SQL2000 from Add/remove program, said it completed, but the shortcuts are still there, the applications that use the db's still work, only it no longer appears in Add/Remove programs.
Is there a command line string to manually remove it, or where do I go to repair my mdac, or is this what I actually want to do?
Thanks in advance
Hugh Esler
Hi,
I am using a sub-report in my main report, passing a customerid parameter into the subreport. The subreport dataset then either returns rows, or it doesn't.
When the dataset returns rows, I am using either COUNT(field) or CountRows(dataset) in a textbox, which then displays fine.
However, if the dataset returns NO ROWS, i need the textbox to return a '0' - which it does if i run the subreport on it's own.
If i run the main report, and the subreport returns no rows, no matter what expressions I use to try and get the '0' to display on the main report....it doesn't.
As a test I also put a background colour on the text control in the subreport, and it looks like the whole text control is not displaying
This is only an issue when 0 rows are returned, but i must display a 0 in this case!?!
Help!
Cheers
Chris
Hi,
I have several packages where I set up a SQL Server log provider. The packages are all called from jobs.
However, for some reason, only ONE of the packages is logging to the sysdtslog90 table in my specified database.
All of packages have the identical set up for the logging. Here are my settings under logging:
PROVIDERS AND LOGS:
Name: SSIS Log Provider for SQL Server (box is checked)
Configuration: ServerName.DBName (I use this same connection for all the packages)
DETAILS:
OnError is checked
CONTAINERS:
All items are checked
So what's going on here? I have successfully logged to this table using test cases in BIDS, and as I mentioned, ONE of the packages is actually logging to the table when it's called from the job.
Thanks
I am trying to sort by a date and then use a secondary sort on another column date field. The first date sorts properly, but the second is not in sorted order. I have also tried ORDER BY first date, second date and that queries the information in sorted by the first date, but then not sorted by the second date. Any ideas?
BJ
Does it make a difference if I use the Float or Int data type for a field such as ReceiptNumber or CheckNumber?
Thanks for any thoughts,
set ANSI_NULLS ONset QUOTED_IDENTIFIER ONgo-- =============================================-- =============================================ALTER PROCEDURE [dbo].[Product_FindByParameters](@Name Varchar(255),@ManufactureID bigint,@ShortDescription Varchar(255),@ManufactureProductID Varchar(255),@ItemsInStock bigint,@StorePartNumber Varchar(255)) ASBEGIN SELECT P.ProductId,P.StorePartNumber,P.ShortDescription,P.ManufactureProductID,P.Name,P.Price,P.ItemsInStock,M.ManufactureName FROM Product P left join Manufacture MON P.ManufactureID=M.ManufactureIDWHERE ( P.Name like '%' + @Name + '%' OR @Name is null)AND (P.ShortDescription LIKE '%' + @ShortDescription + '%' OR @ShortDescription is null)AND( P.ManufactureProductID LIKE '%' + @ManufactureProductID + '%' OR @ManufactureProductID is null)AND (P.ItemsInStock=@ItemsInStock)AND (P.ManufactureID = @ManufactureID OR @ManufactureID is null)END--exec [dbo].[Product_FindByParameters] 'Heavy-Duty ',7,'Compact Size','DC727KA' ,0,''--exec [dbo].[Product_FindByParameters] 'Heavy',7,'','','',''--exec [dbo].[Product_FindByParameters] 'Heavy','' ,'','','' ,'' First 2 exec statement gives many data row as result,But why the last donot give any row ;( ;(how can i rewrite the stored procedure, such that it gives out put even if i don't supply ManufactureID as inputkindly help me
View 2 Replies View RelatedHi all,
I use SqlHelper to connect to database. I need to set a timeout to execute some sp because I don´t want to wait for this sp if it make me wait a lot. So I set the Connection TimeOut every time I execute a sp. But this seems not working properly. I set this value to 5 and I execute a sp than runs in 15 but It waits for it.
Any idea
Best regards
www.ITCubo.net
Hi,
Running SQL 7.0 on NT4.
When server boots the SQL Server Agent appears to start normally. NT Service starts, Event Viewer shows service is started, Service Control Manager shows service started.
However when I try to view a job or create a lob I get the following error:
Error 14258: Cannot perform this operation while SQLServerAgent is starting. Please try again later.
Has anyone seen this behaviour before??
Thanks in advance
Michael
ARRGGH, I'm going to kill Excel or SQL Server!! I've spent an hour on this and I can't get it working. I'm importing some address data, and several of the zip codes are importing as NULL, even though in the Excel sheet, they look fine! Excel is complaining, something about the zip codes are numbers entered as text..whatever.. so I edited my SQL import and made it so the ZIP field gets created as varchar 50 instead of float. It still won't import right.
Someone please tell me what I'm doing wrong.
We've altered an UPDATE trigger - now it doesn't work properly. When updating a record (via a web application) by clearing the value for a particlar column, we get error:
UPDATE failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'.
Code = 800a0c93
Source = ADODB.Recordset
Description = Operation is not allowed in this context.
What the trigger does is prevent a zero-length string from being inserted into the database, which violates a check constraint, and instead inserts NULL. The web app - whose code we cannot modify - apparently does this. This was working fine, until we altered the trigger to add an additional column for update. Our SQL developer person has left, so us less knowledgable (about SQL) folks are trying to pick up the slack.
trigger code:
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
ALTER TRIGGER trigViaNetCardholderUpdateEmptyStrings
ON viewViaNetCardholder
INSTEAD OF UPDATE AS
UPDATE tblIDCross
SET
tblIDCross.chUSCId = CASE WHEN ins.chUSCId = '' THEN NULL ELSE ins.chUSCId END,
tblIDCross.chNineDigit = CASE WHEN ins.chNineDigit = '' THEN NULL ELSE ins.chNineDigit END,
tblIDCross.chPID = CASE WHEN ins.chPID = '' THEN NULL ELSE ins.chPID END,
tblIDCross.chEmployeeId = CASE WHEN ins.chEmployeeId = '' THEN NULL ELSE ins.chEmployeeId END,
tblIDCross.chAIMSNumber = CASE WHEN ins.chAIMSNumber = '' THEN NULL ELSE ins.chAIMSNumber END,
tblIDCross.intCustomerType = ins.intCustomerType
FROM INSERTED ins
WHERE tblIDCross.intUSCardId = ins.intUSCardId
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Please help...