When I use the following to get the StoredProc parameters, the IN
variables are returned correctly as "IN" but all the OUT variables
returned as "INOUT" in the PARAMETER_MODE instead of "OUT". Is this a
bug?
SELECT PARAMETER_MODE,PARAMETER_NAME,DATA_TYPE FROM
INFORMATION_SCHEMA.PARAMETERS WHERE SPECIFIC_NAME =
'SPName_with_IN_OUT_Variables'"
Thanks,
Frankk
I'm working with PivotTable on Excel 2000 which is connected to an OLAP server (from SQL Server 7 installation). The pivot is intended to analyze Sales during April 2001. Yesterday I found out that OLAP/Excel returned/displayed inconsistent data. The 'April Total' value is NOT equal to the 'Quarter 2 Total' (I already inspected the underlying database and sure that there is absolutely NO data for months after April 2001). The value for 'April Total' is the correct one. I'm not sure whether the problem resides on the OLAP Server or Excel (pivot) itself. For ones who like to help me I would be glad to supply you with the screenshots (just email me).
Please help.
Regards
Dave
Hi all,
I am a newcomer to Microsoft Database Technology and have appeared to come across an issue with the SUM function in SQL Server Mobile Edition.
I am running Visual Studio 2005 and have created 2 tables:
Orders and OrderLines which are set up in master detail fashion.
The SQL Statement I create in the Query Builder is as follows:
SELECT Orders.OrderNo, Orders.OrderDate, Orders.Priority, Orders.Address, SUM(OrderLines.Quantity * OrderLines.QualityReference) AS Total
FROM Orders, OrderLines
WHERE Orders.OrderNo = OrderLines.OrderNo
GROUP BY Orders.OrderNo, Orders.OrderDate, Orders.Priority, Orders.Address
Now, the SUM returns a total for all records in the OrderLines table, and not just the records whose OrderNo is the same as Orders.OrderNo
Can someone out there please clarify whether its an issue with my code or a bug with SQL Server Mobile???
Here are a couple of screenshots which will demonstrate what I mean.
Here is the contents of the Orders and OrderLines tables. Each order has only 1 line item in it.
http://public.fotki.com/AussieNoobie/sqlerrors/vs2005sqlceordersdata.html
When performing the summation over the OrderLines table, it produces the SUM of the all records in OrderLines and not according to the GROUP BY clause. See the following screen shot.
http://public.fotki.com/AussieNoobie/sqlerrors/vs2005sqlceerror1.html
I hope this explains it better.
Anyone have any ideas???
Thanks in advance
STDEV() gives incorrect values with reasonable input.
I have a table filled with GPS readings. I've got a column LATITUDE (FLOAT) with about 20,000 records between 35.6369018 and 35.639890. (Same value to the first 5 digits of precision---what can i say, it's a good gps.)
Here's what happens when I ask SQL Server ("9.00.1399.06 (IntelX86)") to compute the standard deviation of the latitude:
// Transact-SQL StdDev function:
SELECT STDEV(LATITUDE) FROM GPSHISTORY
WHERE STATTIME BETWEEN '2007-10-23 11:21:00.859' AND '2007-10-23 17:00:00.062' AND GPSDEVICEID = 0x004A08BC04050000;
0
// Zero. ZERO??!?!!
//Let's re-implement Std Dev from the definition using other aggregate functions:
DECLARE @AVERAGE FLOAT;
SELECT @AVERAGE = AVG(LATITUDE) FROM GPSHISTORY
WHERE GPSDATE BETWEEN '2007-10-23 11:21:00.859' AND '2007-10-23 17:00:00.062' AND GPSDEVICEID = 0x004A08BC04050000;
SELECT SQRT(SUM(SQUARE((LATITUDE - @AVERAGE)))/COUNT(LATITUDE)) FROM GPSHISTORY
WHERE GPSDATE BETWEEN '2007-10-23 11:21:00.859' AND '2007-10-23 17:00:00.062' AND GPSDEVICEID = 0x004A08BC04050000;
6.03401924005392E-06
// That's better. Maybe STDEV is using fixed point arithmetic?!?
SELECT STDEV(10 * LATITUDE)/10 FROM GPSHISTORY
WHERE GPSDATE BETWEEN '2007-10-23 11:21:00.859' AND '2007-10-23 17:00:00.062' AND GPSDEVICEID = 0x004A08BC04050000;
4.77267753808509E-06
SELECT STDEV(100 * LATITUDE)/100 FROM GPSHISTORY
WHERE GPSDATE BETWEEN '2007-10-23 11:21:00.859' AND '2007-10-23 17:00:00.062' AND GPSDEVICEID = 0x004A08BC04050000;
1.66904329068838E-05
SELECT STDEV(1000 * LATITUDE)/1000 FROM GPSHISTORY
WHERE GPSDATE BETWEEN '2007-10-23 11:21:00.859' AND '2007-10-23 17:00:00.062' AND GPSDEVICEID = 0x004A08BC04050000;
8.11904280806654E-06
// The standard deviation should, of course, be linear, e.g.
DECLARE @AVERAGE FLOAT;
SELECT @AVERAGE = AVG(LATITUDE) FROM GPSHISTORY
WHERE GPSDATE BETWEEN '2007-10-23 11:21:00.859' AND '2007-10-23 17:00:00.062' AND GPSDEVICEID = 0x004A08BC04050000;
SELECT SQRT(SUM(SQUARE(100*(LATITUDE - @AVERAGE)))/COUNT(LATITUDE))/100 FROM GPSHISTORY
WHERE GPSDATE BETWEEN '2007-10-23 11:21:00.859' AND '2007-10-23 17:00:00.062' AND GPSDEVICEID = 0x004A08BC04050000;
6.03401924005389E-06
// Std Dev is a numerically stable computation, although it does require traversing the dataset twice.
//
// This calculation is not being done correctly.
//
// Incidently, SQRT(VAR(Latitude....)) gives 4.80354E-4, which is also way off.
I will redefine STDEV to use a stored procedure similar to the above, but the algorithm used to compute VAR, STDEV etc should be reviewed and fixed.
-Rob Calhoun
i have sql2000 & sql2005 on the same machine. I am unable to register my localhost in sql2000, get an access denied error. How can I make my localhost use sql2000 database?
View 1 Replies View RelatedGrrr!I'm trying to run a script:print 'Declaring cursor'declare cInv cursor forward_only static forselectdistinctinv.company,inv.contact,inv.address1,inv.city,inv.state,inv.postalcode,inv.cmcompanyidfromdedupe.dbo.ln_invoice as invleft joindedupe.dbo.customerid as cidondbo.fnCleanString(inv.company) = cid.searchcowhere((inv.customerid is nulland cid.searchco is null)and (inv.date >= '01/01/2003' or (inv.date < '01/01/2003' andinv.outstanding > 0.01))and not inv.company is null)print 'Cursor declared'declare@contact varchar(75),@company varchar(50),@address1 varchar(75),@city varchar(30),@state varchar(20),@zip varchar(10),@cmcompanyid varchar(32),@iCount int,@FetchString varchar(512)open cInvprint 'cursor opened'fetch cInv into@company,@contact,@address1,@city,@state,@zip,@cmc ompanyidprint 'Cursor fetched @@Cursor_rows = ' + cast(@@cursor_rows asvarchar(5))All the prints are there to help me figure out what's going on!When I get to the Print 'Cursor fetched @@cursor_rows....the value is 0 and the script skips down to the close and deallocate.BUT, if I just highlight the Select...When section, I get over 2,000rows. What am I missing?Thanks.
View 6 Replies View Related
i am in the process of Migrating SQL 2000 to my new SQL2000 server i want to know the what would the best way for me to migrate one SQL server to another SQL server on the same network and rename the new server to the old server and bring it up for use in our ecommerce website.
This is the error it gives me for my code and then it calls out line 102. Line 102 is my buildDD(sql, ddlPernames) When I comment out this line the error goes away, but what I don't get is this is the same way I build all of my dropdown boxes and they all work but this one. Could it not like something in my sql select statement. thanksPrivate Sub DDLUIC_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles DDLUIC.SelectedIndexChanged
Dim taskforceID As Byte = ddlTaskForce.SelectedValueDim uic As String = DDLUIC.SelectedValue
sql = "select sidstrNAME_IND from CMS.dbo.tblSIDPERS where sidstrSSN_SM in (Select Case u.strSSN from tblAssignedPersonnel as u " _
& "where u.bitPresent = 1 and u.intUICID in (select intUICID from tblUIC where intTaskForceID = " & taskforceID & " and strUIC = '" & uic & "'))"ddlPerNames.Items.Add(New ListItem("", "0"))
buildDD(sql, ddlPerNames)
End Sub
What I am trying to create a query to check, If recDT is not value or null, then will use value from SELECT top 1 recDtim FROM Serv. Otherwise, will use the value from recDT. I have tried the below query but it doesn't work. The error says, Incorrect syntax near the keyword 'SELECT'.Incorrect syntax near the keyword 'else'.1 SELECT
2 case when recDT='' then SELECT top 1 recDtim FROM Serv else recDT end
3 FROM abc
4
Anyone can help? Thanks a lot.
CREATE PROCEDURE UpdateAccount
(
@AccountID int,
@EmailAddress varchar(255),
@Password varchar(16)
)
AS
DECLARE @actualPassword varchar(16)
SELECT @actualPassword = Password
FROM Account WHERE AccountID = @AccountID
If Exists( SELECT EmailAddress FROM Account
WHERE EmailAddress = @EmailAddress )
RETURN -1
ELSE IF @Password <> @actualPassword
RETURN - 2
ELSE
UPDATE Account
SET
EmailAddress = @EmailAddress,
Password = @Password
WHERE AccountID = @AccountID
GO
Whats wrong with the following code,its compiling but returning only first memberid. i want all memberids to be returned
DECLARE @memberList VARCHAR(4000),@hDoc INT
SET @memberList = '<MemberList><MemberID>7136</MemberID><MemberID>7137</MemberID><MemberID>7138</MemberID></MemberList>'exec sp_xml_preparedocument @hDoc OUTPUT, @memberListSELECT MemberID FROM OPENXML (@hdoc, 'MemberList', 2) WITH (MemberID BIGINT)
EXEC sp_xml_removedocument @hDoc
@Names is a query string passed in, I need to count the number of records as a result of the below query/
Dim test As String
Dim sqlConnection3 As New SqlConnection("data Source=EQ-520-WEBSQLEXPRESS;Initial Catalog=CRDB.MDF;Integrated Security=True")
Dim cmd As New SqlCommand
Dim returnValue As Object
cmd.CommandText = "SELECT COUNT(ReqID) AS Expr1, LineManager FROM TblReqMain GROUP BY LineManager HAVING (LineManager = @Names)"
cmd.CommandType = Data.CommandType.Text
cmd.Connection = sqlConnection3
cmd.Parameters.Add("@Names", Data.SqlDbType.NVarChar)
sqlConnection3.Open()
cmd.Parameters("@Names").Value = test
If test = "" Then
Response.Write("An error occured")
Exit Sub
Else
returnValue = cmd.ExecuteScalar()
sqlConnection3.Close()
Label6.Text = "Number " & returnValue
I have a stored procedure with an output bit variable. When I set this variable to 0, I get an output of 0. When I set this variable to 1, I get an output of -1. What is going on?
View 9 Replies View RelatedThe following query returns 0 when executing in Query Analyzer:SELECT isnull(Count(*),0) as total FROM SplitDetail WHERE SiteCode = 14 AND ProjectID = 4367Yet ExecuteScalar() in vb.net return a -1.
Any ideas on what I might be doing wrong... ?
I have an sp (attached below) that returns records as desired when run in the query analyzer, but when run from within my adp I get only a "the sp ran successfully but did not return any records" message box. I would like to use it as a record source for a report, and when I try to do that I get a message box saying that the "Provider command for child rowset does not produce a rowset".
To repeat, the sp runs fine in QA, just not in access.
Any thoughts, anybody? Many thanks. David
Alter Procedure spDailyMicroLabel
@Date Datetime = null
AS
IF @Date IS NULL
BEGIN
SET @Date=getdate()
SELECT FERMID, fldfermtank AS "Tank", CONVERT(varchar(12), fldfilltime, 101) AS "Filled",
fldfermprod AS "Brand", CONVERT(varchar(15),fldfilltime, 108) AS "FillTime", CONVERT(varchar(12), @Date, 101) as "ReportDate",
fldfermidold AS "FermenterCode",
CAST(CAST(CONVERT(char(8),GETDATE(),112) AS datetime) - flddtferfil AS int) AS "FillDay",
"Sample Test" = CASE
WHEN CAST(CAST(CONVERT(char(8),GETDATE(),112) AS datetime) - flddtferfil AS int)=1
THEN 'HLP/UBA'
WHEN CAST(CAST(CONVERT(char(8),GETDATE(),112) AS datetime) - flddtferfil AS int) = 3 THEN 'XTF'
WHEN CAST(CAST(CONVERT(char(8),GETDATE(),112) AS datetime) - flddtferfil AS int) = 4 THEN 'HLP, TF'
WHEN CAST(CAST(CONVERT(char(8),GETDATE(),112) AS datetime) - flddtferfil AS int) = 6 THEN 'TA'
WHEN CAST(CAST(CONVERT(char(8),GETDATE(),112) AS datetime) - flddtferfil AS int) = 7 THEN 'HLP/UBA, BU/Color'
WHEN CAST(CAST(CONVERT(char(8),GETDATE(),112) AS datetime) - flddtferfil AS int) = 10 THEN 'HLP'
WHEN CAST(CAST(CONVERT(char(8),GETDATE(),112) AS datetime) - flddtferfil AS int) = 13 THEN 'HLP/UBA'
WHEN CAST(CAST(CONVERT(char(8),GETDATE(),112) AS datetime) - flddtferfil AS int) = 16 THEN 'HLP'
WHEN CAST(CAST(CONVERT(char(8),GETDATE(),112) AS datetime) - flddtferfil AS int) = 19 THEN 'HLP/UBA'
WHEN CAST(CAST(CONVERT(char(8),GETDATE(),112) AS datetime) - flddtferfil AS int) = 90 THEN 'HLP/UBA'
WHEN CAST(CAST(CONVERT(char(8),GETDATE(),112) AS datetime) - flddtferfil AS int) = 91 THEN 'HLP/UBA'
WHEN CAST(CAST(CONVERT(char(8),GETDATE(),112) AS datetime) - flddtferfil AS int) = 92 THEN 'HLP/UBA'
WHEN CAST(CAST(CONVERT(char(8),GETDATE(),112) AS datetime) - flddtferfil AS int) = 93 THEN 'HLP/UBA'
WHEN CAST(CAST(CONVERT(char(8),GETDATE(),112) AS datetime) - flddtferfil AS int) = 94 THEN 'HLP/UBA'
WHEN CAST(CAST(CONVERT(char(8),GETDATE(),112) AS datetime) - flddtferfil AS int) = 95 THEN 'HLP/UBA'
WHEN CAST(CAST(CONVERT(char(8),GETDATE(),112) AS datetime) - flddtferfil AS int) = 96 THEN 'HLP/UBA'
ELSE 'No Test Micro'
END
INTO #tblTempA
FROM vwUnfilteredFermenters
SELECT #tblTempA.FermenterCode AS "Fermenter ID", #tblTempA.Filled AS "Fill Date", #tblTempA.FillDay,
tblMicroTestDays.Test, tblMicroTestDays.HLPResults AS "HLP Results", tblMicroTestDays.UBAResults AS "UBA Results"
FROM tblMicroTestDays RIGHT JOIN #tblTempA ON tblMicroTestDays.TestDay = #tblTempA.FillDay
END
hello,
could any one tell how to go about keeping carriage returns and line breaks when inserting a chunk of text so that when that text is recieved it will still contain those carriage returns and line breaks?
also, what datatype would be most appropriate?
thakyou,
zaffi.
When I was using a simple query using select statement with where clauses, I can get the results. When
I use AND to specify more conditions. It returns no row even though I get the result when query seperately.
What should be the possible cause of this. I am using SQL Server 6.5. Thank you
Hi folks,
I have a problem where my subquery is returning two values and I need it to return only one. I believe it is doing this because of the group by DocType clause as there are two DocTypes.
Is there anyway I can sum these values in the subquery, to return only one value. This way the query should run ok.
Thanks for any help you might be able to give,
robinsql
SELECT OCRD.Cardcode,
OCRD.cardname,
@YearStart AS YearStart,
@YearEnd AS YearEnd,
@PeriodStart AS PeriodStart,
@PeriodEnd AS PeriodEnd,
OCRD.County,
(SELECT CASE WHEN OINV.DocType = 'I' AND SUM(INV1.Quantity) <>0 THEN COALESCE(SUM(INV1.Price * INV1.Quantity), 0) ELSE COALESCE(SUM(INV1.Price), 0) END FROM INV1, OINV WHERE INV1.DocEntry = OINV.DocEntry AND OINV.CardCode = OCRD.CardCode AND OINV.DocDate >= @YearStart AND OINV.DocDate <= @YearEnd GROUP BY OINV.DocType) AS YTDInvoice,
FROM
OCRD, OINV
WHERE
CardType='C'
I've got some text files that I need to read and make some calculations, before inserting data into a table.
When I use:
Code:
select BulkColumn as txt from openrowset(BULK 'c:Tempdata35.txt', SINGLE_NCLOB) as text
It only retrieves the first row. How can I select all the lines in the file?
I'm having trust issue with my HAVING clause.
I'm trying to find customers who have spent X (in this case 500 or more) dollars in a month
--photogs spending $500 or more on products in April, 2008
SELECT PhotogCode, SUM(ProductRevenue)
FROM TBL_Reporting_DailySalesRevenueAndProductUseBy_Photog
WHERE DATEPART(MM, [Date]) = 4
AND DATEPART(YY, [Date]) = 2008
GROUP BY PhotogCode
HAVING SUM(ProductRevenue) >= 500
However, I'm finding that if I use a variation of this (see below)
--all photogs spending money on products in April, 2008 ordered by total
SELECT PhotogCode, SUM(ProductRevenue)
FROM TBL_Reporting_DailySalesRevenueAndProductUseBy_Photog
WHERE DATEPART(MM, [Date]) = 4
AND DATEPART(YY, [Date]) = 2008
GROUP BY PhotogCode
ORDER BY SUM(ProductRevenue) DESC
and I look at the returned rows, I'm discovering that there are an additional 12 customers who have spent $500 or more...is there something I'm missing with the query using HAVING?
Thanks, Steph
I havethe following query which returns one row of data, however, the MedicalcodeID is NULL.
SELECT db1.dbo.Referral.ReferralGuidDigest, dbo.patient.PatientID, dbo.Consultation.ConsultationID, dbo.Staff.StaffID,
db1.dbo.Referral.EffectiveDateTime AS EventDate, db1.dbo.Referral.Status AS ReferralStatus, db1.dbo.Referral.Mode AS ReferralMode,
db1.dbo.Referral.ServiceType, db1.dbo.Referral.Urgency, db1.dbo.Referral.Direction, db1.dbo.Referral.Transport,
db1.dbo.Referral.EndedDate, db1.dbo.Referral.ReceivedDate, dbo.lkupMedical.MedicalCodeID, db1.dbo.Referral.Term,
[code]...
It is clear from teh above - that the expected MedicalCodeID = 33959 and NOT NULL. I dont understand why SQL added the COLLATE SQL_Latin1_General_CP1_CS_AS to the query - am working on a database developed by another person. Could it be the ACode and ReadCode in dbo.lkupMedical is not set up with SQL_Latin1_General_CP1_CS_AS. How to implement to LkupMedical table....
I changed HIGHLIGHTED JOIN to Inner/Right but it never yielded any results, no record found..
When I'm checking the syntax in Query Analyzer for the following code, I get the error:
"Must declare the variable '@TempVariableTable' "
spGetTableTesting returns a table
CREATE PROCEDURE spGetTable
(@VALUE1 nvarchar(50),
@VALUE2 nvarchar(50))
AS
SET NOCOUNT ON
DECLARE @TempVariableTable table
(Field1 int)
EXEC @TempVariableTable = spGetTableTesting @VALUE1,@VALUE2
GO
How do I set the @TempVariableTable to the results from spGetTableTesting?
Thanks
It is common for me to need to create ratios from data in my database such as
SELECT
( list_value / sale_price ) as ratio
FROM
values
The value returned is always an integer whether decimal is cast or not. IE if sale_price is > list_value then 1 or 0 is returned instead of the percentage (ratio) as expected. Only whole numbers are returned.
BTW. Same is true in postgres db I have as well. What is it that I am doing wrong?
doco
Hi all
According to BOL, Stored Procedure SP_DEPENDS will return two result sets , showing Dependency of an object for example a Table
USE Northwind
EXEC sp_depends 'Customers'
But when i execute the above command nothings happens !!! and sql server returns nothing ? why? where am i wrong?
Thanks in advance.
Regards.
Goodmorning,Could I have a SELECT statement that normally returns two rows,but that instead returns one row appending to the first row the secondone of the result ?For exampleQuery: "SELECT username from tab1 where year in (2001,2002)"Result:1° - "'John'"2° - "'Adam'"Instead I need:Result:"'John','Adam'"?I have Win2000 Pro , SqlServer2000.Thank You--Posted via Mailgate.ORG Server - http://www.Mailgate.ORG
View 3 Replies View RelatedMy first foray into the SQL CLR world is a simple function to return the size of a specified file.
I created the function in VS2005, where it works as expected.
Running the function in SSMS, however, returns a value of zero, regardless of the file it is pointed at.
Here's the class member code:
Public Shared Function GetFileSize(ByVal strTargetFolder As String, ByVal strTargetFile As String) As Long
' Returns the size of the specified file.
' Parameters: strTargetFolder = path to target file, strTargetFile = target file name.
Dim lngFileSize As Long
Dim objFileInfo As FileInfo
' Confirm file exists.
If Not File.Exists(strTargetFolder & "" & strTargetFile) Then
Return -1
End If
Try
objFileInfo = My.Computer.FileSystem.GetFileInfo(strTargetFolder & "" & strTargetFile)
lngFileSize = objFileInfo.Length
Catch
' TODO: add error handling; system folders cause error during processed.
End Try
Return lngFileSize
End Function
In SSMS (sp2), here's my assembly steps (this is my local dev machine; I have admin rights):
sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO
-- For file system access, set the database TRUSTWORTHY property.
ALTER DATABASE dba_use
SET TRUSTWORTHY ON
CREATE ASSEMBLY GetFolderInfo
FROM 'C:ProjectsGetFolderInfoGetFolderInfoinDebugGetFolderInfo.dll'
WITH PERMISSION_SET = UNSAFE
Here's the udf declaration:
CREATE FUNCTION dbo.udfGetFileSize( @strTargetFolder nvarchar(200), @strTargetFile nvarchar(50) )
RETURNS bigint
AS EXTERNAL NAME
GetFolderInfo.[GetFolderInfo.clsFolderInfo].GetFileSize
And the function call - note the target file is on a remote server. Actual file name differs slightly:
SELECT dbo.udfGetFileSize('\SomeServerName$MSSQL2000MSSQLData', 'SomeDBName_Data.MDF')
This always returns zero with no error displayed. Running Profiler was little help and there's not much in the Event Log.
The function returns correct values in VS2005.
The assembly is created with UNSAFE because using EXTERNAL_ACCESS resulted in a security error that prevented the assembly from being created, let alone running. Security is, I suspect, at the root of this issue as well, but I'm not sure what or where to look to verify this.
Any assistance is greatly appreciated.
Hi all,
My Problem is Hoe the Stored Procedure returns data to the Calling Env.If it is fieds values then we can use the OUT Parameters.In Case of My SP returns more number of rows .Then how we capture the data and returs to the Calling Env.i know that we can use the Cursors in Oracle but how is it in SQLSERVER???????pls
pls Help me throw some samll exps.Any help Really appriciate..ThaksRams
After doing an insert, I am using a System.Data.SqlClient.SqlDataReader to fetch the identity of the inserted row with the following code:
dbCommand.CommandText = "SELECT @@IDENTITY As LogID SET NOCOUNT OFF"Dim dataReader As System.Data.SqlClient.SqlDataReader = dbCommand.ExecuteReaderdataReader.ReaddataReader.GetInt32(0)dataReader.CloseThe problem is that I'm getting an invalid cast exception. If I use dataReader.GetDataTypeName(0), it tells me that value is a decimal? This is odd because the identity column in the SQL table is an int (size: 4). When I use very similar code for another database on the same server, it works .Anybody have any ideas?
Hello Dears;
I added an SqlDataSource to count rows number and display it in the footer of GridView. but idon't know how to refer to that ogject to display the rowcounts. I tried to declare it with Dim but didn't worked out.
Any Idea , pleas?
Thanks alot
Hi,
how can I remove newline characters and tabs from a char field in sql.
We are directing the results from a query to an excel and having the newlines in one of the columns is messing up the whole output.
thanks
Zoey
Hi,
I am trying to run the following:
SQLMAINT.EXE -USA -P -S abc -D db1 -CkDB -Rpt c:log_backup.rpt -DelTxtRpt 1days
But it simply returns the
SQLMaint switches and doesn't run the actual -CkDB?
Do I need to register any DLL to make this work ?
Thanks in advance,
I bet every SQL-Server guy in this forums has fought pageiolatch issues from one time to another. Either bad indexing, overloaded disks / controllers etc. I know I have, with a great variety of systems and solutions.
Anyways.... I simply have to ask for any of your experiences, as I'm currently stuck with a Navision client, that ocassionally stalls on pageiolatch on even simple queries.
User selects a customer account, asks to see his / her details, scrolls up and down a bit, and whammo, stall, and pageiolatch appears on the server.
Something tells me that this might be an ODBC driver issue rather than actual sql-server issues. Fetching the initial data isn't the issue, but scroling back and forth in the recorsset it seems to cause the cursor to freeze or at least lose track or stall in the progress of paging through the records.
All of the above sounds very unclear, I know, I'm just trying tro track down or center on the problem, so that I might find a solution. So if you have any experiences with clients, cursors and odbc driver version for sql-server, that really make a lot of trouble, let me know.
Cheers, Trin
I have written sql query
select INVOICE.InvoiceTypeCode, INVOICE.TarrifHeadNumber,CETSH.GoodsDescription,
INVOICETYPEMASTER.InvoiceTypeName, INVOICEITEMS.ItemQuantity as SumQuantity,
INVOICE.BasicValue ,INVOICE.BasicValue * INVOICE.ExchangeRate +
[Code].....
I am getting different amount 984000.0000 and quantity 9.