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
ADVERTISEMENT
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
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
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
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
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
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
View Related
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
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
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
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
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
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
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
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
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
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
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
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
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
Jun 8, 2008
Please help
I'm trying to do a select command but doesn't return any record
I have two tables one is "lists" another one is "list_records"
in the liss table I have 4 records and in the list_records I have only one record which is tell who is already visited the site so I created a query to get people who is NOT visited the site
Here is my query (I got 0 record return)
SELECT *FROM lists
list_reccords
WHERE NOT EXISTS (SELECT *
FROM lists,
list_records WHERE list_records.is_visited = 1
AND lists.list_id = list_records.list_id)
View 4 Replies
View Related
Aug 4, 2004
Hi I have a following SP that does not return results. Could somebody tell me what I am doing wrong here.
CREATE procedure dbo.wfiDocuments_Get
(
@ModuleIdint,
@PortalIdint,
@IsActivebit=NULL
)
AS
declare @SQL nvarchar(4000)
SET @SQL =
'select
DocumentId,
ModuleId,
PortalId,
CreatedByUserId,
''CreatedByUserName'' = Users.FirstName +'' '' + Users.LastName,
CreatedDate,
Title,
Category,
Syndicate,
Clicks,
Size,
Description,
FileName,
VPath,
IsActive
from wfiDocuments
left outer join Users on wfiDocuments.CreatedByUserId = Users.UserId
where PortalId=@Portal and ModuleId =@Module'
IF @IsActive IS NOT NULL
SET @SQL = @SQL + ' AND IsActive=@Active'
SET @SQL = @SQL + ' ORDER BY Title'
EXEC sp_executesql @SQL,
N'@Portal INT,@Module INT,@Active bit',
@PortalId,@ModuleId,@IsActive
GO
View 11 Replies
View Related
Jan 17, 2005
I need to be able to specify which column to sort by, BUT SQL 2000 does not allow me to
SELECT * FROM #TempTable
WHERE ID > @FirstRec
AND
ID < @LastRec
AND
EmployerID = @EmployerID
AND
Job_no = @Job_no
ORDER BY @WHICHCOLUMN asc
You can see that @WHICHCOLUMN is can be Surname, Age ETC, I have tried to make it a variable but, it started complaining of @FIRSTREC not defined, what's going on pls help, However, how do you combine dynamic queries with parameters as the say
"Sql server does not accept variables as part of sql"
my yahoo is abujajob@yahoo.com
WORKING CODE without WHICHCOLUMN
CREATE PROCEDURE [GetApplicants]
@CurrentPage int,
@PageSize int,
@TotalRecords int output,
@EmployerID int,
@Job_no int,
@WhichColumn varchar,
@SortBy varchar
AS
--Create a temp table to hold the current page of data
--Add and ID column to count the records
CREATE TABLE #TempTable
(
ID int IDENTITY PRIMARY KEY,
Job_no int,
EmployerID int,
JobseekersID int,
Email varchar (100)
)
--Fill the temp table with the Customers data
INSERT INTO #TempTable
(
Job_no, EmployerID,JobseekersID,Email
)
SELECT Job_no, EmployerID,JobseekersID,Email FROM ApplicantsManagement
--Create variable to identify the first and last record that should be selected
DECLARE @myStatement varchar(500)
DECLARE @FirstRec int, @LastRec int
SELECT @FirstRec = (@CurrentPage - 1) * @PageSize
SELECT @LastRec = (@CurrentPage * @PageSize + 1)
--Select one page of data based on the record numbers above
SELECT * FROM #TempTable
WHERE ID > @FirstRec
AND
ID < @LastRec
AND
EmployerID = @EmployerID
AND Job_no = @Job_no
ORDER BY surname asc
--Return the total number of records available as an output parameter
SELECT @TotalRecords = COUNT(*) FROM Customers
GO
View 1 Replies
View Related
Aug 27, 2005
Hi
The query below with table join works on my dev server sql server MC
v2, but returns only NULL values for Questiona and Module on the live
server MC v1.2, is this error to do with the version or something else
like relationships?
SELECT
Induction_Module.Induction_Module, Induction_Questions.Question,
Induction_AnswerIncorrectStats.AnswerIncorrectNo,
Induction_Questions.AnswerCorrect,
CAST(Induction_AnswerIncorrectStats.DateAnswered AS VarChar(11)) AS
DateAnswered
FROM Induction_Questions LEFT OUTER JOIN
Induction_Module ON Induction_Questions.InductionModuleID =
Induction_Module.Induction_ModuleID RIGHT OUTER JOIN
Induction_AnswerIncorrectStats ON Induction_Questions.QuestionID =
Induction_AnswerIncorrectStats.QuestionID
Regards
Steve
View 4 Replies
View Related
Sep 22, 2005
hyei set in the connection string "connect timeout=400" and the application send error after 30sec ... why ?
*in the sql server properties , the connection time set to 0 . *i know the run time of the query is very long , but in this case its doesn't matter :)
View 1 Replies
View Related
Jan 31, 2006
I've the following simple code bound to a button on a Web page
string time = (string)Cache["KEY"];
if (time == null)
{
SqlConnection sqlConnection = new SqlConnection(@"Server=BIZYUSUFSQL2005;Database=Deneme;User Id=sa;Password=;");
SqlCommand command = new SqlCommand(@"select KOLON1 from dbo.CACHE", sqlConnection);
sqlConnection.Open();
SqlCacheDependency dependency = new SqlCacheDependency(command);
time = System.DateTime.Now.ToString();
Cache.Insert("KEY", time, dependency);
command.ExecuteNonQuery();
sqlConnection.Close();
}
return time;
This code has to return the time value from cache. And when a record is inserted into the CACHE table, the cache item has to be invalidated and the new time value has to be returned.
The code works properly for 2-3 minutes. But when there is no activity for 5 minutes, the cache invalidation does not work anymore.
View 1 Replies
View Related
Feb 9, 2006
can any help me why the same exact code works in vb.net but not in C# . case is very simple
I have a table called MenuItems which has menu items and a table sizeandprice which has price for each menuitem based on the size. simple case of 1 to many relationship between menuitems table to sizeandprice table. I am trying to display in a gridview control couple of fields from menuitems table and have another template field in which i am display price and size field from the child table which is sizeand price. so basically this is how my code looks like
Page in vb works fine
<%@ Page Language="VB" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script runat="server">
Protected Sub GridView1_RowDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs)
If e.Row.RowType = DataControlRowType.DataRow Then
Dim ds As SqlDataSource = CType(e.Row.FindControl("SqlDataSource2"), SqlDataSource)
ds.SelectParameters("fkMenuItemID").DefaultValue = GridView1.DataKeys(e.Row.RowIndex).Value
End If
End Sub
</script>
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:PPQ_DataConnectionString1 %>" SelectCommand="SELECT [MenuItemID], [MenuItemType], [ItemName] FROM [MenuItems]"></asp:SqlDataSource>
<br />
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="MenuItemID"
DataSourceID="SqlDataSource1" OnRowDataBound="GridView1_RowDataBound">
<Columns>
<asp:BoundField DataField="MenuItemID" HeaderText="MenuItemID" InsertVisible="False"
ReadOnly="True" SortExpression="MenuItemID" />
<asp:BoundField DataField="MenuItemType" HeaderText="MenuItemType" SortExpression="MenuItemType" />
<asp:BoundField DataField="ItemName" HeaderText="ItemName" SortExpression="ItemName" />
<asp:TemplateField HeaderText="Size And Price">
<ItemTemplate>
<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:PPQ_DataConnectionString1 %>"
SelectCommand="SELECT [ItemSize], [ItemPrice] FROM [SizeAndPrice] WHERE ([fkMenuItemID] = @fkMenuItemID)">
<SelectParameters>
<asp:Parameter Name="fkMenuItemID" Type="Int32" />
</SelectParameters>
</asp:SqlDataSource>
<asp:Repeater ID="Repeater1" runat="server" DataSourceID="SqlDataSource2">
<ItemTemplate>
<%#Eval("ItemSize")%>: <%#Eval("ItemPrice", "$ {0:F2}")%><br />
</ItemTemplate>
</asp:Repeater>
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
</div>
</form>
</body>
</html>
if you would notice that in order populate my repeater with childrows i need to know the menuitemid from the parent row which i do in rowdatabound event however same code in C# does not render any values for my item price and size defined inside the repeater control. here is how the c# page looks like
<%@ Page Language="C#" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script runat="server">
protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.DataRow)
{
SqlDataSource source2 = e.Row.FindControl("SqlDataSource2") as SqlDataSource;
source2.SelectParameters["fkMenuItemID"].DefaultValue = GridView1.DataKeys[e.Row.RowIndex].Value as string;
}
}
</script>
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:PPQ_DataConnectionString1 %>"
SelectCommand="SELECT [MenuItemID], [MenuItemType], [ItemName] FROM [MenuItems]">
</asp:SqlDataSource>
</div>
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="MenuItemID"
DataSourceID="SqlDataSource1" OnRowDataBound="GridView1_RowDataBound" >
<Columns>
<asp:BoundField DataField="MenuItemID" HeaderText="MenuItemID" InsertVisible="False"
ReadOnly="True" SortExpression="MenuItemID" />
<asp:BoundField DataField="MenuItemType" HeaderText="MenuItemType" SortExpression="MenuItemType" />
<asp:BoundField DataField="ItemName" HeaderText="ItemName" SortExpression="ItemName" />
<asp:TemplateField HeaderText="Size And Price">
<ItemTemplate>
<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:PPQ_DataConnectionString1 %>"
SelectCommand="SELECT [ItemSize], [ItemPrice] FROM [SizeAndPrice] WHERE ([fkMenuItemID] = @fkMenuItemID)">
<SelectParameters>
<asp:Parameter Name="fkMenuItemID" Type="Int32" />
</SelectParameters>
</asp:SqlDataSource>
<asp:Repeater ID="Repeater1" runat="server" DataSourceID="SqlDataSource2">
<ItemTemplate>
<%#Eval("ItemSize")%>: <%#Eval("ItemPrice", "$ {0:F2}")%><br />
</ItemTemplate>
</asp:Repeater>
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
</form>
</body>
</html>
can anyone tell me what i am doing wrong?
View 1 Replies
View Related
Mar 25, 2006
HiI am using stored procedure in my asp.net application.filling stored procedure in datagrid.output of stored procedure is a temprory table.but resultset is blank for second time.after reconnecting to connection it is working.what is the problem?help me Thank you,
View 2 Replies
View Related
Mar 31, 2006
I have a stored procedure with a where clause like this:
WHERE
Q.EffectiveDate >= @FromEffectiveDate AND Q.EffectiveDate <= @ToEffectiveDate AND I.InsuredName LIKE '%' + isnull(@PreQuoteDesc,I.InsuredName) + '%' AND isnull(Q.LineOfBusinessDescription,'') LIKE '%' + coalesce(@LOBProperty,Q.LineOfBusinessDescription,'') + '%' AND isnull(Q.LineOfBusinessDescription,'') LIKE '%' + coalesce(@LOBGeneralLiability,Q.LineOfBusinessDescription,'') + '%' AND isnull(Q.LineOfBusinessDescription,'') LIKE '%' + coalesce(@LOBInlandMarine,Q.LineOfBusinessDescription,'') + '%' AND isnull(Q.LineOfBusinessDescription,'') LIKE '%' + coalesce(@LOBMotorTruckCargo,Q.LineOfBusinessDescription,'') + '%' AND isnull(Q.LineOfBusinessDescription,'') LIKE '%' + coalesce(@LOBOwnersContractorsProtective,Q.LineOfBusinessDescription,'') + '%' AND isnull(Q.LineOfBusinessDescription,'') LIKE '%' + coalesce(@LOBSpecialEvents,Q.LineOfBusinessDescription,'') + '%' AND rsu.FirstName LIKE '%' + isnull(@OwnerFirstName, rsu.FirstName) + '%' AND rsu.LastName LIKE '%' + isnull(@OwnerLastName, rsu.LastName) + '%' AND Q.quoteID = isnull(@quoteID,Q.QuoteID) AND Q.QuoteStatusID = isnull(@quoteStatusID, Q.QuoteStatusID) AND rsu.AspNetUserID = isnull(@ASPNetUserID, rsu.AspNetUserID)
-------------------------------------------------------------------
All is working well except for the line of business:
------------------------------------------------------------
AND isnull(Q.LineOfBusinessDescription,'') LIKE '%' + coalesce(@LOBProperty,Q.LineOfBusinessDescription,'') + '%' AND isnull(Q.LineOfBusinessDescription,'') LIKE '%' + coalesce(@LOBGeneralLiability,Q.LineOfBusinessDescription,'') + '%' AND isnull(Q.LineOfBusinessDescription,'') LIKE '%' + coalesce(@LOBInlandMarine,Q.LineOfBusinessDescription,'') + '%' AND isnull(Q.LineOfBusinessDescription,'') LIKE '%' + coalesce(@LOBMotorTruckCargo,Q.LineOfBusinessDescription,'') + '%' AND isnull(Q.LineOfBusinessDescription,'') LIKE '%' + coalesce(@LOBOwnersContractorsProtective,Q.LineOfBusinessDescription,'') + '%' AND isnull(Q.LineOfBusinessDescription,'') LIKE '%' + coalesce(@LOBSpecialEvents,Q.LineOfBusinessDescription,'') + '%'
---------------------------------------------------------------------------------
If the user checks just 'Property' results look like:
Property
Property
Property, General Liability
If the user checks just 'General Liability' the resultes look like:
Genral Liablility
General Liability
General Liability, Inland Marine
If the user checks both Property and General Liability all they get back is:
Property, General Liability
They should get back everything including just Property or just General Liability or both.
So I tried to change the ANDs to ORs and it doesn't work.
-----------------------------------------
AND ( isnull(Q.LineOfBusinessDescription,'') LIKE '%' + coalesce(@LOBProperty,Q.LineOfBusinessDescription,'') + '%' OR isnull(Q.LineOfBusinessDescription,'') LIKE '%' + coalesce(@LOBGeneralLiability,Q.LineOfBusinessDescription,'') + '%' OR isnull(Q.LineOfBusinessDescription,'') LIKE '%' + coalesce(@LOBInlandMarine,Q.LineOfBusinessDescription,'') + '%' OR isnull(Q.LineOfBusinessDescription,'') LIKE '%' + coalesce(@LOBMotorTruckCargo,Q.LineOfBusinessDescription,'') + '%' OR isnull(Q.LineOfBusinessDescription,'') LIKE '%' + coalesce(@LOBOwnersContractorsProtective,Q.LineOfBusinessDescription,'') + '%' OR isnull(Q.LineOfBusinessDescription,'') LIKE '%' + coalesce(@LOBSpecialEvents,Q.LineOfBusinessDescription,'') + '%'
)
I know this is incredibly hard to follow because its incredibly hard to write out.
Is there anyone smart out there who can figure this out?
Thanks
View 2 Replies
View Related
Nov 2, 2001
On some servers the below stored procedure runs fine. On others we get error message - 2147217900 Incorrect Syntax Near '___ ___' - which is from the line that starts with AND (ISNUMERIC(SUBSTRING etc
-----------------------------------------------------------------------------
IF @Specialty = 'None'
BEGIN -- No agent specialty selected.
EXEC('
TRUNCATE TABLE tblAgents
INSERT INTO tblAgents
SELECT DISTINCT c1.accountno, c1.recid, c1.company, c1.contact, c1.secr,
c1.address1, c1.address2, c1.city, c1.state, LEFT(UPPER(c1.zip),5) AS zip,
c1.phone1, c1.fax, '''' AS email, ''0'' AS dist, NULL AS lat, NULL AS long
FROM contact1 c1
WHERE c1.u_key1 LIKE ''' + @DsgntrAgnt + '''
AND (c1.u_key4 IN (' + @PrefDsgntrList + ')
AND (ISNUMERIC(SUBSTRING(c1.zip,1,5)) = 1) OR (c1.zip LIKE ''___ ___''))
')
END
--------------------------------------------------------------------------
Thanks
Doug
View 2 Replies
View Related
Sep 9, 1998
Hi,
Why SQL server dose work as follows when I dose not provide any join hints?
It looks like HASH join is the best plan, but SQL dose not. What kind of
JOIN method is used by SQL optimizer?
Thanks in advance,
Wonhyuk William Chung
wonhyukc@usa.net
MCSE/ MCT
-----------
use northwind
go
select orderid, CompanyName --productname,
from orders o inner join customers c on o.customerID = c.CustomerID
/*
Table `Orders`. Scan count 91, logical reads 184, physical reads 0,
read-ahead reads 0.
Table `Customers`. Scan count 1, logical reads 1, physical reads 0,
read-ahead reads 0.
.0553
*/
select orderid, CompanyName --productname,
from orders o inner hash join customers c on o.customerID = c.CustomerID
/*
hash
Table `Customers`. Scan count 1, logical reads 1, physical reads 0,
read-ahead reads 0.
Table `Orders`. Scan count 1, logical reads 4, physical reads 0, read-ahead
reads 0.
.115
*/
select orderid, CompanyName --productname,
from orders o inner merge join customers c on o.customerID = c.CustomerID
/*
merge
Table `Customers`. Scan count 1, logical reads 4, physical reads 1,
read-ahead reads 3.
Table `Orders`. Scan count 1, logical reads 4, physical reads 0, read-ahead
reads 0.
.115
*/
select orderid, CompanyName --productname,
from orders o inner loop join customers c on o.customerID = c.CustomerID
/*
loop
Table `Customers`. Scan count 830, logical reads 1681, physical reads 0,
read-ahead reads 0.
Table `Orders`. Scan count 1, logical reads 5, physical reads 0, read-ahead
reads 0.
.116
*/
View 1 Replies
View Related
Jun 16, 2000
Does SQL Server 7.0 works with Windows2000 Pro?
We installed SQL 7.0 on Windows 2000 Professional and
cannot seems to find the server components of it.
Was wondering if anybody successfully installed SQL7.0
on Windows2000 Pro?
Pl. send an email.
TIA
DIN
dk@i1.net
View 1 Replies
View Related
Aug 7, 2001
Ok, this is in reference to the previous post about replicated server with difference.
I have a Case statement that checks for NULL values and works on one server and not the other. For example:
Select Case LineItems.Item When 'BILL' then Activities.InvoiceState When Null Then Activities.InvoiceState Else States.State End As Sate.
The second server is not recognizing the NULL in this statement. Any ideas??
Thanks alot for any help.
View 1 Replies
View Related
