i have created my sql mdf file
now i was tring to create sql script for the same how do i do that?
script to create databse and tables
in object explorer i right clicked on my databse file name
selecdted tasks and then generate script
after that i clicked on next button
now how do i proceed further?
On one of our servers the sqlseragent was running fine. But now it is not running. We se the option to start the server agent each time the OS starts. Even when I tried to start the agent manually from service manager it is not starting. Would any one help on this urgently.
Also how can we identify under what account any job runs. The reason is when we deleted some NT user accounts one of the job failed as this job runs under that userid. But in the properties it shows that sa is the owner of that job. What is best way to create a job so that these jobs run under one userid so that it won't fail when we delete users.
Thanks
Hi Everyone,
Am really interested to know how data mining project is implemented and what are the skill set required to do that. How the algorithms are applied the real world problem. I know how to use SQL Server 2005 BI suite. Please give some basic idea how to start with..
Regards,
Mitra
I have master tables that I will be updating from our ERP system. Some examples I have seen take an approach of dropping a table in SQL server then creating it again before importing; some, and probably my choice, append and update; I have not seen an example where records are all deleted then the data appended afterwards. Of the three approaches which is generally regarded as best practise / most efficient?
View 1 Replies View Relatedto create a code.
Hi everyone,
I have SQL SERVER 2005 on my desktop and i need to run queries from remote machines on my SQL SERVER.
I thought it would be a good i idea to create a web page on my machine to where remote users can contact and activate SQL QUERIES.
My question is very simple and i wonder i a simple answer is available:
How do i run SQL queries whereby ASP code ?
How do i make a connection between a web page (.asp code) and a SQL sever ?
If the answer is complicated as i'm afraid it is, could any one provide me a link for a tutorial dealing with the above issues ?
Thanks a lot !
Hello, I have an urgent need for a DBA who knows SQL server 6.5 and 7 very well. Also if you have any ASP experience this would be music to my ears. Please give me a call to find out more 02 9221 6601 Australia.
View 2 Replies View RelatedI have a function written in postgresql that I want to create in sql server (UDF). After effort of full day I am seding this request to please help me, here is the function:
CREATE OR REPLACE FUNCTION fn_comma_env(int4)
RETURNS text AS
$BODY$
DECLARE
rec record;
str text;
comstr text;
BEGIN
str := '';
comstr := '';
FOR rec IN SELECT class.classname FROM hostenv, class WHERE hostenv.classid = class.classid AND (hostenv.hostid = $1) LOOP
str := str || comstr || rec.classname;
comstr := ',';
END LOOP;
RETURN str;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
Thanks in advance,
Syed
These two queries when executed seperately give results in under 10 secs A union between these two does not give results even after 20 minutes ... Any idea why this is happening SELECT T001W.NAME1, t25a5.bezek SKU, QTY = SUM(CASE MSEG.BWART WHEN '101' THEN MSEG.ERFMG WHEN '102' THEN (-1)*MSEG.ERFMG END), YPLNT.VKGRP FROM MARA ,MSEG, MKPF,YPLNT,T001W,t25a5 WHERE MSEG.MANDT = MKPF.MANDT AND MKPF.MANDT = MARA.MANDT and mkpf.mandt =yplnt.mandt and mkpf.mandt = t25a5.mandt AND MKPF.MBLNR = MSEG.MBLNR AND MARA.MATNR = MSEG.MATNR AND YPLNT.PPLNT= MSEG.WERKS AND MSEG.WERKS = T001W.WERKS and t25a5.ww004 = SUBSTRING(MARA.PRDHA, 10, 3) AND MARA.PRDHA <> '' AND MKPF.VGART IN ('WR','WF') AND MKPF.MJAHR=YEAR(@BUDAT1) AND MSEG.AUFNR IS NOT NULL AND MSEG.BWART IN ('101','102') GROUP BY t25a5.bezek,T001W.NAME1,YPLNT.VKGRP SELECT T001W.NAME1, t25a2.bezek SKU, QTY = SUM(CASE MSEG.BWART WHEN '101' THEN MSEG.ERFMG WHEN '102' THEN (-1)*MSEG.ERFMG END), YPLNT.VKGRP FROM MARA ,MSEG, MKPF,YPLNT,T001W,t25a2 WHERE MSEG.MANDT = MKPF.MANDT AND MKPF.MANDT = MARA.MANDT and mkpf.mandt =yplnt.mandt and mkpf.mandt = t25a2.mandt AND MKPF.MBLNR = MSEG.MBLNR AND MARA.MATNR = MSEG.MATNR AND YPLNT.PPLNT=MSEG.WERKS AND MSEG.WERKS = T001W.WERKS and t25a2.ww001 = SUBSTRING(MARA.PRDHA, 1, 3) AND MARA.PRDHA <> '' AND MKPF.MJAHR=YEAR(@BUDAT1) AND MKPF.VGART IN ('WR','WF') AND MSEG.AUFNR IS NOT NULL AND MSEG.BWART IN ('101','102') GROUP BY t25a2.bezek,T001W.NAME1,YPLNT.VKGRP
View 14 Replies View RelatedHello Friends,
1) I have a table and column names are
Executiveid
companyname
positiontitle
start date
enddate
I want to see all executives who worked for more than 1000 days in one job. I am not sure but may be i need to use datediff function.
i have 3 tables and i have to use joins
table 1 executive
executiveid
firstname
lastname
companyid
table2 company
companyid
companyname
sales
currencycode
table3 currency
currencycode
description
how am i supposed to join 3 tables. i know how to give joins on 2 tables.
Your help will be greatly appreciated. Thanks
hi all ,
i am working on a migration project of database from access 2003 to sql server 2000 but facing a problem in converting queries from access to sql server , is there any way or tool through which i can convert the queries to sql server format
Thanks in advance
Diwakar
I have a function written in postgresql that I want to create in sql server (UDF). After effort of full day I am seding this request to please help me, here is the function:
CREATE OR REPLACE FUNCTION fn_comma_env(int4)
RETURNS text AS
$BODY$
DECLARE
rec record;
str text;
comstr text;
BEGIN
str := '';
comstr := '';
FOR rec IN SELECT class.classname FROM hostenv, class WHERE hostenv.classid = class.classid AND (hostenv.hostid = $1) LOOP
str := str || comstr || rec.classname;
comstr := ',';
END LOOP;
RETURN str;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
Thanks in advance,
Syed
Hi, My site (in VB.NET) has developed a intermediate timeout problem.
System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
Can someone tell me what could cause this intermediate problem. Is it something wrong with the query? It is definitely the problem from the query as I have 2 queries using same design and both of them develop similar problem.
Pleaee help as our site will have major advertising next month so need to fix it by then.
My server is running Win2003 on SQL 2000.
I have shown the cut-down version of the original query:
***************************************************************CREATE PROCEDURE dbo.sp_getmyitems(@CurrentPage tinyint,@PageSize int,@TotalRecords int OUTPUT)AsSet NoCount ON
Declare @FirstRec intDeclare @LastRec intSet @FirstRec = (@CurrentPage - 1) * @PageSizeSet @LastRec = (@CurrentPage * @PageSize + 1)
Create Table #TempTable(LocalItemID int IDENTITY PRIMARY KEY,ItemID int,Title varchar(250),)
Insert Into #TempTable(ItemID,Title)SELECT ItemID, TitleFROM tbl_Items
Select ItemID,TitleFrom #TempTableWhere LocalItemID > @FirstRec AND LocalItemID < @LastRec
Select @TotalRecords = Count(*) FROM #TempTableGO
**************************************************************Thank you
4 days ago we had a Server crash luckily we have the daily full database backups...Since the server which was crashed is unusable I installed SQL 7.0 on another machine...I tried to restore databases from our backup tapes...Here is the T-SQL I am running:
restore database pentatel from tape='.ape0' with file=2,move 'PentaTel_dat' TO 'D:mssql7datapentatel.mdf', move 'pentatel_log' to 'D:mssql7datapentatel.ldf'
The error message I am getting is:
Processed 8992 pages for database 'pentatel', file 'PentaTel_dat' on file 2.
Processed 1 pages for database 'pentatel', file 'PentaTel_log' on file 2.
Server: Msg 9004, Level 21, State 1, Line 1
The log for database 'pentatel' is corrupt.
Connection Broken
After these when I look at the SQL Enterprise manager I see that the database is in loading status...I left it in that status for hours but there was no reaction...So I tried to detach and attach a single file.Here are the TSQLs and Error messages...
exec sp_attach_single_file_db pentatel, 'd:mssql7datapentatel.mdf'
Server: Msg 945, Level 14, State 2, Line 1
Database 'pentatel' cannot be opened because some of the files could not be activated.
Server: Msg 1813, Level 16, State 2, Line 1
Could not open new database 'pentatel'. CREATE DATABASE is aborted.
We have 7 backups and I tried all of them...Same error messages...
When I try to change the status from loading to emergency or normal the database is always inaccessible
I are running these statements
exec sp_configure 'allow',1
reconfigure with override
update sysdatabases set status=32768 where db_id=6
and I stop and start the Mssql7 service but still inaccessible
Any idea???
I despretaly need help...Any recomendation is greatly apreciated...
Sefa Sevtekin
Pls consider the following table:---
Table Name: ChemoAdmin
Fields:
Field Name: Field Type: Typical Value:
patientID (Varchar 10) XYZABC001
stationDate (DateTime) 09/17/2004
drugName (Varchar 25) Drug 1
dose (Numeric) 5
doseUnit (Varchar 5) mg
I require the following output using one query:---
DATE 09/17/2004 09/21/2004
Drug 1 5 mg Nil
Drug 2 2 mg 4 mg
Drug 3 Nil 1 mg
Pls help.
Pls consider the following table:---
Table Name: ChemoAdmin
Fields:
Field Name: Field Type: Typical Value:
patientID (Varchar 10) XYZABC001
stationDate (DateTime) 09/17/2004
drugName (Varchar 25) Drug 1
dose (Numeric) 5
doseUnit (Varchar 5) mg
I require the following output using one query:---
DATE 09/17/2004 09/21/2004
Drug 1 5 mg Nil
Drug 2 2 mg 4 mg
Drug 3 Nil 1 mg
Pls help.
hi guys
i have a query which will take lot of load on the sql server and it need to optimized.
can any one suggest in optimizing the below query.
SELECT TOP 1 p.store_purchase_id
FROM valid_purchase p WITH (NOLOCK)
INNER JOIN purchase_item pitm WITH (NOLOCK)
ON p.purchase_id = pitm.purchase_id
INNER JOIN customer_phone c WITH (NOLOCK)
ON p.customer_brand_id = c.customer_brand_id
INNER JOIN phone ph WITH (NOLOCK)
ON c.phone_id = ph.phone_id
WHERE
p.last_name = ''
AND pitm.item_datetime > CURRENT_TIMESTAMP
AND pitm.item_datetime <= DATEADD(d, 12, CURRENT_TIMESTAMP)
AND REPLACE(ph.number, '-', '' ) = ''
Thanks
Ramakanth
Hi,I urgently need help with this. I am trying to implement custom paging with Sql 2000. However I have ran into a few problems.I really hope that someone would be able to help me out. I need to get this done by tomorrow!! (I know I screwed up big time, but I didn;t relize that built in paging fetches all the records, and the displays it page by page!!) 1) Currently the paging seems to be working. However the primary key always resets itself. For example, lets say I have 4 records ID 1,2,3,4When I delete ID 3, instead of displaying ID 1,2,4 it shows ID 1,2,3. Is there any way to solve this problem?2) The database that I am currently creating will have about 40,000 records, is it advisible for me to use your control (I hope so)?Thank you and do have a nice day.http://www.codeproject.com/aspnet/ASPNETPagerControl.asp CREATE PROCEDURE [dbo].[GetPagedProducts11]
(
@PageSize int,
@CurrentPage int,
@ItemCount int output
)
AS
Declare @UpperBand int, @LowerBand int
-- Get The Count Of The Rows That They Meet the Criteria
SET @ItemCount = (SELECT COUNT(*) FROM aduan)
-- Calculate the @LowerCount and @UpperCount
SET @LowerBand = (@CurrentPage - 1) * @PageSize
SET @UpperBand = (@CurrentPage * @PageSize) + 1
-- create a temporaty table
CREATE TABLE #AllRows(
ad_id int PRIMARY KEY IDENTITY(1, 1),
ad_nama varchar(40),ad_tarikh datetime , ad_status varchar(30) ,ad_title varchar(100)
)
-- INSERT ALL THE Rows that meets the Criteria
INSERT INTO #AllRows
SELECT ad_nama,ad_tarikh, ad_status,ad_title
FROM aduan
-- AND finally select and return desired -Paged- Rows
SELECT ad_id, ad_nama, ad_tarikh, ad_status,ad_title
FROM #AllRows
WHERE ad_id > @LowerBand AND ad_id < @UpperBand
RETURN
CREATE TABLE [omx].[UserAuthorization]( [MemberAuthorizationRecordIdentifier] [varchar](50) NOT NULL, CONSTRAINT [PK_UserAuthorization] PRIMARY KEY CLUSTERED( [MemberAuthorizationRecordIdentifier] ASC)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]) ON [PRIMARY]
View 4 Replies View RelatedMy DTS package for importing data from oversea's database fail becasue it use many time ( more 2 hours ) . It will be complete When the run duration was used less than 2 hours and fail when the run duration was used more than 2 hours . If we eleminate to improve network , there is any way to do ?
Thanks you
Hi, I'm building a SSAS Integration project, it consist of extracting data from a Microsoft Access 2003 database and consolidate data into a SQL Server 2005 database for datawarehousing.
I need to make several operations for integration, but i'm struck in the following:
- I added an Ole DB Origin that connects to my access database
- I made an sql query with multiple joins that extract all data i need for first instance.
- in data i extracted, i got one column named: FECHA (and many other colums), it contains data of the date of the actual sale; but in previous steps i fill a table named DimTiempo from my sql server database that contanins all the dates (with no repeating) of the sales and added an unique primary key for them; so, now i need to get that primary key for each column FECHA i gotted in my sql query in OLEDB ORIGIN, for example:
I have: 20/20/2004 (column FECHA on my OLEDB ORIGIN gotted with an sql query with multiple joins)
I need: 001 (extracted from DimTiempo, its the primary key of the registry 001 - 20/20/2004)
I need that code or primary key to insert into my new table on my sql server database because its related directly with my DimTiempo table filled previously.
For more directions, i don't have that primary key column on the original access data base; i reestructure completely the database on sql server to make the datawarehouse work fine.
Please, i hope you help me, tell me what do i need to do to get all the data to fill my sql server 2005 database.
Thanks.
Hi All,First of all its very very urgent. Secondly I am not an expert. Now here is my problem.I want to loop through my html files and want to save the data in my table column. here is the Code which need correction.CODE:USE [AdventureWorks]DECLARE @cmd varchar(1000);DECLARE @FileName varchar(100);DECLARE @FilePath varchar(100);SET @FileName = 'C: est'+@FilePath+'html';SET @cmd = 'INSERT INTO myTable(FileName, FileType, Document)' + 'SELECT ' + @FilePath + ', FileType, * FROM OPENROWSET(BULK @FileName, SINGLE_CLOB) AS Document'EXEC (@cmd);Select * from myTableJust for practice I am using Adventureworks db. I am using SQL Express 2005.My html files are named something like this:AC0234.htmlDB9803.htmlCG4571.htmlI cannot change my file names.Thanks in advance.Regards;
View 1 Replies View Relatedhi Friends...
I have got a ordermaster table in which the orderid field is autoincremental.Now I also have an Ordercode field which is a combination of [current (orderid)-LICON)]
eg 1-LICON ..
I achieve this using the following query ...
insert into ordermaster(ordercode,orderamt,orderdate) select cast(max(orderid)+1 as varchar)+'-LICON' as ordercode,1000,'3/6/2004' from ordermaster
But I think the above query could lead to an inconsistent state ....If say following occurs :
1) USER1 reads max orderid as 1
2) User2 also reads max orderid as 1
3)USER 1 inserts record with ordercode as 2-LICON.
4)USER 2 will also insert record for ordercode as 2-LICON since max orderid read by USER2 is also the same as USER1.
How do I avoid this....I know I there are LOcktypes. But aren't locktypes used when we are updating same records....
Please help me out .I really need help in ths context
We have a BIG problem that has been occurring for quite sometime. We have a RAGGED-RIGHT FFS (FlatFileSource) component which receives a FF and then transforms to XML. We define 10 FIELDS with the last field as {CRLF} per ragged right style. Simple right? The source file is sent to us from multiple separate groups, some of which don€™t use the last two fields and have early line termination, so they basically are not there. The problem is that when we define X amount of fields in SSIS, it expects X amount of fields to be there. So when we receive the source file that doesn€™t contain last two fields, SSIS tries to read in the next line, and in most cases successfully places the fields from the next line into the missing fields. Now what we end up with is a source file with 10 rows, but only half successfully transformed, with every other row fitted into the last two XML fields. SSIS completely ignores the {CRLF} until those specific numbers of spaces/fields have been met €“ even if it has to go onto the next line to get them. This seems horribly wrong to me. I would think that at the very least there should be some type of validation that prevents it from grabbing the next line. I thought that is what fixed-length SSIS style is for, and ragged right for allowing to parse multiple rows. We could enforce all clients to produce X char spaces (and we do), but there can be cases in any aspect where a file could get accidentally sent over with a shortened row. I have heard from certain individuals that SSIS adapter wasn€™t designed with this in mind. Is this true, I am to believe that this would still get parsed without throwing any errors and potentially damage the ERP system it is going into? If this is the case then I feel extremely disheartened about SSIS €“ especially with all of the great advancements it has made.
We desperately need a workaround or at the very least some way to validate that a new row {CRLF} isn€™t getting picked up in the first row of the flat file connection. Please can someone help ASAP!?
Hi all,
i think i have serious problem. i have almost 20,000 rows with the following conflict on a table (generated by merge replication over a period of around 3 weeks) :
'The row was updated at 'SubscriberServer3.DatabaseA' but could not be updated 'at 'PublicationServer1.DatabaseA'. Metadata mismatch'
i have 20 remote subscribers & 1 publisher. The above message originates from practically everywhere.
Besides dropping(not practical at the moment) & recreating replication to get rid of them, what else can i do to resolve this problem?? Do i have to resolve this problem row by row?? i'm at my wit's end. Please help!!
Thanx in advance.
Hi!I have a large project that is due to complete this week. In order tocomplete it I need SQL Server 2000 installed on a remote server. Mydisk is corrupt and to order another media disk would damage mydeadline. I have the licence and serial key, but now need good installfiles. I am even ready to buy another retail box, if I can find asupplier that would give me a download site for the media, while I waitfor the shipment!Please PLEASE help!Regards,Barry
View 6 Replies View RelatedHello there guyz!ahm, i just want to ask, how can i upload a MS word text file to a SQL Dbase? I know i must use the Input File, the one in the Toolbox menu for HTML controls. but i need to know 1. How can i link the SQL dbase to the Input file Button? 2. How to put the MS word file onto the SQL dbase? 3. what should i use to declare the MS Word file on the SQL Dbase? is it variable? char? etc... THank you so much! Please i Really need this one to put up my thesis system. and if it isn't much of a task, please can anyone send me the SOURCE CODES for my questions?
View 9 Replies View RelatedPlease provide me with this queries solution
"select leadsource_id from Leadsource_Master where trim(ucase(leadsource_name))='" & Address_Module.name_check(Trim(UCase(cmbLeadSource ))) & "'"
It is a query in access, I want to use the same in SQL SERVER 2000 using VB 6.0
I need to create either a sql query or a procedure and i am not sure how to go about it.Simply i have two fields in a table. 1 for products values and 1 for how many of that product was purchased.What i am wondering is how to calculate this in a sql query.can someone help pleaseIlan
View 5 Replies View RelatedI am deleting a database but I am not seeing any entry in server logs or in event viewer. and same thing is happens when I am deatching a database.Can any body tell me the way through which I can know when a particualr database was deleted or deatched.
View 2 Replies View RelatedThis is probably a very silly question.I started learning ASP.net by following ASP.NET Unleashed. I am stuck where he wants me to open a connection to SQL Server database. I have just downloaded
MSDE. But I dont know where to type this code and how to run it..so as to connect to the database.
<%@ Import Namespace="System.Data.SqlClient" %>
<Script Runat="Server">
Sub Page_Load
Dim conPubs As SqlConnection
conPubs = New SqlConnection( "server=localhost;uid=webuser;pwd=secret;database=pubs" )
conPubs.Open()
End Sub
</Script>
Connection Opened!
Now do i have to change the uid to SA ? (i had to assign one when i downloaded and installed MSDE?
Thanks for the help.
Hi all,
am not very experienced in using DTS and really need your help. I have a dts package that i have scheduled to run every day. Here's what i want the package to do:
1. Check whether a value for a certain column in a certain row of a table in my database is 0 or 1. If it is 1, then
2. Run the dts task (which i have created and is working)
In other words, when the package is started, i want to execute a stored procedure or sql task or whatever, and if that returns 1 then i want to continue, if it returns 0 i want to finish the package without running the dts task. I'm sure there's a simple way to do this, but i could use your help...!
Thanks,
Elisabet
Psting For Charan,
Hi,
I am trying to connect MS SQL 2000 with C language using ODBC drivers in win 2k environment
i have a UTF 8 string stored in a nvarchar field of the MS SQL table and i am trying to retrieve this field from C and store it in a txt file
everythin is working perfectly fine the probs is that the UTF 8 string is not getting being retrieved correctly
Help Required Urgently
Tks in Adv
Ssg
Hello All,
When I execute my SP it throws an error.
Could not allocate space for object '(SYSTEM table id: -449020546)' in database 'TEMPDB' because the 'DEFAULT' filegroup is full.
Any help is apperciated.
Nirene