I have production server 2000. The server gets disconnected sometime by itself and sometime it is working fine.Sometimes it even doesn't get restarted. Is there any problem with service packs and some performance issues.
Can you SQL guru give me best suggestion and how should i proceed.
Hi Everybody,I am facing another probs. I have created a trigger for table Tab1 forperticular column col1 for checking value ranges.But at time for usinginsert statement it is working fine but when I am importing data fromflat file through DTS the trigger is not working.How to solve this.RegardsArijit Chatterjee
View 1 Replies View Relatedthis is my first time using sql server, so i guess i'm def doing something wrong here..
my connection:
Code:
objConn = "Driver={SQL Server};Server=SHEMPCWEBSERVER;Database=aoms;Trusted_Con nection=yes;"
Set conn=Server.CreateObject("ADODB.Connection")
conn.Open objConn
Set rsLogin = Server.CreateObject("ADODB.recordset")
'Select the data from the database using the submitted data.
rsLogin.Open "SELECT * FROM tblUsers", conn
Response.Write rsLogin("fldUsrsName")
my error:
Code:
Microsoft OLE DB Provider for ODBC Drivers error '80040e4d'
[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'SHEMPCIUSR_XBOARDS-VRORER9'.
/aoms/testdb.asp, line 6
can anyone see what i'm doing wrong?
Thanks
Shem
Hello...
I am stuck with update statement...
I am having 3 tables abc,def and xyz...
i want to update column1 of abc with column2 of def values (here the probs is i dont have any common fields between them)
i am having common values between def(column10) and xyz(column10_new)
so i am using inner join something like below....
but this looks like wrong to me...bcoz when i do this (only select):
select column2 FROM def inner join xyz on def. column10 = xyz. column10_new
i am getting different as compared to the update below....
UPDATE abc
SET column1 = column2 FROM def inner join xyz
on def. column10 = xyz. column10_new
can anyone suggest how to solve this update???
Thanks
Hi Group,I am trying to display the multiplication through this way----------------------select 1163436036*100----------------------Getting the error============================Server: Msg 8115, Level 16, State 2, Line 1Arithmetic overflow error converting expression to data type int.============================For that reason I was tried to convert that to nvarchar------------------------select convert(numeric(36,2),1163436036*100)------------------------But still getting the error=============================Server: Msg 8115, Level 16, State 2, Line 1Arithmetic overflow error converting expression to data type int.=============================Please help me to solve it out..Thanks and RegardsArijit Chatterjee
View 3 Replies View RelatedI keep getting errors while trying to install SQL 2000 enterprise edition.....
Internal Error: Contact MS Tech Support
Help !!!
Hi All,
I have this sql syntax which displays the records within the xml but instead of displaying 4 records (3 records relating to the last question ID) but instead resulting in only two records picking only the first options 'Unhelpful'.
Definitely doing something wrong here, please advise!
DECLARE @doc xml
SET @doc =
'<DivisionName>
<QuestInfo Custref="18759" SubDate="2006-01-01T00:00:00"
Polref="30018759" AgentID="4189" ClaimRef="14024-5647-890"/>
<DVName>Ho</DVName>
<DvcodeNo>1</DvcodeNo>
<ClaimGroup>
<CustSurveyNo>4</CustSurveyNo>
<ClaimGroupType>Water</ClaimGroupType>
<Questions>
<QuestionID>45</QuestionID>
<Answer>
<AnswerID>43</AnswerID>
<Ansoption />
</Answer>
</Questions>
<Questions>
<QuestionID>34</QuestionID>
<Answer>
<AnswerID>13</AnswerID>
<Ansoption>
<Options>Unhelpful</Options>
</Ansoption>
</Answer>
</Questions>
</ClaimGroup>
</DivisionName>'
DECLARE @docHandle int
EXEC sp_xml_preparedocument @docHandle OUTPUT, @doc
SELECT *
FROM
OPENXML(@docHandle, '/DivisionName/ClaimGroup/Questions/Answer/
Ansoption', 2)
WITH
(DVName varchar (20) '../../../../DVName',
DvcodeNo int '../../../../DvcodeNo',
CustSurveyNo int '../../../CustSurveyNo',
ClaimGroupType varchar (20) '../../../ClaimGroupType',
QuestionID int '../../QuestionID',
AnswerID int '../AnswerID',
Ansoption varchar (30)'Options')
EXEC sp_xml_removedocument @docHandle
Hi folks,
I got a strange Problem with this statement:
select * from [db1].[dbo].[table1] AS db1
INNER JOIN [db2].[dbo].[table1] AS db2
ON (db1.Text = db2.Text)
Text fields are both nvarchar(50)
I think this should work, but it doesnt?
I got a SQL Server Error 446
I know, text compares are not that good, but it exists no other way....
Thanks for any advice!!!
when i update i want only date portion tp be displayed from datetime datatype...
create table temp11 (datecolumn datetime)
insert into temp11 values (getdate ())
insert into temp11 values (getdate ())
insert into temp11 values (getdate ())
insert into temp11 values (getdate ())
now when i am running this query,i am getting what i want...
select convert (varchar, datecolumn,111) from temp11
but when i am tyring to update in the temp11 table using the below query...
update temp11
set datecolumn = convert (varchar, datecolumn,111)
i am getting date and time as well like...
2008-01-14 00:00:00.000
2008-01-14 00:00:00.000
2008-01-14 00:00:00.000
2008-01-14 00:00:00.000
i only want the date portion in my updated new table.....
any suggestions plzzzzzzzzz
is it poss thru any sql query???or shud be done in the front end only???
Hi All,
I've designed a simple sales report , first grouped by Transport type and then by item.
Example
Category Item Sales
DHL A 2000
DHL C 1500
DHL E -1700
AmEx A 200
Amex E 20
-------------------------------
when click on sales under group DHL it shouild sort -say descending-
so result should be
DHL A 2000
DHL C 1500
DHL E -1700
instead I get results
DHL A 2000
DHL E -1700
DHL C 1500
this is just an example but this is the sort of results I get when I try to use interactive sorting.
Any Ideas?
thanks
Sonny
You notice after installing SQL Server 2005 that there is a warning message in the SQL Server log that SQL Server cannot lock pages in memory. What must you do to allow SQL Server to lock memory pages in memory?
After installing SQL Server, your customer complains that transactions are slow. You notice that there is excessive I/O in the TempDb. You want to move TempDb to another set of disks, so you have the sytem administrator configure two more logical disks on the server. What must you do to configure the SQL Server TempDb on this new set of disks?
You are instructed to install SQL Server in an Active / Active cluster environment. What two network related pieces of information must you have prior to performing the installation?
Has anyone faced problems in calling one stored procedure from within another stored procedure ? I am considering using nested SPs, and would appreciate any inputs on it.
Thks,
SC
Hi All,
It would be great if anybody could help me how to write a T-SQL script to generate a report in spreadSheet from a table in SQLserver 2000 database.
And this report should be generated as per the following timings(Sheduled timings only).<script></script>
As well it should be able to send a mail regarding the availability of the report to user groups.
Incase if the generation of report fails then we need to send a mail reg the failure.
Any HELP is greatly appreciated!!!
Thanks in Advance!!!
Regards,
Sashi
Hi:
Am trying to import XLS data into SQL 2005 SP2 thro a SSIS Data Flow task. My Excel Connection string has IMEX=1, ImportMixedTypes is set to Text and the typeguessrows is set to 0.
Import works fine for cells of Format Text, but when I have a large number (in a general Format cell) it gets converted into scientific notation(e.g. 3.234175e+7) in the table.
What am I doing wrong?
TIA
Kar
Hi. I am trying to figure out the code for sorting a manual (non-identity) number column in my table. the purpose is to
show the user's pictures in perfect order (1,2,3,4,5,6...).
The Jist of my problem... When a user first inserts six pictures, he gets:
|1|
|2|
|3|
|4|
|5|
|6|
All is good. But, say he deletes picture |3|. Now the list order looks like this:
|1|
|2|
<- |3| is removed
|4|
|5|
|6|
And, then he inserts two more pictures, now he his this:
|1|
|2|
|4|
|5|
|6|
|7| <- |7| & |8| are added
|8|
What i want to acheive is a "reshuffling" of the number order every time a picture is removed. So, when |3| is removed, |4| becomes |3|, |5| becomes |4| and so on. There should never be a gap in the order.
I am new to stored procedures, and have been trying to figure this out. Below is my guesswork:
Code:
ALTER PROCEDURE dbo.sp_NewPersonalPic
(
@photo_name VARCHAR(50) = NULL,
@photo_location VARCHAR(100) = NULL,
@photo_size VARCHAR(50) = NULL,
@user_name VARCHAR(50) = NULL,
@photo_caption VARCHAR(150) = NULL,
@photo_default BIT = NULL,
@photo_private BIT = NULL,
@photo_number INTEGER = NULL,
@photo_date DATETIME = NULL
)
AS
BEGIN
SELECT @photo_date = CONVERT(DATETIME,convert(char(26), getdate(), 109))
END
BEGIN
SET @photo_number = 1
SELECT
@photo_number = (
SELECT COUNT(*)
FROM dbo.PersonalPhotos b
WHERE
a.photo_date < b.photo_date
)
FROM
dbo.PersonalPhotos a
ORDER BY
a.photo_date
END
BEGIN
My thinking is that it would be a safe bet to use the "photo_date" column as a litmus for my "photo_number" column (ie, the most recent record inserted by the user will always be at a later date than the previously inserted record). So:
photo_number photo_date
|1| 2006-06-26 21:43:36.653
|2| 2006-06-26 21:43:50.000
|3| 2006-06-26 21:45:25.217
|4| 2006-06-26 21:45:33.763
|5| 2006-06-26 22:39:42.670
|6| 2006-06-26 22:39:49.200
If |3| is removed above, the numbers are reordered based on the time of entry sequence.
Any suggestions on how to acheive this in my stored procedure? Currenly, i get the correct order, but it goes crazy when i delete and add.
Thanks and sorry for the verbose post.
I have this simple full text search query that works perfectly on my own computer using sql server 2005 express, however, on the production server(shared hosting)when I added the first 50+ rows, the full text search works perfect, but as the number of rows increases, the full text search can only see the first50+ rows, but not the new ones. Is there any quick solution for this or it's just a common mistake for developers for not properly indexed columns?Is there a way to re-indexed all rows without loosing data on the live server? search query: SELECT TOP 50 *FROM li_BookmarksWHERE FREETEXT(Keywords,@Keywords)
View 2 Replies View RelatedI currently have a SQL Server cluster setup with a Primary DB Server SERVER1 and the Standby server SERVER2. SERVER1 has been failing more than normal is the past few weeks and its takes upto 5 mins for SERVER2 realize that SERVER1 is down. I am looking for a better way to implement a backup server on production with minimum downtime. Please adivse..
View 1 Replies View RelatedProduction and development servers are on different domains and they do not trust each other. How do I import data from the table t1 from a database db1 in production and load it into table t1 inside database db1 in development?
View 3 Replies View RelatedAny help would be greatly appreciated.My problem is that I need to set up a backup SQL Server 2000 machinewhich can be used in case of a failure to my primary. All databases(30 as of now) must be an up to the minute exact copy of productionand include most recent changes in data as well as any structurechanges (Tables, Views, SP's, Triggers, Users . . etc).When I tried this using Transactional Replication, the replicationprocess gets fouled up once I introduce any kind of structure changesto the DB. I've considered the idea of doing periodic backups andrestoring it to my backup SQL server, but this does not give me theconcurrency needed with 0 latency.I've seen articles that recommend using Transaction Replication with'Scheduled Table Refresh', and also doing database dumps to restore onthe backup machine, but I have not been able to find any documentationregarding this to try out. How can I implement this type of backupstrategy in SQL 2000?
View 2 Replies View Related
I would like to deploy several reports to production server, Do i need to install reporting services entire software in order to run the reports or is it possible to just have runtime files installed on it to run the reports.
please help, i have almost 100 reports to be deployed on this server which is located in other country.
Thanks for the helpful information.
(i am using SQL server 2005 / reporting services 2005.)
ok ok, stop laughing. for real, is there any programatic way of doing this? whom ever created this database i inherited (SQL 2000) created the LDF and DATA files on the same drive and in the same folder for that matter. just trying to do a little disaster magament.
thanks
matt
This feels like a silly question, but I`m going to ask it anyway...
I have limited SQL Server experience, but have run into a wall with a client`s Web/Access combination. I need to upgrade to SQL Server. I have Beta3 installed on a development box and am very happy with it. Is anyone running this thing in a production environment? This isn`t going to be experience huge loads, so I`m tempted. Tell me if I`m crazy for wanting to try it.
I have a brand new database server with system databases.
I need to copy like four production database from another server to this new server. Can i do restore of the last production backups and restore them on the new server without creating the empty databases on the new server.If any one has better approach i will appreciate
1.First issue
The port number already allocated was 2059. We have changed that into the default port 1433 in the node. Now we are able to connect the node from the client application. But we are not able to see the configuration manager in any of the environment now.
2. 2nd issue
Cluster Environment
Error while executing the Package The connection details are not loaded in the connection manager tag
The same error with advanced information.
Another error window
R.S.Moorthy
Hi,I ran test data on my development machine and it took 1 minute toinsert the data. Ran the same set of data on the server and took 5minutes.Check both database and everything is the same. I even copied theproduction DB on my machine and it was taking still about 1 minute.Look at the fragmentation, and all the numbers are better on the serverthan my development machine so it should be faster.In the application I put some timer and discover that the insert istaking 0.015 ms on the server and 0 on the development. So the problemis on the insert.It is a Web application using ASP.NET.Here are the spec of the computers:Development: P4 HT 3.2GHz 1gig memory running WIN XPServer: Xeon 2.8GHz 1.5Gig memory running WIN 2000 serverAny idea how I to pinpoint the problem? I'm not at the point ofthinking that it can be the hardware, but how to verify that?ThanksFrank
View 2 Replies View RelatedHi,
Presently our Organisation is in process of Implementing SOX.
Under Compliance all the User have to be removed from the Production Server.
1.My Question is Do a DBA Should have Admin Privilages on the Production Server.
If yes then what are the Actitvies that a DBA has to perform only if DBA have admin privilages.
Regards
Sufian
Like most enterprise there is the database administrator (dba) and there are the developers(dev). The dba are conservative while the developers are also exploring their options.
One of the current usage I'm experimenting on is to provide data visualization - image for the data. Like most I needed to "create" the System.Drawing assembly in the database, marking it unsafe.
During my testing, my code had some exception and that brought down SQL Server.
I read that the CLR is better compare to the sp_OAs as well as the extended stored procedures written in C++ because it isolates the execution in a separate app domain and termination is clean - in case of any errors, it should not bring down SQL Server.
Also I read marking assembly unsafe void these benefits of isolation.
Instead of having to manage the situation where it involves code review by the dba and asking the dba to take some risk, is there a technique where all CLR code that runs in the production server does not pose stability issues.
How can I apply SP2 on running production server if SP resets all protocols (from TCP/IP enabled on production server to Shared Memory enabled) without disrupting its connections and thereafter its work?
View 1 Replies View Related
Howdy;
I've tried this in the 'tools' area, but that didn't work too well. I suspect, I will have to generate a T-SQL code then schedule it as a job. Why I can't just drag and drop with basic desires, is beyond me, but THAT probably does exist.
anyway here is the problem
[this server has many databases, on SQL 2000 sp2]
1. User only wants me to use Monday morning's full backup, which is good in that it doesn't include transaction logs.
2. Restore that data overtop/into Developement db. = good, no data to worry about damaging.
3. User does NOT want me to do this by hand, but schedule it.
ok,
a. must do a RESTORE WITH FILELISTONLY from [?] what ?, master? and if I user the *.bak of the production, it has
a coded date field in the name entry SO, I would, I guess, have to generate all sorts of wonderful code to find the date and build a file name. Why, because using the FROM DISK = 'F:MSSQLBACKUPDBPRODUCTION_yyyyddmm.BAK' is not going to work with a wild card.
Can I do a file lookup using a 'PRODUCTION' prefix into a variable, then use that or should I look for latest file date [remember there are several database backups here], or ????
then. How does one schedule such a T-SQL. Do I save it to some text file, and invoke it using a job scheduler.
any help appreciated.
IS there an easier way.
rik
Per the subject line :Is SQL Server Express designed for production use ?
View 11 Replies View RelatedGroup,
I have an ASP.NET web application using the default ASPNETDB that I did in VWD 2005, and deployed to a production server (IIS).
I need to make a table addition to the production database (ASPNETDB). I'm trying to use SQL Management Studio to attache the database, but it keeps saying the database is locked by a process. I shut down all the services that would likely be using it, but it's still showing locked. What do I need to shut down to attach the database to make the change?
Or is there a better way to do it?
Hey everybody!
I'm getting a pretty annoying error on my production server when i want to run an app ..
Error Msg (german): SqlDateTime-Überlauf; muss zwischen 1/1/1753 12:00:00 AM und 12/31/9999 11:59:59 PM liegen.
Funny thing is that on my client development machine i'm not getting an error at all. The DateTimes I use (C# and SQL Server) are dd/mm/yyyy hh:mm:ss formatted. I also don't write to the databse - only read!
Anyone familiar with this issue?
Thanks in advance & best regards!
I am in a deep shit with this...its my life and survival...
How to configure a linked server for oracle.
My oracle instance name is dbserver.internal.ca.com and how do i query since it gives me an error when i use the above name i had this come up some time early.
what information do i need to create a linked server for orale on sql server either using ole-db or odbc.
One more question is there a way where i can import data from oracle to sql server with having a oracle client installed on the machine where sql server exists.
Thanks,
Tina