I am trying to fill a table from 2 other tables in MS SQL 2000
the structure ::
Table 1 --> Info
InfoID
Name
Table 2 --> Item
InfoID
Num
Value
TRANSFORM Max(Item.Value) AS MaxValue
SELECT Info.Name
FROM Info INNER JOIN Item ON Info.InfoID = Item.InfoID
WHERE Item.Num In (10,12,15,100)
GROUP BY Info.Name
PIVOT Item.Num
in ACCESS 2000 it works fine I get a View with 5 columns --> Name,10,12,15,100
but in MS SQL it doesnt work at all
does someone knows how to translate it for MS SQL (the table structures are exactly the same)?
SPs in Access 2000 (SQL) / Crosstab problem / returning dataset I've recently "upsized" from Access97 (Jet) to Access 2000 (SQL) client/server using MS SQL Server 2000. As a result, I'm new to the concept of Stored Procedures. I am trying to work out a general solution to the fact SQL doesn't allow an easy way to create dynamic crosstab queries (from within Access client/server).
I've included the SP code I found (sp_crosstab) to create the crosstab solution. To execute the sp_crosstab, I use another SP (execute_crosstabs) which defines the input parameters.
If I run the SPs in Query Analyzer, the results are returned as a dataset. However, if I run them in MS Access 2000, the following message is returned:
"The stored procedure executed successfully but did not return records." Likewise, if I attach an Access form to the SP, it returns the same message.
I've seen ADO code which could return the records (to Access), but I would prefer an alteration to the SP (sp_crosstab) which would return the records automatically.
For example, if I run the SP below (sp_MyTables which executes sp_tables), a dataset is returned automatically instead of the message "The stored procedure executed successfully but did not return records." If I attach sp_MyTables to an Access form, the records are returned in the form as well.
My question is this: How can I get sp_crosstab to act like sp_tables (executed by sp_MyTables) to return a dataset instead of the infernal message?
I've looked all over the Internet and have not seen this issue addressed directly. Your help would be EXTREMELY appreciated (and will probably make Internet history)!
-- Work variables declare @ReturnSet varchar(255), @sql varchar(8000), -- Hold the dynamically created sql statement @colname varchar(255), -- The current column when building sql statement @i smallint, -- know when we reached the last column (@i = @cols) @cols smallint, -- Number of columns @longest_col smallint, -- the len() of the widest column @CrLf char(2) -- Constants declare @max_cols_in_table smallint, @max_col_name_len smallint, @max_statement_len smallint, -- @sql7 bit, -- 1 when version 7, 0 otherwise. @err_severity int
set nocount on
set @max_cols_in_table = 255 set @max_statement_len = 8000 set @max_col_name_len = 128 set @err_severity = 11 set @CrLf = char(13) + char(10)
-- Check inputs if @tablename is null or @crosscolumn is null or @crossrow is null or @crossvalue is null begin raiserror ('Missing parameter(s)!',@err_severity,1) return @@rowcount end
-- Check for existence of the table. if (not exists(select * from sysobjects where name like @tablename))begin raiserror ('Table/View for crosstab not found!',@err_severity,1) return 0 end
-- Don't check for columns because we may actually get an expression as the column name
-- prepare for future feature of checking database version to validate -- inputs. Default to version 7 --set @sql7 = 1 --if (patindex('%SQL Server 7.%',@@version) = 0) begin -- set @sql7 = 0 --end
-- Extract all values from the rows of the attribute -- we want to use to create the cross column. This table -- will contain one row for each column in the crosstab. create table #crosscol (crosscolumn varchar(255)) set @sql = ' insert #crosscol Select Distinct ' + @crosscolumn + ' From ' + @tablename --+ --' Group By ' + @crosscolumn --print @sql exec (@sql) set @cols = @@rowcount
if @cols > @max_cols_in_table begin raiserror ('Exceeded maximum number of columns in Cross-tab',@err_severity,1) return 0 end else begin if @cols = 0 begin raiserror ('Could not find values to use for columns in Cross-tab',@err_severity,1) return 0 end else begin -- Check if any of the data is too long to make it a name of a column select @longest_col = max(len(convert(varchar(129),crosscolumn))) from #crosscol
if @longest_col > @max_col_name_len begin raiserror ('Value for column name exceeds legal length of column names',@err_severity,1) return 0 end else begin
-- All Validations OK, start building the dynamic sql statement
set @sql = '' -- Use tmp table rows to create the sql statement for the crosstab. -- each row in the table will be a column in the cross-tab set @sql = 'select isnull(convert(varchar(255), ' + @crossrow + '),''Undefined'') As ' + @crossrow + ', ' + @CrLf + space(4)
declare cross_sql cursor for select crosscolumn from #crosscol order by crosscolumn
--print 'Sql cross statment: ' + @sql
open cross_sql fetch next from cross_sql into @colname -- Use "@i" to check for the last column. We need to input commas -- between columns, but not after the last column set @i = 0 while @@FETCH_STATUS = 0 begin set @i = @i + 1 set @colname = isnull(@colname,'Undefined') set @crossvalue = isnull(@crossvalue, 0)
Hi all I'm transferring some Access queries to SQL server and the crosstabs don't want to work, can anyone shed any light on the query below.
Thanks.
TRANSFORM Min(tCompany.cCompanyName) AS CompanyName SELECT tProjContacts.ProjectID FROM tCompany INNER JOIN (tProjContacts INNER JOIN tCompanyType ON tProjContacts.CoTypeId = tCompanyType.CoTypeId) ON tCompany.CompanyID = tProjContacts.CompanyID WHERE (((tCompanyType.CoType) Like "*topo*" Or (tCompanyType.CoType) Like "*ground*")) GROUP BY tProjContacts.ProjectID PIVOT tCompanyType.CoType;
I'm trying to port a dymanically renderred form from a .mdb to a .adp. In the Access .mdb. The form is a representation of a crosstab query with unknown column headings (hence, the need for the form to be dynamically rendered at runtime).
I tried to port the crosstab from the .mdb to a stored procedure, but SQL Server doesn't like TRANSFORM, and perhaps the PIVOT as well. How do you do this in sql server?:
TRANSFORM Count(d.CAR_INIT) AS CountOfCAR_INIT SELECT b.WKLD_SEQ, c.TRN_ID, c.TRK_NBR FROM ((TSA_HS_MPCT_CNT a INNER JOIN TSA_HS_COMB2 b ON a.RECC_COMB_ID = b.COMB_ID) INNER JOIN TSA_HS_WKLD c ON b.WKLD_ID = c.WKLD_ID) INNER JOIN TSA_HS_OBJ_TRN d ON c.WKLD_ID = d.WKLD_ID GROUP BY b.WKLD_SEQ, c.TRN_ID, c.TRK_NBR PIVOT d.LST_HMP_DTM + d.OBJ_DEP_TRN
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!!!??
I need some help in converting this crosstab SQL from an Access query to a View in SQL Server Express:TRANSFORM First(tblPhones.PhoneNumber) AS FirstOfPhoneNumber SELECT tblPhones.ClientID FROM tblPhones GROUP BY tblPhones.ClientID PIVOT tblPhones.PhoneType;
Hello --I think this is the term for what I want (something that could be generatedin ACCESS using a pivot table, or, maybe Yukon).We have data for sales by sales people in sales regions. More than oneperson sells in a region.We want to display data as follows:salesperson's names----------- ----------- ----------- ----------- ----------- -----------region 1region 2 row/column values are sales amounts for person inthat regionregion 3We will add a WHERE clause for the period of time covered.I don't want to have to change the query if a new salesperson or new regionis added.Can this be done in SQL Server 2000, Analysis Services, OLAP, anywhere?Can someone direct me to examples of how to do this?Thanks for any direction.Larry Mehl
I have several SQL 2000 servers I need to setup transactional (non updatable) replication with. The structure is:
SQL Server 2000 as Publisher/Distributor
SQL Server 2005 Standard as Subscriber
The connection is via the Internet with snapshots using FTP.
I setup the first set (2 databases at location A). They work wonderfully. I created the publication and then subscribed using MGMT Studio for 2K5.
II am setting up the same scenario for location B. Here is my problem:
In MGMT Studio I connect to the publisher (SANDRA). I right-click a publication and choose New Subscriptions..., the publication is already selected. I click next - Run each agent at its Subscriber is selected and the only option (this is desired), I click Next
HERE IS THE PROBLEM:
On the Subscriber's screen there are no Subscribers listed. When setting up location A the subscribing server was listed and I could choose a database. The Next button is greyed out and there is no way to create/add one.
I tried setting up the subscription by right-clicking the subcribing server's Replication folder in MGMT Studio but I get the same result (except that I have to authenticate with the publishing server which works fine).
WHAT'S DIFFERENT:
Location A is SQL Server Standard (SP3) running on SBS2K3. It is obviously on a domain and so SQL Server and the SQL Agent are running under domain accounts. Location B is a Windows XP SP2 machine running SQL Server Personal Edition (it actually says Development Edition in the properties window).
The databases are the same strucutre, different data. At location A the firewall is set to allow 1433->*any* and *any*->1433 where *any* is 1024 or higher. On the XP machine the firewall is set to allow port 1433. I don't think this is the issue because I've turned the firewall off on the XP machine and I get the same result.
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
I have written a intranet page that writes some info into a sql database, basically following the 'SQL Server 2005 Express for Beginners' video.When I debug the application from within 'Visual Web Develop 2005 express' it works fine entries are entered into the DB and I can then edit the db using the admin page.But when I host the site using IIS I doesn't work, submissions to the database seem to fail I can see the DB in the admin page but if I try to edit them or delete them it fails. What could I doing wrong could I be missing a setting in IIS? Any ideas??Here's my webconfig if that helps at all: <?xml version="1.0"?><configuration xmlns="http://schemas.microsoft.com/.NetConfiguration/v2.0"> <connectionStrings> <add name="studentprofilesConnectionString1" connectionString="Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|studentprofiles.mdf;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient"/> </connectionStrings> <system.web> <roleManager defaultProvider="AspNetWindowsTokenRoleProvider" /> <compilation debug="true" defaultLanguage="c#" /></system.web></configuration>
I have an application where users can enter data into any (or all) of 6 search fields, to produce a filtered query.
This works fine using my Access version(see code below), but as SQLS2k cannot use "IIF", I tried to replace these bits with "CASE/WHEN/THEN/ELSE" lines, which does not work with numeric fields as these cannot be "wild-carded" in the same way as Access allows.
Can anyone suggest a way forward that does not involve coding all the possible permutations of "SELECT" blocks driven by lots of nested "IF/THEN/ELSE"s?
FROM (((tAudit LEFT JOIN tCategories ON tAudit.CategoryID = tCategories.CategoryID) LEFT JOIN tClients ON tAudit.ClientID = tClients.ClientID) LEFT JOIN tContracts ON tAudit.ContractID = tContracts.ContractID) LEFT JOIN tProducts ON tAudit.ProductID = tProducts.ProductID
WHERE (((tAudit.ActionType) Like IIf(IsNull([CurrentType]),"*",[CurrentType])) AND ((tAudit.ClientID) Like IIf(IsNull([CurrentClientID]),"*",[CurrentClientID])) AND ((tAudit.ContractID) Like IIf(IsNull([CurrentContractID]),"*",[CurrentContractID])) AND ((tAudit.ProductID) Like IIf(IsNull([CurrentProductID]),"*",[CurrentProductID])) AND ((tAudit.CategoryID) Like IIf(IsNull([CurrentCategoryID]),"*",[CurrentCategoryID])) AND (([tAudit].[DateStamp]) Between [FromDate] And [ToDate]));
Getting a weird error while trying out a query from Access 2003 on aSQL Server 2005 table.Want to compute the amount of leave taken by an emp during the year.Since an emp might be off for half a day (forenoon or afternoon), havethe following computed field:SessionOff: ([ForenoonFlag] And [AfternoonFlag])The query works fine when there's no criterion on SessionOff.However, when I try to get the records where the SessionOff equals 0, Iget the following error:~~~~~ODBC--call failed. [Microsoft][SQL Native Client][SQL server]Incorrect syntax near the keyword 'NOT'. (#156)~~~~~I checked the SQL of the Access query, but there's no NOT anywhere init:~~~~~SELECT tblWorkDateAttendance.*FROM tblWorkDate INNER JOIN tblWorkDateAttendance ONtblWorkDate.WorkDate = tblWorkDateAttendance.WorkDateWHERE (((([ForenoonFlag] And [AfternoonFlag]))=0) AND((tblWorkDateAttendance.WorkDate)<Date()) AND((Year([tblWorkDate].[WorkDate]))=Year(Date())) AND((Weekday([tblWorkDate].[WorkDate])) Between 2 And 6) AND((tblWorkDate.HolidayFlag)=False));~~~~~What gives?
We have 2 databases ( Guider and Talker ) and we have a WCF service that is logged in with a domain identity.
In our SQL Server we have the service ID added to the Data Server Logins and both Guider and Talker are given access to the user.
When we access Guider we have no problems getting data.
When we access Talker we have a login failure:
Cannot open database 'Talker' requested by the login. The login failed.
Login failed for user 'AcornCommunicationServices'.
The thing that gets me is that the user is created at the Server level, in both Databases, and at the server level both databases are checked for the user. master has been set as the default database for the user.
Basically, as far as I can see Talker and Guider are configured identically! So I cannot figure out why I cannot login to the second database!
Is there a specific setting I'm missing somewhere to grant login access to the user? I'm using
When our production site was deployed on the client's WinServer2003, my webservice is throwing a "server does not exist or access denied" exception. I'm using the same connection string (typed once) as i'm using in my web forms on the user visible sections of the site. the service also works fine on my XP testing machine. unfortunately, I'm not a 2003 admin. If anyone can help, i would greatly appreciate it, trying to find what is misconfigured on the client's server is driving me bonkers.
a while back i had to do a project with an access database, one of the biggest problems i had back then was gettting the primary key of a datarow you had just inserted into the database.
After a long set of trial and error i came up with the following:
- add the tablemappings of a table - call the dataadapte.fillschema method
then after inserting a new row into the database the primary key gets filled in automatically!
now thing is
i was hoping to duplicate this in sql server
but it doesn't seem to work at all
so after i insert a row into my datatable and update it the row is in the database but in vb the datarow primary key is not filled in! anyone have an idea?
prefereabely one that does not resort to stored procedures with return parameters etc
I am runing Windows 2003 which has both SQL2000 and SQL2005.
The following works in 2000 but not 20005
Code Snippet DECLARE @sql nvarchar(4000) set @sql = 'sp_addlinkedserver @server = N''dbAccessPO'', @provider = N''Microsoft.Jet.OLEDB.4.0'', @srvproduct = N''OLE DB Provider for Jet'', @datasrc = N''C:Temppopts.mdb''' exec sp_executesql @sql set @sql = 'sp_addlinkedsrvlogin ''dbAccessPO'', FALSE, ''sa'', ''Admin'', NULL' exec sp_executesql @sql
Running this:
Code Snippetselect * from dbAccessPO...MyTable
Produces this error:
Code Snippet OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "dbAccessPO" returned message "Unspecified error". Msg 7303, Level 16, State 1, Line 1 Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "dbAccessPO".
I have a stored procedure which is run through MS Access (yuck). It does not appear to fail, but it does not populate certain tables, and is also rather complex. I did not write it, and am trying my best to fix it. However, when I run the same procedure with the same parameters from within Management Studio (database state is the same, I restore from backup before trying out each execution) it populates the tables correctly. I have profiled both executions (from MS and from Access) and it is running with the same NTUsername, and thus the same permissions. The process used to work and then some changes were made to the DB which seem to have broken it from Access. As far as I know, the Access code has not changed, although it may have done so. The proc call from Access uses the same parameters, and does not throw an error, although it does not appear to close the connection to the DB (I'm looking into this). Access uses an ODBC connection to connect to SQL Server.
I've got a simple transactional replication set up. I have a separate publisher, distributor, and subscriber with 76 articles (tables only) being pushed from the distributor.
I have this exact setup with the same tables and data working in the SQL 2000 environment. I am testing replication on our SQL 2005 test servers before moving to production, however when the distributor attempts to push out the initial snapshot I keep getting this error.
Error messages:
Incorrect syntax near ')'. (Source: MSSQLServer, Error number: 102) Get help: http://help/102
Incorrect syntax near ')'. (Source: MSSQLServer, Error number: 102) Get help: http://help/102
Incorrect syntax near the keyword 'end'. (Source: MSSQLServer, Error number: 156) Get help: http://help/156
The following query works in SQL Server 2000 but gives follwoing error in SQL Server 2005
Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to float.
=======================
SELECT DISTINCT VWCONTACT.[ID] ID FROM VWCONTACT WHERE VWCONTACT.ACCOUNTID = 2615 AND VWCONTACT.VIRTUALDELETIONDATE IS NULL AND VWCONTACT.EMAIL LIKE '%@%' AND NOT EXISTS (SELECT VWCONTACT.ID FROM (SELECT DISTINCT VWCONTACT.[ID] ID FROM VWCONTACT JOIN (SELECT CONTACTID FROM VWCONTACTATTRIBUTEVALUE WHERE CONTACTATTRIBUTEID = 22943 AND ISNUMERIC(ATTRIBUTEVALUE) = 1 AND CONVERT(float,ATTRIBUTEVALUE) >= 77002) TMP0 ON VWCONTACT.ID = TMP0.CONTACTID JOIN (SELECT CONTACTID FROM VWCONTACTATTRIBUTEVALUE WHERE CONTACTATTRIBUTEID = 22943 AND ISNUMERIC(ATTRIBUTEVALUE) = 1 AND CONVERT(float,ATTRIBUTEVALUE) <= 77099) TMP1 ON VWCONTACT.ID = TMP1.CONTACTID WHERE VWCONTACT.ACCOUNTID = 2615 AND VWCONTACT.VIRTUALDELETIONDATE IS NULL ) NESTEDQUERY1 WHERE VWCONTACT.ID = NESTEDQUERY1.ID) AND NOT EXISTS (SELECT VWCONTACT.ID FROM (SELECT DISTINCT VWCONTACT.[ID] ID FROM VWCONTACT JOIN (SELECT CONTACTID FROM VWCONTACTATTRIBUTEVALUE WHERE (CONTACTATTRIBUTEID = 22943 AND ISNUMERIC(ATTRIBUTEVALUE) = 1 AND CONVERT(float,ATTRIBUTEVALUE) = 77336) OR (CONTACTATTRIBUTEID = 22943 AND ISNUMERIC(ATTRIBUTEVALUE) = 1 AND CONVERT(float,ATTRIBUTEVALUE) = 77338) OR (CONTACTATTRIBUTEID = 22943 AND ISNUMERIC(ATTRIBUTEVALUE) = 1 AND CONVERT(float,ATTRIBUTEVALUE) = 77345) OR (CONTACTATTRIBUTEID = 22943 AND ISNUMERIC(ATTRIBUTEVALUE) = 1 AND CONVERT(float,ATTRIBUTEVALUE) = 77365) OR (CONTACTATTRIBUTEID = 22943 AND ISNUMERIC(ATTRIBUTEVALUE) = 1 AND CONVERT(float,ATTRIBUTEVALUE) = 77396) OR (CONTACTATTRIBUTEID = 22943 AND ISNUMERIC(ATTRIBUTEVALUE) = 1 AND CONVERT(float,ATTRIBUTEVALUE) = 77489) OR (CONTACTATTRIBUTEID = 22943 AND ISNUMERIC(ATTRIBUTEVALUE) = 1 AND CONVERT(float,ATTRIBUTEVALUE) = 77504) OR (CONTACTATTRIBUTEID = 22943 AND ISNUMERIC(ATTRIBUTEVALUE) = 1 AND CONVERT(float,ATTRIBUTEVALUE) = 77598)) ATTR ON ATTR.CONTACTID = VWCONTACT.ID WHERE VWCONTACT.ACCOUNTID = 2615 AND VWCONTACT.VIRTUALDELETIONDATE IS NULL ) NESTEDQUERY1 WHERE VWCONTACT.ID = NESTEDQUERY1.ID) AND NOT EXISTS (SELECT VWCONTACT.ID FROM (SELECT DISTINCT VWCONTACT.[ID] ID FROM VWCONTACT JOIN (SELECT CONTACTID FROM VWCONTACTATTRIBUTEVALUE WHERE CONTACTATTRIBUTEID = 23102 AND ATTRIBUTEVALUE = 'Houston') TMP0 ON VWCONTACT.ID = TMP0.CONTACTID JOIN (SELECT CONTACTID FROM VWCONTACTATTRIBUTEVALUE WHERE CONTACTATTRIBUTEID = 22944 AND ATTRIBUTEVALUE = 'TX') TMP1 ON VWCONTACT.ID = TMP1.CONTACTID WHERE VWCONTACT.ACCOUNTID = 2615 AND VWCONTACT.VIRTUALDELETIONDATE IS NULL ) NESTEDQUERY1 WHERE VWCONTACT.ID = NESTEDQUERY1.ID)
when I modify the query like the following in SQL Server 2005 it works. Now the problem is since it is adynamically generated query from our application based on users selection of criteria, it means a lot to us to change the code.
SELECT DISTINCT VWCONTACT.[ID] ID FROM VWCONTACT WHERE VWCONTACT.ACCOUNTID = 2615 AND VWCONTACT.VIRTUALDELETIONDATE IS NULL AND VWCONTACT.EMAIL LIKE '%@%'
EXCEPT
((SELECT DISTINCT VWCONTACT.[ID] ID FROM VWCONTACT JOIN (SELECT CONTACTID FROM VWCONTACTATTRIBUTEVALUE WHERE CONTACTATTRIBUTEID = 22943 AND ISNUMERIC(ATTRIBUTEVALUE) = 1 AND CONVERT(float,ATTRIBUTEVALUE) >= 77002) TMP0 ON VWCONTACT.ID = TMP0.CONTACTID JOIN (SELECT CONTACTID FROM VWCONTACTATTRIBUTEVALUE WHERE CONTACTATTRIBUTEID = 22943 AND ISNUMERIC(ATTRIBUTEVALUE) = 1 AND CONVERT(float,ATTRIBUTEVALUE) <= 77099) TMP1 ON VWCONTACT.ID = TMP1.CONTACTID WHERE VWCONTACT.ACCOUNTID = 2615 AND VWCONTACT.VIRTUALDELETIONDATE IS NULL ) UNION (SELECT DISTINCT VWCONTACT.[ID] ID FROM VWCONTACT JOIN (SELECT CONTACTID FROM VWCONTACTATTRIBUTEVALUE WHERE (CONTACTATTRIBUTEID = 22943 AND ISNUMERIC(ATTRIBUTEVALUE) = 1 AND CONVERT(float,ATTRIBUTEVALUE) = 77336) OR (CONTACTATTRIBUTEID = 22943 AND ISNUMERIC(ATTRIBUTEVALUE) = 1 AND CONVERT(float,ATTRIBUTEVALUE) = 77338) OR (CONTACTATTRIBUTEID = 22943 AND ISNUMERIC(ATTRIBUTEVALUE) = 1 AND CONVERT(float,ATTRIBUTEVALUE) = 77345) OR (CONTACTATTRIBUTEID = 22943 AND ISNUMERIC(ATTRIBUTEVALUE) = 1 AND CONVERT(float,ATTRIBUTEVALUE) = 77365) OR (CONTACTATTRIBUTEID = 22943 AND ISNUMERIC(ATTRIBUTEVALUE) = 1 AND CONVERT(float,ATTRIBUTEVALUE) = 77396) OR (CONTACTATTRIBUTEID = 22943 AND ISNUMERIC(ATTRIBUTEVALUE) = 1 AND CONVERT(float,ATTRIBUTEVALUE) = 77489) OR (CONTACTATTRIBUTEID = 22943 AND ISNUMERIC(ATTRIBUTEVALUE) = 1 AND CONVERT(float,ATTRIBUTEVALUE) = 77504) OR (CONTACTATTRIBUTEID = 22943 AND ISNUMERIC(ATTRIBUTEVALUE) = 1 AND CONVERT(float,ATTRIBUTEVALUE) = 77598)) ATTR ON ATTR.CONTACTID = VWCONTACT.ID WHERE VWCONTACT.ACCOUNTID = 2615 AND VWCONTACT.VIRTUALDELETIONDATE IS NULL ) UNION (SELECT DISTINCT VWCONTACT.[ID] ID FROM VWCONTACT JOIN (SELECT CONTACTID FROM VWCONTACTATTRIBUTEVALUE WHERE CONTACTATTRIBUTEID = 23102 AND ATTRIBUTEVALUE = 'Houston') TMP0 ON VWCONTACT.ID = TMP0.CONTACTID JOIN (SELECT CONTACTID FROM VWCONTACTATTRIBUTEVALUE WHERE CONTACTATTRIBUTEID = 22944 AND ATTRIBUTEVALUE = 'TX') TMP1 ON VWCONTACT.ID = TMP1.CONTACTID WHERE VWCONTACT.ACCOUNTID = 2615 AND VWCONTACT.VIRTUALDELETIONDATE IS NULL ))
I have this query in a stored procedure. I will simplify it so that it shows the issue I am having.
I have two tables. One table has a PK called PRSubLineID. the other table has that in a foreign key.
I did a query like this using the IN Statement
SELECT PRSubLineID, PRSUBLINENumber, f2, f3, FROM PRSUBLINES PRSL WHERE PRSL.PRSUBLINENumber LIKE '%test%' AND PRSL.PRSubLineID NOT IN (SELECT CSL.PRSUBLineID FROM CtrSubLines CSL)
This is supposed to select those items from one table with a PRSubLineNumber containing the work Test which has a primary key that has not been used as a foreign key in the other table. However I am getting an empty resultset back.
But when I comment out the "And" and end the query after '%test%', Like this:
SELECT PRSubLineID, PRSUBLINENumber, f2, f3, FROM PRSUBLINES PRSL WHERE PRSL.PRSUBLINENumber LIKE '%test%'
I show a resultset containing three records whose PRSubLineID' s are 2384, 2385, 2386.
But when I add this query to the query window:
Select * FROM CtrSubLines CSL WHERE CSL.PRSubLineID in (2384, 2385, 2386)
I also get a null resultset. So hoping to decipher what was going on I changed the initial query to look like this.
SELECT PRSubLineID, PRSUBLINENumber, f2, f3, FROM PRSUBLINES PRSL WHERE PRSL.PRSUBLINENumber LIKE '%test%' AND PRSL.PRSubLineID /*NOT*/ IN (SELECT CSL.PRSUBLineID FROM CtrSubLines CSL)
And this still returned a null result set.
Basically this is saying that my PRSubLineIDs are neither included nor excluded in the set of all values of PRSubLineID in the CtrSubLines table. But that is simply impossible.
HTTP Error 401.1 - Unauthorized: Access is denied due to invalid credentials. Internet Information Services (IIS)
when I attempt to connect to Reports or ReportServer from my desktop, but it works normally when I login with the same userid and run it directly on the server, using IE. It is prompting me for a login 2 or 3 times before failing.
My configuration is:
Report Server system:
Windows Server 2003 R2 SP2 - 32bit
SQL Server 2005 Reporting Services Enterprise Edition Windows & Web Service run as a domain account Database connection is using domain account - not in db_owner, but in RSExec roles Database is on another server, and is Native, not SharePoint integrated Reports & ReportServer are in a separate application pool Reporting Services had SP2 installed before it was configured The Rport Server is a VM
Database server
Windows Server 2003 R2 SP2 - x64 SQL Server 2005 Enterprise Edition 64bit Version 9.00.3200.00
The Report Manager was not working at all, and I discovered that there was no entry in the .Net Framework version on the APS.Net tab in properties for the Reports virtual directory. I am not seeing any errors or anything unusual in the Event log or in the ReportServer log files.
FrontPage 2002 extensions were installed, and then removed. I noticed that this installed a SharePoint virtual directory, and that disappeared when I removed FrontPage extensions.
The domain group my userid is in is in the local Administrators group on the ReportServer system, and I have added this group as a System Administrator and Content Manager through the report Manager.
Hi! I have installed SQL 2000 and SQL 2005 in my computer.I tried to use some new features like row_number(),try..catch.. but are not working giving syntax error.� So someone told me that i had to check the version and when I cheked I realized I was working with SQL 2000: “Microsoft SQL Server 2000 - 8.00.194 (Intel X86) Aug 6 2000 00:57:48 Copyright (c) 1988-2000 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)� So, how can I change it for working with SQL 2005?. It’s like I’m working with SQL 2005 interface but internally it works as SQL 2000. Can you help me please?
I have a SP that works on SQL 2000 but not on 2005
It is just suppose to step through my code and insert values into tables where it finds the "ticked" values
here is apiece of my code. I hope it Helps.
Code Snippet INSERT INTO Members (ClientID, Name, Surname, Email, Username, Password, Active, WlcSent) SELECT ClientID, [Name], Surname, Email, Username, Password, Active, [Welcome Sent] FROM StageMemberUploading WHERE ID = @numValues
SET @CurrentValue = (SELECT SCOPE_IDENTITY()) IF @ClientID IS NOT NULL BEGIN INSERT INTO MemberUsergroup (MemberID, ClientID, UsergroupID) VALUES (@CurrentValue, @ClientID, @UsergroupID) END IF @DateOfBirth IS NOT NULL BEGIN INSERT INTO _MemberProfileCharacterValues (MemberID, OptionID, OptionValue) VALUES (@CurrentValue, 1, @DateOfBirth) END -------------------My Code Stops here ------------------------------ IF @Male = 'x' BEGIN INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID) VALUES (@CurrentValue, 2, 1) END IF @Female = 'x' BEGIN INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID) VALUES (@CurrentValue, 2, 3) END
I have installed SQL 2000 and SQL 2005 in my computer. But none of 2005 feature is working. So someone told me that i had to check the version and when I cheked I realized I was working with SQL 2000: €œMicrosoft SQL Server 2000 - 8.00.194 (Intel X86) Aug 6 2000 00:57:48 Copyright (c) 1988-2000 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)€?
So, how can I change it for working with SQL 2005?. It€™s like I€™m working with SQL 2005 interface but internally it works as SQL 2000. Can you help me please?
I have a simple update statement that is running forever in SQL 2005 but works fine in SQL 2000. We have a new server we put SQL 2005, restored db. The table in question WEEKLYSALESHISTORY I even re-indexed all the indexes and rebuilt the stats as well. But still no luck, still running extremely long. 1 hour 20 minutes.
I'll try to give you some background on these table. Weeklysalehistory has approx 30 fields. I have 11 indesxes set up weekending date being one of them. And replication control has index on lasttrandatetime as well. So I think my indexes are fine.
/* Update WeekEnding Date for current weeks WeeklySales Records */ Update WeeklySalesHistory set weekendingdate = (SELECT LastTransDateTime from ReplicationControl where TableName = 'WEEKHST') where weekendingdate is null
Weekly sales has approx 100,000,000 rows Replication control has 631,000 (Ithink I can delete some from here to bring it down to 100 or 200 records) Although I don't think this is issue since on 2000 has same thing and works fine.
I was trying to do this within SSIS and thought that was issue. I am new so SSIS but it runs long even if I just run it as a job with this simple Update statement so I think its something with tables, etc that is wrong.
One thing on noticed if I look at the statistics in SQL Server Management studio there is a ton of stats. some being statistics on indexes which makes sense then I have a ton of hind_113_9_6 and simiiar one like this. I must have 90 or so named like this. Not sure how to check on SQL 2000 all the stats to see if they moved over from there or what. I checked a few other tables and don't have all these extra stats. Could this be causing the issue do I need to delete all these extras? Any help would be greatly appreciated.
I have made my website using SQL Server Express edition. It is totally database driven and manypages use databases to display data. My problem is that after paying for webhosting fees my web host told me that they do not support SQL Server express edition. Instead they have support for MySQL and Ms Access. Except that there is this MS 2000 Database service for an additional cost. Any recommendations what I should do except changing the host? If I pay for MS 2000 Database servicem, will my website work? My user management(i.e ASPNETDB database) was created by Visual Web developer and I don't want to do user management myself. Any advice?
The DTS works perfectly when I run it manually. However, when I run itas a job it fails. Before you ask if i'm running it under differentsecurity context. I have already made sure of that. I was logged intothe server through remote viewer, when I created and ran the package,as well as scheduling the job. So the accounts they're running underare consistent. They're the same accounts as the SQL Agent is runningunder and it's the sys admin account.The data source is a Fox pro database with a pull of two tables. I amusing Microsoft OLE DB Foxpro driver as my source connection and OLE DBConnection for SQL Server as my destination. I am doing a simple tableto table transformation. The path of my connection is a mapped Drive:E:Main. There are other packages and jobs within my job queue that arepointing to the same database and they seem to run fine using the abovemapped drive. The ONLY difference between this package and otherpackages are that, they're few months old and this one was created lastnight. I have also enabled logging on this package and here is thebelow error when the job fails:Package Steps execution information:Step 'DTSStep_DTSDataPumpTask_1' failedStep Error Source: Microsoft OLE DB Provider for Visual FoxProStep Error Description:Invalid path or file name.Step Error code: 80040E21Step Error Help File:Step Error Help Context ID:0Step Execution Started: 9/23/2006 11:39:17 AMStep Execution Completed: 9/23/2006 11:39:17 AMTotal Step Execution Time: 0.031 secondsProgress count in Step: 0
I created an updateable partioned view of a very large table. Now I get an error when I attempt to declare a CURSOR that SELECTs from the view, and a FOR UPDATE argument is in the declaration.
There error generated is:
Server: Msg 16957, Level 16, State 4, Line 3
FOR UPDATE cannot be specified on a READ ONLY cursor
Here is the cursor declaration:
declare some_cursor CURSOR
for
select *
from part_view
FOR UPDATE
Any ideas, guys? Thanks in advance for knocking your head against this one.
PS: Since I tested the updateability of the view there are no issues with primary keys, uniqueness, or indexes missing. Also, unfortunately, the dreaded cursor is requried, so set based alternatives are not an option - it's from within Peoplesoft.
Hi,Simple question: A customer has an application using Access 2000frontend and SQL Server 2000 backend. Data connection is over ODBC.There are almost 250 concurrent users and is growing. Have theysqueezed everything out of Access? Should the move to a VB.Net frontendtaken place ages ago?CheersMike
