Transact SQL :: Advanced Query With Windows Function?

Jun 16, 2015

SQL Sever 2012, I have a table where I store data related with a Log, the columns of the table are three, IDTable, DateLog and CountLog, the column CountLog is a cumulative value, each new rows can increment this value, my query will receive two parameters related with the Date,  @dateFrom and @dateTo, with this information is necesary to get the diff between two months, for example:

CREATE TABLE MyTable
(
  IDTable INT IDENTITY NOT NULL,
  DateLog  DATE NOT NULL,
  CountLog INT NOT NULL
)

[code]....

In this case I need to apply a filter with the Dates:

@dateFrom ='2014-01-01',
@dateTo     ='2014-04-28'

But according with this filter I need to limit the results only for this period between the date, but I need that the first month (January 2014) take the information of the max value from previous month even not included in the filter, in this case the results will be the next:

Id        MonthYear      Diff
1            Jan2014       5
2            Feb2014       3
3            March2014   0
4            April2014      5

With the results, for the first mont (January) is, according to the range of times, January is the lower limit,the last cumulative value of CountLog before the first month of the range  was 2 (December 2013), the max value of the CountLog for this first month was 7, the diff is 7-2 equal to 5 , in the case of the next month (February) the max value of the CountLog was 10, the diff is 10 - 7 equal to 3 and this way.  

[URL]

View 16 Replies


ADVERTISEMENT

Transact SQL :: Subtract Two Columns With Windows Function

Dec 5, 2015

How can i subtract two columns:

I have table:
       
ID COL 1 COL 2
-------------------------
1 200.00 70.00
2 200.00 30.00
3 200.00 90.00
4 200.00 110.00Col1 - COL2

But to continue for each row and value is reduced by the previously value.

My output should be like as:

ID COL 1 COL 2 [COL3 AS RESULT]
-------------------------
1 200.00 70.00 130
2 200.00 30.00 100
3 200.00 90.00 10
4 200.00 110.00 -100

View 3 Replies View Related

Transact SQL :: How To Use Divide Function In Query

Jun 9, 2015

When I am doing the divide all values are showing Zero.

DECLARE @test_sample TABLE (ClientID VARCHAR(5), FiscalYear varchar(4), QtrNumerator int, QtrrDenominator int)
INSERT INTO @test_sample VALUES
('ABC','2014',0,100),
('ABC','2015', 10,40),
('CDE','2013',14,0),
('CDE','2012',20,50)
select QtrNumerator/nullIf(QtrrDenominator,0) as QTR from @test_sample

and also I want to show the  QTR with %, for example, 66.57%

View 6 Replies View Related

Transact SQL :: Apply Partition Scheme To Existing Table In Query Windows

Jun 9, 2015

I have a non-partitioned table (TableToPartition) and I want to apply an existing partition scheme (PartSch) to it using a query. I didn't find any option so I used the StorageCreate Partition wizard to generate the script.why this clustering magic needed if it is dropped at the end? Isn't there another way without indexing to partition a table, say something with ALTER TABLE? (SQL Server 2012)

BEGIN TRANSACTION
CREATE CLUSTERED INDEX [ClusteredIndex_on_PartSch_635694324610495157] ON [dbo].[TableToPartition]
(
[ID]
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PartSch]([ID])
DROP INDEX [ClusteredIndex_on_PartSch_635694324610495157] ON [dbo].[TableToPartition]
COMMIT TRANSACTION

View 2 Replies View Related

Transact SQL :: Convert Sub-query Into Scaler Function?

Sep 19, 2015

Select DATEPART(year, OrderDate) As Years,SO.TotalRevenue
From Sales.Orders S
Cross Apply (Select Sum(SD.Qty*SD.unitprice-SD.discount) As TotalRevenue From Sales.OrderDetails SD where S.orderid = SD.orderid
) SO
Group by DATEPART(year, OrderDate),SO.TotalRevenue

View 7 Replies View Related

Windows 2000 Advanced Server On SQL 7.0

May 4, 2000

We are testing our SQL 7.0 server on Windows 2000 Advanced Server.

We are getting some bottlenecks on stored procedures.

Has any one out there used this set up and if so what kind of problems have you seen?

Thanks,
Dianne

View 6 Replies View Related

Transact SQL :: Create A Function To Define Query-filter

Jul 6, 2015

Is there a way to define a query-filter (Example: WHERE column1 > 5 AND column2 = 'value') in a function?

So I can create a query like this:

SELECT *
  FROM Table
  WHERE MyFunction()

I know it's a bit of a strange question, but I'm writing a dynamic software that will have the ability to run Stored Procedures on any database to create some data-checks. Through parametrisation, a user can define for a specific Stored Procedure that some results are no longer necessary in the result-set.

Example:
ID - Name - State
1 - Jozef De Veuster - Mad
2 - Piet Husentruut - Not Happy
3 - Jeroen Meus - Angry

Is the result of a Stored Procedure "Show_me_unhappy_persons". But we already know that Jozef De Veuster is ALWAYS Mad, so a user can say: Exclude ID = 1, so it won't appear anymore in the result.

I want to handle this by doing:

SELECT *
FROM PeopleStates
WHERE --Some stuff--
AND CheckUserExclusions(SomeID)
And CheckUserExclusions will translate to "NOT (ID = 1)"

View 14 Replies View Related

Transact SQL :: Return A Query Based On Dateadd Function

Aug 3, 2015

I'm trying to return a query based on the dateadd function.  I have a column in the database called date_added which is am successfully using the the DATEADD function above as date1.  The Var1 variable I need to populate from the database too from a column called life_span which is an int data type.  The error I get is An expression of non-boolean type specified in context where a condition is expected near select

My query is as follows: select guid, dateadd(day,life_span,date_added) as datepayday. From User_table

View 5 Replies View Related

Problems Restoring DBs, SQL Express SP1 Advanced && Windows Vista

Sep 1, 2006

Is there some special settings, permissions, or special choices I have to make when install SQL Express SP1 Advanced on Vista Beta 2 to allow me to restore DBs?

I have a user, which is the user during Vista Beta 2 install that it created as 'Administrator' account.

With this user, I have righ-clicked and Run As Administrator the SQL Express SP1 Advanced install..

All my attempts as an Administrator or with this user to restore a DB, results in a DB that the user can't connect to and run a simple select on. I either flat out can't restore the DB, or if I can somehow through lots of messing around get the DB restored, I then get a SQL permissions on object error from SQL Server Express on a simple select.

I don't know what I'm doing wrong, does anyone have any experience with this and any pointers on where I could be getting hung-up?

View 4 Replies View Related

Problem Install SQL Server 2005 Expess SP2 With Advanced Services On Windows XP SP3

May 20, 2008



When I try to install SQL Server 2005 Expess SP2 with Advanced Services on a clean Windows XP system that has had Service Pack 3 applied I get the error "Setup is missing prerequisites: - MSXML6" when it starts to install SQL Server Management Studio Express. This is despite the fact that earlier in the installation is says it installed MSXML6, and my understanding is that MSXML6 is part of Windows XP Service Pack 3 anyway.

The SQL Express installation stops installing components at this point. The database server itself does seem to be operational.

Is there anything I can do to work around this? I would like our customers to be able to install and use Management Studio Express.

Here is the relevant snippet from SQLSetup0006_LUDQAVMXPEN_SSMSEE_1.log

Action start 8:03:54: AppSearch.
MSI (s) (CC:98) [08:03:54:078]: PROPERTY CHANGE: Adding INSTALLSQLSHAREDDIR property. Its value is 'C:Program FilesMicrosoft SQL Server'.
MSI (s) (CC:98) [08:03:54:078]: PROPERTY CHANGE: Adding UI_INSTALLSQLSHAREDDIR property. Its value is 'C:Program FilesMicrosoft SQL Server'.
MSI (s) (CC:98) [08:03:54:078]: Note: 1: 1402 2: HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL ServerSSMSEECurrentVersion 3: 2
MSI (s) (CC:98) [08:03:54:078]: Note: 1: 2262 2: IniLocator 3: -2147287038
MSI (s) (CC:98) [08:03:54:078]: Note: 1: 2262 2: DrLocator 3: -2147287038
MSI (s) (CC:98) [08:03:54:078]: Note: 1: 1402 2: HKEY_LOCAL_MACHINESOFTWAREMicrosoftMSXML60Setup 3: 2
MSI (s) (CC:98) [08:03:54:078]: Note: 1: 2262 2: IniLocator 3: -2147287038
MSI (s) (CC:98) [08:03:54:078]: Note: 1: 2262 2: DrLocator 3: -2147287038
MSI (s) (CC:98) [08:03:54:078]: PROPERTY CHANGE: Adding SNACPRESENT property. Its value is '{11111111-1111-1111-1111-111111111111},{B0F9497C-52B4-4686-8E73-74D866BBDF59},{58D379F7-62BC-4748-8237-FE071ECE797C}'.
MSI (s) (CC:98) [08:03:54:078]: Note: 1: 1402 2: HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL ServerASOLEDB9MSIRefCount 3: 2
MSI (s) (CC:98) [08:03:54:078]: Note: 1: 2262 2: IniLocator 3: -2147287038
MSI (s) (CC:98) [08:03:54:078]: Note: 1: 2262 2: DrLocator 3: -2147287038
MSI (s) (CC:98) [08:03:54:078]: Note: 1: 1402 2: HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL ServerADOMDMSIRefCount 3: 2
MSI (s) (CC:98) [08:03:54:078]: Note: 1: 2262 2: IniLocator 3: -2147287038
MSI (s) (CC:98) [08:03:54:078]: Note: 1: 2262 2: DrLocator 3: -2147287038
MSI (s) (CC:98) [08:03:54:078]: Note: 1: 1402 2: HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL ServerXMOMSIRefCount 3: 2
MSI (s) (CC:98) [08:03:54:078]: Note: 1: 2262 2: IniLocator 3: -2147287038
MSI (s) (CC:98) [08:03:54:078]: Note: 1: 2262 2: DrLocator 3: -2147287038
MSI (s) (CC:98) [08:03:54:078]: Note: 1: 1402 2: HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL Server90ToolsShell 3: 2
MSI (s) (CC:98) [08:03:54:078]: Note: 1: 2262 2: IniLocator 3: -2147287038
MSI (s) (CC:98) [08:03:54:078]: Note: 1: 2262 2: DrLocator 3: -2147287038
MSI (s) (CC:98) [08:03:54:093]: PROPERTY CHANGE: Adding DW20PATH_X86.8D56D6B0_25DE_4471_A64F_717FC5AB6330 property. Its value is 'C:PROGRA~1COMMON~1MICROS~1DWDW20.EXE'.
MSI (s) (CC:98) [08:03:54:093]: Note: 1: 2262 2: IniLocator 3: -2147287038
MSI (s) (CC:98) [08:03:54:093]: Note: 1: 2262 2: DrLocator 3: -2147287038
MSI (s) (CC:98) [08:03:54:093]: Note: 1: 1402 2: HKEY_LOCAL_MACHINECLSID{31411198-a502-11d2-bbca-00c04f8ec294}InprocServer32 3: 2
MSI (s) (CC:98) [08:03:54:093]: Note: 1: 2262 2: IniLocator 3: -2147287038
MSI (s) (CC:98) [08:03:54:093]: Note: 1: 2262 2: DrLocator 3: -2147287038
MSI (s) (CC:98) [08:03:54:093]: Note: 1: 1402 2: HKEY_LOCAL_MACHINECLSID{31411198-a502-11d2-bbca-00c04f8ec294}InprocServer32 3: 2
MSI (s) (CC:98) [08:03:54:093]: Note: 1: 2262 2: IniLocator 3: -2147287038
MSI (s) (CC:98) [08:03:54:093]: Note: 1: 2262 2: DrLocator 3: -2147287038
MSI (s) (CC:98) [08:03:54:093]: Doing action: CA_URTVersionCmd.3643236F_FC70_11D3_A536_0090278A1BB8
Action ended 8:03:54: AppSearch. Return value 1.
MSI (s) (CC:98) [08:03:54:093]: PROPERTY CHANGE: Adding URTVersion property. Its value is 'v2.0.50727'.
Action start 8:03:54: CA_URTVersionCmd.3643236F_FC70_11D3_A536_0090278A1BB8.
MSI (s) (CC:98) [08:03:54:093]: Skipping action: CA_SSMSAlreadyInstalled (condition is false)
MSI (s) (CC:98) [08:03:54:093]: Doing action: MISSINGMSXML6.6D658EB9_E2B8_429A_92C4_84337700878E
Action ended 8:03:54: CA_URTVersionCmd.3643236F_FC70_11D3_A536_0090278A1BB8. Return value 1.
MSI (s) (CC:98) [08:03:54:093]: PROPERTY CHANGE: Adding MISSINGREDISTPREREQS property. Its value is '
- MSXML6'.
Action start 8:03:54: MISSINGMSXML6.6D658EB9_E2B8_429A_92C4_84337700878E.
MSI (s) (CC:98) [08:03:54:093]: Skipping action: MISSINGSNAC.6D658EB9_E2B8_429A_92C4_84337700878E (condition is false)
MSI (s) (CC:98) [08:03:54:093]: Skipping action: MISSINGPTS9.6D658EB9_E2B8_429A_92C4_84337700878E (condition is false)
MSI (s) (CC:98) [08:03:54:093]: Skipping action: MISSINGADOMD9.6D658EB9_E2B8_429A_92C4_84337700878E (condition is false)
MSI (s) (CC:98) [08:03:54:093]: Skipping action: MISSINGXMO.6D658EB9_E2B8_429A_92C4_84337700878E (condition is false)
MSI (s) (CC:98) [08:03:54:093]: Doing action: MISSINGPREREQERROR.6D658EB9_E2B8_429A_92C4_84337700878E
Action ended 8:03:54: MISSINGMSXML6.6D658EB9_E2B8_429A_92C4_84337700878E. Return value 1.
MSI (s) (CC:98) [08:03:54:109]: PROPERTY CHANGE: Adding MISSINGREDISTPREREQERROR property. Its value is 'Setup is missing prerequisites:
- MSXML6
Go to http://go.microsoft.com/fwlink/?LinkId=54583'.
Action start 8:03:54: MISSINGPREREQERROR.6D658EB9_E2B8_429A_92C4_84337700878E.
MSI (s) (CC:98) [08:03:54:109]: Doing action: CA_ErrorMissingPreReqs
Action ended 8:03:54: MISSINGPREREQERROR.6D658EB9_E2B8_429A_92C4_84337700878E. Return value 1.
Action start 8:03:54: CA_ErrorMissingPreReqs.
MSI (s) (CC:98) [08:23:30:937]: Product: Microsoft SQL Server Management Studio Express -- Setup is missing prerequisites:
- MSXML6
Go to http://go.microsoft.com/fwlink/?LinkId=54583
Setup is missing prerequisites:
- MSXML6
Go to http://go.microsoft.com/fwlink/?LinkId=54583
Action ended 8:23:30: CA_ErrorMissingPreReqs. Return value 3.
Action ended 8:23:30: INSTALL. Return value 3.


Going to the mentioned link offers me a download of MSXML6, among other things....

Thanks,
---
Barry Roskuski barry.roskuski@esko.com
EskoArtwork
Ludlow, MA

View 1 Replies View Related

Problem Install SQL Server 2005 Expess SP2 With Advanced Services On Windows XP SP3

May 22, 2008

I originally posted this on the SQL Server Setup and Upgrade forum, but then I found this forum and though it might be more appropriate...



When I try to install SQL Server 2005 Expess SP2 with Advanced Services on a clean Windows XP system that has had Service Pack 3 applied I get the error "Setup is missing prerequisites: - MSXML6" when it starts to install SQL Server Management Studio Express. This is despite the fact that earlier in the installation is says it installed MSXML6, and my understanding is that MSXML6 is part of Windows XP Service Pack 3 anyway.

The SQL Express installation stops installing components at this point. The database server itself does seem to be operational.

Is there anything I can do to work around this? I would like our customers to be able to install and use Management Studio Express.

Here is the relevant snippet from SQLSetup0006_LUDQAVMXPEN_SSMSEE_1.log

Action start 8:03:54: AppSearch.
MSI (s) (CC:98) [08:03:54:078]: PROPERTY CHANGE: Adding INSTALLSQLSHAREDDIR property. Its value is 'C:Program FilesMicrosoft SQL Server'.
MSI (s) (CC:98) [08:03:54:078]: PROPERTY CHANGE: Adding UI_INSTALLSQLSHAREDDIR property. Its value is 'C:Program FilesMicrosoft SQL Server'.
MSI (s) (CC:98) [08:03:54:078]: Note: 1: 1402 2: HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL ServerSSMSEECurrentVersion 3: 2
MSI (s) (CC:98) [08:03:54:078]: Note: 1: 2262 2: IniLocator 3: -2147287038
MSI (s) (CC:98) [08:03:54:078]: Note: 1: 2262 2: DrLocator 3: -2147287038
MSI (s) (CC:98) [08:03:54:078]: Note: 1: 1402 2: HKEY_LOCAL_MACHINESOFTWAREMicrosoftMSXML60Setup 3: 2
MSI (s) (CC:98) [08:03:54:078]: Note: 1: 2262 2: IniLocator 3: -2147287038
MSI (s) (CC:98) [08:03:54:078]: Note: 1: 2262 2: DrLocator 3: -2147287038
MSI (s) (CC:98) [08:03:54:078]: PROPERTY CHANGE: Adding SNACPRESENT property. Its value is '{11111111-1111-1111-1111-111111111111},{B0F9497C-52B4-4686-8E73-74D866BBDF59},{58D379F7-62BC-4748-8237-FE071ECE797C}'.
MSI (s) (CC:98) [08:03:54:078]: Note: 1: 1402 2: HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL ServerASOLEDB9MSIRefCount 3: 2
MSI (s) (CC:98) [08:03:54:078]: Note: 1: 2262 2: IniLocator 3: -2147287038
MSI (s) (CC:98) [08:03:54:078]: Note: 1: 2262 2: DrLocator 3: -2147287038
MSI (s) (CC:98) [08:03:54:078]: Note: 1: 1402 2: HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL ServerADOMDMSIRefCount 3: 2
MSI (s) (CC:98) [08:03:54:078]: Note: 1: 2262 2: IniLocator 3: -2147287038
MSI (s) (CC:98) [08:03:54:078]: Note: 1: 2262 2: DrLocator 3: -2147287038
MSI (s) (CC:98) [08:03:54:078]: Note: 1: 1402 2: HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL ServerXMOMSIRefCount 3: 2
MSI (s) (CC:98) [08:03:54:078]: Note: 1: 2262 2: IniLocator 3: -2147287038
MSI (s) (CC:98) [08:03:54:078]: Note: 1: 2262 2: DrLocator 3: -2147287038
MSI (s) (CC:98) [08:03:54:078]: Note: 1: 1402 2: HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL Server90ToolsShell 3: 2
MSI (s) (CC:98) [08:03:54:078]: Note: 1: 2262 2: IniLocator 3: -2147287038
MSI (s) (CC:98) [08:03:54:078]: Note: 1: 2262 2: DrLocator 3: -2147287038
MSI (s) (CC:98) [08:03:54:093]: PROPERTY CHANGE: Adding DW20PATH_X86.8D56D6B0_25DE_4471_A64F_717FC5AB6330 property. Its value is 'C:PROGRA~1COMMON~1MICROS~1DWDW20.EXE'.
MSI (s) (CC:98) [08:03:54:093]: Note: 1: 2262 2: IniLocator 3: -2147287038
MSI (s) (CC:98) [08:03:54:093]: Note: 1: 2262 2: DrLocator 3: -2147287038
MSI (s) (CC:98) [08:03:54:093]: Note: 1: 1402 2: HKEY_LOCAL_MACHINECLSID{31411198-a502-11d2-bbca-00c04f8ec294}InprocServer32 3: 2
MSI (s) (CC:98) [08:03:54:093]: Note: 1: 2262 2: IniLocator 3: -2147287038
MSI (s) (CC:98) [08:03:54:093]: Note: 1: 2262 2: DrLocator 3: -2147287038
MSI (s) (CC:98) [08:03:54:093]: Note: 1: 1402 2: HKEY_LOCAL_MACHINECLSID{31411198-a502-11d2-bbca-00c04f8ec294}InprocServer32 3: 2
MSI (s) (CC:98) [08:03:54:093]: Note: 1: 2262 2: IniLocator 3: -2147287038
MSI (s) (CC:98) [08:03:54:093]: Note: 1: 2262 2: DrLocator 3: -2147287038
MSI (s) (CC:98) [08:03:54:093]: Doing action: CA_URTVersionCmd.3643236F_FC70_11D3_A536_0090278A1BB8
Action ended 8:03:54: AppSearch. Return value 1.
MSI (s) (CC:98) [08:03:54:093]: PROPERTY CHANGE: Adding URTVersion property. Its value is 'v2.0.50727'.
Action start 8:03:54: CA_URTVersionCmd.3643236F_FC70_11D3_A536_0090278A1BB8.
MSI (s) (CC:98) [08:03:54:093]: Skipping action: CA_SSMSAlreadyInstalled (condition is false)
MSI (s) (CC:98) [08:03:54:093]: Doing action: MISSINGMSXML6.6D658EB9_E2B8_429A_92C4_84337700878E
Action ended 8:03:54: CA_URTVersionCmd.3643236F_FC70_11D3_A536_0090278A1BB8. Return value 1.
MSI (s) (CC:98) [08:03:54:093]: PROPERTY CHANGE: Adding MISSINGREDISTPREREQS property. Its value is '
- MSXML6'.
Action start 8:03:54: MISSINGMSXML6.6D658EB9_E2B8_429A_92C4_84337700878E.
MSI (s) (CC:98) [08:03:54:093]: Skipping action: MISSINGSNAC.6D658EB9_E2B8_429A_92C4_84337700878E (condition is false)
MSI (s) (CC:98) [08:03:54:093]: Skipping action: MISSINGPTS9.6D658EB9_E2B8_429A_92C4_84337700878E (condition is false)
MSI (s) (CC:98) [08:03:54:093]: Skipping action: MISSINGADOMD9.6D658EB9_E2B8_429A_92C4_84337700878E (condition is false)
MSI (s) (CC:98) [08:03:54:093]: Skipping action: MISSINGXMO.6D658EB9_E2B8_429A_92C4_84337700878E (condition is false)
MSI (s) (CC:98) [08:03:54:093]: Doing action: MISSINGPREREQERROR.6D658EB9_E2B8_429A_92C4_84337700878E
Action ended 8:03:54: MISSINGMSXML6.6D658EB9_E2B8_429A_92C4_84337700878E. Return value 1.
MSI (s) (CC:98) [08:03:54:109]: PROPERTY CHANGE: Adding MISSINGREDISTPREREQERROR property. Its value is 'Setup is missing prerequisites:
- MSXML6
Go to http://go.microsoft.com/fwlink/?LinkId=54583'.
Action start 8:03:54: MISSINGPREREQERROR.6D658EB9_E2B8_429A_92C4_84337700878E.
MSI (s) (CC:98) [08:03:54:109]: Doing action: CA_ErrorMissingPreReqs
Action ended 8:03:54: MISSINGPREREQERROR.6D658EB9_E2B8_429A_92C4_84337700878E. Return value 1.
Action start 8:03:54: CA_ErrorMissingPreReqs.
MSI (s) (CC:98) [08:23:30:937]: Product: Microsoft SQL Server Management Studio Express -- Setup is missing prerequisites:
- MSXML6
Go to http://go.microsoft.com/fwlink/?LinkId=54583
Setup is missing prerequisites:
- MSXML6
Go to http://go.microsoft.com/fwlink/?LinkId=54583
Action ended 8:23:30: CA_ErrorMissingPreReqs. Return value 3.
Action ended 8:23:30: INSTALL. Return value 3.


Going to the mentioned link offers me a download of MSXML6, among other things....

Thanks,
---
Barry Roskuski barry.roskuski@esko.com
EskoArtwork
Ludlow, MA

View 3 Replies View Related

Advanced Transact-SQL Reference Book - Need Recommendations!

Jul 20, 2005

I currently have two SQL server books for MS SQL Server 2000. One is aprep book for the 70-229 exam, the other is a Wrox book: "professionalSQL Server 2000 Programming."I'm looking for more T-SQL books that give me PRACTICAL tips onwriting advanced queries. What book do you refer to? Please post them.

View 3 Replies View Related

SQL And ASP.NET Advanced Query

Jun 19, 2008

I want to query a database through a ASP.NET page   and I want to set the page up like an advanced search page would look.  My question is,    how do I write the stored procedure and/or what value do I pass from ASP.NET to accomplish null or blank values.    For instance, if someone wants to query by TRIP id and not vehicle ID   then how do I write the where claue? 

View 10 Replies View Related

Advanced Query

Feb 7, 2006

I have the following figured out, however what I want to do is almost come up with a new column based on if the row actually exists in the employeeeval column:
SELECT e.DeptID, e.LastName + ', ' + e.FirstName AS EmpName, e.EmployeeID, u.UserName FROM Employee e LEFT OUTER JOIN EmployeeEval ev ON e.EmployeeID = ev.EmployeeID LEFT OUTER JOIN [User] u ON u.Department = e.DeptID WHERE (u.RoleID = 'supervisor') AND (e.CompanyID = @CompanyID) AND (e.FacilityID = @FacilityID) AND (ev.PersonalScore IS NULL) ORDER BY e.DeptID, e.LastName
so in my select I want to add something like ev.approved which then that brings back either Null or 0.  Then based on that create a variable to bring back as a string and if it = 0 make my string say 'In Progress' and if it's Null, say 'Not Started'.  I would imagine I would need a Declare @Status nvarchar(15) --- but I get lost after that b/c wouldn't I need some sort of way to count throught the rows of my result set and do an IF statement? I can't quite figure this process out, after numerous attempts.

View 14 Replies View Related

SQL Query Advanced Option

Apr 18, 2001

I want to run a query overnight through the SQL agent using osql. However I need to turn off the print header option that you normally turn off through the advanced options tab in the Query Analyzer. Does anyone know if this is possible?? Many Thanks in advance..

View 1 Replies View Related

Advanced Union Query

Sep 6, 2004

Hi!

I'm trying to get the following query to work:

SELECT item.name AS name, t15.f167 AS start, t15.f168 AS end, t15.f180 AS organizer
FROM item, t15
WHERE item.row_id = t15.id AND t15.f167 > getDate()
UNION ALL
SELECT item.name AS name, t30.f221 AS start, t30.f222 AS end, '<a class=kalender_link href=http://www.xxx.no/default.asp?V_ITEM_ID=' + item.id
+ '> KAN < / a > '
FROM item, t30
WHERE item.row_id = t30.id AND t30.f221 > getDate()
ORDER BY start DESC

The problem is the organizer field. I need to generate an url, but I get all sorts of problems with data types, ie. you can convert an int into ntext.

So the question is basically, how do I get the following to work:

'<a class=kalender_link href=http://www.xxx.no/default.asp?V_ITEM_ID=' + item.id + '> KAN < / a >'


Vidar

View 6 Replies View Related

Advanced Sql-Shape Query - Help

Jan 12, 2006

Hi,This is my basic sql shape query:------------------------------------------------------------SHAPE {select * from tbl1}APPEND({SELECT * FROM tbl2 where field1=1} AS RS2 RELATE field TOfield)------------------------------------------------------------With this query i get a RecordSet (RS1), who handle all the recordsfrom table tbl1, and a secondary RecordSet (RS2) who handle all therecords from table tbl2, who applies to the criteria that field1=1.It is possible that RS2 will be empty (zero records) since there is norecord in tbl2 who applies to that criteria.My wish is to design a query, that will collect only the records fromtbl1, that will have records from tbl2 who applies to the criteria -that RS2 won't be empty !I want to influence on the main part of the query (RS1), through thecriteria that is being used in the secondery query (RS2).I hope that my question is clear enough. thanks !

View 3 Replies View Related

Advanced SQL Query Problem ...

Jul 20, 2005

Dear Expert,I have 2 tables as the following,Table : ProductColumn : ID, Code, NameTable : TrxColumn : Trx_ID, Trx_Date, Product_IDHow can perform the following query in MSSQL or is there any other wayto perform the same query ??? For you info I can do this in Sybase andit's work fine. Thank you.================================================== ==========Select Trx.Product_ID,(Select Produc.CodeFrom ProductWhere Product.ID = Trx.Product_ID) as Product_CodeFrom TrxWhere Product_Code = '0001'================================================== ==========Regards,Chen Fock Siong2004-05-27

View 4 Replies View Related

SQL Query JOIN Needed? Too Advanced For Me

Sep 5, 2006

THIS LOOKS LIKE A LOT BUT I JUST WANTED TO GIVE ALL INFORMATION NEEDED FOR YOU TO EASILY ASSIST ME.  I am not good with SQL so please bare with me. I will give a dumbed down table for us to work with:timestamp          -   DATA1 - DATA2 - MAXVALUENEEDED - DATA32006/8/12 12:00       123         125             1289                      121 2006/8/12 12:05       124         128             1291                      1232006/8/15 12:00        130        128              1190                     1242006/8/15 1:00          131        127             1196                      123  The columns labeled DATA do not really matter.  The only important ones are timestamp and MAXVALUENEEDED but I do need the query to return all columns. What the query has to do: The query needs to find the MAX of MAXVALUENEEDED for a given DAY.  Example Output- 2006/8/12      124   128   1291   1232006/8/15      131   127   1196   123As you can see the MAX values were grabbed for each given day and the unique records were returned on a per day basis. My current SQL Query is:  SELECT CONVERT(varchar, timestamp, 101) AS Time, CONVERT(int,LV50AEPM_AMPS_A_VAL0) AS AMPS_A, CONVERT(int,LV50AEPM_AMPS_B_VAL0) AS AMPS_B, CONVERT(int,LV50AEPM_AMPS_C_VAL0) AS AMPS_C,
CONVERT(int,LV50AEPM_KVA_TOT_VAL0) AS KVA_TOTAL, CONVERT(int,LV50AEPM_KW_TOT_VAL0), CONVERT(int,LV50AEPM_VLL_CA_VAL0), CONVERT(int,LV50AEPM_VLL_BC_VAL0), CONVERT(int,LV50AEPM_VLL_AB_VAL0)
FROM Table_1 T1
WHERE CONVERT(int,T1.LV50AEPM_KVA_TOT_VAL0) = (SELECT MAX(CONVERT(int,T2.LV50AEPM_KVA_TOT_VAL0)) FROM Table_1 T2 WHERE CONVERT(varchar,T1.timestamp,101) = T2.timestamp)
ORDER BY Time ASC The problem is it only returns the max value for one day instead of all days.  Can anyone help me correct this?  Thanks in advance for your help. 

View 34 Replies View Related

Help With A Confusing And Advanced Query - If Exsists?

Nov 14, 2003

Ok,

I need to select a list of products based on a complex criteria. First off, here is a list of the tables I am using and their function.


Dbo.Products – This table holds product names and information.

Dbo.Products_Attributes – This table holds a list of attributes for each product. It has a many to 1 relationship with Products. This table holds only 2 fields – ProductID and AttributeID

Dbo.Customers – This table holds basic customer information and some of the needed criteria for the product search.

Dbo.Customers_Attributes – This table contains a list of attributes that the customer needs in his/her products. This table has a many to 1 relationship with Customers and only has 2 fields, CustomerNum and AttributeID

Dbo.Attributes – This table contains all of the different attributes possible for our products to have. Each can be used by a customer when choosing criteria.

Background: Each attribute is a True / False. Either they want that attribute, or they do not care if they get it or not. This is where the hard part comes in. If a particular attribute is listed as needed in dbo.Customers_Attributes, then I do not want any records pulled from Products that DOES NOT have this attribute. On the other hand, if the attribute is NO listed in the customers_attributes list, it is assumed that the customer wants it. In other words, if there were NO attributes in the customers_attributes list, ALL products would be returned. Attributes listed in the customers_attributes table are “required� (no product will be returned that does not have what is in the list).

I hope I was clear enough… I had to change the table names a functions slightly because of some stupid policy about giving out too much proprietary information here.

If any of you know how to do the above with a subquery of some kind, please let me know.

Dave Larson

View 1 Replies View Related

Advanced Search Query In SQL Server2000

Jul 25, 2006

Hi query-experts,

I have an application that connects to a db running on SQL server 2000. I want the user to be able to do a google-like search, which returns all records containing all given keywords specified by the user in any of the fields, example:

Table has the fields: id, fruit, color

User enters: apple pear green

The query should return all records that contain apple AND pear AND green in any of the fields.

Now I do it like this:

SELECT * FROM table WHERE (fruit LIKE '%apple%' OR fruit LIKE '%pear%' OR fruit LIKE '%green%') AND (color LIKE '%apple%' OR color LIKE '%pear%' OR color LIKE '%green%')

this query works fine, but since my actual table contains about 10 fields that have to be searched, the query get's quite huge, especially when 2 or more keywords are given.

Question: is there an easier way to achieve this?



(this is what my query actually looks like, using the keywords dekker warmenhuizen 0226:)

SELECT
tKlant.nKlant AS nKlant,
MAX(sAchternaam) AS sAchternaam,
MAX(sVoornaam) AS sVoornaam,
MAX(sVoorletters) AS sVoorletters,
MAX(sBedrijfsnaam) AS sBedrijfsnaam,
MAX(sPlaats) AS sPlaats,
MAX(sStraat + ' ' + sHuisnummer) AS sAdres,
MAX(sPostcode) AS sPostcode,
MAX(sCommunicatie) AS sCommunicatie,
(CASE DATALENGTH(mOverig) WHEN 0 THEN 0 ELSE 1 END) AS HasOverig,
MAX(sKlantGroep) AS sKlantGroep
FROM tKlant
LEFT JOIN tKlantGroep ON tKlant.nKlantGroep = tKlantGroep.nKlantGroep
LEFT JOIN tCommunicatie ON tKlant.nKlant = tCommunicatie.nKlant
LEFT JOIN tAdres ON tKlant.nKlant = tAdres.nKlant
WHERE (tKlant.sAchternaam LIKE '%dekker%' OR tKlant.sBedrijfsnaam LIKE '%dekker%' OR tKlant.sKlant LIKE '%dekker%' OR tKlant.sVoornaam LIKE '%dekker%' OR tKlant.mOverig LIKE '%dekker%' OR tCommunicatie.sCommunicatie LIKE '%dekker%' OR tAdres.sStraat + ' ' + tAdres.sHuisnummer LIKE '%dekker%' OR tAdres.sPostcode LIKE '%dekker%' OR tAdres.sPlaats LIKE '%dekker%' OR tKlantGroep.sKlantGroep LIKE '%dekker%')
AND (tKlant.sAchternaam LIKE '%warmenhuizen%' OR tKlant.sBedrijfsnaam LIKE '%warmenhuizen%' OR tKlant.sKlant LIKE '%warmenhuizen%' OR tKlant.sVoornaam LIKE '%warmenhuizen%' OR tKlant.mOverig LIKE '%warmenhuizen%' OR tCommunicatie.sCommunicatie LIKE '%warmenhuizen%' OR tAdres.sStraat + ' ' + tAdres.sHuisnummer LIKE '%warmenhuizen%' OR tAdres.sPostcode LIKE '%warmenhuizen%' OR tAdres.sPlaats LIKE '%warmenhuizen%' OR tKlantGroep.sKlantGroep LIKE '%warmenhuizen%')
AND (tKlant.sAchternaam LIKE '%0226%' OR tKlant.sBedrijfsnaam LIKE '%0226%' OR tKlant.sKlant LIKE '%0226%' OR tKlant.sVoornaam LIKE '%0226%' OR tKlant.mOverig LIKE '%0226%' OR tCommunicatie.sCommunicatie LIKE '%0226%' OR tAdres.sStraat + ' ' + tAdres.sHuisnummer LIKE '%0226%' OR tAdres.sPostcode LIKE '%0226%' OR tAdres.sPlaats LIKE '%0226%' OR tKlantGroep.sKlantGroep LIKE '%0226%')
GROUP BY tKlant.nKlant, DATALENGTH(mOverig)
ORDER BY sAchternaam, sPlaats, sAdres;

View 3 Replies View Related

Search For Missing Values In Advanced Query

Jan 15, 2013

KEYIDGROUP
1 1 a
2 1 b
3 2 a
4 2 b
5 3 a
6 3 b
7 4 a
8 5 a

This is my simple table I need a query that will identity the ID's that are missing the group "b" but I don't want ID 1,2,3 to come up because they are part of a and b. I just need to see anything missing only "b" but not if it's part of a and b.

query should reveal answer should be missing the group b
KEYID
7 4
8 5

I tried the NULL search but since the records don't exist it cant find a null. I am writing a query to identify the missing ID without B but exclude ID that are part of A and B

View 3 Replies View Related

Write Query To Show Results In DB View -Advanced-

Sep 10, 2007

I have 2 TableAuthorsID Name1 Clint2 Voke
BooksBookID ID BookName Price1           1        Book1  10 2           1        Boo21  12 3           2        Book3  6 4           1        Book4  13 5           1        Book5  2
Now I want to List All Authors and only show Most Expensive book Name of each Author.So I need this Fields :ID,Name,BookName,BookID,Price.
How could I Write SQL query For It (I want to show results in DB Without Using SP).I want to Create NEw Views Which Shows my required Results.
thanks,
 
 

View 2 Replies View Related

Stored Procedure Query Problem - Very Advanced. Need Assistance.

May 10, 2004

Hiya,

This is a fairly detailed problem, so this will be a long post... I do appologize. I have been agonizing over this now for over a week and cannot find a viable solution. Hopefully one of you can help.

First off, I work for a realestate company, and this query will display a list of properties based on a number of different criteria and criteria types. There are multiple tables involved:

dbo.Prop:
Property Database. Holds basic info about each property

dbo.Prop_Features:
holds all the features (such as Pool, Carpet, Drapes etc) for each property. The only information stored in this table are PropID and FeatureID (PropID being the Identity of the Prop table, FeatureID being the Identity of the Features Table)

dbo.Features
Holds information on each possable "feature" in the system.

dbo.Members
Holds basic information and criteria for each of our members.

dbo.Members_Features
This table holds the MemberID and FeatureID where members have chosen one ore more features to be used for criteria when searching for a property.

Ok, now... That said, here is my problem. The query I had written (by a professional hired thru Robert Half Technologies) takes over 30 seconds to execute. I will post a copy of that SP below. This is unacceptable. We have to process thousands of these per hour, and a 30 second process time is very bad. Can any of you give me a idea of how to better approach this problem?

In the code below, you will notice there are other tables I did not mention - they are not important. The Speed problem is surrounding a single function, which I will mention below.


CREATE PROCEDURE dbo.Member_Get_List
(
@MemberID Int,
@UpdatesOnly Bit

)
AS
Declare

@RentMin FLoat,
@RentMax Float,
@BedMin SmallInt,
@BedMax SmallInt,
@MinBaths Float,
@MinGarage Float,
@Acreage SmallInt,
@PropCount int,
@LastUpdate SmallDateTime



BEGIN
Select @Rentmin = Rentmin,
@RentMax = Rentmax,
@BedMin = BedMin,
@BedMax = BedMax,
@MinBaths = MinBaths,
@MinGarage = MinGarage,
@Acreage = Acreage,
@LastUpdate = LastUpdate


FROM
Members
WHERE
MemberID = @MemberID
END


BEGIN

SET @PropCount = (SELECT Count(*)
FROM Members_Features
WHERE MemberID = @MemberID )
END

IF @PropCount = 0
BEGIN

SELECT Top 100
P.PropID,
P.Bedrooms,
P.Baths,
P.Garage,
PT.PropName,
P.Rent,
P.Address,
P.Xstreets,
'DateAvailable' = CASE
WHEN DateDiff(Day, P.DateAvailable, GETDATE()) < 0 THEN 'NOW!'
ELSE CONVERT(varchar(10), P.DateAvailable, 101)
End,
P.Lease,
dbo.Prop_Get_Feature_List_Fun(P.PropID) + CASE Len(P.CustomFeatures)
WHEN 0 THEN ''
ELSE ', ' + P.CustomFeatures
End
+ CASE Len(P.Comments)
WHEN 0 THEN ''
ELSE ', ' + P.Comments
End
as 'Features',
P.Deposit,
Phone1 = SUBSTRING(L.Phone1, 1, 3) + '-' + SUBSTRING(L.Phone1, 4, 3) + '-' + SUBSTRING(L.Phone1, 7, 4),
A.AreaName,
Z.County,
Z.City,
Z.State


FROM Prop P

INNER JOIN Area_Zipcode AZ

ON P.Zip = AZ.Zipcode

INNER JOIN Area_Areas A

ON AZ.AreaID = A.AreaID

INNER JOIN Members_Areas MA

ON A.AreaID = MA.AreaID

INNER JOIN Members_PropTypes MP

ON P.PropType = MP.PropType

INNER JOIN Prop_Types PT

ON P.PropType = PT.PropType

INNER JOIN LandLords L
ON P.LandLordID = L.LandLordID

INNER JOIN ZipCode Z
ON P.ZIP = Z.ZipCode


WHERE

P.Active = 1
AND
P.Rent BETWEEN @RentMin AND @RentMax
AND
P.Bedrooms BETWEEN @BedMin AND @BedMax
AND
P.Baths >= @MinBaths
AND
P.Garage >= @MinGarage
AND
P.Acreage >= @Acreage

AND
MA.MemberID = @MemberID
AND
MP.MemberID = @MemberID

AND P.ListDate >
Case @UpdatesOnly
When 0 then '01/01/1900'
When 1 then @LastUpdate
End


END

ELSE
BEGIN

DECLARE @Flag int,
@FeatureID int,
@PropID int,
@Bedrooms tinyint,
@Baths float,
@Garage float,
@DisplayText varchar(75),
@Rent float,
@Address varchar(100),
@Xstreets varchar(100),
@DateAvailable varchar(10),
@Lease tinyint,
@Features Varchar(3500),
@Deposit float,
@Phone1 varchar(12),
@AreaName Varchar(50),
@County Varchar(30),
@City varchar(30),
@State varchar(75)

CREATE TABLE #Prop
(
PropID int,
Bedrooms tinyint,
Baths float,
Garage float,
DisplayText varchar(75),
Rent float,
Address varchar(100),
Xstreets varchar(100),
DateAvailable varchar(10),
Lease tinyint,
Features Varchar(3500),
Deposit float,
Phone1 varchar(12),
AreaName Varchar(50),
County Varchar(30),
City Varchar(30),
State Varchar(75)
)

DECLARE curProp Cursor FORWARD_ONLY for

SELECT Top 100
P.PropID,
P.Bedrooms,
P.Baths,
P.Garage,
PT.PropName,
P.Rent,
P.Address,
P.Xstreets,
'DateAvailable' = CASE
WHEN DateDiff(Day, P.DateAvailable, GETDATE()) < 0 THEN 'NOW!'
ELSE CONVERT(varchar(10),P.DateAvailable, 101)
End,
P.Lease,
dbo.Prop_Get_Feature_List_Fun(P.PropID) + CASE Len(P.CustomFeatures)
WHEN 0 THEN ''
ELSE ', ' + P.CustomFeatures
End
+ CASE Len(P.Comments)
WHEN 0 THEN ''
ELSE ', ' + P.Comments
End
AS 'Features',
P.Deposit,
Phone1 = SUBSTRING(L.Phone1, 1, 3) + '-' + SUBSTRING(L.Phone1, 4, 3) + '-' + SUBSTRING(L.Phone1, 7, 4),
A.AreaName,
Z.County,
Z.City,
Z.State


FROM Prop P

INNER JOIN Area_Zipcode AZ

ON P.Zip = AZ.Zipcode

INNER JOIN Area_Areas A

ON AZ.AreaID = A.AreaID

INNER JOIN Members_Areas MA

ON A.AreaID = MA.AreaID

INNER JOIN Members_PropTypes MP

ON P.PropType = MP.PropType

INNER JOIN Prop_Types PT

ON P.PropType = PT.PropType

INNER JOIN LandLords L
ON P.LandLordID = L.LandLordID

INNER JOIN ZipCode Z
ON P.ZIP = Z.ZipCode

WHERE

P.Active = 1
AND
P.Rent BETWEEN @RentMin AND @RentMax
AND
P.Bedrooms BETWEEN @BedMin AND @BedMax
AND
P.Baths >= @MinBaths
AND
P.Garage >= @MinGarage
AND
P.Acreage >= @Acreage
AND
MA.MemberID = @MemberID
AND
MP.MemberID = @MemberID

AND P.ListDate >
Case @UpdatesOnly
When 0 then '01/01/1900'
When 1 then @LastUpdate
End

OPEN curProp
FETCH NEXT
FROM curProp

INTO @PropID,
@Bedrooms,
@Baths,
@Garage,
@DisplayText,
@Rent,
@Address,
@Xstreets,
@DateAvailable,
@Lease,
@Features,
@Deposit,
@Phone1,
@AreaName,
@County,
@City,
@State

WHILE @@FETCH_STATUS = 0
BEGIN

SET @Flag = 1
-- print 'PropID = ' + convert(varchar(20),@propID)
DECLARE curMembers Cursor FORWARD_ONLY FOR
SELECT MF.FeatureID

FROM Members_Features as MF

INNER JOIN Members as M

ON MF.MemberID = M.MemberID

WHERE M.MemberID = @MemberID


OPEN curMembers

FETCH NEXT FROM curMembers into @FeatureID

WHILE @@FETCH_STATUS = 0
BEGIN
--print 'FeatureID = ' + convert(varchar(20),@FeatureID)

IF (EXISTS(
SELECT *

FROM Prop_Features

WHERE FeatureID = @FeatureID

AND PropID = @PropID))

FETCH NEXT FROM curMembers INTO @FeatureID
ELSE
BEGIN
SET @Flag = 0
BREAK

END

END -- While

CLOSE curMembers
DEALLOCATE curMembers
IF (@Flag = 1)
-- PRINT 'Success!!! PropID = ' + convert(varchar(20),@PropID)
--PRINT @PropID
INSERT INTO #Prop

VALUES (
@PropID,
@Bedrooms,
@Baths,
@Garage,
@DisplayText,
@Rent,
@Address,
@Xstreets,
@DateAvailable,
@Lease,
@Features,
@Deposit,
@Phone1,
@AreaName,
@County,
@City,
@State
)

FETCH NEXT
FROM curProp

INTO @PropID,
@Bedrooms,
@Baths,
@Garage,
@DisplayText,
@Rent,
@Address,
@Xstreets,
@DateAvailable,
@Lease,
@Features,
@Deposit,
@Phone1,
@AreaName,
@County,
@City,
@State

END -- While
CLOSE curProp
DEALLOCATE curProp

SELECT * FROM #Prop
DROP TABLE #Prop
END


Okey, now. Please notice this part of that code:

dbo.Prop_Get_Feature_List_Fun(P.PropID) + CASE Len(P.CustomFeatures)
WHEN 0 THEN ''
ELSE ', ' + P.CustomFeatures
End
+ CASE Len(P.Comments)
WHEN 0 THEN ''
ELSE ', ' + P.Comments
End
AS 'Features',


This function is the cause of the speed problem, methinks. When run alone, it takes 29 seconds to process with the same MemberID that takes about 33 seconds to process completely (the whole Proc). This function simply returns a list of comma delimited features for each property, for displaying on a customer list. Here is that function:


CREATE Function dbo.Prop_Get_Feature_List_Fun (@PropID int)
RETURNS Varchar(3500)

AS
BEGIN

Declare @FeatureList Varchar(3500)



select @FeatureList = Coalesce(@FeatureList + ', ', '' ) + F.FeatureName
FROM
Prop P

INNER JOIN
Prop_Features PF

ON
P.PropID = PF.PropID

INNER JOIN
Features F

ON
PF.FeatureID = F.FeatureID

WHERE

P.PropID = @PropID

ORDER BY

F.FeatureName

Set @FeatureList = isnull(@FeatureList,'Please call for features.')


RETURN @FeatureList
END



Now, I know I probably gave lots more information then needed to solve this issue - but its better to have too much then not enough.

Any help at all in speeding up this function or describing another way to do this would be most appreciative.

Dave

View 5 Replies View Related

What Are Some Good Books On SQL Query Optimization And Other Advanced SQL Topics?

Aug 6, 2007

I graduated from college about three years ago and have been working as a programmer using MS SQL 2000 and even though I've learned a TON in these past three years I know there is tons more I can learn.

Because my company uses Microsoft SQL Server 2000 I'd like to focus on that, but anything ANSI-SQL is perfectly fine.

So... what are some good books on SQL query optimization and other advanced SQL topics?

Thanks in advance...

View 2 Replies View Related

Should I Use A Clr Function Or Write A Windows Service

Jul 9, 2007

hi all.

please help with my dilemma.

this is my task:

i should write a service that will run every 1 hour.

this service should check a folder.

inside this folder there are some xml files, all of them in the same format exactly.

when the service encounters with this xml file, it should open it,pull some data out of it, and update a table in the db.

NOW - I HAVE 2 OPTIONS, AND THIS IS THE DILEMMA:

should i write a clr function to do this task, or should i just write a standard c# windows service, that will open a connection to the db when it should perform update?

what are the advatages?

View 3 Replies View Related

Transact SQL :: Get Windows Environment Variables

Aug 31, 2015

I am migrating the BE of an Access app. to SQL server 2012. I need to get the user's login name (Windows Authentication login). This can be done using xp_cmdshell, but, xp_cmdshell is considered dangerous and I wouldn't be able to run it once I deploy the app. to the company servers (currently I have SQL server on my computer and as an admin I can enable xp_cmdshell to run, but IT doesn't allow it in company servers for security reasons).

Another question, is it possible to send data from the logged in user from Access to SQL server? What I need to do is let SQL know the username of the logged in user, then, use it to filter the data on SQL. Idea is that user can only run queries for his data (he can't view other user's data unless he is a manager or an admin (currently the app. in Access logs the user in automatically if his Windows Domain username is found in the user's table, and set's his role found in the Roles table). It is this functionality that is giving me some problems to migrate to SQL.

I created a function that uses the System_User SQL built-in function, this retrieves the SQL login username, but, the app. uses 1 SQL local account to connect to the server, so in essence it doesn't work as I need the Windows Domain account username.

View 10 Replies View Related

Transact SQL :: Using Linked Server And Windows Authentication

Sep 24, 2015

I have set up two SQL server (e.g. Server1 and Server2) which are set up to use Windows Authentication with my login (e.g. login1).On Server1 I have created a linked server to Server2, I have then selected the Security tab and added my local login and selected impersonate. I have then selected ‘Be Made using the login’s current security context’.I then receive the error message ‘The linked server has been updated but failed a connection test. Do you want to edit the linked server properties?’

View 9 Replies View Related

Transact SQL :: Getting Delta Value With Lead Windows Functions?

Jul 1, 2015

I have a table with the next structure:

DECLARE @MaxCountHistogram TABLE 
 (
  MaxId   INT IDENTITY PRIMARY KEY NOT NULL, 
  PublicationId   INT NOT NULL,
  ProviderId      INT NOT NULL,
  DateLog  DATETIME NOT NULL,
  Amount        FLOAT NOT NULL
  )
INSERT INTO @MaxCountHistogram
VALUES(432,3,'20150530',10.2564),(432,3,'20150630',13.2564),(432,5,'20150530',8),(432,5,'20150630',13),(433,3,'20150530',9),(433,3,'20150630',11),(433,5,'20150530',13),(433,5,'20150630',21)

I need to take for each Publication and Provider  and getting the diferential between two different months, for example:

Period                        Delta Amount                     Provider      PublicationId
20150530                   10.2564                                     3 432
20150630                     3  Result of (13.2564- 10.2564 )        3                      432

View 4 Replies View Related

Strange Situation With Windows Authentication And Undocumented Function GET_SID

Dec 21, 2004

SQL Server 2000
Windows 2000 Advanced Server

We recently moved our servers from one domain to another. Now, we can't grant Windows users access to any databases. We CAN create the user IDs successfully via Enterprise Manager, and get a corresponding row in MASTER..SYSXLOGINS, but can't click on the Database Access tab in the SQL Server Login Properties dialog and grant accesss to any databases. When we try to do so, we get error 15401, "Windows user or group xxx not found". The underlying call is to MASTER..SP_GRANTDBACCESS, and running that via Query Analyzer returns the same error (naturally)

Looking through the code of SP_GRANTDBACCESS, I've determined that what is failing is a call to the undocumented TSQL function GET_SID. This proc takes two parameters, the first is either G<nt group name> or U<nt user name> and the second is NULL in the call in SP_GRANTDBACCESS. If I execute

SELECT GET_SID('U<valid user>', NULL)

it returns NULL, however, if I run

SELECT SUSER_SID(<valid user>)

then I get the Windows SID of whatever valid user name I supply.

We have 3 servers in question, namely production, development and test. We noticed the problem on production. Curiously enough, development and test worked fine.

NOW THE PLOT THICKENS. If I run this query on the dev box....

SELECT SUSER_SID(<id>), GET_SID('U<id>',NULL)

...the first function returned the SID, BUT THE SECOND FUNCTION DID NOT!!! How could that be? Clearly GET_SID was working inside of SP_GRANTDBACCESS, but not as a discrete call. So I went into the master database and added code to print out the SID returned by GET_SID to the proc. Lo and behold, SP_GRANTDBACCESS promptly failed with a 15401 error. It continues to get 15401s now, even after I returned it to the original code. What gives? Now my dev box has the same error production has, and all I did was recompile SP_GRANTDBACCESS a couple of times. FWIW, I did *not* ever make any changes to SP_GRANTDBACCESS on production.

Why doesn't GET_SID() work outside of SP_GRANTDBACCESS?
Why did recompiling SP_GRANTDBACCESS break it permanently?

It almost seems like the query compiler can't correctly link a call to GET_SID to the correct function in some DLL, except I thought that the compiled code didn't survive a restart, and all TSQL procs were recompiled the first time they were called after a restart. If that's the case, then the compiler is - or at least was - producing a functioning compiled version of SP_GRANTDBACCESS after every restart.

View 3 Replies View Related

How To Retrieve The Char(1) Column From SQL Server With Dbbind() Function In Windows C Programming?

May 10, 2007

I have used the following Windows C codes to retrieve records from the bus_newjob table in SQL server:



==========================================================

// construct command buffer to be sent to the SQL server
dbcmd( dbproc, ( char * )"select job_number, job_type," );
dbcmd( dbproc, ( char * )" disp_type, disp_status," );
dbcmd( dbproc, ( char * )" start_time, end_time," );
dbcmd( dbproc, ( char * )" pickup_point, destination," );
dbcmd( dbproc, ( char * )" veh_plate, remark," );
dbcmd( dbproc, ( char * )" customer, cust_contact_person," );
dbcmd( dbproc, ( char * )" cust_contact_number, cust_details" );
dbcmd( dbproc, ( char * )" from bus_newjob" );
dbcmd( dbproc, ( char * )" where disp_status = 0" );
dbcmd( dbproc, ( char * )" order by job_number asc" );

result_code = dbsqlexec( dbproc ); // send command buffer to SQL server

// now check the results from the SQL server
while( ( result_code = dbresults( dbproc ) ) != NO_MORE_RESULTS )
{
if( result_code == SUCCEED )
{
memset( ( char * )&disp_job, 0, sizeof( DISPATCH_NEWJOB ) );
dbbind( dbproc, 1, INTBIND, ( DBINT )0, ( LPBYTE )&disp_job.new_job.job_number );
dbbind( dbproc, 2, NTBSTRINGBIND, ( DBINT )0, ( LPBYTE )&disp_job.new_job.job_type );
dbbind( dbproc, 3, INTBIND, ( DBINT )0, ( LPBYTE )&disp_job.new_job.disp_type );
dbbind( dbproc, 4, INTBIND, ( DBINT )0, ( LPBYTE )&disp_job.new_job.disp_status );
dbbind( dbproc, 5, NTBSTRINGBIND, ( DBINT )0, ( LPBYTE )&disp_job.new_job.start_time );
dbbind( dbproc, 6, NTBSTRINGBIND, ( DBINT )0, ( LPBYTE )&disp_job.new_job.end_time );
dbbind( dbproc, 7, NTBSTRINGBIND, ( DBINT )0, ( LPBYTE )&disp_job.new_job.pickup_point );
dbbind( dbproc, 8, NTBSTRINGBIND, ( DBINT )0, ( LPBYTE )&disp_job.new_job.destination );
dbbind( dbproc, 9, NTBSTRINGBIND, ( DBINT )0, ( LPBYTE )&disp_job.new_job.veh_plate );
dbbind( dbproc, 10, NTBSTRINGBIND, ( DBINT )0, ( LPBYTE )&disp_job.new_job.remark );
dbbind( dbproc, 11, NTBSTRINGBIND, ( DBINT )0, ( LPBYTE )&disp_job.new_job.customer );
dbbind( dbproc, 12, NTBSTRINGBIND, ( DBINT )0, ( LPBYTE )&disp_job.new_job.cust_contact_person );
dbbind( dbproc, 13, NTBSTRINGBIND, ( DBINT )0, ( LPBYTE )&disp_job.new_job.cust_contact_number );
dbbind( dbproc, 14, NTBSTRINGBIND, ( DBINT )0, ( LPBYTE )&disp_job.new_job.cust_details );

// now process the rows
while( dbnextrow( dbproc ) != NO_MORE_ROWS )
{
new_job = malloc( sizeof( DISPATCH_NEWJOB ) );
if( !new_job )
return( 0 );
memcpy( ( char * )new_job, ( char * )&disp_job, sizeof( DISPATCH_NEWJOB ) );
append_to_list( &Read_Job_List, new_job );
}
}
else
{
sprintf( str, "Results Failed, result_code = %d", result_code );
log_str( str );
break;
}
==========================================================



where the job_type columIn is of the char(1) type, NTBSTRINGBIND is the vartype argument in the dbbind() function.



However, what I have gotten is nothing more than a null string from the job_type column. I have alternatively changed the vartype argument to STRINGBIND, CHARBIND and even INTBIND, but the results are the same.



Who can tell me the tricks to retrieve a char(1) column from SQL server?



View 1 Replies View Related

SQL Server 2000 Standard On Windows 2000 Advanced Server

Jul 20, 2005

What is the limitation of memory that SQL Server 2000 Standard can usewhen running on a Windows 2000 Advanced Server platform?

View 1 Replies View Related

Transact-SQL IsXML Function (wish)

Oct 4, 2007

Hello All,
I need to determine that a piece of XML in a character field or variable is well-formed XML. I need to store this validation result (valid or not valid flag) in the table.
Does SQL provide some function for this? Is there any other way?
Thank you!
David

View 2 Replies View Related







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