So Frustrated...why Does It Have To Be So Difficult
Nov 22, 2006
Why have you made connecting to a sql server express database so difficult?
I have it working locally, but going to production has been nothing but a nightmare.
I can't even connect locally on the production box.
I am on a dedicated server and my login is an Admin on the box. I have just installed SQL Express + the fancy management interface.
I have made a copy of my database that I was connecting to dynamically (which was failing remotely) and I have attached it to the server. I have made myself a user of the database and granted my self evey permission available.
I have turned on impersonation in my web.config. The database knows it's me trying to connect and STILL denies me when I'm actually ON the production server. It is not even a remote connection problem.
How can I sit there an look at myself as a user of the database in the admin interface, yet I cannot connect via a web app. With SQL server 2000 and MSDE it was soooo simple....
Here is a snippet from my web.config
<connectionStrings>
<remove name="LocalSqlServer"/>
<add name="LocalSqlServer" connectionString="Data Source=localhost;Integrated Security=True;Initial Catalog=TEST" providerName="System.Data.SqlClient"/>
...
</connectionStrings>
Here is the error:
Cannot open database requested in login 'TEST'. Login fails.
Login failed for user 'DEDICATEDquick'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Cannot open database requested in login 'TEST'. Login fails.
Login failed for user 'DEDICATEDquick'.
I have a screen shot of the admin interface to prove I am a user.
This should be a 5 minute task that has eaten up days.
So frustrated...
Bernie Quick
View 6 Replies
ADVERTISEMENT
May 21, 2004
I'm not to familiar with SQL so I'm frustrated. The deal is this: I have a table with product information in it. I'm creating an xml doc from this data via a datagrid. I have to add two extra fields into the table to match my DTD. I did that fine and what I need to do is set those fields equal to the value in another. I need to pull my id field value and store that in the BuyUrl field which is appended to the end of a url. How can I achieve this? Thanks for your help.
View 5 Replies
View Related
May 22, 2008
Please help!
I have just set up SQL server and Visual Studio Pro to set up a development machine for a uni course I am doing, I have begun writing databases and at first everything seems okay, I write a small app fire it off in debug and all works, then without warning Visual Studio is unable to connect to the database despite using the corrrect credentials, I knows its an SQL problem because I get a server error through IIS telling me that it cannot connect through <em>ComputerNameLoginName</em>
if i turn everything off and restart IIS I can get a connection but then despite it retrieveing the database when testing a query (within the add SQL connection form in VS2008, it shows the table info)
Then once again al is lost and it refuses to tafe valid connections until I restart (however I CAN login through SQL Management)
Any suggestions gratefully recieved!
Jimmy
Me transmitte sursum, caledoni
View 2 Replies
View Related
Aug 21, 2006
Greetings, all!I've searched the MS SQL Server Books Online and this group.I've been doing Oracle on UNIX for 12+ years, and now I have some SQLServer databases.I've discovered the INFORMATION_SCHEMA tables (just like the good ol'dba_* views inOracle)I've figured out how to use SQL2005 Management Studio.What'd I'd like to know is - can SQL Server be used to write SQL likeOracle can; e.g:spool csyn.sqlset heading offset pagesize 6000select 'CREATE PUBLIC SYNONYM '||table_name||' FORSYSADM.'||TABLE_NAME||';'from dba_tables where owner='SYSADM';spool offIn SQL*Plus, the above will create a script to create public synonymsfor tables in a PeopleSoft database (tables in PeopleSoft multiply likeTribbles in a storage bin of quadrotriticale...).Yah, GUI's are fine....until you have thousands and thousands of tablesto deal with!Thanks ever so much
View 1 Replies
View Related
Oct 2, 1998
Can anyone get SQL Mail to work? Just so as I know that what I`m doing is not a complete waste of time.
Scenario
SQL Server 6.5 SP4, NT 4.0 SP3, Outlook 97/Exchange Client 4.0
I want to automatically email a workstation the status Scheduled Tasks from SQL Server. This mail will then be forwarded via SMS to a mobile phone.
I can send messages from SQL successfully. These I can see in the Outbox of Outlook, but the messages do not always leave the Outbox. Users have recieved SQL mail, but this generally requires restarting Outlook on the server (which tends to crash most of the time so various processes have to be terminated - all very messy). I can send `manual` mail to users using Outlook, no problem. I even can see these messages leave the Outbox, while SQL Mail stays.
Has anyone experienced similiar problems, or got any suggestions
Thanks in advance
Frustrated!?!
Jasper
View 2 Replies
View Related
May 14, 2008
Folks, I've been trying to extract the minimum and maximum date from the following query:
select top 27 calendar_day.date_value from calendar_day where calendar_day.date_value < current_date() and calendar_day.insession = 1 order by calendar_day.date_value desc
When I then turn this into a derived table and try to extract the minimum/maximum dates, I get the actual minimum/maximum dates of the whole table rather than the result set from the derived table, so obviously it's an aggregate problem. I'm at a loss to figure out how to pull these two dates out and would appreciate any help from more seasoned SQL folks than me.
Thanks,
David
P.S. I'm using SYBASE SQL
View 9 Replies
View Related
Mar 15, 2006
I want to use a Service Broker Queue as a processing queue. I found several examples on the WEB and have tried to make them work but they do not. Below is the example I used and I ASSUME I should get a Hello World in the Message field at the end but its empty.
Any ideas as to why?
I have tried
select * from ReceiverQueue
and
select * from SenderQueue
before I do a RECIEVE and both QUEUE's are empty
Scooter
----------------------------------CODE--------------------------------------------
USE AdventureWorks
GO
CREATE MESSAGE TYPE HelloMessage VALIDATION = NONE
GO
CREATE CONTRACT HelloContract (HelloMessage SENT BY INITIATOR)
GO
CREATE QUEUE SenderQueue
CREATE QUEUE ReceiverQueue
GO
CREATE SERVICE Sender ON QUEUE SenderQueue
CREATE SERVICE Receiver ON QUEUE ReceiverQueue (HelloContract)
GO
DECLARE @conversationHandle UNIQUEIDENTIFIER
DECLARE @message NVARCHAR(100)
BEGIN
BEGIN TRANSACTION;
BEGIN DIALOG @conversationHandle
FROM SERVICE Sender
TO SERVICE 'Receiver'
ON CONTRACT HelloContract
-- Send a message on the conversation
SET @message = 'Hello, World';
SEND ON CONVERSATION @conversationHandle
MESSAGE TYPE HelloMessage (@message)
COMMIT TRANSACTION
END
GO
--select * from ReceiverQueue
--select * from SenderQueue
RECEIVE CONVERT(NVARCHAR(max), message_body) AS message FROM ReceiverQueue
-- Cleanup
DROP SERVICE Sender
DROP SERVICE Receiver
DROP QUEUE SenderQueue
DROP QUEUE ReceiverQueue
DROP CONTRACT HelloContract
DROP MESSAGE TYPE HelloMessage
GO
View 7 Replies
View Related
Dec 15, 2006
I have the following table:tblFriendsOwnerCode FriendCode7 107 1410 710 1210 1312 1013 1013 1814 718 13
I need a SP which return the following (im unsure about the best return datatype and the sql statement):
I want return all friendcodes of user nr 7 (10 and 14)and I want to return all friendcodes of user 10 and 14 (7,12,13,7) WITHOUT user 7
(if possible WITHOUT the use of a temptable!)
View 4 Replies
View Related
Mar 11, 2008
I have roles set up for different companies. The role names are structured companyname_department, ex.,
CallawayContracting_SalesDepartment
CallawayContracting_Administration
FredsAutobody_PaintSales
How would I search the roles and return only departments that belong to a certian company and also users that belong to a certian company. I appear to have gotten myself into quite a bind. Any help would be much appreciated! I will be certian to click best answer.
View 4 Replies
View Related
Apr 6, 2008
I'm pretty new to this so I'll explain as best I can.
I am building a small DB which will track attendance for employees based on a point system. I believe I'm almost there (with this piece) but am stuck.
The basic concept:
1. Collect all records from the attendance table for the previous 14 day period
2. sum the points column in the attendance table and group by UID, storing in a new table called TOTAL_POINTS ONLY for those UID's which have a value > 0
3. Perform a basic insert into statement on the attendance table for each UID matching those found in the previous TOTAL_POINTS table
Number 3 is where I'm failing and could really use some help.
My code thus far...
-------------------------------
/*Declare local variables for current date and start date.*/
--
DECLARE @DateNow DATETIME
DECLARE @StartDate DATETIME
SET @DateNow=getdate()
SET @StartDate = DATEADD(Day, -14, @DateNow)
--
/*Create table to hold totals for future calculations*/
CREATE TABLE POINT_TOTALS
(UID int, TOTAL float)
/*select ALL records from the table within the above defined date range
and group them by UID tallying the score column*/
--
INSERT INTO POINT_TOTALS
SELECT UID, SUM (POINTS) AS TOTAL_POINTS
FROM attendance
WHERE date >= @StartDate
GROUP BY UID
--
/*If the TOTAL_POINTS > 0 for the 14 day period, insert a record in to the
attendance table which deducts .5 points for the UID in question*/
*** This is where I'm failing ***
--This was just to make sure I was returning the correct results to the POINTS_TOTAL table.
SELECT UID FROM POINT_TOTALS
WHERE TOTAL > 0
/*All I want to do now is for each of the UID's in the POINT_TOTALS table,
I want to perform a basic insert on the ATTENDANCE table where the UID's in both
match. I would think this to be fairly simple but I can't seem to figure it out.
*/
DROP TABLE POINT_TOTALS
View 2 Replies
View Related
Apr 25, 2008
--This is works
SELECT *
from vwClientsByAge
WHERE age like '18'
--This gives me an error
SELECT clientId, firstName, age
from vwClientsByAge
WHERE age < CONVERT(int, '18')
Error: The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
Whats wrong with the conversion?
However, this works:
SELECT age
from vwClientsByAge
WHERE age < CONVERT(int, '18')
What is wrong?
View 3 Replies
View Related
May 29, 2008
Hey i have a query i need help with.
I have a table where i have 4 columns in it which i need to group together and then sum up a cost column also. I want to sum up the columns where i have a parent and and child and then i want to sum up the other column where i have only a child.
Example of the data is below. I think i need to do this in a sub query
ID Ind Parent Child Cost
P110041012705921.8000
W11004101270595.4500
A110041012705921.8000
B110041012705916.3500
R110041012705916.3500
B0100420043.3000
P0100420043.3000
W0100420021.6500
View 2 Replies
View Related
Aug 2, 2005
Hello !This is my table:Ordernr Date ArticleO1 1.1.05 22O2 2.2.05 33O3 5.5.05 22O4 2.2.05 33O7 8.8.05 55I need one result-row for each article with the newest Order(max(date)):article lastDate lastOrdernumber22 5.5.05 O333 2.2.05 O455 8.8.05 O7How can I get this ?I tried this:SELECT distinct article, max(date), max(ordernr)FROM tableGROUP BY articlearticle and max(date) is ok, but I am not sure that max(ordernr) andmax(date) comes from the same row.I think, I will need complex subqueries.Many thanksaaapaul
View 8 Replies
View Related
Jan 25, 2006
Hello !I habe 2 TablesTable1: OrdersFields: Ordernr, OpiecesTable2: CalloffsOrdernr, CpiecesIn Table1 ordernr is primary key.In Table2 the same ordernr can exist oftenMy problemIf the sum(Cpieces) < Opieces:I have to create a new virtual calloffwith Cpieces = opieces - sum(cpieces)Its too high for me.Please helpBest regardsaaapaul
View 8 Replies
View Related
Jul 20, 2005
HiI am trying to produce an update trigger. I understand the concept ofdelete and insert triggers without a problem. Unfortuantely, theupdate triggers do not have particularly simple documentation in BoL.So, can someone please explain to me, quite simply how I would producea trigger on the following:I have table 1 which we'll call simon. In here are various columns androws. I also have table 2, called simon_a, my audit table.Whenever anything is updated or deleted in simon, I want it sent tothe simon_a table. Delete, as above, is fine since it's conceptual buthelp me out on the update one. I cannot seem to figure out how to getthe information from the table before it's updated.As ever, champagne and beer for the successful answer.With thanksSimon
View 5 Replies
View Related
Jul 20, 2005
I have a table that stores billing rates for our employees by client.Each employee can have a different billing rate for each client for aspecified period. Here are the columns in the table.eid - Employee ID#cid - Client ID#startdt - start date of billing rateenddt - end date of billing ratebrate - billing rateI need to create a script that will verify that for a given eid, and cidthat either the startdt or enddt for one billing rate, the periods donot overlap.For example, I need to be able to detect overlaps such as this:eid cid startdt enddt brate001 001 1/1/2003 12/31/2003 $50001 001 11/01/2003 04/01/2004 $75*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 2 Replies
View Related
Jul 20, 2005
suppose I have the following table:CREATE TABLE (int level, color varchar, length int, width int, heightint)It has the following rows1, "RED", 8, 10, 122, NULL, NULL, NULL, 203, NULL, 9, 82, 254, "BLUE", NULL, 67, NULL5, "GRAY", NULL NULL, NULLI want to write a query that will return me a view collapsed from"bottom-to-top" in order of level (level 1 is top, level 5 is bottom)So I want a query that will returnGRAY, 9, 67, 25The principle is that looking from the bottom level up in each columnwe first see GRAY for color, 9 for length, 67 for width, 25 forheight. In other words, any non-NULL row in a lower level overridesthe value set at a higher level.Is this possible in SQL without using stored procedures?Thanks!- Robert
View 22 Replies
View Related
Jan 6, 2008
I Installed CE 3.1 months ago to play around with it to see how easy or difficult it would be to move from SQL Express to CE. Got it all to work with SQL Managment and VS 2005. Now, months later I am back to really upgrading my product to use CE. Oops, now we are on version 3.5. Decided to stay with 3.1 until I realized transact-sql command "TOP" is not supported in 3.1. So, onto the hours of research to do a simple upgrade. Here's where I stand:
1. Uninstalled CE 3.1. Installed CE 3.5. Oh, if only it were that easy. Visual Studio 2005 is still using the dll from 3.1 and still can only see reference to 3.1 in the GAC.
2. SQL Management Studio now cannot open the .sdf database.
3. There is no mention of using CE 3.5 with VS 2005, only VS 2008. Is VS 2008 a requirement to be able to work with .sdf file in VS? I remember I had to install "Microsoft SQL Server 2005 Compact Edition Tools for Visual Studio 2005" when I went through the first installation of CE. Is this now obsolete if I want to use 3.5? I don't even remember what its purpose was other than I needed it.
It seems there is and upgrade.exe file that need to be run command line to updgrade my .sdf file. Are you kidding?
Has anyone upgraded while still using VS 2005? Any advice is greatly appreciated.
View 6 Replies
View Related
Nov 13, 2006
Hi.
I am developing for a system that receives an input from an external modem.
The Transaction is split into 2 sections,
Section 1 = grants the transaction ID,
Section 2 = deliver the transaction Data.
I have 2 corresponding tables,
One called tblremoteunitrequestID (Where the transaction ID is granted)
The other called tblremoteunitrequests (Where the transaction is completed, about 1 second later)
I am writing a diagnostic report that determines if the first part of the transaction completes but the second part fails.
I am having difficulties designing the SQL for this.
Here is some sample data for tblremoteunitrequestID: (The first stage of the transaction)
RecordDate | Serial
13/11/2006 14:00:36 0000-0000-0000-0006
13/11/2006 14:00:30 0000-0000-0000-0004
13/11/2006 13:59:04 0000-0000-0000-0092 (This didtn transaction didnt complete)
13/11/2006 12:15:22 0000-0000-0000-0092 (nor did this one)
13/11/2006 10:31:54 0000-0000-0000-0092
13/11/2006 10:00:29 0000-0000-0000-0006
Here is some sample data for tblremoteunitrequests: (The second stage of transaction, 1st stage has to be completed beforehand)
DateReceived | Serial
13/11/2006 14:00:37 0000-0000-0000-0006
13/11/2006 14:00:31 0000-0000-0000-0004
13/11/2006 10:31:56 0000-0000-0000-0092
13/11/2006 10:00:31 0000-0000-0000-0006
13/11/2006 10:00:25 0000-0000-0000-0004
13/11/2006 07:19:13 0000-0000-0000-0020
From this data I can see that serial number 0000-0000-0000-0006 Successfully completed part 1 and part 2 of the transaction, as did serial number 0000-0000-0000-0004.
Serial number 0000-0000-0000-0092 had trouble, it connected at 13:59:04 (tblremoteunitrequestID) but part 2 didnt complete, so it wasent saved in tblremoteunitrequests. The same happened at 12:15:22 but at 10:31:54 it was successful so it was saved.
I Only want to display the transactions that didnt complete, sounds easy huh?
This is what I hope to get in my Results table:
DateReceived | Serial
13/11/2006 13:59:04 0000-0000-0000-0092
13/11/2006 12:15:22 0000-0000-0000-0092
I was experimenting with T-SQL today, this is what I have done so far:
SELECT DISTINCT
TBLRemoteFeildUnitRequestID.Serial, TBLRemoteFeildUnitRequestID.RecordDate,
CASE WHEN TBLRemoteUnitRequests.DateReceived BETWEEN DATEADD(SECOND,-1,TBLRemoteFeildUnitRequestID.RecordDate) AND DATEADD(SECOND,10,TBLRemoteFeildUnitRequestID.RecordDate)
THEN ' Ok'
ELSE ' Not ok'
END AS PROBLEM
FROM TBLRemoteFeildUnitRequestID LEFT OUTER JOIN
TBLRemoteUnitRequests ON TBLRemoteFeildUnitRequestID.Serial = TBLRemoteUnitRequests.Serial
WHERE TBLRemoteFeildUnitRequestID.RecordDate BETWEEN DATEADD(WEEK, - 2, GetDate()) AND GetDate()
ORDER BY RecordDate DESC
This kinda worked, but it caused records that satisfied the between condition to be displayed twice, once as "Ok" and once as "Not ok".
Heres a sample of the result I got:
Serial | RecordDate (1st part of transaction) | Status
0000-0000-0000-0006 2006-11-13 14:00:36.000 Ok (Duplicated)
0000-0000-0000-0006 2006-11-13 14:00:36.000 Not ok
0000-0000-0000-0004 2006-11-13 14:00:30.000 Not ok (Duplicated)
0000-0000-0000-0004 2006-11-13 14:00:30.000 Ok
0000-0000-0000-0092 2006-11-13 13:59:04.000 Not ok (Correct) (Not duplicated)
0000-0000-0000-0092 2006-11-13 12:15:22.000 Not ok (Correct) (Not Duplicated)
0000-0000-0000-0092 2006-11-13 10:31:54.000 Not ok (Duplicated)
0000-0000-0000-0092 2006-11-13 10:31:54.000 Ok
0000-0000-0000-0006 2006-11-13 10:00:29.000 Ok (Duplicated)
0000-0000-0000-0006 2006-11-13 10:00:29.000 Not ok
I have just about had enough, I have wasted an entire day on this
Someone please Help
Dan
View 4 Replies
View Related
Jan 4, 2000
Hello:
I am working with an application in mssql 65.5, with sp4.
I have the database option 'truncatelog on checkpoint' set, there are no transaction log dumps taken at this time.
We plan now that y2k is over to upgrade to sp 5a. I am not not sure the new configuration
parameter 'logLRU buffers'(I don't have the sp5a readme text with me now) in our situation.
Here is our situtation with the transaction logs: The log, mostly with month-end high actvity,
fills up when long-running, high activity transactions are run.
The log gets to 100% sometimes rather quickly and sometimes near that or above 80%easily.
We check with dbcc open tran and there are no current transaction running. We expand the log just a little.
But it still shows 100@ used when we run dbcc sqlperf (logspace).
Here's the rub: when we try to dump the log, with wither or both back-to-back: dump tran abcdb
with truncate_only and then dump tran with no_log still nothing happens.
Sometimes we stop and start the mssql server and that helps but not always and sometimes when
we are really desperate we stop/start the NT server box(at off hours). Sometimes, the transaction log
reduces to nearly 0% used when we run dbcc sqlperf (logspace) and sometimes not. So we run dump tran
a couple of more times. We wait for several minutes usually to if the dump tan started and if it shows up
under EM under curent activity
This is becoming very frustrating and dangerous as well.
1) has any one had similar experiences with mssql 6.5 sp4?
2) Can anyone advise what we can do to more easily to dump the transaction log? Are we doing
any thing really wrong?
3) Would upgrading to sp 5a really help or should we just start planning upgrading to mssql 7.0?
Any suggestiosn you can furnish will be helpful. Thanks.
David Spaisman
View 1 Replies
View Related
Jul 6, 2005
hello,
I could need some help with a little query.
table "acme"
name1 varchar(128)
name2 varchar(128)
idate datetime
content
A,H,1/1/2005
A,H,2/1/2005
A,I,2/1/2005
A,J,3/1/2005
B,K,4/1/2005
B,L,5/1/2005
I want the following result (for 'A'):
1/1/2005,1
2/1/2005,3
3/1/2005,4
I want to filter for Column "Name1" and cumulative count the entries grouped by date.
what's the simplest solution?
best regards, thilo.
View 4 Replies
View Related
Sep 20, 2007
I'm writing a workflow management application for my work, and its somewhat complicated, here's a general idea of how it works:
- Anything that a company does is defined by a workflow.
- A workflow consists of tasks.
- Some tasks in a workflow can't be started until other tasks have been completed. If task A can't be started until tasks B and C are finished, then task A depends on B and C.
You might imagine that a bank has a workflow for handling a house loan. Before a bank could sign a contract with an applicant, they'd need proof of house ownership, but before they could get proof of house ownership they need an applicant's proof of identity like a driver's license or military ID.
Here's an oversimplified visual:
Each arrow points to its dependency. Each task can have multiple dependencies.
The setup above is represented in the database by a Tasks and a Dependencies table. Tasks has an ID field, and Dependencies has a TaskID and DependencyID field which are both foreign keys to Tasks.ID.
Code:
[Tasks]
ID Status Name
-- ------ ----
1 Done Start Processing Loan Application
2 Done Photocopy applicant's driver's license
3 NotDone Photocopy proof of house ownership
4 NotDone Get a copy of applicant's W-2 forms
5 NotDone Perform credit check on applicant
6 NotDone Sign loan contract
[Dependencies]
TaskID DependencyID
------ ------------
1 0
2 1
3 1
4 2
5 2
5 3
6 4
6 5
Tasks has a many-to-many relationship with itself.
Here's the hard part:
- A task can't be started until all of its dependencies have been completed.
- after a task is completed (meanings its status is marked "done"), I need to return a list of all the new tasks that are ready to be started.
When TaskID 2 is marked "Done", then TaskID 4 is ready to begin; however, TaskID 5 is not ready to begin since it depends on 2 and 3, and 3 hasn't been completed yet.
The requirements of the query are very simple, but the implementation is difficult.
I'll post a prelimenary solution in the next post:
View 1 Replies
View Related
Nov 23, 2005
Hello,Here is a brief summary:Table 1 = All Accounts- with fields such as Customer ID and Account #Table 2 = Deposit Balance Table- with fields such as Account #, BalanceTable 3 = Loan Balance Table- with fields such as Account #, BalanceAll accounts are either deposit accounts or loan accounts. What I needto do is to gather information about total balances in both depositsand loans for each customer. I haven't been able to hit the right queryfor doing this. I can easily get information about one or the other,such as the following:SELECT All_Accounts.Customer_ID, COUNT (DISTINCT(Deposit_Balance_Table.Account_Number)), Sum(Deposit_Balance_Table.Balance)FROM Product_Table, Deposit_BalanceWHERE (Product_Table.Account_Number=Deposit_Balance.Acco unt_Number)GROUP BY Product_Table.Customer_ID ORDER BY 1Which will give me one row for each user, and show me the total numberof deposit accounts each customer has and a sum of the balances in eachof those accounts. I can make a similar query involving Loan Accounts.As soon as I try to draw both, however, I wind up below my depth.Something to do with the handedness of my joins, I believe. Often Iwill get one column of information (either deposits or loans), or thequery will fail because the join I'm attempting is invalid, etc. I needto take every row in the All_Accounts table, match each one to itsbalance in either the Deposit or Loan table, and then group them all bythe Customer ID and sum them, so that I can find out the totalrelationship balance per customer. Any help would be appreciated.
View 5 Replies
View Related
Jul 7, 2007
Hi all,
i have a table containing 24 columns, i would like to generate a new table by input the program gets from the user concurning of the columns he would like to see.
sound like an easy "select ? from table", but the thing is that how can the function know how to get a different number of variables, i mean, one time the user will want to see one column, and afterwards he will want to see 10 columns, is there a solution except generating 24 functions?.
i looked in all kinds of SQL tutorials and nothing came up so i came here, tnx for your help!.
Alon.
View 4 Replies
View Related
Jan 8, 2007
Well, I finally got RS2005 installed on Vista, only problem is, now it won't run. I've installed SP2 (Ctp) and still no joy. It just errors when attempting to access the web service.
Here's the error:
w3wp!library!5!01/08/2007-20:36:56:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException: An internal error occurred on the report server. See the error log for more details., ;
Info: Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException: An internal error occurred on the report server. See the error log for more details. ---> System.NullReferenceException: Object reference not set to an instance of an object.
at Microsoft.ReportingServices.Authentication.WindowsAuthentication.GetUserInfo(IIdentity& userIdentity, IntPtr& userId)
at Microsoft.ReportingServices.WebServer.WebServiceHelper.ConstructRSServiceObjectFromSecurityExtension()
at Microsoft.ReportingServices.WebServer.Global.ConstructRSServiceFromRequest()
at Microsoft.ReportingServices.WebServer.Global.get_Service()
at Microsoft.ReportingServices.WebServer.Global.ShouldRejectAntiDos()
at Microsoft.ReportingServices.WebServer.Global.Application_AuthenticateRequest(Object sender, EventArgs e)
Any clues anyone? I'm all out.
Cheers,
Paul
View 3 Replies
View Related
May 18, 2006
I created a package from an Import Data Wizard, in 2000 you had the option to schedule that job, right then and there, but apparently that was too convenient, anyway... After creating my stored package within the database, I try to set up a job to run that package, it is pretty straightforward and I think it is going well until I try to execute the job and I get the following error.
Message
Executed as user: servernamesystem. The package execution failed. The step failed.
So after some investigation, I believe it is a permissions issue, and at that time (default install) all services were running under "Local System". I had created the package with my domain account which is in the administrators group of the server. So, I added another service domain account in the administrators group and changed the services to run under that account. Again. No Dice, now I get this error...
Message
Executed as user: domainservice_account. The package execution failed. The step failed.
Any help would be appreciated at this point in time.
Thanx
View 6 Replies
View Related
Aug 18, 2007
GO
CREATE TABLE [dbo].[Product]
(
[ProductId] [smallint] IDENTITY(1,1) NOT NULL CONSTRAINT PkProduct_ProductId PRIMARY KEY,
[Name] [varchar](52) NOT NULL,
[Type] [smallint] NOT NULL,
)
For this table
I have to write the querywhich willget the TOP 1 Row of each Type.
I know the alternate way of doing this by union.
But this is not professional.
Can anyone resolve this issue?
View 2 Replies
View Related
Jul 2, 1999
How do you shrink the tempdb in SQL Server 7 after you allocated a large space to it but later realized you allocated too much.... I need to reclaim the space allocated to it but when I try to change the size or do a shrinkdb it either tells me I cannot shrink it smaller than allocated size or it shrinks tempdb to current (which is too large)... I looked online but could not find anything...
Any suggestions or assistance would be much appreciated... up to my eyes in tempdb..... Bill.
--------------------------------------------------------------------------------
View 1 Replies
View Related
Nov 14, 2007
Hi! I am rather new to SQL, and could use some help. I have a table with lots of records, with fields like this:
TableA.Batch
TableA.Wafer
TableA.DieID
TableA.Param
TableA.Value
For each batch, there are ~25 wafers. For each wafer, there are thousands of dies. For each die there are several parameters, and for each die and parameter there is a value.
I want to calculate the median and robust sigma (inter-quartile range/1.35) per wafer, for a parameter. The following code works perfectly for me:
DECLARE @Q1 FLOAT, @MEDIAN FLOAT, @Q3 FLOAT, @SIGMA FLOAT, @x INT, @y INT, @z INT
SET @Q1 = SELECT TOP 1 value FROM (
SELECT TOP 25 PERCENT value
FROM TableA
WHERE batch=@x
AND wafer=@y
AND param=@z
ORDER BY value) Quartile1
ORDER BY value DESC
SET @MEDIAN = SELECT TOP 1 value FROM (
SELECT TOP 50 PERCENT value
FROM TableA
WHERE batch=@x
AND wafer=@y
AND param=@z
ORDER BY value) Median
ORDER BY value
SET @Q3 = SELECT TOP 1 value FROM (
SELECT TOP 25 PERCENT value
FROM TableA
WHERE batch=@x
AND wafer=@y
AND param=@z
ORDER BY value DESC) Quartile3
ORDER BY value
SET @SIGMA = (@Q3-@Q1)/1.35
SELECT @MEDIAN, @SIGMA
Ok, if you are still there: I need to do this for hundreds of wafers, and hopefully without having to manually set the batch and wafer numbers. I'm using MS SQL server by the way. As far as I've understood, there is no for-loop in SQL, and I can't see how I can do this by using GROUP BY.
Any help is very appreciated.
View 5 Replies
View Related
Aug 5, 2005
Hallo !I have 2 tables with similar construction.DataOLD and DataNEW.In my grid I need a JOINED table.On the one hand I need all the data from the old table and the datafrom then new table with the same id (left outer join works)But additionally I need all rows from the new table where no equivalentrows are in the old table.example:TableOLDdiameter amountOLD20 10050 20030 300TableNEWdiameter amountNEW20 50060 60070 120Wished Result:diameter amountOLD amoutNEW20 100 50050 200 NULL30 300 NULL60 NULL 60070 NULL 120Who can help me?Many Thanks.aaapaul
View 6 Replies
View Related
Jul 20, 2005
Hello all.I have a table defined in sql server as follows:ROW_ID (identity)DEPTH_FROM Number (8,3)DEPTH_TO Number (8,3)COLOUR Char(10)With typical data like:ROW_ID DEPTH_FROM DEPTH_TO COLOUR---------------------------------------------------------------1 0 5BLUE2 5 8BLUE3 8 10RED4 10 12GREEN5 12 16GREENI want to create a view that will 'compress/roll up' the data soit appears like:DEPTH_FROM DEPTH_TO COLOUR---------------------------------------------------------0 8 BLUE8 10 RED10 16 GREENI have been working on this for several days, with no luck,any help would be appreciated. BTW: there are no overlapsallowed in the depth_from, depth_to values.Thanks in advance.
View 6 Replies
View Related
Jul 20, 2005
Hi,I have a table as followingaa Text1 aa, Join Bytes!, 15267aa Text1 aa, Join Bytes!, 16598aa Text1 aa, Join Bytes!, 17568aa Text2 aa, Join Bytes!, 25698aa Text3 aa, Join Bytes!, 12258I have to write a query as follows ...SELECT DISTINCT TOP 500 fldText, fldContact, fldItemidFROM tableWHERE fldCat = 10 AND CONTAINS (fldText, 'Text1')In the example you can see the table has rows in which text and contact ordouble but with different itemid's. Now my employer wants me to show only 1row when text and contact or the same. He doesn't mind which itemid I show.... but I have to show one.I've an idea of how to do this using a cursor and a temporary table but Iguess that will be fatal for the performance because then I have to loopthrough all selected rows, check each row with all other rows and store theprimary key in the temporary table if dedected it isn't double. AfterwardsI can execute ... SELECT ... FROM TABLE where primary key in (selecttemp_primarykey from #temptable).I hoped I could do everything in 1 "easy" SELECT but I should not know how?Any ideas are much appreciated.Thanks a lot.Perre Van Wilrijk.
View 1 Replies
View Related
Jan 7, 2008
If you know the answer please explain what you're doing if possible, that'll help me :)I have the following tables:CREATE TABLE [dbo].[tblUserData]( [UserCode] [int] IDENTITY(1,1) NOT NULL, [UserName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [DisplayName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,) ON [PRIMARY]CREATE TABLE [dbo].[tblFriends]( [UserCodeOwner] [int] NOT NULL, [UserCodeFriend] [int] NOT NULL, [createdate] [datetime] NOT NULL CONSTRAINT [DF_tblFriends_createdate] DEFAULT (getdate())) ON [PRIMARY]in tblFriends relations are stored twice, so for a relation between user 5 and 6, there will be 2 rows: 5-6 and 6-5Now, I want to get the columns (UsercodeOwner,UsercodeFriend,createdate,username,displayname) for relations that were created in tblFriends in the last 10 days for the FRIENDS of a person with usercode 5.Example:tblUserdata5 peter Petertje6 john Johnny11 simon SimonSays15 monique MontjetblFriends5 6 'createdate 30 days ago'5 11 'createdate 5 days ago'6 5 'createdate 30 days ago'6 11 'createdate 3 days ago'6 15 'createdate 7 days ago'11 5 'createdate 5 days ago'11 6 'createdate 3 days ago'15 6 'createdate 7 days ago'The resultset for a query on usercode 5 would now be (usercode1, username1, displayname1,usercode2, username2, displayname2,createdate):6 john Johnny 11 simon SimonSays 'createdate 3 days ago'6 john Johnny 15 monique Montje 'createdate 7 days ago'As you can see each relation is only returned twice even though there are always two entriesWhat would be the SQL statement, if possible without temp table..Thanks!
View 21 Replies
View Related
