Dts Works From SqlServer But Not From ASP.net

Apr 18, 2005

I have an asp.net page that executes a DTS. When I execute that DTS from enterprise manager it takes about 5000 rows from the as400 and insert into sql serverIt works right. but when I execute it from my asp.net page I have this error.Error al procesar DTS TransferirDatos(ExistMP) en el paso DTSStep_DTSActiveScriptTask_1System.Exception: Error al procesar DTS TransferirDatos(ExistMP) en el paso DTSStep_DTSActiveScriptTask_1 at LibreraLentos.exec.ejecuta_SP_EXISTENCIASMP() in C:Documents and SettingsluisvalenMis documentosVisual Studio ProjectsInventariosLentosLibreraLentosexec.vb:line 43 at InventariosLentos.generacionprocesomateriaprima.btnenviar_Click(Object sender, EventArgs e) in C:AplicacionesWebInventariosLentosgeneracionprocesomateriaprima.aspx.vb:line 60LibreraLentos I have this on my ASP page   Private Sub btnenviar_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnenviar.Click        Try            objexecsp.ejecuta_SP_EXISTENCIASMP()            lblmensajes.Text = "Proceso generado satisfactoriamente"        Catch ex As Exception            lblmensajes.Text = ex.Message + ex.GetBaseException.ToString + ex.Source.ToString        End Try    End Subthis on my Data Classs   Public Function ejecuta_SP_EXISTENCIASMP()        ' call UpdatePrice using a parameter array of SqlParameter objects        Try            Dim ejecutardts As New cDTS            ejecutardts.EjecutarDTS("TransferirDatos(ExistMP)")        Catch ex As Exception            Throw ex        End Try    End FunctionThis is what executes the DTSImports System.Runtime.InteropServicesImports System.Configuration.ConfigurationSettingsImports DTSPublic Class cDTS    Public Sub EjecutarDTS(ByVal NombreDTS As String)        Dim pkg As New DTS.Package        Dim oStep As DTS.Step        Try            pkg = New DTS.Package            'pkg.LoadFromSQLServer(AppSettings("MED20NT"), AppSettings("user"), AppSettings("pwd"), DTSSQLServerStorageFlags.DTSSQLStgFlag_Default, "", "", "", "pruebaCdr1")            pkg.LoadFromSQLServer("MED20NT", "sa", "prueva", DTSSQLServerStorageFlags.DTSSQLStgFlag_Default, "", "", "", NombreDTS, "")            pkg.AutoCommitTransaction = True            pkg.Execute()            For Each oStep In pkg.Steps                If oStep.ExecutionResult = DTSStepExecResult.DTSStepExecResult_Failure Then                    Throw New Exception("Error al procesar DTS " & pkg.Name & " en el paso " & oStep.Name)                End If            Next        Catch ex As System.Runtime.InteropServices.COMException            Throw ex        Catch ex As Exception            Throw ex        Finally            pkg.UnInitialize()            pkg = Nothing        End Try    End SubEnd Class

View 2 Replies


ADVERTISEMENT

I Can't Login To Sqlserver With A Username And A Password But Works With NT Integrated Security !!

Nov 21, 2006

hi
i have created a username and a password in sqlserver 2000 from logins in Enterprise manager
and i permit him to the database i need to connect to ..
and i check all server roles for that user and i make sqlserver authentication for him with a password and then i goto the udl file to connect to that database using that user it fails !! and says

"login failed for that user 'myusername' reason not associated with a trusted sqlserver connection"

while i use NT integrated security it works well
so how can i connect to sqlserver using a username and a password

thanks in advance

View 3 Replies View Related

SQL SERVER ACCESS DENIED!! BUT Everything Works On MSDE And Works SHOWING RECORDS ON SQL SERVER!! PLEASE HELP

Jul 26, 2004

I've got a popular problem so i get a message that server acces denied! ..

But that what is different in my error.... When i use same setting same database and connection string (on MSDE server) there is no problem...

On SQL server i have got windwos authentication but i added all accounts as ASPNET and SA.... and when i try to connect by

RETTO - name of my server

server=RETTO;uid=sa;pwd=password;database=db1;
or by
Integrated Security=SSPIserver=RETTO;uid=RETTOASPNET;database=db1;

I CAN BROWSE RECORDS THERE ARE NO PROBLEMS WITH CONNECTION!!! but when i try to update or iinsert or delete something in database there becomame this error that access denied or server does not exist!!!


PLEASE HELP I'm FIGHTING WITH THAT FOR OVER 5 DAYS!!!

I MADE FOR MY ACCOUNTS (SA, ASPNET) ALL THINGS ALLOWED AS EXECUTING stored procedures.. OR ACCESING datatables with insert delete and update query WHERE IS THE PROBLEM!!!??

View 3 Replies View Related

Telnet Connection Works, Sql Cmd Connection Works, SQL Server Managment Studio 2005 Does Not

Jun 20, 2007

I'm having a strange problem with this but I know (and admit) that the problem is on my PC and nowhere else. My firewall was causing a problem because I was unable to PING the database server, switching this off gets a successful PING immediately. The most useful utility to date is running netstat -an in the command window. This illustrates all the connections that are live and ports that are being listed to. I can establish a connection both by running



telnet sql5.hostinguk.net 1433 and

sqlcmd -S sql5.hostinguk.net -U username -P password



See below:



Active Connections

Proto Local Address Foreign Address State

TCP 0.0.0.0:25 0.0.0.0:0 LISTENING

TCP 0.0.0.0:80 0.0.0.0:0 LISTENING

TCP 0.0.0.0:135 0.0.0.0:0 LISTENING

TCP 0.0.0.0:443 0.0.0.0:0 LISTENING

TCP 0.0.0.0:445 0.0.0.0:0 LISTENING

TCP 0.0.0.0:1026 0.0.0.0:0 LISTENING

TCP 0.0.0.0:1433 0.0.0.0:0 LISTENING

TCP 81.105.102.47:1134 217.194.210.169:1433 ESTABLISHED

TCP 81.105.102.47:1135 217.194.210.169:1433 ESTABLISHED

TCP 127.0.0.1:1031 0.0.0.0:0 LISTENING

TCP 127.0.0.1:5354 0.0.0.0:0 LISTENING

TCP 127.0.0.1:51114 0.0.0.0:0 LISTENING

TCP 127.0.0.1:51201 0.0.0.0:0 LISTENING

TCP 127.0.0.1:51202 0.0.0.0:0 LISTENING

TCP 127.0.0.1:51203 0.0.0.0:0 LISTENING

TCP 127.0.0.1:51204 0.0.0.0:0 LISTENING

TCP 127.0.0.1:51206 0.0.0.0:0 LISTENING

UDP 0.0.0.0:445 *:*

UDP 0.0.0.0:500 *:*

UDP 0.0.0.0:1025 *:*

UDP 0.0.0.0:1030 *:*

UDP 0.0.0.0:3456 *:*

UDP 0.0.0.0:4500 *:*

UDP 81.105.102.47:123 *:*

UDP 81.105.102.47:1900 *:*

UDP 81.105.102.47:5353 *:*

UDP 127.0.0.1:123 *:*

UDP 127.0.0.1:1086 *:*

UDP 127.0.0.1:1900 *:*

Both these utilities show as establishing a connection in netstat so I am able to connect the database server every time, this worked throughout yesterday and has continued this morning.

The problem is when I attempt to use SQL Server Management Studio. When I attempt to connect to tcp:sql5.hostinguk.net, 1433 nothing shows in netstat at all. There is an option to encrypt the connection in the connection properties tab in management studio, when I enable this I do get an entry in netstat -an, see below:



TCP 81.105.102.47:1138 217.194.210.169:1433 TIME_WAIT

TCP 81.105.102.47:1139 217.194.210.169:1433 TIME_WAIT

TCP 81.105.102.47:1140 217.194.210.169:1433 TIME_WAIT



Amost as if it's trying the different ports but you get this time_wait thing. The error message is more meaningful and hopefull because I get:

A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: SSL Provider, error: 0 - The certificate chain was issued by an authority that is not trusted.) (.Net SqlClient Data Provider)

I would expect this as the DNS has not been advised to encrypt the conection.

This is much better than the : Login failed for user 'COX10289'. (.Net SqlClient Data Provider) that I get, irrespective of whether I enter a password or not.


This is on a XP machine trying to connect to the remote webhosting company via the internet.

I can ping the server

I have enabled shared memory and tcp/ip in protocols, named pipes and via are disabled

I do not have any aliases set up

No I do not force encryption

I wonder if you have any further suggestions to this problem?

View 7 Replies View Related

I Don't Understand How This Works, However It Works (sometimes)

Sep 26, 2006

I use the code below for updating data from a AS400 Liked server. I dont understend how the WHERE NOT EXISTS( sections work however usualy they do, in this case it does not andt I can't seem to find out why.

Does anyone see the error?

Thanks

--=========================================
--Create a local temporary table that hold
--all the data from the source table
--=========================================

SELECT * INTO #TEMP FROM dbo.LINK_LTTSTOC

--=========================================
--Remove table entries that are no longer
--needed or that have to be updated
--=========================================

DELETE FROM LTTSTOCK

WHERE NOT EXISTS( SELECT * FROM #TEMP

WHERE LTTSTOCK.WarehouseNo = LTWHLO

AND LTTSTOCK.Location = LTWHSL

AND LTTSTOCK.ItemNo = LTITNO

AND LTTSTOCK.NumberAvail = LTAVAL

)

--=========================================

--Insert data that is missing or that

--needed to be updated and was previously

--deleted

--=========================================

INSERT INTO dbo.LTTSTOCK(WarehouseNo,Location,ItemNo,NumberAvail,rowguid)

SELECT DISTINCT LTWHLO,LTWHSL,LTITNO,LTAVAL, NEWID()

FROM #TEMP

WHERE NOT EXISTS( SELECT * FROM LTTSTOCK

WHERE WarehouseNo = LTWHLO

AND Location = LTWHSL

AND ItemNo = LTITNO

AND NumberAvail = LTAVAL

)

--========================================

--Remove local temporary table.

--========================================

DROP TABLE #TEMP

View 2 Replies View Related

Class Method Is Smoking Fast When Executed Outside Of SQLServer, Dog Slow As A CLR Function Is SQLServer - Anyone?

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

Problem Unicode Data 0x2300 In SQLServer 2000 SQLServer 2005 Express

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

Trying To 'load' A Copy Of A SQLServer 2000 Database To SQLServer 2005 Express

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

MIcrosft SQLServer Best Practices Document On Securing SQLServer

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

Replacing Sqlserver 2000 With Sqlserver 2005 Express

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

Upgrade SQLServer Mobile (.sdf) Database To SQLServer 2005

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

SQLSERVER 2005 X64 Linked Server To SQLSERVER 7.0

Jun 20, 2007

Hello people.

I am in the process of planning a server upgrade to sql2005 x64.

I created 2 linked servers: one to a SQL2000 sp4 server and one to a SQL7.0 SP3.

I have the following error when I query the linked servers.
OLE DB provider "SQLNCLI" for linked server "IVDM2K" returned message "Unspecified error".
OLE DB provider "SQLNCLI" for linked server "IVDM2K" returned message "The stored procedure required to complete this operation could not be found on the server. Please contact your system administrator.".
Msg 7311, Level 16, State 2, Line 1
Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "SQLNCLI" for linked server "IVDM2K". The provider supports the interface, but returns a failure code when it is used.

I am aware of KB 906954.
http://support.microsoft.com/default.aspx?scid=kb;en-us;906954

I applied the instcat.sql on the SQL2000SP4 server and my linked server issues for that one are gone.

However, I ran the instcat.sql script on the SQL7.0 sp3 server and the linked server is still giving me an issue.

Can someone help me find a solution to this?

View 1 Replies View Related

Post Update For SQLServer SP2--is There One For SQLServer Express?

Apr 18, 2007

Regarding KB935356, is there a "post" service pack 2 update for SQLServer Express?



Thanks.

View 7 Replies View Related

How To Import Access To SQLServer With Parameter From SQLServer, Help Pls!

Jul 8, 2006

Hello Expert!

I have 2 Database €“ Access & SQLServer(ver 7)

I need to Import Data TblShift from Access to SQLServer €“ using DTS I€™ve done this successfully!

Now I want to use parameter so I only importing record within range (e.g. ShiftDate BETWEEN 05-24-2006 AND 06-23-2006)

In SQLServer, I have created table to store the date range as following:

TblParameter
DateFrom: 04/24/2006
DateTo: 05/23/2006

How do I use the date range from TblParameter(SQLServer) to import record from TblShift(Access) using DTS?

Is this possible or any better solution for this?

TIA

Regards,

View 4 Replies View Related

Differences Between Sqlserver 2000 And Sqlserver 7.0

Dec 29, 2005

Hi Friends,
 
Can some please let me know the differences between sqlserver 2000 and sqlserver 7.0

View 1 Replies View Related

Difference In SQLSERVER/sqlExpress And SQLSERVER

Jun 6, 2007

Hi,
I am new to SQL Server 2005. I tried connecting to my local machine by using my machine name as Server name and then tried running the following query:
SELECT * FROM SYS.Objects. It gives me following error:
Invalid object name 'sys.objects'.

Whereas, if I connect to my local machine using mahcinenameSQLEXPRESS, then the above mentioned query runs fine.

Why is this difference? What is the difference when I login in these 2 different ways.
Any help would be appreciated.

Thanks in advance.

Any help

View 1 Replies View Related

How To Transfer Data From A SqlServer Database To A SqlServer Express Database

Mar 29, 2006

Is there a way to transfer data from a SqlServer db to a SqlServer Express db. I tried to use the backup file of SqlServer, but this file is not valid for SqlServer Express. Or there any alternatives?

thanks,

Henk

View 7 Replies View Related

DTS. Works Sometimes, Not Others

Sep 17, 2001

I'm running a DTS package that works correctly when I do "Execute Package" directly, by right clicking the package.

But, if I schedule the job, it fails.

It's trying to write data to another server, but I can't see why it doesn't work as a scheduled job. I tried changing all the parameters I can think of. I'm no Windows expert, so if you have any ideas, please feel free to explain as if you're talking to a "newbie" (since I am one)

Win 2000, SQL 2000

View 1 Replies View Related

This Works, But...

Apr 8, 2008

I am going to tangle with our ERP system for the rest of my natural life, and in so doing, will need to create new SQL queries on an almost-daily basis. Based on the handful of queries that I have created against it so far, I recognize that I need to get better at authoring them.

Towards that end, I was wondering if anyone with a little firmer grasp on the subject could take a look at this query (which works & does exactly what I need it to do) and make suggestions as to what I might have done better/differently.

I've changed the names of everything to make it more comprehensible (I hope).

The query returns one row for each unique record in DetailTable, with data from the SummaryTable and a PartDescription from ThirdTable.

SELECT SummaryTable.RecordID,
SummaryTable.Status,
SummaryTable.CustomerName,
SummaryTable.CustomerNumber,
SummaryTable.OrderNumber,
SummaryTable.AssignedTo,
SummaryTable.ResolvedBy,
SummaryTable.ResolveDate,
SummaryTable.PartNumber,
SummaryTable.PartRevision,
SummaryTable.OrderQuantity,
SummaryTable.IssueCategory,
SummaryTable.IncidentDate,
SummaryTable.InquiryDate,
SummaryTable.IssueClass,
SummaryTable.Severity,
SummaryTable.IssueNumber,
SummaryTable.AuthorizedBy,
SummaryTable.Facility,
DetailTable.AssignedTo,
DetailTable.ActionDate,
DetailTable.ActionBy,
DetailTable.JobNumber,
DetailTable.ActionTaken,
DetailTable.NextAction,
DetailTable.IncidentNotes,
ThirdTable.PartDescription

FROMtheDatabase.dbo.DetailTable
LEFT JOIN
theDatabase.dbo.SummaryTable ON DetailTable.RecordID=SummaryTable.RecordID
LEFT JOIN
theDatabase.dbo.ThirdTable on SummaryTable.PartNumber = ThirdTable.PartNumber
AND SummaryTable.PartRevision = ThirdTable.PartRevision

WHERE (SummaryTable.IssueCategory='1' OR SummaryTable.IssueCategory='2' OR SummaryTable.IssueCategory='3'
OR SummaryTable.IssueCategory='D' OR SummaryTable.IssueCategory='E' OR SummaryTable.IssueCategory='L'
OR SummaryTable.IssueCategory='O' OR SummaryTable.IssueCategory='R' OR SummaryTable.IssueCategory='S'
OR SummaryTable.IssueCategory='V' OR SummaryTable.IssueCategory='X' OR SummaryTable.IssueCategory='Z')
AND SummaryTable.Facility='Default' AND SummaryTable.Status='OPEN'

ORDER BY SummaryTable.RecordID

View 6 Replies View Related

Sometimes It Works, Sometimes Not...

Sep 12, 2007

I have a VB app that loads 9 text files for 8 different products, one product at a time. The user must select the product to import. For each product the application:

1. Copies the 9 text files from the source directory to a "process" directory.
2. Calls a stored procedure that sequentially calls a different SSIS package for each text file.
3. Performs some additional processing.

Here's the problem. For the 7th product in the sequence, at least 2 of the text files are not being loaded, and no error exceptions are being thrown. For the 8th product in the sequence at least one text file is not being loaded. If I comment out the call to the stored proc in VB and run the stored proc manually at that point in the program, all files are processed.

On the text file side, the files are being copied correctly and the read-only flag on the file is not being set. In addition, I can open the files in a text editor without any problem.

I'm totally stumped here, so any helpful advice would be appreciated.

TIA,

Mike

View 2 Replies View Related

SMO Backup Works On One Not The Other

Jun 9, 2006

Can anyone enlighten me on sqlexpress smo?
I have compiled an exe using vbc running the folloing code
Imports SystemImports Microsoft.SqlServer.Management.SmoImports Microsoft.SqlServer.Management.CommonImports System.IO
Module SMOtest
    Sub Main()      Try        Kill(System.Environment.GetFolderPath Environment.SpecialFolder.ProgramFiles) & " estSMO_BACKUP")      Catch      End Try      Try        System.Threading.Thread.Sleep(500)        Dim bkpfileName As String = System.Environment.GetFolderPath(Environment.SpecialFolder.ProgramFiles) & " estSMO_BACKUP"        Dim backDeviceItem As New BackupDeviceItem(bkpfileName, DeviceType.File)        Dim db As String = "test"  ' Define and set db        Dim bck As New Backup()  ' Instantiate a Backup object        bck.Action = BackupActionType.Database  ' Set Action
   bck.BackupSetName = db & "_BackupSet"  ' Set Backup
        bck.Database = db  ' Set Database name property        bck.Devices.Add(backDeviceItem)
        Dim srv As New Server()  ' Instantiate a Server object        bck.SqlBackup(srv)  ' Invoke Backup object's SqlBackup method      Catch      End Try    End SubEnd Module
This works fine on a win2k dev system though when moving to another win2k system the code will error on non system databases. If db= model,master,tempdb etc it executes fine. When I try to backup a created db (any) I get an error stating the name is not found in sysdatabases.
SELECT name FROM master..sysdatabases
where name not in ('master','tempdb')
shows all the created databases including "test" in this case
keep in mind many recompiles using different db's work fine on one system and not the other. Even restored "test" from a backup from the system with no problems only to have a 3041 error consistently on the other. System databases work fine for all attempts on the offending system.
Any ideas? (the offending system is a SAT raid which has been evil from the get go) corruption seems to be the only conclusion I can reach
 
 
 
 

View 11 Replies View Related

From Access To SQL And Now Nothing Works!

Apr 11, 2006

Help, I had my entire DB created and when i thought i was done, i upsized to SQL and now almost none of my queries work?

The below works when i remove Distinct, but then i have doubles?


Code:

SELECT DISTINCT
Equip_ProductName.ProductName, Equip_ProductName.ProductInfoID, Equip_ProductName.ProductDesc, Equip_ProductName.ProductSearchTerm,
Equip_ProductName.ProductMore, Equip_ProductName.Visible, Equip_Products.ProductID, Equip_Products.CategoryID
FROM Equip_Products INNER JOIN
Equip_ProductName ON Equip_Products.ProductInfoID = Equip_ProductName.ProductInfoID
WHERE (Equip_ProductName.Visible = 1) AND (Equip_Products.CategoryID = 1)
ORDER BY Equip_ProductName.ProductName

View 6 Replies View Related

Normalisation Vs If It Works Just Do It!

Apr 16, 2006

Hi All,

As an accomplished web devver of many years using ASP and ASP.NET in conjunction with Access and SQL Server, I am a bit pedantic on the rules of good data structures.

Specifically the two main rules of data redundancy and normalisation.

The latter dictates at the lowest level that a data table should NOT contain a field that can be gleaned from one or a combination of others.

I have a problem with this now, I am building a betting system which will take the odds given, plus the stake placed and calculate the winnings or losses accordingly.

There is an added complication in that not all profit is calculated the same way, as a horse can also be 'placed' which does the same calculation as for profit, but then quarters it, so one single select statement won't do.

I could calculate this at data entry stage on a per entry basis and simply store in a Profit/Loss field and keep the value for each bet, however I know this is not the correct thing to do!

My other alternative [and the correct method] is to do this calculation at data request time, but that would involve the use of a cursor or loop in the SP.

I am aware of the huge resources a cursor can consume and I am not sure which is worse, using a cursor or ignoring the normalisation procedures.

So the question is this, what would you do here?

Since I may not be the same SQL Server expert as I am a programmer, is there an alternative way of reading all the bets and doing these calcs on SQL server and bang them back to ASP as a self contained recordset with all the profit/losses calculated for each bet?

Each bet as a unique EntryID and there is a field called Result which stores 'Win, Place or Loss' accordingly.

Thanks in advance of any help/opinions.

:)

View 4 Replies View Related

Dts Works But Run Same Dts Via Sql Job Failed

Nov 14, 2007

hi all,
i posted this somewhere else but i think here is the right group.

i have a dts that shld write to a text file located at a mapped drive. i read somewhere that sql job does not recognise mapped drive, so i use UNC path in form \128.1.1.1dtsfile, which dtsfile folder is the shared folder name. i opened the folder permission to everyone.

thats abt the remote server. the sql server i am running is on windows NT, logon using Administrator to local. my sql is SQL 2000. i register the server using sa username. the server agent is start up using System account.

now the problem is when i run the dts interactively/manually, it runs succesfully. but when i run it thru SQL job it says "Access denied". Or full error is like this,

DTSRun: Loading... DTSRun: Executing... DTSRun OnStart: DTSStep_DTSDataPumpTask_1 DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005) Error string: Error opening datafile: Access is denied. Error source: Microsoft Data Transformation Services Flat File Rowset Provider Help file: DTSFFile.hlp Help context: 0 Error Detail Records: Error: 5 (5); Provider Error: 5 (5) Error string: Error opening datafile: Access is denied. Error source: Microsoft Data Transformation Services Flat File Rowset Provider Help file: DTSFFile.hlp Help context: 0 DTSRun OnFinish: DTSStep_DTSDataPumpTask_1 DTSRun: Package execution complete. Process Exit Code 1. The step failed.

i really hope and appreciate if someone out there can help me out. thanks!

ps: pls let me know if more clues needed from my environment settings.

View 7 Replies View Related

DTS Works But Job Fails

Dec 18, 2007

Hi

I have a DTS to copy data from Oracle to SQL Server. When I logon to SQL Server box with a userID xxx, I can run the DTS from EM and it works perfectly fine but when I schedule the DTS as job, it fails.

SQL Server agent is running with same account "xxx"
DTS connects to SQL Server with sa authentication
Job owner is same account "xxx"

Job error log

Executed as user: DOMAINNAMExxx. ... Drop table [IFS_PROD].[dbo].[INVENTORY_PART] Step DTSRun OnStart: Drop table [IFS_PROD].[dbo].[PART_CATALOG] Step DTSRun OnStart: Drop table [IFS_PROD].[dbo].[SUPPLIER_INFO] Step DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1 DTSRun OnFinish: Drop table [IFS_PROD].[dbo].[PART_CATALOG] Step DTSRun OnFinish: Drop table [IFS_PROD].[dbo].[INVENTORY_PART] Step DTSRun OnStart: Create Table [IFS_PROD].[dbo].[INVENTORY_PART] Step DTSRun OnStart: Create Table [IFS_PROD].[dbo].[PART_CATALOG] Step DTSRun OnFinish: Drop table [IFS_PROD].[dbo].[SUPPLIER_INFO] Step DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart: Create Table [IFS_PROD].[dbo].[SUPPLIER_INFO] Step DTSRun OnStart: Create Table [IFS_PROD].[dbo].[SUPPLIER_INFO_ADDRESS] Step DTSRun OnFinish: Create Table [IFS_PROD].[dbo].[PART_CATALOG] Step DTSRun OnFinish: Create Table [IFS_PROD].[dbo].[INVENTORY_PART] Step DTSRun OnStart... Process Exit Code 6. The step failed.


I copied the DTS to another one and scheduled it

This time I got the error log
Executed as user: DOMAINNAMExxx. ...... DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart: Create Table [IFS_PROD].[dbo].[SUPPLIER_INFO_ADDRESS] Step DTSRun OnFinish: Create Table [IFS_PROD].[dbo].[SUPPLIER_INFO_ADDRESS] Step DTSRun OnStart: Copy Data from SUPPLIER_INFO_ADDRESS to [IFS_PROD].[dbo].[SUPPLIER_INFO_ADDRESS] Step DTSRun OnError: Copy Data from SUPPLIER_INFO_ADDRESS to [IFS_PROD].[dbo].[SUPPLIER_INFO_ADDRESS] Step, Error = -2147467259 (80004005) Error string: Oracle client and networking components were not found. These components are supplied by Oracle Corporation and are part of the Oracle Version 7.3.3 or later client software installation. Provider is unable to function until these components are installed. Error source: Microsoft OLE DB Provider for Oracle Help file: Help context: 0 Error Detail Records: Error: -2147467259 (80004005); Provider Error: 0 (. The step failed.

Please help!
Thanks in advance

View 3 Replies View Related

The Code Works ... But Is It Right?

Dec 11, 2006

Hi,

Is there a better (more elegant) way to build the file name than the following code:


/* File name has format mmddhhmmDX.748 with all time/date values zero padded */

DECLARE @FileName char(14)
DECLARE @out_dte datetime
DECLARE @CharDate char(8)
DECLARE @CharTime char(8)

set @out_dte = '2006-08-03 04:05:12.670'
set @CharDate = convert(char(8),@out_dte,1)
set @CharTime = convert(char(8),@out_dte,8)


set @FileName = substring(@CharDAte,1,2) +
substring(@CharDate,4,2) +
substring(@CharTime,1,2) +
substring(@CharTime,4,2) +
'DX.748'
select @FileName

/* Expected Result: 08030405DX.748 */


My first attempt was this:

DECLARE @FileName char(14)
DECLARE @out_dte datetime

set @out_dte = '2006-08-03 04:05:12.670'
set @FileName = cast(DATEPART(mm,@out_dte) as char(2)) +
cast(DATEPART(dd,@out_dte) as char(2)) +
cast(DATEPART(HH,@out_dte) as char(2)) +
cast(DATEPART(mm,@out_dte) as char(2)) +
'DX.748'


select @FileName

… but then date and time values were left justified spaced filled.
Example output: 8 3 4 8 DX.748

I don't just want to get code that works. I want to learn how to write the best possible code.

Thanks,

Laurie

View 3 Replies View Related

How Clustering Works?

Mar 5, 2007

Hi,

I am having data like this

Studid Date Perf

001 01/01/2008 90

001 02/01/2008 89

001 03/02/2008 91

002 01/01/2008 75

002 02/01/2008 79

002 03/02/2008 69

I gave Perf as PREDICT. When I use the

"SELECT * FROM [Cluster_Model]"

Query I am getting

Perf

82.

Can anyone help me how clustering works? and how to write a Query to group the values here based on StudId?

View 1 Replies View Related

How Does Sp_start_job Works?

May 27, 2008

Hi:

I would like to use sp_start_job to execute a SSIS Package but I am not sure how it works. If there are two requests run the job two times simultaneously or sequentially?

View 4 Replies View Related

Pkg Execution Works In VS But Not In SQL

Jun 2, 2006

I'm able to execute a package in VS Pro on my machine. However when I upload it to the sql server and try to execute the package directly it fails with:

Error: The product level is insuficient for component "...." (3129)
Error: The product level is insuficient for component "...." (5411)

The first component is a DataReader Source which is consuming an ODBC (Noble Systems ATOMIX Driver) connection and the second component is a DataConversion object. Anyone know why this would work running it on my pc in VS but not when I execute it from SQL on the same machine?

View 13 Replies View Related

How Top And Set Rowcount Actually Works

Jan 15, 2008

I'm coming from Oracle world and my knowledge about SQL Server is quite limited so I apriori apologize for probably stupid questions

DB version is SQL Server 2005.
The business scenario is - there is search form with many criteria. User may enter very unrestrictive criteria matching probably millions of rows. To prevent that we'd like to show him no more than N rows (N ~200). Any rows matching criteria are good enough, however these FOUND rows we'd like to sort. I do not want to find all potentially X million rows, then sort them and only then show forst N rows.
So in Oracle I know how to do that. I just find N rows in subquery, and then in outer query sort them. So I avoid to find all rows and then sorting millions of rows.
Here is an example:



Code Block
SQL> create table t (id number, data varchar2
Table created.
SQL> insert into t values (1, 'aaa');
1 row created.
SQL> insert into t values (2, 'bbb');
1 row created.
SQL> insert into t values (3, 'ccc');
1 row created.
SQL> select * from (
2 select * from t where rownum <=2)
3 ;
ID DATA
---------- --------------------
1 aaa
2 bbb
SQL> ed
Wrote file afiedt.buf
1 select * from (
2 select * from t where rownum <=2)
3* order by data desc
SQL> /
ID DATA
---------- --------------------
2 bbb
1 aaa





However how can I avoid sort of potential big result in SQL Server? I've searched google but unfortunately found nothing.
I've tried to use both TOP and SET rowcount without success i.e. from these examples I assume that DB will find ALL rows matching where clause then sort them keeping only first N. It seems that order by clause in outer query is pushed into inner query both for top and set rowcount.




Code Block
create table t (id integer, data varchar(20));
insert into t values (1, 'aaa');
insert into t values (2, 'bbb');
insert into t values (3, 'ccc');

select * from (
select top 2 * from t) as q


1 aaa
2 bbb


select * from (
select top 2 * from t) as q
order by data desc

3 ccc
2 bbb


set rowcount 2
select * from (
select * from t) as q


1 aaa
2 bbb


set rowcount 2
select * from (
select * from t) as q
order by data desc

3 ccc
2 bbb







And I'd like to avoid sort because of two reasons:
1) I predict that generally finding all rows will be much more costly than finding just any no more than N
2) Sorting all found rows also probably will be slower than just N (however DB has to find only first N rows, so not ALL rows should be sorted/kept sorted)


Are my concerns reasonable? If yes what can I do to just find N rows and sort only these?

TIA, Gints

View 12 Replies View Related

A CLR SVF Works On My DB Instance But Not On The Other One

Jun 29, 2007

Hello,



I have scalar valued function that simply convert a date from UTC to LET by using the .NET functions..

The code is very simple:

Partial Public Class UserFunction

<Microsoft.SqlServer.Server.SqlFunction(isDeterministic:=True, Name:="ConvertLETDatetoUTC")> _

Public Shared Function ConvertLETDatetoUTC(ByVal DateLETFormat As SqlDateTime) As DateTime

' Add your code here

Dim DateLETFormatToConvert As Date

DateLETFormatToConvert = DateLETFormat.Value

Return DateLETFormatToConvert.ToUniversalTime

End Function
End Class



select ConvertLETDatetoUTC('2007-06-25 10:00:00')



Now the problem is that the function works properly on my sql server instanc but in the moment I deploy the same code on the sql server instance on a remote machine the conversion doesn't work that means that date I pass is not converted.



On the SQl server machine there the .net framework 1.1 and 2.0 ...



I really don't know what to check to solve the problem .. some idea?



Thank you

View 7 Replies View Related

Ms Works Does Not Open

Jul 25, 2007

when I try to open wrks, I get something like this... that ?CbFromWz@MWblStrings@@SAHPBG@z could not be found in my dynamic links library WkWbl.dll

I tryed downloadind a new dll file and putting it in windows/system and ms work directory... but it still does not work,,, can anyone help me?

View 1 Replies View Related

Upload File Only Works For Dbo

Nov 6, 2007

Hi there,
My problem is that the upload works in testing for our asp.net site only for dbo. Being mindful of security, I would prefer not use this account to execute all sp_/sql. One solution could be using impersonation only in the content management system where the uploading is done, this is code in web.config:
  <location path="Manage.aspx">    <system.web>    <identity impersonate="true" userName="dbo" password="****" />      <authorization>        <deny users="?" />      </authorization>    </system.web>  </location>
To do this I would have to change the dbo password as set up by previous employee, not a real dba so not sure of the implications (enough permissions to be dangerous though;). Is the above impersonation ok, or should I redo logins security in sql? Thanks.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved