I have a windows-based application that I use to manage fitness events (road races, mountain bike races, etc.). I have the application set up on multiple computers for three reasons:
1) We often manage multiple events on the same day, and
2) I back-up race data during the event in case of catastrophic data loss/hard drive crash/etc.
3) I am trying to set up the possibility of letting race participants sit down and enter their registration data on a laptop to save our staff (generally me) from having to enter several hundred ourselves on the morning of the event.
That is the scenario. Here is the problem: the entire system works very well on our domain/network but when I take the system to a remote site and set up a work-group it is not reliable in the sense that it sometimes works but other times times out.
For instance, lets say a participant sits down at computer 1 and enters their data. The data is sent to sql server on computer 2 via a vb6 windows application. And a registration form is printed out on a computer connected to computer 2 and shared amongst all of the workgroup computers. In some cases it works but generally speaking it times out. Often times if I set the workgroup up at my location it will work but not at a remote location. Several of the computers have wireless capability which I use at home but not at the race site. I connect to the workgroup via cat 5, etc...
The thing that frustrates me is that it works at home but not on the road. Are there any sql server gurus that can point me in the right direction? I REALLY need to get this resolved.
Hi. I want to return multiple rows into a single row in different columns. For example my query returns something like thisThe query looks like thisSelect ID, TYPE, VALUE From myTable Where filtercondition = 1ID TYPE VALUE1 type1 121 type2 152 type1 16 2 type2 19Each ID will have the same number of types and each type for each ID might have a different value. So if there are only two types then each ID will have two types. Now I want to write the query in such a way that it returnsID TYPE1 TYPE2 VALUE1 VALUE21 type1 type2 12 152 type1 type2 16 19Type1, Type2, Value1, and Value2 are all dynamic. Can someone help me please. Thank you.
Previously I was performing the process of populating my DataMart and then processing my cubes via 2 separate job scheduled one after the other and with about 30 minutes idle time in-between (just in case).
I now have tried to make a single job with both of the above as 2 steps (one after the other) of a single job.
However when I was done, I did a right click and tried to run the newly created job but instead of what I was hoping (that the job would start, would perform the 1st step, followed by the 2nd), it instead popped up a dialog box asking (I think) which step needs to be executed :(
Is there a solution so that when the job is scheduled to run, it should start performing the steps in the order rather than waiting for someone to select which step to perform.
Previously I was performing the process of populating my DataMart and then processing my cubes via 2 separate job scheduled one after the other and with about 30 minutes idle time in-between (just in case).
I now have tried to make a single job with both of the above as 2 steps (one after the other) of a single job.
However when I was done, I did a right click and tried to run the newly created job but instead of what I was hoping (that the job would start, would perform the 1st step, followed by the 2nd), it instead popped up a dialog box asking (I think) which step needs to be executed :(
Is there a solution so that when the job is scheduled to run, it should start performing the steps in the order rather than waiting for someone to select which step to perform.
hi, I have settup up sql mail and did the following: 1. created an E-mail account and configured Out look by creating a pop3 mail profile. tested it by sending and receiving mail, that is ook 2. I Created one domain account for MSsqlserver and Sql Agent service. both services use same account and start automatically in the control panel-services 3. I used the profile that I created in outlook to test the sql mail but got an error: Error 22030 : A MAPI error ( error number:273) occurred: MapiLogon Ex Failed due to MAPI Error 273: MAPI Logon Failed
I really do not know what went wrong. I followed the steps from bol and still having a problem. Am I missing something.
I do have a valid email account I do have a valid domain account I tested outlook using the email account and it worked. so why sql server does not recognise MAPI.
My next question, How to configure MAPI in Sql server if what I did was wrong.
Hi, I have 2 windows 2000 server in cluster with sql server 2000 enterprise edition installed. I have activated the Server-Requested Encryption by using the sql server network utility (Force Protocol Encryption). After this, I have stoped sql server service. But I can't start it at this moment. The error is: 19015: The encrypton is required but no available certificat has been found.
I want to send 1 email with all clientname records which the cursor gets for me. My code however is sending 1 email for 1 record i.e clientname got from db. What's wrong? please help. I ano table to understand here about the while if right. thanks. +++++++++++++++++++++++++++++++++++++++++ CREATE PROCEDURE test1 AS
declare @clientName varchar(1000)
declare myCursor CURSOR STATIC for
select client_name from clients ------------------------- -- now prepare and send out the e-mails declare @ToEmail varchar(255) declare @FromEmail varchar(255) declare @Subject varchar(255) declare @Body varchar(2000) declare @UserID numeric(38) declare @UserName varchar(255) declare @SMTPServer varchar(100) set @SMTPServer = 'test.testserver.com'
-- loop for each record
open myCursor fetch next from myCursor into @clientName
--loop now: while (@@fetch_status=0)
begin -- while(@@fetch_status=0) -- check if valid "To" e-mail address was found if ((@clientName is null) or (ltrim(@clientName) = ''))
begin --should not come here anytime ideally set @FromEmail = 'me@test.com' set @ToEmail = 'me@test.com' set @Subject = 'was emailed to wrong person' set @Body = 'the client name got is : '+ @clientName + 'client is null or empty'
end --if
else
begin set @FromEmail = 'me@test.com' set @ToEmail = 'me@test.com' set @Subject = '-testing' set @Body =
'this will send ClientName:'+ @clientName end --end else
-- send the e-mail --exec dbo.usp_SendCDOSysMailWithAuth @FromEmail, @ToEmail, @Subject, @Body, 0, @SMTPServer --fetch next from myCursor into @clientName
fetch next from myCursor into @clientName
end --while(@@fetch_status=0) exec dbo.usp_SendCDOSysMailWithAuth @FromEmail, @ToEmail, @Subject, @Body, 0, @SMTPServer close myCursor deallocate myCursor
I am using ODBC (ODBCLink/SE) to connect to HP3000 system;Retrieving the data into Microsoft Excel goes fine usingMicrosoftQuery.But if I try to use SQLServer2000-DTS on Windows2003 to do the import,it always fails and gives the message:"ODBCLINKSE does not allow multiple thread"Does anybody knows how to do that?I need to synchronize data in HP3000 into my database in SQLServer; andI dont see any other ways to do that besides using DTS-scheduled-jobs.Pls help..
I am trying to create a report using Reporting Services.
My problem right now is that the way the table is constructed, I am trying to pull 3 seperate values i.e. One is the number of Hours, One is the type of work, and the 3rd is the Grade, out of one column and place them in 3 seperate columns in the report.
I can currently get one value but how to get the information I need to be able to use in my reports.
So far what I've been working with SQL Reporting Services 2005 I love it and have made several reports, but this one has got me stumped.
Any help would be appreciated.
Thanks.
I might not have made my problem quite clear enough. My table has one column labeled value. The value in that table is linked through an ID field to another table where the ID's are broken down to one ID =Number of Hours, One ID = Grade and One ID= type of work.
What I'm trying to do is when using these ID's and seperate the value related to those ID's into 3 seperate columns in a query for using in Reporting Services to create the report
As you can see, I'm attempting to change the name of the same column 3 times to reflect the correct information and then link them all to the person, where one person might have several entries in the other fields.
As you can see I can change the names individually in queries and pull the information seperately, it's when roll them altogether is where I'm running into my problem
Thanks for the suggestions that were made, I apoligize for not making the problem clearer.
Here is a copy of what I'm attempting to accomplish. I didn't have it with me last night when posting.
--Pulls the Service Opportunity
SELECT cs.value AS "Service Opportunity"
FROM Cstudent cs
INNER JOIN cattribute ca ON ca.attributeid = cs.attributeid
WHERE ca.name = 'Service Opportunity'
--Pulls the Number of Hours
SELECT cs.value AS 'Number of Hours'
FROM Cstudent cs
INNER JOIN cattribute ca ON ca.attributeid =cs.attributeid
WHERE ca.name ='Num of Hours'
--Pulls the Person Grade Level
SELECT cs.value AS 'Grade'
FROM Cstudent cs
INNER JOIN cattribute ca ON ca.attributeid =cs.attributeid
WHERE ca.name ='Grade'
--Pulls the Person Number, First and Last Name and Grade Level
SELECT s.personnumber, s.lastname, s.firstname, cs.value as "Grade"
FROM student s
INNER JOIN cperson cs ON cs.personid = s.personid
INNER JOIN cattribute ca ON ca.attributeid = cs.attributeid
WHERE cs.value =(SELECT cs.value AS 'Grade'
WHERE ca.attributeid = cs.attributeid AND ca.name='Grade')
This morning I can not connect to our SQL Server 7.0 whatever from client or server. The error message which I list below:
++++++++++++++++++++++++++++++++++++++++++++++++++ ++++++++++++++++++++++++++++ A connection could not be estabished to server--Timeout expired Please verfy SQL Server is running and check your SQL Server registration properties and try again. ++++++++++++++++++++++++++++++++++++++++++++++++++ ++++++++++++++++++++++++++++
We use windows NT authentication. We did not do any change on NT. The SQL Server daily schedule job usally stoped at 10:00AM, but today from the Window NT Task Manager, we can see that the SQL Server is still running untill now.
I'm trying to get some XML data into SQL Server but i ran into problem when inserting the data (multiple orders with multiple order details) using a single sproc. Is it possible, or do I have to do in some other way? :confused:
I simplified my example to this: ----------------------------- --CREATE PROCEDURE sp_InsertOrders AS
DECLARE @docHandle INT, @xmlDoc VARCHAR(4000), @orderID INT
--DROP TABLE #Orders CREATE TABLE #Orders ( OrderId SMALLINT IDENTITY(1,1), FkCustomerID SMALLINT NOT NULL, OrderDate DATETIME NOT NULL )
--DROP TABLE #OrderDetails CREATE TABLE #OrderDetails ( OrderDetailsId SMALLINT IDENTITY(1,1), FkOrderID SMALLINT NOT NULL, ProductID SMALLINT NOT NULL, UnitPrice SMALLINT NOT NULL )
INSERT INTO #Orders (FkCustomerID, OrderDate) SELECT CustomerID, OrderDate FROM OpenXML(@docHandle, 'Orders/Order', 3) WITH ( CustomerID INTEGER, OrderDate DATETIME )
SET @OrderID = @@IDENTITY;
--INSERT INTO #OrderDetails (@OrderID, ProductID, UnitPrice) SELECT @OrderID AS OrderID, ProductID, UnitPrice FROM OpenXML(@docHandle, 'Orders/Order/OrderDetails', 3) WITH ( ProductID INTEGER, UnitPrice INTEGER ) -----------------------------
All orders are inserted first which makes the use of @@IDENTITY incorrect (it works fine if you insert a single order with multiple order details). Since it was quite some time since I last worked with SQL I am not sure if am doing it the right way... :confused: :confused: Anybody out there who knows how to solve the problem?
I concatenate multiple rows from one table in multiple columns like this:
--Create Table CREATE TABLE [Person].[Person_1]( [BusinessEntityID] [int] NOT NULL, [PersonType] [nchar](2) NOT NULL, [FirstName] [varchar](100) NOT NULL, CONSTRAINT [PK_Person_BusinessEntityID_1] PRIMARY KEY CLUSTERED
[Code] ....
This works very well, but I want to concatenate more rows with different [PersonType]-Values in different columns and I don't like the overhead, of using the same table in every subquery ([Person_1]). Is there a more elegant way to do this, without using a temp table or something else?
I need to update multiple columns in a table with multiple condition.
For example, this is my Query
update Table1 set weight= d.weight, stateweight=d.stateweight, overallweight=d.overallweight from (select * from table2)d where table1.state=d.state and table1.month=d.month and table1.year=d.year
If table matches all the three column (State,month,year), it should update only weight column and if it matches(state ,year) it should update only the stateweight column and if it matches(year) it should update only the overallweight column
I can't write an update query for each condition separately because its a huge select
I think this is a question for the specialists among us.
Can I use one general reporting server (installed on instance MAINREPORTING) for multiple customers who all have their own sql instance (CUST1, CUST2, CUST3, ..) I would use UserAuthenciation on the reportserver url to display the specific reports customers can use.
Is this possible, and what do i have to take care off concerning installation and/or configuration (especially on the reporting side) ?
Hello, I am facing a huge problem in my sql server database using access as a front end.The main problem is trying to execute queries "views" ,since they reside on sql server now,and using variables or parameters in reports and forms to filter on this query. Ex. how can the following be implemented using the same query but in sql server? Access ------ SELECT MAT_Charts.YYYYMM FROM MAT_Charts WHERE ((([Area_Code] & "-" & [GROUP_CODE])=[Reports]![MAT_Chart_C1].[MAT_Key])) GROUP BY MAT_Charts.YYYYMM;
It is specifically this statement in which I am interested: [GROUP_CODE])=[Reports]![MAT_Chart_C1].[MAT_Key]))
Is there a way in SQL 7 ( NT ), one can allocate how much CPU time to be used for a big query or a job, so others can work and not feeling much of performance hit ? Looks to me when a query is submitted, server used all of available resources and slow down other user requests.
i want to create fulltext search for that i have started Microsoft Search component but when i am tring to open SQL server Enterprise manager, i am getting sql server group but it showing "no items" not any database or any other data. what to do.
hi all i try all date convertion function that u imagen in my asp.net project to insert a date to sqlserver datetime field but always sql server get the data as "1/1/1900 00:00:00" how can i fix that from sql server plz
There is Production server with different Unicode Id and its standard edition, I need to build the server with same configuration of production server, Can anyone help me as to how to get all the info of the server
I'm running 7.0 sp2,windows 2000, 1gig of ram, and a 933mhz cpu. Server has been very stable with no problems until I moved a 2gig table into the database. Query performance is excellent even table scans take less than 2 minutes. The problem is that once a table scan is performed on the table (I can't index for every possible query) the query finishes but the enterprise manager freezes on the server and users can no longer connect. I've set SQL server to have only 650 MB of ram and the rest is free, problem also existed when the memory was controlled entirely by SQL server.
My cache Hit Ratio was 97% and Cache flushes 0.0 (unfortunately these can't be checked when the problem exist because the box is frozen). I may have a concurrency issue but I'm not sure how to be positive. I don't want to just throw memory at the problem because I'm not sure the problem will be fixed.
I have a Sql Server cluster set up in an active/active configuration. It is running NT4.0 SP6 and SQL Server 7.0. The problem is that node 2 has failed over to node 1 and whatever I try, I cannot get node 2 to failback. I think I have tried just about everything. Any SQL Cluster gurus out there ? Any help would be very much appreciated. Thanks in Advance
I'm having a problem with linking two different servers..basically what i need is to run a stored procedure from server b where the tables are present in server A i tried out this on test using sp_addlinkedserver it worked fine but when i'm trying to use the same process real time its giving me this error message. server A is Baan and Server b is Baan-test.
[OLE/DB provider returned message: Client unable to establish connection] [DBNMPNTW]ConnectionOpen (CreateFile()).
what i did is use this procedure link the server and run the command from the destination server.I thought the login has a problem so made sure that the login for sa and pwd are same at both ends.
Or could anyone guide me through an alternative approach which can be imlemented.
Hi , I am an Informix user who has shifted to SQL Server,,,,I just wanted to know the equivalent of 'ISOLATION TO DIRTY READ' as in Informix in SQL SERVER....
My Production server has Windows NT and Sql server 7.0 installed, there are 3 drives my data lies on physial E: which is 8.46 GB and 1.91 GB is used it is mirrored. I need to create a Database with two tables with varchar, datetime and numeric columns. the data would be 30 millions. everynite 10 millions data would be deleted and then same amount would be inserted. During day time it would be only selects on that database. My main corncern is can SQL Server handles this big db and does inserts and deletes be completed in 7 hrs during nite?
I have a very urgent situation and to do with SQL Server 6.5, cannot start and the error message I got was: Initdata: Suballocation for buffer pages failed (838860 Bytes requested. Can anyone help me urgent matter.
Hi, I am installing the SQL server 7.0 as a production server and as soon as the server is started, within a week the date will be around 10 millinon rows, so I want to know how much space should I allocate for the tempdb and what should be the incremental percentage of the the tempdb(New server).
Hello, We stopped one of our SQL Servers from the SQL Enterprise Manger earlier. For some reason it doesn't want to start back up. I deleted the SQL Server registration for that box in my SQL Enterprise Manager. Tried to register it again and got the error message: "General network error. Check you network documentation.|ConnectionOpen(connect().
I restarted the box. The SQLServerAgent services were configured to start automatically, but they don't start the server. I tried to do it manually, didn't help.
Is there any way to start the server from the DOS command line, or some other way?
I am unable to connect to the SQL Server if i am not logged on to the NT domain. Is there any way i can connect to the SQL Server even if i am logged on to only my workstation. I get an error :
Unable to connect to server. ConnectionOpen ( Createfile() )