What Is Called In Sqlserver For RPAD And LPAD?
May 31, 2001
Running sqlserver 2000
In oracle, it is called rpad and lpad for adding character to the left of
the output string or right of the output string.
Example,
I am selecting a zip_code 10200 and it truncates last 2 zeros when I
run select on the table.
If I just run select zip_code from test;
I will receive following result in oracle.
102
So, I run select rpad(zip_code,5,'0') from test;
This will add last two zeros to the the string.
Result in Oracle;
10200
How can you do this in sqlserver?
Please let me know.
Thanks,
Ranjan
View 2 Replies
ADVERTISEMENT
Mar 8, 2006
Hi
I do the next SP, but when I try to run I got an error
Line that I try to run in Query analyzer:
exec SP_RPAD('tere',7,'W')
Error:
Line 3: Incorrect syntax near 'tere'.
Procedure
CREATE PROCEDURE SP_RPAD
@Cadena varchar(200), --Cadena Original a la que se va a rellenar
@LongCadena int, --Longuitud final que tendra la cadea
@Char_llenarchar(1)--Caracter con el que se va a rellenar
AS
SELECT RIGHT(REPLICATE(@Char_llenar,@LongCadena) + @Cadena ,@LongCadena) AS RPAD
GO
Any idea what I am doing wrong?
View 3 Replies
View Related
Oct 13, 2007
Hi,
In oracle there is are two functions rpad and lpad.
In SqlServer is there any function similar to this.
View 3 Replies
View Related
Jul 23, 2002
Hello all, dumb question here I'm sure.
I have social security numbers stored in an INT data type column. Leading zeros keep getting dropped.
I am executing a stored procedure to export the data into other tables and fixed length text files, and when I do, the leading zeros drop.
Example
SSNO = 11111
I want this 000011111
But I get the 11111
I've used this command.
select (REPLICATE(0, (9 - len(11111))) + '11111')
Which works fine.
But! When I substituted @v_ssno for the literal string, It truncates the leading zeros!
select (REPLICATE(0, (9 - len(@v_ssno))) + @v_ssno)
So I have tried CAST to make it a VARCHAR, hopeing that it would not clip the zeros. But it still does every time I put it in a variable holder.
Oracle has a command called LPAD. Basically you tell it the total length of the field, and the character to "pad" on the left with.
I can't for the life of me find the equivalent in MS SQL 2000.
Any information is massively appreciated!
Thanks.
View 3 Replies
View Related
Mar 11, 2008
Hi,
I just want to know how do you called when you are accessing another pc thru \. I forgot how it is called.
Second, I like to know what [MACH] and [INST]. They don't look like directories.
\MultforestHEALTH[MACH][INST]HEALTH_WAREHOUSEDeploy_20080228p_Vitals_FACT.sql'
View 9 Replies
View Related
May 10, 2007
We have a static class that makes an HTTPWebRequest to get XML data from one of our vendors. We use this as input to a stored proc in SQLServer2005. When I compile this class and call it from a console application in visual studio it executes in milliseconds, everytime. When I compile it, create the assembly and clr function and execute it in SQLServer, it takes around 14 seconds to execute the first time, then on subsequent requests it is again really fast, until I wait for 10 seconds and re-execute, once again it is slow the first time and then fast on subsequent requests. We do not see this behavior when executing outside SQLServer. Makes me think that some sort of authentication is perhaps taking place the first time the function is run in SQLServer? I have no idea how to debug this further. Anyone seen this before or have any ideas?
Here is the class:
Code Snippet
using System;
using System.Collections.Generic;
using System.Text;
using System.Net;
using System.IO;
namespace Predict.Services
{
public static class Foo
{
public static string GetIntradayQuote(string symbol)
{
string returnQuote = "";
HttpWebRequest request = (HttpWebRequest)(WebRequest.Create("http://data.predict.com/predictws/detailed_quote.html?syms=" + symbol + "&fields=1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,28,30"));
request.Timeout = 1000;
HttpWebResponse response = (HttpWebResponse)(request.GetResponse());
StreamReader streamReader = new StreamReader(response.GetResponseStream());
returnQuote = streamReader.ReadToEnd();
streamReader.Close();
response.Close();
return returnQuote;
}
}
}
When I run call it from a console app it is fine.
I compile it into a dll and then create the assembly and function as follows:
Code Snippet
drop function fnTestGetIntradayQuoteXML_SJS
go
drop assembly TestGetIntradayQuoteXML_SJS
go
create ASSEMBLY TestGetIntradayQuoteXML_SJS from 'c:DataBackupsCLRLibrariesTestGetIntradayQuote_SJS.dll' WITH PERMISSION_SET = EXTERNAL_ACCESS
go
CREATE FUNCTION fnTestGetIntradayQuoteXML_SJS(@SymbolList nvarchar(max)) RETURNS nvarchar(max) AS EXTERNAL NAME TestGetIntradayQuoteXML_SJS.[Predict.Services.Foo].GetIntraDayQuote
go
declare @testing nvarchar(max)
set @testing = dbo.fnTestGetIntradayQuoteXML_SJS('goog')
print @testing
When I execute the function as above, again, really slow the first time, then fast on subsequent calls. Could there be something wrong with the code, or some headers that need to be set differently to operate from the CLR in SQLServer?
Regards,
Skipper.
View 1 Replies
View Related
Sep 20, 2006
Hi experts;
I have a problem with unicode character 0x2300
I created this table
create table testunicode (Bez nchar(128))
Insert Data
insert into testunicode (Bez)values('Œ€„¢')
with 2 Unicode characters
Œ€ = 0x2300
„¢ = 0x2122
Selecting the data
select Bez from testunicode
I see
"?„¢"
„¢ = 0x2122 is ok but instead of 0x2300 there is 0x3f
When I modify the insert statement like that ( 8960 = 0x2300 )
insert into testunicode (Bez)values(NCHAR(8960)+'„¢')
and select again voila i see
"Œ€„¢"
Does anyone have an idea?
Thanks
View 1 Replies
View Related
Apr 18, 2008
I am trying to 'load' a copy of a SQLServer 2000 database to SQLServer 2005 Express (on another host). The copy was provided by someone else - it came to me as a MDF file only, no LDF file.
I have tried to Attach the database and it fails with a failure to load the LDF. Is there any way to bypass this issue without the LDF or do I have to have that?
The provider of the database says I can create a new database and just point to the MDF as the data source but I can't seem to find a way to do that? I am using SQL Server Management Studio Express.
Thanks!!
View 1 Replies
View Related
Jan 16, 2004
How can I execute or access a web page from a DTS package?
Both SQL server AND website are hosted on the same server (a Dual 2.4Gz Xeon with 2Gb RAM, RAID 5 etc)
I have 2 tables in SQL server 2000 that hold orders. These need to be posted into another table at a predefined time (ie: 4:30pm) and at the same time, access a remote address (a web service) and post certain elements of the order back.
Basically, can anyone help me out on how to execute a web page from a DTS.
I do NOT want to access a DTS from a webpage, which is all I'm finding at the moment.
View 5 Replies
View Related
Oct 17, 2001
Hello,
Question is, when I try to create relationships in SQL 7 without using the wizard..upon adding the tables I get an errror message that says "Coinitialization has not been called". What does this mean?
View 1 Replies
View Related
Jan 24, 2007
CREATE PROCEDURE sp_getT
@m1 int ,
@txn int ,
@Pan varchar(50) ,
@Act varchar(50) OUTPUT,
@Bal Decimal(19,4) OUTPUT,
@CBal Decimal(19,4) OUTPUT
AS
declare @pBal money, @pCbal money, @pAct money
SET NOCOUNT ON
IF @m1 = 200
BEGIN
IF @txn = 31
BEGIN
exec ChkBal @Pan, @pBal output, @pCbal output, @pAct out
END
END
SET @Act = @pAct
SET @Bal = cast(@pBal as Decimal(19,4))
SET @CBal = cast(@pCBal as Decimal(19,4))
return @Act
return @Bal
return @CBal
the above code returns this error message
"Server: Msg 8144, Level 16, State 2, Procedure CheckBalance, Line 0
Procedure or function ChkBal has too many arguments specified."
How do i specify all the arguments i want in the called procedure?
View 14 Replies
View Related
Jul 31, 2007
Hi everyone -
I'm stumped on what to cal this, there might even be
a method or pattern named for what i am trying to accomplish...
In the database, a number field is included on each table
When the DAL reads the record from the database, it is passed to
the client - work is possibly done to the record and is sent
back to the DAL for update.
A query is done against the table to retrieve the record again,
the numbers are compared - if they don't match, it is assumed the record
been modified by another user/thread/activity. An error is returned to the client stating the data has been changed.
if the numbers match, the record is updated with the number field being incremented by one.
what is this methodology called (beside crap :-) )
thanks
tony
View 7 Replies
View Related
Mar 30, 2006
Hello, perhaps you guys have heard this before in the past, but here iswhat I'm looking for.I have a SQL 2000 table with Social security numbers. We need tocreate a Member ID using the Member's real SSN but since we are notallowed to use the exact SSN, we need to add 1 to each number in theSSN. That way, the new SSN would be the new Member ID.For example:if the real SSN is: 340-53-7098 the MemberID would be 451-64-8109.Sounds simply enough, but I can't seem to get it straight.I need this number to be created using a query, as this query is areport's record source.Again, any help would be appreciated it.
View 9 Replies
View Related
Jan 19, 2007
Hi
I have a drop down list which gets populated from database but I want to add the default value as
--Select----
View 4 Replies
View Related
Apr 22, 2008
This seems like a basic question but I have not been able to find the answer in the help files or by searching this forum.
Is a trigger called for each row updated or is it called once for all rows updated?
for example if I have:
Code Snippet
CREATE TRIGGER mytrigger
ON mytable
AFTER UPDATE
AS
BEGIN
EXEC e-mail-me inserted, N'mytrigger', getdate()
END
and I do this
Code Snippet
UPDATE mytable
SET mycolumn = N'whatever'
WHERE ID > 5 AND ID <= 10
Assuming there is a record for each nteger value of ID, than will mytrigger run 5 times (once for each row updated) or one time (with inserted containing all 5 rows)?
View 3 Replies
View Related
May 6, 2008
Hi,
When a column is evaluated against an UDF in a SELECT ... or WHERE ... It makes sense that the UDF is called for every row of the SELECT. But is it still true if the UDF is called in a subquery as below?
Code Snippet
SELECT LineID, AnyText FROM dbo.UdfTest WHERE LineID IN (SELECT LineID FROM dbo.F_Bogus())
I've made a test and the SQL Profiler Trace shows that the UDF is called only once.
Can anyone confirm if my test is valid and most importantly that the UDF is called only once? FYI, I never use sub queries. This is to clarify a technical detail for our performance investigation.
Thank in advance for any help.
Here is the code to setup the test:
USE NorthWind
GO
CREATE TABLE dbo.UdfTest (
LineID int Identity(1,1) NOT NULL,
AnyText varchar(200) COLLATE database_default NOT NULL
)
GO
INSERT dbo.UdfTest (AnyText) VALUES ('Test1')
INSERT dbo.UdfTest (AnyText) VALUES ('Test2')
INSERT dbo.UdfTest (AnyText) VALUES ('Test3')
GO
CREATE FUNCTION dbo.F_Bogus (
)
RETURNS @tab TABLE (
LineID int NOT NULL,
AnyText varchar(100) COLLATE database_default NOT NULL)
AS
BEGIN
INSERT @tab (LineID, AnyText) VALUES (1, 'UDF1')
INSERT @tab (LineID, AnyText) VALUES (2, 'UDF2')
INSERT @tab (LineID, AnyText) VALUES (3, 'UDF3')
INSERT @tab (LineID, AnyText) VALUES (4, 'UDF4')
INSERT @tab (LineID, AnyText) VALUES (5, 'UDF4')
RETURN
END
GO
Here is the capture of SQL Profiler when executing the statement:
SELECT LineID, AnyText FROM dbo.UdfTest WHERE LineID IN (SELECT LineID FROM dbo.F_Bogus())
SQL:BatchStarting SELECT LineID, AnyText FROM dbo.UdfTest WHERE LineID IN (SELECT LineID FROM dbo.F_Bogus()) 51 2008-05-06 17:58:31.543
SQLtmtStarting SELECT LineID, AnyText FROM dbo.UdfTest WHERE LineID IN (SELECT LineID FROM dbo.F_Bogus()) 51 2008-05-06 17:58:31.543
SPtarting SELECT LineID, AnyText FROM dbo.UdfTest WHERE LineID IN (SELECT LineID FROM dbo.F_Bogus()) 51 2008-05-06 17:58:31.577
SPtmtCompleted -- F_Bogus INSERT @tab (LineID, AnyText) VALUES (1, 'UDF1') 51 2008-05-06 17:58:31.577
SPtmtCompleted -- F_Bogus INSERT @tab (LineID, AnyText) VALUES (2, 'UDF2') 51 2008-05-06 17:58:31.577
SPtmtCompleted -- F_Bogus INSERT @tab (LineID, AnyText) VALUES (3, 'UDF3') 51 2008-05-06 17:58:31.577
SPtmtCompleted -- F_Bogus INSERT @tab (LineID, AnyText) VALUES (4, 'UDF4') 51 2008-05-06 17:58:31.577
SPtmtCompleted -- F_Bogus INSERT @tab (LineID, AnyText) VALUES (5, 'UDF4') 51 2008-05-06 17:58:31.577
SPtmtCompleted -- F_Bogus RETURN 51 2008-05-06 17:58:31.577
SQLtmtCompleted SELECT LineID, AnyText FROM dbo.UdfTest WHERE LineID IN (SELECT LineID FROM dbo.F_Bogus()) 51 2008-05-06 17:58:31.543
SQL:BatchCompleted SELECT LineID, AnyText FROM dbo.UdfTest WHERE LineID IN (SELECT LineID FROM dbo.F_Bogus()) 51 2008-05-06 17:58:31.543
View 5 Replies
View Related
Aug 20, 2007
My package inserts rows into tables with triggers.
The triggers are not being called.
What might be causing this?
View 1 Replies
View Related
Jul 29, 2005
I'm chasing after a documetn that was available on one of the Microsoftwebsites that was titled somethign like "MS SQL Server Best Practices"and detailed a nyumber of best practices about securing the server.Included in this was revoking public access to the system tableobjects.Can someone post the URL where I can pick this up, or drop me a note oncontacting them for a copy of the document?
View 2 Replies
View Related
Jun 14, 2006
I have an app that uses a sqlserver 2000 jdbc driver to connect to a sqlserver 2000.
Is it possible to do a direct replacement of sqlserver 2000 with sqlserver 2005 express just by reconfiguring the app to point to the express? The app would still be using the sqlserver 2000 jdbc driver to try and make the connection.
If that is a possibility, what can be some differences in the configuration? Previously with 2000 the config information I entered is:
server name: "machinename"( or ip). I've also tried "machiname/SQLEXPRESS"
DB name: name of db instance
port: 1433(default)
user and pass.
My attempts so far results in
"java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]Error establishing socket."
and
"java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]Unable to connect. Invalid URL."
View 1 Replies
View Related
Feb 9, 2006
Hello,
I have an SQLServer Mobile database, and I would like to know if there is a way to upgrade it to SQLServer 2005 (.mdf) database. My database has no records in it, just the structure (tables etc). What I am actually asking is if I can create automatically a new SQLServer 2005 Database with the same structure as my existin SQLSErver Mobile database
Thanks in advance,
TassosTS
View 1 Replies
View Related
Oct 12, 2005
Hello everyone:
I have some nightly jobs that execute stored procedure to call the tables? I want to know which table are called by these stored procedures. Is it possible? Any idea will be appreciated.
Thanks
ZYT
View 5 Replies
View Related
Oct 13, 2007
I am encountering an issue which is effecting our production environment, none of our sql jobs are now saving any kind of job history, nor are the status of the jobs being saved.
I have run a profiler trace and it seems as if procedure : sp_sqlagent_log_jobhistory is not being called during the execution of any of our jobs
Has anyone else encountered anything similar?
View 3 Replies
View Related
May 3, 2006
Hi
In a stored procedure the following code snippet 1 checks against duplicate data being inserted. I've tested it with snippet 2 and it works as expected. However, when the procedure is called from ASP.NET the check seems ineffective. I still get the error msg. The application uses a SqlDataSource with the following parameters.
Any suggestions?
Thanks,
Bakis.
PS I want to ask a question on the ASP.NET forum .The login/pwd for this forum "get me in" to the .net forum in the sense that when I log in I see a logout link. I don't get an "ask a question" button though. Is there a separate screening for each forum?
<UpdateParameters>
<asp:Parameter Name="CatItemUID" Type="Int32" />
<asp:Parameter Name="CatName" Type="String" />
<asp:Parameter Name="Item" Type="String" />
<asp:Parameter Name="Quad" Type="Int16" />
<asp:Parameter Name="UID" Type="Int64" />
</UpdateParameters>
snippet 1 :
if (@CatItemComboExists > 0 )
BEGIN
--print @CatItemComboExists
return 0
END
snippet 2:
begin tran
declare @return_status int
EXECUTE @return_status = spUpdateCatItemRec 343, 'blah','blih', 2,3
print @return_status
rollback
error msg only if proc is called from app
Violation of UNIQUE KEY constraint 'IX_tblCatItemUID'. Cannot insert duplicate key in object 'tblCatItemUID'.
View 3 Replies
View Related
Oct 12, 2006
I am maintaining some C# and ASP.NET code with SQL server 2000. My code calls a stored procedure. The code is a little confusing as to the name of the SQL Server stored procedure that is calling. At this point I don't know how to trace into or debug the stored procedure. Kind of hard to do in the first place when you are not absolutely certain as to the stored proc to be called.I can take an educated guess as to which stored procedure is being called. I figure if I can deliberately make a certain stored procedure fail then this might be able to somehow give me the name of the stored proc that I am calling.So is there a way to do this. Namely make a stored procedure fail, or to return the name of the stored Proc?I would sincerely appreciate some help with this problem.
View 1 Replies
View Related
Feb 23, 2007
Im using a SqlDataSource control. Ive got my "selectcommand" set to the procedure name, the "selectcommandtype" set to "storedprocedure"What am i doing wrong ? Ive got a Sql 2005 trace window open and NO sql statements are coming through "ds" runat="server" ConnectionString="<%$ ConnectionStrings:myConnectionString %>" SelectCommand="my_proc_name" SelectCommandType="StoredProcedure">
"txtF1" Name="param1" Type="String" />
"txtF2" Name="param2" Type="String" />
"" FormField="txtF3" Name="param3" Type="String" />
"" FormField="txtF4" Name="param4" Type="String" />
View 2 Replies
View Related
Sep 12, 2005
Hi All,I have a report ASP.NET page that allow users to run a report by clicking a buttion to call a store procedure to generate the report, however, the store procedure is taking a few minutes to return the data, thus I got the 'timeout' error message on my page. How do I extend the time on my page?Thanks
View 3 Replies
View Related
Dec 11, 2001
Can someone please reply w/ example syntax on how to receive calculated variables from an invoked SP. DESCRIPTION:
sp_caller invokes sp_calc_values (passing @var1, @var2) via:
exec sp_calc_values @var1, @var2
sp_calc_values receives @var1, @var2 then calculates @var3, @var4
HOW does sp_caller receive the calculated values @var3, @var4 ???
thx in advance
View 2 Replies
View Related
Feb 2, 1999
Hi,
Please help me to elucidate a mistery !
I have a little SP in a database:
CREATE PROCEDURE XXXTest AS
DECLARE @a varchar(50)
SELECT @a = NULL
IF @a NOT IN ('x', 'y')
BEGIN
RAISERROR ('Error',16,1)
RETURN 0
END
RETURN 1
If I execute it from SQL Server, it returns 0. (after the error, of course).
If I execute it from MS ACCess through a Pass Through Query, it returns 0.
On the same server !!!
How could it to make me that ? I have been thought the SP is executed entirely on the server !!!
Thanks in advance clearing me the mind.
Mircea.
View 2 Replies
View Related
Sep 14, 2005
Hello all. I have a stored procedure that seems to skip the execution of an entire line of code (which happens to be a call to execute another stored procedure), but ONLY if it is being used from C# (using the System.Data.SqlClient namespace... object: SqlCommand, etc).
Basic structure:
Event SP: top-most parent
-->Calls UpdateXs SP: This is the SP that will not call his child SP
----->Calls UpdateX: This is the SP that is being ignored when called from .NET (In a loop, each iteration, max iterations 10).
(1) I can call the top-most stored procedure (Event SP) with the same arguments in Query Analyzer and all SP's will execute.
(2) I can call UpdateXs SP with the same arguments that are being passed to it from Event SP in the Query Analyzer, and again, it's child sp executes (UpdateX).
(3) I can also call the last child (UpdateX) directly in Query Analyzer with the same arguments that are passed in production (from executing the top-most parent in C#), and it executes correctly.
I'm positive that at no point the last child (UpdateX) is being passed NULL values. I made debug tables and before execution of an SP from within another SP, I store all the arguments in a debug table--none are null. Also, to figure out what was wrong with the last child (UpdateX) , the first statement in inserts into another debug table, just to show that that execution has made it into the sp--when the top parent is called from C#, it doesn't insert into my table (doesn't execute).
There are statements inside Event SP that execute after the sp call to UpdateXs SP. These statement always execute. Also, there are statements inside UpdateXs SP that execute after the sp call to the last child. These statement always execute too.
I am now clueless. I had developed & debugged all sp's in Query Analyzer before trying to use it live (with the C#). It's always fine in Query Analyzer, but ALWAYS skips the last sp in production.
Is there some fundamental principle I am missing here in MS SQL stored procedures? I am only 3 levels deep in SP calls, so I didn't think that would be an issue (I have made SP's that went a LOT deeper in calls than that in the past with no issues).
Any insight much appreciated. Thanks in advance.
View 2 Replies
View Related
Jan 27, 2007
Hello,
I am a student and is currently busy with an SQL project. I am having trouble with creating a view to calculate and display the hours worked for each call rate..
It goes...
I have two tables: rate and consultancydetail.
The rate table consists of columns named - id, time descripiton and rate.
The consultancydetail table - invoiceID, rateID date, startTime and endTime.
My view should display the rateId column and then there should be a column created in the view for the total hours.
total hours is calculated by adding the difference between startTime and endTime and then added to each total for each rateId.
PLEASE CAN SOMEONE HELP. I am new to this and know it should be a simple thing.
cheers,
Arend
View 5 Replies
View Related
Jan 31, 2007
Hi,
I have to create a view that displays the average quantity used per invoice for each product. and substitute NULL values with zeros.
Please I need help and will appreciate any!
I have a MATERIALDETAIL table with columns invoiceID, productCode and Quantity.
THIS IS WHAT I HAVE SOFAR:
*************************
create view vw_ProductAverage
as
select distinct product_code
from materialdetail
go
select * from vw_productAverage
go
***************************
PLEASE help.
View 2 Replies
View Related
Jul 20, 2005
Hi,I have a large FoxPro table with an index that I need to be Queried from SQLServer by OLE.DB or ODBC. If I query the DBF directly a search takes 1Minute +. Is there a way I can call the data from the table and use theexisting FoxPro Index?ThanksSteve
View 1 Replies
View Related
May 27, 2008
OK, here's the scenario:
I have some libraries (DLL) that I call from a regular console application. The data access methods get the connection string from the app.config, nothing special there.
The thing is: now I need to use the libraries inside a SSIS package. I call them from a script task, and everything is fine, except for the connection string.
Is there a way that I can get the connection string from the SSIS package configuration like I would from the app.config? Maybe some alternative to ConfigurationManager.AppSettings["ConnString"] in the DTS Runtime?
Thanks in advance...
View 3 Replies
View Related
