How Can I Retrieve Sql 2000 Encrypted Column Data From SQL 2005 Stored Proc?????

Aug 4, 2006

Hi...

I want to retrieve SQL 2000 Encrypted Column Data From SQL 2005 strored proc. My Stored Procedure was on SQL 2000 and it works fine....Then I restore Database From SQL 2000 to SQL 2005. The Following Statement is on my store proce.

select user_id , Encrypt(user_pass) from OpenRowset('SQLOLEDB','myserver';'sa';'mypass',databasename.dbo.users) as a

The Following Error I get When I execute the above statement.

Msg 195, Level 15, State 10, Line 1

'Encrypt' is not a recognized built-in function name.

Thank you.

Bal.

View 9 Replies

Stored Proc From 2000 To 2005

Apr 3, 2008

Hi, I have the following stored procedure working in SQL 2000:

(
@p_ServiceID INT = NULL
)

AS

SELECT
S.ServiceID,
.............
.............

FROM
tblServices AS S
WHERE
(@p_ServiceID = NULL OR @p_ServiceID = S.ServiceID)

This returns all services if a null or nothing is passed, and returns a specific service if a proper service ID is passed.

However, in 2005 this doesn't work. If I pass a service ID, it returns it, but if I pass nothing or a null, it returns no rows.

Any ideas?

Thanks,
James

View 5 Replies View Related

Retrieve Parameter Details Prior To Execution Of Stored Proc

Nov 13, 2004

I want to dynamically build a page for a given stored proc.

Given the name of the sproc how can i return the names, datatypes & sizes of the parameters ?

I then needs to find the fields in the result set to generate the page.

Any ideas ?

View 4 Replies View Related

Backup Database With Encrypted Column Data

Jan 25, 2007

Hi,
I have database with encrypted column data in my customer server. Sometime, I might need to backup their database back to office for troubleshooting.

How could I backup/restore master key, symmetric and asymmetric key created for my database?

Thank you

View 6 Replies View Related

SQL 2005, Replicating Encrypted Data?

Jun 1, 2006

Hello,

I've been searching for information on this for awhile unsuccessully. I am using SQL Server 2005 Enterprise Edititon and transactional replication with separate publisher, distributor and subscribers. I need to encrypt data on our publisher and then I need to replicate that data out (i.e., encrypted data, not encrypted transmission). Since the keys are server specific, I imaging there might be some difficulty in doing this, but I figure I can't be the only one with this problem. Does anyone have a resource or know what must be done to get this accomplished?

Thanks,

Aaron Lowe

View 8 Replies View Related

Execute Stored Procedure Y Asynchronously From Stored Proc X Using SQL Server 2000

Oct 14, 2007

I am calling a stored procedure (say X) and from that stored procedure (i mean X) i want to call another stored procedure (say Y)asynchoronoulsy. Once stored procedure X is completed then i want to return execution to main program. In background, Stored procedure Y will contiue his work. Please let me know how to do that using SQL Server 2000 and ASP.NET 2.

View 3 Replies View Related

SQL 2000 Stored Proc Vs DTS

Jun 8, 2007

Is there any capability / advantage that a DTS gives you over writing native Stored Procs using linked servers for data transfers? Besides a nice GUI?

Thanks!
DotNetNow

View 2 Replies View Related

How To Retrieve Data From SQL SERVER 2000 ?

Nov 25, 2005

Hello,
Im using Visual Studio 2005 to code ASP.NET
How to query my MS SQL SERVER 2000 to retrieve data from my DataBase?
Ive tried some tutorials, but i kept giving me errorsCan you put a simple source code to retrieve something like "select userID, Name, Age from Users"
Thank you,

View 2 Replies View Related

Passing A Column Into A Stored Proc?

Sep 7, 2006

I'm writing a simple voting script and have columns for each options. I need to update the data based on whichever option the user picks.I.e...If the user picks option 1 then execute UPDATE mytable SET option1 = option1 + 1If the user picks option 2 then execute UPDATE mytable SET option2 = option2 + 1 Etc., etc.What's the best way to do that without building an ad-hoc SQL statement? There could be many options so I dont want to have lots of redundant SQL statements.Can I just use a varible in a stored proc and do something like this? UPDATE mytable SET @optionUserpicked=@optionUserpicked + 1Thanks in advance

View 2 Replies View Related

Connnecting To An MS Access DB With An MS SQL 2000 Stored Proc

Jul 20, 2005

I would like to access a table in MS Access from a stored procedure on MSSQL 2000. The MS Access DB is running on a diferent computer attached on thesame network. I think I need to create an ODBC connection from the MS SQL2000 to the MS Access DB but not sure. Secondly how do I refer to the tablein the MS SQL stored proc?Thanks for your input,Joe Seamour

View 1 Replies View Related

Retrieve Data Into 3 Column Table

Feb 3, 2014

What would be the most efficient method of constructing a sql statement to retrieve certain record into (this gets tricky) rows of a table with 3 columns date.desc.

<table>
@foreach(var row in maytable){
<tr>
<td>@row.Most_Recent_Record1</td>
<td>@row.Most_Recent_Record2</</td>
<td>@row.Most_Recent_Record3</</td>
</tr>
}
</table>

View 1 Replies View Related

Retrieve Data From Web Services Using DTS (SQL Server 2000)

May 2, 2006

The project I'm currently working on involves combining data from one SQL Server 2000 databases and XML returned from a web service into a 3rd SQL Server 2000 database.

This process must be scheduled to happen once a day. If it weren't for the Web Service, I'd say that this is a no-brainer and I'd use DTS. However, I'm not sure if I can even access a Web Service with DTS. Has anyone done this or have any tips?

View 1 Replies View Related

Load Blob Into DB Using A Stored Proc, SQL Server 2000

May 15, 2008

Hi,

I want to load a file into a blob column using a stored proc. The file will be on a UNC path on the network. Is there a system stored proc or any utility that can be fired off from the sp, using xp_cmdshell that will take a file path from the filesystem and load it into a blob column in a database table.

I have worked with blob fields in the past but in those instances I loaded the blobs into the database from front end ADO.net code. This time around I need to pull this off stricktly from a stored proc.

Any help will be appreciated.

thank you.

View 1 Replies View Related

Failed To Retrieve Long Data For Column

Mar 23, 2007

Hi,

We have a SSIS project where we load lot of image files using an OLE DB Source component and put it across the database as varbinary into OLEDB Destination component.

Things were fine until now where am getting an error like this. alongside the log also reads like,

There is not enough space on the disk. Long data was retrieved and can't be added to the data flow task buffer.

SSIS Error Code: DTS_E_InducedTransformFailureOnError.

Is this method of loading the files using an OleDb Souce not the efficient one ? If not is there a way I can get this done. Comments are highly appreciated!!

Thanks in Advance

View 8 Replies View Related

Daily Totals In Column Format Using A Stored Proc

Mar 27, 2008

I am usings transact sql..

Using a stored proc. I need a generic starting point. I am trying to get order categories ie. oranges, bananas, pears on the horizontal and then the daily totals vertically for each day of the month. Just say the # under ea is the total of boxes received daily. The total under each category would be determined by the receipt date. Also
I need a total category for each fruit under each as month-date and year-to-date. This should be updated daily. Obviously my actual prog is more complicated then this but I just need an idea where to start.
Product Oranges Bananas Pears Total Fruit
03/1/08 1 2 5 8
03/2/08 0 2 1 3
03/3/08 4 0 1 5
mtd
ytd

View 2 Replies View Related

Using The AS Clause In A SQL Stored Proc -- Problem Using Datetime Column

Jul 20, 2005

I'm trying to concatenate fields in SQL stored proc for use in textfield in asp.net dropdownlist. I'm running into a problem when I tryto use a DateTime field, but can't find the answer (so far) on theInternet. Was hoping someone here would know?My sql stored proc:SELECT AnomalyID, DateEntered + ', ' + Station + ', ' + Problem As'SelectInfo'FROM tblAnomalyWHERE WorkOrder=@varWO AND Signoff=NullORDER BY DateEnteredbut I get the error:The conversion of a char data type to a datetime data type resulted inan out-of-range datetime value.I'm not the brightest bulb on the block, so any clues greatlyappreciated!Thanks, Kathy

View 3 Replies View Related

Debugging SQL Server 2005 Stored Proc With Visual Studio 2005

Aug 2, 2006

Hello,

we have a SQL server 2005 with Visual studio Prof. 2005 in the
employment.

The debuggers function only in Visual studio correctly, as long as no
code on the SQL server must be implemented.

If a BREAK POINT in a Stored Procedure is set, this is not activated,
since this cannot be bound.

Does someone know, what it lies and can like one it eliminate?

Thank you for your assistance in advance.

Yours sincerely

Big_Ben_31

This entry was translated automatically with the translation
service babel.altavista.com from the German into English.

View 1 Replies View Related

Creating A View To Retrieve Data From More Than One Database Sql Server 2000

Jul 26, 2007

Hi everyone,

we have some reference tables in in a specific database. that other applications need to have access to them. Is it possible to create a view in the application's database to retrive data from ref database while users just have access to the application Database not the view's underlying tables?

Thanks

View 1 Replies View Related

Find Stored In Server Vide Proc For Given Table And Column

Jul 1, 2004

Hi,
I need to list out all the stored proc for all the DB in entire server.
My inpute parameter will be table name and column1/column2/column3...etc.
Please give me your input for my request.
Thank,
Ravi

View 2 Replies View Related

Retrieve Data From A Stored Procedure

Jul 27, 2004

Hello Group
I am new to stored procedures and I have been fighting this for a while and I hope someone can help. In my sp it checks username and password and returns an integer value. I would like to retrieve some data from the same database about the user (the users first and last name and the password of the user). I can’t retrieve the data. Here is my code.
CREATE PROCEDURE stpMyAuthentication
(
@fldUsername varchar( 50 ),
@fldPassword Char( 25 )--,
--@fldFirstName char( 30 ) OUTPUT,
--@fldLastName char( 30 ) OUTPUT
)
As
DECLARE @actualPassword Char( 25 )
SELECT
@actualPassword = fldPassword
FROM
tbMembUsers
Where
fldUsername = @fldUsername
IF @actualPassword IS NOT NULL
IF @fldPassword = @actualPassword
RETURN 1
ELSE
RETURN -2
ELSE
RETURN -1

SELECT
fldFirstName,
fldLastName,
fldPassword
FROM
tbMembUsers
Where
fldUsername = @fldUsername
GO
############### login page ################
Sub Login_Click(ByVal s As Object, ByVal e As EventArgs)
If IsValid Then
If MyAuthentication(Trim(txtuserID.Text), Trim(txtpaswrd.Text)) > 0 Then
FormsAuthentication.RedirectFromLoginPage(Trim(txtuserID.Text), False)
End If
End If
End Sub

Function MyAuthentication(ByVal strUsername As String, ByVal strPassword As String) As Integer
Dim myConn As SqlConnection
Dim myCmd As SqlCommand
Dim myReturn As SqlParameter
Dim intResult As Integer
Dim sqlConn As String
Dim strFirstName, strLastName As String

sqlConn = ConfigurationSettings.AppSettings("sqlConnStr")
myConn = New SqlConnection(sqlConn)

myCmd = New SqlCommand("stpMyAuthentication", myConn)
myCmd.CommandType = CommandType.StoredProcedure

myReturn = myCmd.Parameters.Add("RETURN_VALUE", SqlDbType.Int)
myReturn.Direction = ParameterDirection.ReturnValue

myCmd.Parameters.Add(Trim("@fldUsername"), Trim(strUsername))
myCmd.Parameters.Add(Trim("@fldPassword"), Trim(strPassword))
myCmd.Parameters.Add("@fldFirstName", strFirstName)
myCmd.Parameters.Add("@fldLastName", strLastName)
myCmd.Parameters.Add("@fldPassword", strPassword)

myConn.Open()
myCmd.ExecuteNonQuery()
intResult = myCmd.Parameters("RETURN_VALUE").Value

myConn.Close()

'If strPassword = 55555555 Then
' Session("intDefaultPass") = 1
'End If

Session("strFullName") = strFirstName & " " & strLastName
Session("strPassword") = strPassword

If intResult < 0 Then
If intResult = -1 Then
lblMessage.Text = "Username Not Registered!<br><br>"
Else
lblMessage.Text = "Invalid Password!<br><br>"
End If
End If

Return intResult
End FunctionAt this time I am not getting any errors. How can I retrieve the data that I am after?
Michael

View 4 Replies View Related

Error: Unable To Retrieve Column Information From The Data Source

Jul 7, 2006

Hi,

I am trying to set up a data flow task. The source is "SQL Command" which is
a stored procedure. The proc has a few temp tables that it outputs the final
resultset from. When I hit preview in the ole db source editor, I see the
right output. When I select the "Columns" tab on the right, the "Available
External Column List" is empty. Why don't the column names appear? What is
the work around to get the column mappings to work b/w source and
destination in this scenario.

In DTS previously, you could "fool" the package by first compiling the
stored procedure with hardcoded column names and dummy values, creating and
saving the package and finally changing the procedure back to the actual
output. As long as the columns remained the same, all would work.
Thats not working for me in SSIS.

Thanks in advance.
Asim.

View 9 Replies View Related

Copy Database With Encrypted Column To New Server And Decrypt Column There

Aug 17, 2006

To do this successfully do I need to backup the Service master, Database master, and database itself from the the Source server, then restore all three of them on the destination server?

(I'm concerned that restoring the source Service Master key to a new target server with an existing sql 2005 install will screw things up big time.)

TIA,

Barkingdog

View 1 Replies View Related

Can You Trace Into A Stored Proc? Also Does RAISERROR Terminate The Stored Proc Execution.

Feb 13, 2008

I am working with a large application and am trying to track down a bug. I believe an error that occurs in the stored procedure isbubbling back up to the application and is causing the application not to run. Don't ask why, but we do not have some of the sourcecode that was used to build the application, so I am not able to trace into the code.
So basically I want to examine the stored procedure. If I run the stored procedure through Query Analyzer, I get the following error message:
Msg 2758, Level 16, State 1, Procedure GetPortalSettings, Line 74RAISERROR could not locate entry for error 60002 in sysmessages.
(1 row(s) affected)
(1 row(s) affected)
I don't know if the error message is sufficient enough to cause the application from not running? Does anyone know? If the RAISERROR occursmdiway through the stored procedure, does the stored procedure terminate execution?
Also, Is there a way to trace into a stored procedure through Query Analyzer?
-------------------------------------------As a side note, below is a small portion of my stored proc where the error is being raised:
SELECT @PortalPermissionValue = isnull(max(PermissionValue),0)FROM Permission, PermissionType, #GroupsWHERE Permission.ResourceId = @PortalIdAND Permission.PartyId = #Groups.PartyIdAND Permission.PermissionTypeId = PermissionType.PermissionTypeId
IF @PortalPermissionValue = 0BEGIN RAISERROR (60002, 16, 1) return -3END

View 3 Replies View Related

Column Level Constraints... I Need A Constraint, Not A Rule, Or A Trigger Or A Stored Proc... Thnx.

Jul 17, 2001

Hello all.

First of all, I've been a reader of swynk.com for quite sometime now, and I'd like to say 'thank you' to everyone who contributes.

Today, I'm the town moron.. haha I'm having issues with column level constraints. I have a varchar(50) where I want to keep *,=,#,/, .. etc, OUT OF the value input. I don't want to strip them. I simply want for sql to throw an error if the insert contains those (and other characters). The only characters that I want in the column are A-Z and 0-9. However, it's not a set number of characters per insert. It always varies... There has to be an easier way to do this than creating a constraint for every possibilty... Any help would be greatly appreciated.

tia,

Jeremy

View 1 Replies View Related

Problem To Retrieve Data From SQL Server 7.0 Via Stored Procedure

Jun 16, 2001

I'm writing VB 6.0 codes to retrieve data from SQL Server 7.0. if the sql code is embedded in VB, everything works fine. But when I tried to use stored procedure, one error called "invalid object name 'author'" occurs. author is a table in my database and obviously is in the database. what's wrong?

appreciate for any suggestion!
...mike

View 1 Replies View Related

Debug Stored Proc's In MSSQL 2005

Mar 15, 2007

In SQL Server 2000's Query Analyzer, you can debug a stored procedure by right-clicking a SP and select "Debug".
You can then step through the SP one line at a time.I don't see this in SQL Server 2005 and have searched bu cannot find any documentation on what happened to this feature.What did Microsoft do with this?

View 1 Replies View Related

SQL 2005 - Consuming Web Services In A Stored Proc

Aug 22, 2006

Assume I've created a web service named 'SaveActivityData' for some SQL 2005 database on some remote server. Also assume that the stored proc takes two parameters; the first is an integer value and the second an XML value.

1) Is it possible for a stored procedure on a completely different SQL 2005 server to consume this web service?

2) If the above is possible, could someone show me an example of the T-SQL syntax required to consume this web service, passing the two required parameters? In your example, you can make up whatever URL you like for the remote web service location.

Thanks very much - Amos.

View 4 Replies View Related

SQL Svr 2005 - Use Same Stored Proc && Views For 2 Databases?

Jan 10, 2007

I am moving a fairly large app from Access 2003 to C#/SQL Server 2005.

The app has one front end mde, but it has two backend data mdbs, one is used in the US and one in the UK.

The two backend mdb's are identical in structure, but of course the data is different. There are 150 tables or so, and we plan to maintain separate databases after the upgrade.

Now, of course, all of the queries are in the front end, the backend db's contain only tables.

In the new world, I do not want to have to maintain the same views and stored procs in two different SQL Server databases.

Is there a way to have all views and stored procs in one database, but have them draw on the data in the two other databases. There must should be a way to deal with this scenario in SQL Server.

Many thanks
Mike Thomas

View 5 Replies View Related

Debug Stored Proc's In MSSQL 2005

Mar 15, 2007

In SQL Server 2000's Query Analyzer, you can debug a stored procedure by right-clicking a SP and select "Debug".

You can then step through the SP one line at a time.

I don't see this in SQL Server 2005 and have searched but cannot find any documentation on what happened to this feature.
What did Microsoft do with this?

View 3 Replies View Related

Stored Proc Duplicating Data

Aug 15, 2006

Hi all, I have a stored proc which returns twice the result and I dontknow why. Can someone have a look at the following code?BTW, I commented the last SELECT/JOIN, cause that one doubled theresult too.CREATE procedure ent_tasks_per_user_company (@companyName as varchar(50),@resourceName as varchar(50))ASSELECTtasks.WPROJ_ID as WPROJ_ID, tasks.ENT_ProjectUniqueID asProjectUniqueID, tasks.ENT_TaskUniqueID as TaskUniqueID,tasks.TaskEnterpriseOutlineCode1ID as TaskEnterpriseOutlineCode1ID,codes.OC_NAME as OC_NAME, codes.OC_DESCRIPTION as OC_DESCRIPTION,codes.OC_CACHED_FULL_NAME as OC_CACHED_FULL_NAME,taskStd.TaskName as TaskName, taskStd.TaskResourceNames asTaskResourceNames, taskStd.TaskPercentComplete as TaskPercentCompleteINTO #myTempFROM MSP_VIEW_PROJ_TASKS_ENT as tasksINNER JOIN MSP_OUTLINE_CODES as codesON(codes.CODE_UID = tasks.TaskEnterpriseOutlineCode1IDANDcodes.OC_CACHED_FULL_NAME LIKE @companyName + '.%')INNER JOIN MSP_VIEW_PROJ_TASKS_STD as taskStdON(taskStd.WPROJ_ID = tasks.WPROJ_IDANDtaskStd.TaskUniqueID = tasks.ENT_TaskUniqueID--AND--taskStd.TaskResourceNames LIKE '%' + @resourceName + '%')WHERE (tasks.TaskEnterpriseOutlineCode1ID <-1)/*SELECT #myTemp.*, taskCode.OC_NAME as Department FROM #myTempINNER JOIN MSP_OUTLINE_CODES taskCodeON(taskCode.CODE_UID = #myTemp.TaskEnterpriseOutlineCode1ID)*/SELECT * FROM #myTemp WHERE #myTemp.TaskResourceNames LIKE '%' +@resourceName + '%'Thank you!Chris

View 3 Replies View Related

Stored Proc For Inserting Data

Jan 12, 2008

got a blank mssql 2005 express database. my table name is aspnet_IPNUmber. got two (2) columns IPNumberStart and IPNumberEnd both varchar(Max).

could somebody make a sample stored procedure for me that will insert the following records? im still learning how to make stored procs and my webhost only allow creating database in my domain but not uploading my database.

977600512
977666047

977731584
977764351

1024000000
1024032767

1024334848
1024334911

1024361504
1024361727

1024361760
1024361775

1024361792
1024361799

1024361824
1024361839

1024361984
1024362495

1024365824
1024366335

1024368128
1024368383

1024369408
1024369919

1024370688
1024371455

1024372224
1024372479

1024373248
1024373503

1024373888
1024374015

1024376192
1024376319

1024376480
1024376511

1024376832
1024393215

1025277952
1025294335

1062222976
1062223039

1062244312
1062244319

1062262784
1062263039

1064211840
1064211967

1072922624
1072922879

1072926720
1072926975

1072934400
1072934655

1072934944
1072934975

1072935680
1072935807

1072936448
1072936959

1074757800
1074757807

1077003688
1077003695

1078428256
1078428263

1079387904
1079388159

1079406080
1079406591

1081582080
1081582087

1081583216
1081583231

1081584168
1081584191

1081589104
1081589111

1091692644
1091692653

1093057408
1093057423

1103678544
1103678551

1103678656
1103678719

1104003456
1104003583

1104265216
1104265727

1104492288
1104492543

1104881088
1104881151

1105153216
1105153279

1106484352
1106484415

1106564608
1106564863

1113643148
1113643157

1113644092
1113644121

1114520064
1114520319

1114520576
1114520831

1120306176
1120306943

1120307968
1120308223

1120310016
1120310783

1120311808
1120312447

1120312576
1120312831

1121469912
1121469919

1122125979
1122125988

1139015776
1139015783

1139016000
1139016063

1211605088
1211605103

1211608032
1211608047

1247174064
1247174071

1247174368
1247174383

1254967080
1254967087

1254973664
1254973671

1266551520
1266551527

1266570304
1266570319

1432131584
1432133631

1946173664
1946173679

1946173952
1946174015

1946176512
1946176767

1949466624
1949499391

1950545920
1950547967

1950648320
1950650367

1952251904
1952284671

1960207360
1960207615

1966784512
1966792703

1969694720
1969696767

1969811456
1969815551

1984151552
1984153599

1985480704
1985482751

1986404352
1986406399

1996627968
1996630015

1998290944
1998299135

2030108672
2030125055

2033377280
2033385471

2033582080
2033614847

2033623040
2033625087

2033893376
2033909759

2036334592
2036465663

2038366208
2038374399

2046951424
2047082495

2050084864
2050088959

2050228224
2050490367

2056273920
2056290303

2072528896
2072530943

2075148288
2075150335

2079508480
2079510527

2080800768
2080817151

2081652736
2081685503

2085814272
2085847039

2087190528
2087452671

2090737664
2090745855

2094596096
2094628863

2097479680
2097545215

2101116928
2101149695

2111045632
2111078399

2113683520
2113683679

2113683744
2113684095

2113684176
2113684255

2113684272
2113684431

2113684440
2113684479

2113684544
2113684735

2113684992
2113685007

2113685024
2113685047

2113685120
2113685231

2113685248
2113686079

2113688320
2113689087

2113690112
2113690367

2113691904
2113692031

2113692160
2113692415

2113694720
2113695231

2113695488
2113695743

2704978756
2704978759

2782658560
2782724095

3231309056
3231311103

3233590784
3233591039

3233668864
3233669119

3236102144
3236106239

3262474113
3262474113

3262474143
3262474143

3262474193
3262474193

3278940156
3278940159

3278942516
3278942519

3278942612
3278942615

3325562880
3325566975

3326118524
3326118527

3326119248
3326119251

3326122972
3326122973

3334995968
3335000063

3389001728
3389005823

3389020928
3389021183

3389092352
3389092863

3389259776
3389263871

3389579264
3389587455

3389788416
3389788927

3389936896
3389937663

3391663104
3391664127

3391722240
3391722495

3391906816
3391907839

3392109824
3392110335

3392110592
3392111103

3392111360
3392112127

3392112640
3392114175

3392446464
3392450559

3392741376
3392765951

3392799232
3392799487

3392856064
3392864255

3392931840
3392933887

3393011712
3393019903

3393302528
3393306623

3393560576
3393568767

3393609728
3393613823

3393695744
3393699839

3393744896
3393748991

3393822720
3393830911

3393910784
3393911807

3394079232
3394079743

3394125824
3394142207

3394279424
3394281471

3394347008
3394355199

3394507776
3394508799

3394527232
3394535423

3394682880
3394686975

3394832384
3394834431

3394879488
3394883583

3394910208
3394912255

3394928640
3394936831

3395002368
3395006463

3395059712
3395067903

3395280896
3395284991

3397027072
3397027327

3397070848
3397074943

3397156864
3397165055

3397263360
3397267455

3397394432
3397402623

3397763072
3397771263

3397793792
3397794303

3398004736
3398008831

3398074368
3398090751

3398612992
3398613503

3398638096
3398638111

3398638120
3398638135

3398638160
3398638167

3398638192
3398638207

3398638432
3398638447

3398638528
3398638575

3398638592
3398638655

3398638720
3398638847

3398638880
3398638911

3398639008
3398639231

3398639248
3398639263

3398639424
3398639455

3398639488
3398639615

3398646784
3398647039

3398902272
3398902783

3399655424
3399659519

3399729152
3399745535

3399786496
3399794687

3399826432
3399826943

3399924736
3399925759

3400336384
3400336639

3400337152
3400337407

3400515584
3400531967

3400998912
3401003007

3406565888
3406566143

3407987712
3407987967

3408066048
3408066303

3409396480
3409396735

3410804736
3410821119

3411052544
3411054591

3411152896
3411154943

3411156992
3411161087

3411212288
3411212799

3411320832
3411329023

3411509248
3411542015

3411806208
3411808255

3412251104
3412251119

3412322304
3412324351

3412606976
3412615167

3413106688
3413110783

3413262336
3413270527

3413344256
3413360639

3413574656
3413575679

3414155520
3414155775

3414230016
3414230527

3414376448
3414409215

3415803392
3415805951

3416131584
3416133631

3416301568
3416317951

3416473728
3416473855

3416487424
3416487487

3416719360
3416727551

3416735744
3416752127

3416850432
3416851455

3416981504
3416982527

3416983040
3416983551

3417047040
3417055231

3417178112
3417179135

3417243648
3417244671

3417374720
3417440255

3418163200
3418165247

3418243072
3418251263

3418326528
3418327039

3418396784
3418396799

3418399232
3418399359

3418399440
3418399455

3418401536
3418401599

3418401632
3418401647

3418401720
3418401727

3418401888
3418401903

3418649888
3418649951

3418652160
3418652163

3418652168
3418652171

3418652184
3418652207

3419412480
3419414527

3419783168
3419791359

3419881472
3419897855

3419924480
3419926527

3448257792
3448258047

3453373136
3453373143

3453374568
3453374583

3453374792
3453374807

3459338496
3459339263

3460948736
3460948799

3463602688
3463602943

3465438208
3465438463

3465475072
3465475583

3465476352
3465476607

3466044904
3466044911

3468076000
3468076031

3468085192
3468085199

3468085552
3468085567

3468096768
3468096895

3470660008
3470660015

3470660896
3470660903

3473096193
3473096447

3474193408
3474193663

3474193920
3474194431

3480605440
3480605695

3480605952
3480606207

3481029376
3481029631

3481032960
3481033727

3481039360
3481039871

3486607872
3486608127

3486615296
3486615551

3486624000
3486624255

3489738752
3489740799

3494454129
3494454158

3496290760
3496290767

3496292320
3496292335

3504922624
3504923391

3505119232
3505119487

3508082688
3508082943

3508098304
3508098559

3508100608
3508100863

3508281344
3508281599

3508286912
3508286927

3508337152
3508337663

3509834208
3509834223

3509836872
3509836879

3512562944
3512563071

3512563968
3512564095

3512565248
3512565503

3512577600
3512577631

3512590976
3512591103

3512592896
3512593151

3512598272
3512598527

3518895720
3518895727

3523297280
3523317759

3523477504
3523493887

3523502080
3523510271

3523559424
3523575807

3524132864
3524145151

3524263936
3524266495

3524266752
3524274175

3524274432
3524296703

3524747264
3524755455

3524763648
3524781791

3524781824
3524788223

3535380480
3535388671

3537190912
3537240063

3570076944
3570076951

3624298496
3624299519

3628154240
3628154303

3632480608
3632480615

3632481288
3632481295

3632483856
3632483863

3632484080
3632484087

3632485632
3632485647

3632490688
3632490695

3632494560
3632494567

3680124928
3680133119

3715719168
3715727359

3732799488
3732832255

3732865024
3732930559

View 7 Replies View Related

TSQL + VBA - Retrieve SQL SERVER 2000 Data Trough Excel 2003 - Time Out Error 80040e31

Sep 17, 2007

Hi guys,
When I thought everything is okay with this script, I got a new problem...
I have a VBA's script from Excel 2003 that builds sql script and retrieves data from SQL SERVER 2000.
in order to make the sql running, I need to use a multi - batch processing, to pass and execute every command line once a time.

Up to here, I am using a test case with Account number = '123456' and getting the desire results.
The code below is running okay with the test case, but when changing the account number (mark as yellow in the code) to include all the accounts (or just one other account), I am getting the following ERROR:
run - time error '-2147217871 (80040e31)' - [Microsoft] [ODBC SQL Server Driver] time out expired.

Now, if I take the same code, with the condition that generates the ERROR, and try it into SQL Server, I get the results without errors.
Thanks in advance,
Aldo.

Below the code:

Code Snippet
Function QuerySalesAging()
'--------------------------------------------------------------
'MUST !!! References: Microsoft ActiveX Data Object 2.1 Library
'--------------------------------------------------------------

Dim ConnString As New ADODB.Connection
Dim RecordSet As New ADODB.RecordSet

'Setting Connection String
Driver = "{SQL Server}"
ServerName = "SERVER"
DB_Name = CompanyName

ConnString = "Driver=" & Driver & ";" & "Server=" & ServerName & ";" _
& "Database=" & DB_Name & ";" & "Uid=" & SQLLoginName & ";" & "Pwd=" & SQLPassword & ";"

'Report Criterias
Criteria05 = " AND " & "Accounts.ACCOUNTKEY Between " & AccountKeyAsRange
' -- ==> With AccountKeyAsRange = '123456' AND '123456' it works okay.
' -- ==> With any other value, in example AccountKeyAsRange = '123456' AND '9999999999' it get's ERROR.

CmdLine01 = " USE " & CompanyName

' Check and drop temporary table
TemporaryTableName = "CTE" ' The table is a regular one
CmdLine02 = " if object_id('" & TemporaryTableName & "') is not null exec('DROP TABLE " & TemporaryTableName & "') "

CmdLine03 = " SELECT ..."
CmdLine03 = CmdLine03 & " INTO " & TemporaryTableName
CmdLine03 = CmdLine03 & " FROM ..."
CmdLine03 = CmdLine03 & " WHERE " & "(" & Replace(Criteria05, "AND", "") & ")"
CmdLine03 = CmdLine03 & " ORDER BY ..."

CmdLine04 = CmdLine04 & " ALTER TABLE " & TemporaryTableName ...

CmdLine05 = CmdLine05 & " UPDATE " & TemporaryTableName ...

CmdLine06 = CmdLine06 & " SELECT ..."
CmdLine06 = CmdLine06 & " FROM ..."

ConnString.Open
ConnString.Execute CmdLine01
ConnString.Execute CmdLine02

RecordSet.Open CmdLine01, ConnString
RecordSet.Open CmdLine02, ConnString
RecordSet.Open CmdLine03, ConnString
RecordSet.Open CmdLine04, ConnString
RecordSet.Open CmdLine05, ConnString
RecordSet.Open CmdLine06, ConnString

ConnString.Execute CmdLine01
ConnString.Execute CmdLine02 ' The debbuger stops here:" if object_id('CTE') is not null exec('DROP TABLE CTE') "
ConnString.Execute CmdLine03
ConnString.Execute CmdLine04
ConnString.Execute CmdLine05
ConnString.Execute CmdLine06
ConnString.Execute CmdLine02

'Retrieve Field titles
For ColNr = 1 To RecordSet.Fields.Count
ActiveSheet.Cells(1, ColNr).Value = RecordSet.Fields(ColNr - 1).Name
Next

ActiveSheet.Cells(2, 1).CopyFromRecordset RecordSet

'Cleanup & Close ADO objects
ConnString.Execute "USE master"
ConnString.Close
Set RecordSet = Nothing
Set ConnString = Nothing
End Function

View 1 Replies View Related

Execute Stored Proc Using A Proxy Account In 2005

Sep 18, 2007

Hi all,
I have a problem while i create a proxy account in SQL Sever 2005.The situation is like this...There is a user who has an login in to the server.He has a stored procedure which calls some on the SSIS packages and XP_cmdshell...so this stored procedure basically load some data in to the tables .So for the login in order to execute the stored procedure as he is not a Sys admin I have created a proxy account in my account as Iam an SA and then in the proxies and in principals I selected his login name and this way I have created a credential and a proxy account.

Now the problem is if he logins with his id and password and try to execute the stored procedure it gives an error message

Server: Msg 15153, Level 16, State 1, Procedure xp_cmdshell, Line 1
The xp_cmdshell proxy account information cannot be retrieved or is invalid. Verify that the '##xp_cmdshell_proxy_account##' credential exists and contains valid information.

....so this mean the login is not able to see the proxy account.So what I did is I created a job and then in the job owner tab I have selected his login and then created a step with the type operating system (CmdExec) as I need to just execute the stored procedure and used the proxy account that I have created.

so I gave the command -- exec <stored procedure> --.
But this job fails and gives the error message as
[298] SQLServer Error: 536, Invalid length parameter passed to the SUBSTRING function. [SQLSTATE 42000]....

So now ....first My question is am I doing in a right way....if its right then why Iam not able to execute the stored procedure.

If there is any other way through which I can execute the stored procedure using a proxy account for the logins who are not sys admins....please do let me know.....

Thanks
Raja.V

View 1 Replies View Related