Hi, is there a software that can dump the SQL result into a file or to another database? I am using the MSSQL Server and I am accessing it remotely. Is there a program where I can issue an SQL statement and then I can dump the result into a file (in SQL inserts or just plain CSV).
Is there a free version?
Thanks!
Does anyone have a code snippet for dumping the actual sql a command would be running?
View 3 Replies View RelatedMy site is hosted with 1and1. They tell me I need to dump my SQL database before uploading it to their site. I have never done this before. I have looked in the online help and have asked them for help, but nothing seems to work. I've tried queries like the following:DUMP DATABASE dbname TO DISK = 'C:folderfilename'It tells me it doesn't recognize "dbname", which is the name of the MDF file (without the ".mdf" extension). So I'm not sure what I should be using for database name.I'm using Visual Studio 2005 Professional. Can someone help me with dumping my database? Thanks!
View 1 Replies View RelatedHey Guys,
I do alot of development on my personal machine and have quite few databases in SQL Server. My PC is very prone to crashing :-p so Im writing a custom batch script to backup up all my data. How can I backup my SQL Server databses into a text file. I dont want to use the BAK file. In MYSQL this is a relatively simple process where i just have to use the mysqldump utility which scripts 'all' my databases (structure and data). Can I do the same thing in SQL Server? Please tell me a way. Its going to save me a lot of trouble.
Thanks in advance guys.
Hi - is it possible to dump the query text of every query runagainst a particular database, or all databases, in an SQL Server2000 installation? We've an issue whereby a 3rd. party applicationrunning queries over ODBC sometimes claims that gobbledegook isreturned, and it would be very useful to identify the query that'sbeing claimed is the problem. I suspect that it's an applicationissue rather than a SQL Server one.__________________________________________________ ___________Are you Catholic ?http://www.CatholicEmail.com100s of FREE email addresses --->http://www.UltimateEmail.comSend an Online Greeting Card http://www.UltimateEcards.com
View 1 Replies View RelatedDoes anyone know how I can dump the transaction log to a file
so I can read it?
Thanks,
Dianne
Using SQL version 6.5, I'm trying to dump our primary database to a backup server
with the following command:
DUMP DATABASE TMSEPRD TO Backup_Server WITH STATS = 10, NOINIT, NOSKIP
I created a backup device called Backup_Sever with the location of
stlcopadmbkackup. When I try the command I get the error
Can't open dump device 'stlcopadmbkackup', device error or device off line.
Please consult the SQL Server error log for more details. (Message 3201)
and in the eventlog it indicates 'access denied' as the cause for the failure.
I've looked in the Books Online and searched the technet database to find an
answer. The Books Online does say that in creating the backup device permissions
are not checked. I've checked and double checked the permissions on STLCOPADMBK
and there shouldn't be any reason why access is being denied.
I've installed the latest service releases for version 6.5 so that shouldn't be
an issue.
Help
I am having problems when trying to dump to a network share (NT Network) from SQL Server 6.5 and would appreciate any insight this group can lend.
To setup this dump, I followed the instructions provided in the SQL Server FAQ (www.swynk.com) and have been able to successfully complete this task on a different server (so I know I'm editing the registry correctly etc..etc..etc..)
none the less,
Here is an exerpt from NT's Event Viewer:
SQL Server Scheduled Task: 16, 'Network Dump - EIWDATA' -- Status: Failed
-- Task Invoked on: 7/23/99 1:45:18 PM -- Message: Can't open dump device 'crosrvdumpeiwdata.dmp', device error or device off line. Please consult the SQL Server error log for more details. (Message 3201)
Thanks for any help :)
mtf8
I was wondering if there is a way to schedule a tast that will dump afixed width text file of all the new entries in a table. So if I hada table with likeusername - varchar(20)created - smalldatetimeI could get a weekly feed each week of all the new users in a textfile. I know I could write a script that would go through and do thisby looking at the time stamp, and the last time that the filepreviously ran and get the new dates but I was hoping there was abuilt way to do this. Or perhaps a more elegant solution.Thanks,Charlie
View 2 Replies View RelatedHi:)I need to dump data from database in to *.sql file. I try it using DUMPDATABASE but it not work that I need it.So, how can I dump data from database in to *.sql file and then run itusing: osql -E -i nameOfDumpedDatabase.sqlThanks for help, greetings, gregory :)
View 4 Replies View RelatedThe transaction log does not truncate despite all
attempts (no_log,truncate_only, etc...) This database is a subscriber
for replication. Tried the reconfigure with override.
There are no open transactions (dbcc opentran)
Need help fast !!!
Danke schoen
Hi all,
While dumping several tables into the same Backup device, I got this error message in the Log :
dbsvolopen: Backup device 'E:BACKPANBackUp_Of_Focus_Tables.DAT' failed to open, operating system error = 32(The process cannot access the file because it is being used by another process.)
There is no other process using this device running at the same time.
Any idea ?
I have some "phantom" tasks running, specifically, DB dumps to a NULL Pipe device and they are not scheduled!!! These do not show up in the SYSTASKS table, where can I look to possibly find these un-scheduled dumps? Thank you....
View 1 Replies View RelatedWhile working with a vast variety of support projects, i find a sql design where all the fields in a single form (say about 100 fields which are dump data as they are not related to any reports and searching criteria) are dumped in a sql database column in a XML format. See below an example
<?xml version="1.0" encoding="utf-8"?><FormBuilder><ClientID>0</ClientID><SiteID>0</SiteID><IncidentType></IncidentType><IncidentCategory></IncidentCategory><IncidentSubCategory></IncidentSubCategory><CreatedBy>2</CreatedBy><CreatedOn>Wednesday, April 02, 2014</CreatedOn><ModifiedOn /><ModifiedBy /><Section SectionID="ASD" SectionDisplayName="ASD" ColumnType="1" IsDeleted="0" SectionPosition="1"><SectionField FieldName="Bro" Section="ASD" ModuleID="0" Length="" PickData="" ChkData="" RadioData="" ListData="" FieldType="Text" Checked="false" ColumnType="1" IsDeleted="0" CoulmnOrder="0" FieldID="1" IsPrimary="" IsMandatory="" SystemMandatory="" RowPosition="1" FullRow="" /></Section></FormBuilder>
Just want to know the comments how far is this design feasible.....
What are the pros and cons of such a design...
Where we should use such type of db design where are the fields are dumped in a single column...
I need to send the result of a procedure to an update statement.Basically updating the column of one table with the result of aquery in a stored procedure. It only returns one value, if it didnt Icould see why it would not work, but it only returns a count.Lets say I have a sproc like so:create proc sp_countclients@datecreated datetimeasset nocount onselect count(clientid) as countfrom clientstablewhere datecreated > @datecreatedThen, I want to update another table with that value:Declare @dc datetimeset @dc = '2003-09-30'update anothertableset ClientCount = (exec sp_countclients @dc) -- this line errorswhere id_ = @@identityOR, I could try this, but still gives me error:declare @c intset @c = exec sp_countclients @dcWhat should I do?Thanks in advance!Greg
View 4 Replies View Related
I have an Execute SQL Task that executes "select count(*) as Row_Count from xyztable" from an Oracle Server. I'm trying to assign the result to a variable. However when I try to execute I get an error:
[Execute SQL Task] Error: An error occurred while assigning a value to variable "RowCount": "Unsupported data type on result set binding Row_Count.".
Which data type should I use for the variable, RowCount? I've tried Int16, Int32, Int64.
Thanks!
Hi all,
I copied the following code from Microsoft SQL Server 2005 Online (September 2007):
UDF_table.sql:
USE AdventureWorks;
GO
IF OBJECT_ID(N'dbo.ufnGetContactInformation', N'TF') IS NOT NULL
DROP FUNCTION dbo.ufnGetContactInformation;
GO
CREATE FUNCTION dbo.ufnGetContactInformation(@ContactID int)
RETURNS @retContactInformation TABLE
(
-- Columns returned by the function
ContactID int PRIMARY KEY NOT NULL,
FirstName nvarchar(50) NULL,
LastName nvarchar(50) NULL,
JobTitle nvarchar(50) NULL,
ContactType nvarchar(50) NULL
)
AS
-- Returns the first name, last name, job title, and contact type for the specified contact.
BEGIN
DECLARE
@FirstName nvarchar(50),
@LastName nvarchar(50),
@JobTitle nvarchar(50),
@ContactType nvarchar(50);
-- Get common contact information
SELECT
@ContactID = ContactID,
@FirstName = FirstName,
@LastName = LastName
FROM Person.Contact
WHERE ContactID = @ContactID;
SELECT @JobTitle =
CASE
-- Check for employee
WHEN EXISTS(SELECT * FROM HumanResources.Employee e
WHERE e.ContactID = @ContactID)
THEN (SELECT Title
FROM HumanResources.Employee
WHERE ContactID = @ContactID)
-- Check for vendor
WHEN EXISTS(SELECT * FROM Purchasing.VendorContact vc
INNER JOIN Person.ContactType ct
ON vc.ContactTypeID = ct.ContactTypeID
WHERE vc.ContactID = @ContactID)
THEN (SELECT ct.Name
FROM Purchasing.VendorContact vc
INNER JOIN Person.ContactType ct
ON vc.ContactTypeID = ct.ContactTypeID
WHERE vc.ContactID = @ContactID)
-- Check for store
WHEN EXISTS(SELECT * FROM Sales.StoreContact sc
INNER JOIN Person.ContactType ct
ON sc.ContactTypeID = ct.ContactTypeID
WHERE sc.ContactID = @ContactID)
THEN (SELECT ct.Name
FROM Sales.StoreContact sc
INNER JOIN Person.ContactType ct
ON sc.ContactTypeID = ct.ContactTypeID
WHERE ContactID = @ContactID)
ELSE NULL
END;
SET @ContactType =
CASE
-- Check for employee
WHEN EXISTS(SELECT * FROM HumanResources.Employee e
WHERE e.ContactID = @ContactID)
THEN 'Employee'
-- Check for vendor
WHEN EXISTS(SELECT * FROM Purchasing.VendorContact vc
INNER JOIN Person.ContactType ct
ON vc.ContactTypeID = ct.ContactTypeID
WHERE vc.ContactID = @ContactID)
THEN 'Vendor Contact'
-- Check for store
WHEN EXISTS(SELECT * FROM Sales.StoreContact sc
INNER JOIN Person.ContactType ct
ON sc.ContactTypeID = ct.ContactTypeID
WHERE sc.ContactID = @ContactID)
THEN 'Store Contact'
-- Check for individual consumer
WHEN EXISTS(SELECT * FROM Sales.Individual i
WHERE i.ContactID = @ContactID)
THEN 'Consumer'
END;
-- Return the information to the caller
IF @ContactID IS NOT NULL
BEGIN
INSERT @retContactInformation
SELECT @ContactID, @FirstName, @LastName, @JobTitle, @ContactType;
END;
RETURN;
END;
GO
----------------------------------------------------------------------
I executed it in my SQL Server Management Studio Express and I got: Commands completed successfully. I do not know where the result is and how to get the result viewed. Please help and advise.
Thanks in advance,
Scott Chang
HI,
I ran a select * from customers where state ='va', this is the result...
(29 row(s) affected)
The following file has been saved successfully:
C:outputcustomers.rpt 10826 bytes
I choose Query select to a file
then when I tried to open the customer.rpt from the c drive I got this error message. I am not sure why this happend
invalid TLV record
Thanks for your help
Ali
As the topic suggests I need the end results to show a list of shows and their dates ordered by date DESC.
Tables I have are structured as follows:
SHOWS
showID
showTitle
SHOWACCESS
showID
remoteID
VIDEOS
videoDate
showID
SQL is as follows:
SELECT shows.showID AS showID, shows.showTitle AS showTitle,
(SELECT MAX(videos.videoFilmDate) AS vidDate FROM videos WHERE videos.showID = shows.showID)
FROM shows, showAccess
WHERE shows.showID = showAccess.showID
AND showAccess.remoteID=21
ORDER BY vidDate DESC;
I had it ordering by showTitle and it worked fine, but I need it to order by vidDate.
Can anyone shed some light on where I am going wrong?
thanks
I am trying to code a WHERE xxxx IN ('aaa','bbb','ccc') requirement but it the return values for the IN keyword changes according to another column, thus the need for a CASE function.
WHERE GROUP.GROUP_ID = 2 AND DEPT.DEPT_ID = 'D' AND WORK_TYPE_ID IN ( CASE DEPT_ID WHEN 'D' THEN 'A','B','C' <---- ERROR WHEN 'F' THEN 'C','D ELSE 'A','B','C','D' END )
I kept on getting errors, like
Msg 156, Level 15, State 1, Line 44Incorrect syntax near the keyword 'WHERE'.
which leads me to assume that the CASE ... WHEN ... THEN statement does not allow mutiple values for result expression. Is there a way to get the SQL above to work or code the same logic in a different manner in just one simple SQL, and not a procedure or T-SQL script.
I'm using a subquery to return a delivery charge line as a column in the result set. I want to see this delivery charge only on the first line of the results for each contract. Code and results are below.
declare @start smalldatetime
declare @end smalldatetime
set @start = '2015-03-22 00:00' -- this should be a Sunday
set @end = '2015-03-28 23:59' -- this should be the following Saturday
select di.dticket [Contract], di.ddate [Delivered], di.item [Fleet_No], di.descr [Description], dd.min_chg [Delivery_Chg], dd.last_invc_date [Delivery_Invoiced],
[code]....
In this example, I only want to see the delivery charge of 125.00 for the first line of contract HU004377. For simplicity I have only shown the lines for 1 contract here, but there would normally be many different contracts with varying numbers of lines, and I only want to see the delivery charge once for each contract.
help strange result whan i do this
Code Snippet
SELECT unit_date, unit, ISNULL(NULLIF ((unit + DATEDIFF(mm, GETDATE(), unit_date)) % 4, 0), 4) AS new_unit
FROM dbo.empList
i try to get next unit value to next month
why i get this -1
on date
01/01/2008
1
-1
unit_date unit new_unit
01/02/2008
2
1
01/02/2008
1
4
01/01/2008
1
-1
01/02/2008
1
4
21/01/2008
1
-1
21/01/2008
1
-1
01/02/2008
1
4
TNX
i am having one ABC database in this a,b,c are the tables
i need to dump a,b,c tables data into z table in the other xyz database
Hi,
I have two table as follows
Tabel Cat
{
ID
Update Datetime
}
Table Master
{
UniqueId
Cat Datetime
}
I wanted to insert millions of rows from Cat to Master table and can be achieved from the following query
Code SnippetIINSERT INTO Master (UniqueId, Cat )
SELECT ID, Update from Cat
Question : Is there any better way do achieve the same operation with better performance mean to say that the execution should be less than the time it takes to execute the above statement
Thanks for your time
~Mohan
Hi,
I have two tables
Table Source
{
category varchar(20),
LastUpdate datetime
}
Table Destination
{
SubjectId varchar(20),
SubjectDate datetime,
category varchar(20),
LastUpdate datetime
}
Please note that the number columns are different in each table.
I wanted to dump the data of Source table to Destination table. I meant to say that the rows of 2 columns in Source table to last 2 rows of Destination table.
And also my oreder of the columns in Destination table will vary. So i need to a way to dynamically insert the data in bulk. but i will know the column names for sure before inserting.
Is there anyway to bulk insert into these columns.
Your quick response will be appreciated
~Mohan Babu
Hi All, I need to For Each loop tied back to a SQL result set. This is what I have so far, I know its not much but I'm stuck. Thanks soooo much!TimDim conn As SqlConnectionDim comm As SqlCommandDim reader As SqlDataReaderDim connectionstring As String = ConfigurationManager.ConnectionStrings("Magic").ConnectionStringconn = New SqlConnection(connectionstring)comm = New SqlCommand("Select Distinct([_Group_]) from _Smdba_._Groupdet_ Where [_InActive_:] = 0", conn)
Tryconn.Open()reader = comm.ExecuteReader()
For Each (FOR EACH SQL RECORD) 'I'm going to put some code in here.... reader.Close()Catch dberrorlabel.Text = "Error Loading Admin Panel"Finally conn.Close()End Try
hello everyone,
i have an sql statement which is like this
select f1,f2, ( sum(isNull(f3,0)) - (select sum(isNull(e1,0)) from otherTable where e2=Value) )
from Table where f3 = 4
it works great!
but there is a case it dont work it
when the subquery which is:
(select sum(isNull(e1,0)) from otherTable where e2=Value)
has no result it dont do the subtraction operation and the value is Null although the sum of F3 (which is subtracted from ) has a value
so is there any way i can give the sum value, the value of zero if there is no result in the record set.
and just for knowledge, i tried
sum(isNull(e1,0)
and
isNull(sum(e1),0)
the both cases and also it returns nothing
any one can help :)
Thank you
In 6.5 I ran a SELECT query with a specific WHERE clause. It gave me a result
set of 430 rows. But when I use the same conditions in WHERE clause in UPDATE,
it updated only 420.
What might be the reason?
Thanks,
Chan
Hi All
Have you got the same result as I have?
select convert(varchar(15), 0x4861707079204368726973746D6173)
HI EVERYBODY
YOU ALL REQUESTED TO HELP ME TO COMEOUT FROM PROBLEM : I HAVE A TABLE NAME PLUCK
IN WHICH DATA STORE: MONYEAR EMPCODE PDATE(ddmmyyy) JOBCODE HCD kg
022006 A01608P 01/02/2006 LA N 0
022006 A01608P 02/02/2006 LA N 0
022006 A01608P 03/02/2006 LA N 0
022006 A01608P 04/02/2006 LA N 0
022006 A01608P 05/02/2006 LH N 0
022006 A01693P 01/02/2006 PL N 21
022006 A01693P 02/02/2006 PL N 25
022006 A01693P 03/02/2006 PL N 15
022006 A01693P 04/02/2006 PL N 25
022006 A01693P 05/02/2006 LH N 0
BUT I WANT REPORT LIKE : 022006 A01608P LA N LA N LA N LA N LH N
022006 A01693P 21 N 25 N 15 N 25 N LH N
(IF JOBCODE = PL THAN INSTEAD OF JOBCODE KGPLUCK IS SHOWN..)
Pls guide how to write the SQL to achive the report...
Thanks
ASM
---sample data insert
USE NORTHWIND
CREATE TABLE PLUCK (MONYEAR CHAR(6), EMPCODE CHAR(7), PDATE SMALLDATETIME,JOBCODE CHAR(2), HCD CHAR(1), KGPLUCK INT)
INSERT INTO PLUCK VALUES('022006', 'A01608P','01/02/2006','LA','N',0);
INSERT INTO PLUCK VALUES('022006', 'A01608P','02/02/2006','LA','N',0);
INSERT INTO PLUCK VALUES('022006', 'A01608P','03/02/2006','LA','N',0);
INSERT INTO PLUCK VALUES('022006', 'A01608P','04/02/2006','LA','N',0);
INSERT INTO PLUCK VALUES('022006', 'A01608P','05/02/2006','LH','N',0);
INSERT INTO PLUCK VALUES('022006', 'A01693P','01/02/2006','PL','N',21);
INSERT INTO PLUCK VALUES('022006', 'A01693P','02/02/2006','PL','N',25);
INSERT INTO PLUCK VALUES('022006', 'A01693P','03/02/2006','PL','N',15);
INSERT INTO PLUCK VALUES('022006', 'A01693P','04/02/2006','PL','N',25);
INSERT INTO PLUCK VALUES
('022006', 'A01693P','05/02/2006','LH','N',0);
Dear All,Can we do multiple result row as a single row while selection ..?Request you to provide the SQL if we can achieve this...For Eg,Table Name = Employeeid - EmployeeName - addresstype - address1 - address2 - City1 - AAA - permanent - 231 - First Street - XYA2 - AAA - Temporary - 343 - Second Street - XYAExpecting Result as a Single row as below,id - EmployeeName - Address1 - Address21 - AAA - 231/First Street/XYA - 343/Second Street/XYAThanks in advanceRgds,Ganapathi sundaram.G
View 1 Replies View RelatedHello,Is there a way to merge the result of a query into one row?For example, let say I want the a list of name of people of 20 year soldMy result will look like that: NAME1 john2 Mike3 Craig I would like to have the result in one row: NAME1 John, Mike, CraigIs there a way to do that?Thanks
View 3 Replies View RelatedDear Experts
Here is my code:
SqlConnectioncn = new SqlConnection(ConfigurationManager.ConnectionStrings["SimvipConnectionString"].ConnectionString);
protected void Page_Load(object sender, EventArgs e)
{
SqlCommand cmd = new SqlCommand("SP_SEARCH", cn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@TypeOfSim", Request.QueryString["NID"]));
cmd.Parameters.Add(new SqlParameter("@CategoryID", Request.QueryString["CID"]));
cmd.Parameters.Add(new SqlParameter("@Price", Request.QueryString["L"]));
cmd.Parameters.Add(new SqlParameter("@Price2", Request.QueryString["S"]));
cmd.Parameters.Add(new SqlParameter("@Title", Request.QueryString["keyword"]));
cmd.Connection.Open();
cmd.ExecuteNonQuery();
SqlDataAdapter sqlAdapter1 = new SqlDataAdapter();
sqlAdapter1.SelectCommand = cmd;
DataSet ds = new DataSet();
sqlAdapter1.Fill(ds, "ds");
CollectionPager1.DataSource = ds.Tables["ds"].DefaultView;
CollectionPager1.BindToControl = DataList1;
DataList1.DataSource = CollectionPager1.DataSourcePaged;
}
My problem is when there is no result(no item in database), the DataList1 will display the text "some text will display here" instead of empty page.
In FormView I know the option <EmptyDataTemplate>. But in the DataList I don't know how to do this. I am a beginner.
Can anybody show me how....Thanks a lots