hi, i want to create a form consist of one table of 2 column and 2 row. when i retrive data from the database, how do i wite the data into the table
example
Database retrived data
student id student name
12 nancy
22 tracy
the table apprence would be like this
c1 r1 value 12
c2r1 value nancy
c1r2 value 22
clr2 value tracy
any one help appreaciated
Dear all,
Do anyone knw that how am I gonna write data from a DB table to TextFiles(.txt)? If use Flat File Destination, how do I do the setting?
Hi,
I need to export data from a table of SQL server 2000 database, into an Excel 2000 sheet. I tried following query from the sql query analyzer
Code SnippetSELECT * INTO [Excel 4.0;Database=F:ew.xls].[sheet1] FROM tab1
It gave me following error
Code SnippetServer: Msg 2760, Level 16, State 1, Line 1
Specified owner name 'Excel 4.0;Database=F:ew.xls' either does not exist or you do not have permission to use it.
The F:ew.xls file is present and it has no additional security applied, hence I think it should be writable.
Please tell me where am I doing wrong.
I don't know if this is the correct news group or not. Let me know if I have to post to some other news group.
- Abhijit
I want to check a table to see what rows have been updated today, then write to a text file some data from the selected rows; then I want to automate this (DTS package? TSQL stored procedure job?) to run every night at midnight. Is the DTS Wizard the best way, that's what I did, but have not confirmed that it is writing a new text file every day, and does each new version write over the old one?
View 1 Replies View RelatedHi everybody.
I have a merge replication scenario between 2 servers.
Everything is OK when I write data in UP server (the server which does the replication).
But when I write data in any tables in the DOWN server throws an error saying that the table where I'm trying to insert data is updating or inicializating for merge replica. This table, where the error throws, isn't involved in the replica. In fact, that table is not involved in none replication.
Can you help me?
Thanks in advance
Hi,
I want to do the following with a ssis package:
INPUT:
A table contains 2 columns with data i need. column A=Filename and column B=FileContent
PROCESS:
I need to loop through ea record in the table and retrieve columns A and B. Then for ea column i need to write the Content hold in column B into File hold in column A.
I so far found out, that i need a Execute SQL Task in Control Flow querying the table and get columns A and B into 2 variables, plus a 3rd var holding the object. Then the output goes into a Foreach Loop Container. From this point i don't know how to continue. I tried to put a Data Flow Task inside the Foreach Loop, but couldn't find out how i now get the 2 variables to the Data Flow Task and use them to for the file to be written and the content to be placed in the file.
Is there any example similiar to that so i could learn how to start on that?
Thanks
Danny
I have been trying to write a cursor to fetch required data from table but somehow its running forever and inserting duplicate records.
I have a temp table named getInvoice where I have five important columns
1. invoice number
2.group
3.invoice status
4. Invoice Expiration date
5. Creation date time
and some other columns.One invoice number can belong to one or more group and there can be one or more records for a particular invoice number and group.
An example is below :
InvoiceNumber Group InvoiceStatus InvoiceExpirationDate CreationDateTime
579312 01 3 NULL 2003-03-24 00:00:00
579312 01 2 2015-12-14 00:00:00 2005-12-24 00:00:00
579312 02 2 2003-12-21 00:00:00 2005-10-12 00:00:00
321244 01 2 2015-12-21 00:00:00 2005-10-12 00:00:00
321244 01 3 2010-12-21 00:00:00 2010-12-21 00:00:00
My query condition is complex and that is why Im facing problem retrieving the output.I need a cursor for getting distinct invoice number from the table and for each invoice number I need to get the latest record for each invoice number and suffix combination based on creationdateand time column and if that record has invoice status of 2 and also the invoice expiration date can be either null or greater than today's date, then I need to get that record and put it in a temp table.
The query I wrote is below
declare myData cursor for
select distinct invoiceNumber from #getInvoice
declare @invoiceNumber varchar(30)
open myData
fetch next from myData into @invoiceNumber
while @@FETCH_STATUS = 0
[Code] .....
This query runs forever and doesn't stop.
Hello All
Just wondered if someone could help me with a bit of T-SQL, i have a application in ASP.NET/VB that allows the user to update a message board by clicking a button "update" this in turn triggers my Stored Procedure for inserting this data into a table, which works great.
It inserts the data into its respective fields and also takes The Title, Line 1, Line 2 and so on and creates a XML file (Using FOR XML) which is stored in the same Table under a column call XML_Data. Which again works great.
My problem now is how do i output this XML_Data to an actual XML file that is on my local machine, i.e. It be created in say C:Inetpubwwwrootxmlfiles("xml file name inserted here from another column that holds xml file name").xml
Any help on this would be greatfully apreciated
Thanks In Advance
Neil
Any easy way for a batch file or automated process to read from one db and table and what ever entry is missing out of another database + table it writes those missing entries to.
This is a simple table in one db that is filled with usernames, I want to see if there are missing usernames in another db and table and write those entries.
db1.usr_table.usr_name = jdoenew
If jdoenew is missing in the 2nd db I will need to write entries like:
db1.usr_table.usr_name = jdoenew
db1.usr_table.password = tmppassword
db1.usr_table.active = 1
Hello, I have worked with SQL Server 2000 but now we have a requirement where I need to write values from an asp.net form into an XML type in SQL Server 2005.I have never used XML as a type in SQL Server 2005. How do we write xml into xml type. For example the structure of XML is something like:<application><applicationID = "value"></applicationID><customerName="value></customerName></application>I have to write this kind of XML into the XML type and later retrieve these XML values and populate the form again.Kindly suggest. Thanks a lot.
View 1 Replies View RelatedHow to write a left join using just one table? I have to select things using left join. Can you give just a example?
View 1 Replies View RelatedHi, we're trying to read from a table and write back to the same table and are having a lot of trouble with blocking. What could we do to prevent our application from hanging due to blocking of this type?
View 1 Replies View RelatedI've got a rather large database, approx 560 tables, that is about 10G. We're running into trouble when an application moves data from my database into GreatPlains accounting software.
The problem seems related to the value 'foo' not coming across correctly. I've looked at all the tables that I think would be involved, but couldn't find the value. So, I'd like to write a script to sequence through the tables and check each column for the value.
Anybody know how to write such a script.
Thanks,
alex8675
I know oracle uses UTL_FILE procedures lik putline, but microsoft?
Thank you for trying to help.
Hi,
Please advice me how to write query from table which is given as input(It is not hard coded)
Declare @varTableName varchar(30)
Select @varTableName = 'table_employee' -- table name
Select * from @varTableName
This gives me an error : Must declare the variable '@varTableName'.
I am running SQL Server 2000 and need to know how to write an IF else statement with a variable for a table name. I am constantly getting errors when I attempt this feat.
Code:
Use [TestDatabase]
Go
CREATE PROCEDURE UserInputAsTable
[code]....
Is there a way to get the last read/write time of a table?
I want to have a few tables, but only allow them to exist if they have been used in the last 30 days. I want to set up a "purge" job to clear out any tables that have not been used in 30 days.
hi all,
I'm working on vb.net 2.0 with clr stored procedures. i've created a project from VB-->Database. there is a stored procedure in my sql server 2005. In the vb project i have to read the stored procedure and write it as xml . and i have to give this writed xml to clr stored procedure.. do any one know the code for this? if so please post here..
I need to write data returned by a stored proc to an XML file. The resultant file will then be consumed by an external application. The file has to adhere to a specific schema. Is there a way to do this through SSIS? Is .NET CLR a better option?
View 1 Replies View RelatedI need to write data returned by a stored proc to an XML file. The resultant file will then be consumed by an external application. The file has to adhere to a specific schema. Is there a way to do this through .NET CLR in SQL Serer 2005? Is SSIS a better option? Thanks.
View 5 Replies View Related
I have big problem now, can somebody help me?
I have a program used ASP+SQL2000 ,the connection string is :
strConnection = "Provider=SQLOLEDB;Server=BOUNwindows,1005;User ID=training;Password=training22;Database=ourtraining;"
Everything works fine, I can insert data, delete or modify.
But since we changed sql2000 server to sql 2005 express, then I can read data but can't insert data(write data into sql 2005)
I checked all permission are correct on sql 2005, program are correct, everything looks good, but only can't write data into sql2005
Can somebody help me????
I want to do a Query in Table ZT_TransFmt9_Headerto join with other Table and then get the value of KeepMonth from ZT_DataBackupbut Table ZT_TransFmt9_Header is far from away ZT_DataBackupit seems have to run a complicated SQL Query to reach goal..
select BusinessCode from ZT_BillerInfo A, ZT_TransFmt9_Header ins where A.JihsunCode=ins.StoreCode
-----------ZT_TransFmt9_Header to join with ZT_BillerInfo for get BusinessCode
when I get BusinessCode , next I need put BusinessCode in where clause to select data from zt_billerinfo ( Table zt_billerinfo have a column named BusinessCode which can mapping with the BusinessCode I have just select from ZT_BillerInfo and ZT_TransFmt9_Header and then join with Table zt_biller get value of CompanyCode, and then use company code in Table Databackup to get the keepmonth
* I know my descrition may cause you feel confused. I past a Table picture herehttp://picasaweb.google.com.tw/jovi.fat/TAbleRelation/photo#5092934117841944082
I need to call a sproc about 1000 times and build a table from all the results.
How do I write an insert statement that will take the recordsets from the sproc and put it into a temp table?
Hello,
Any help here much appreciated.
I am using sql server 2000 to perform address cleansing. there is a point in my scripting when a table i pass values to becomes read/write.
i suspect this is when i run a cursor through the table.
Is anyone able to confirm for me whether running a cursor changes a table's properties?
Many thanks.
Tim
Ps as the table seems to be read/write it is harder to tell if NULLs are in the table and this is messing with joins I have further down the track.
Help,
I have a package that maps the current getdate to a variable.
I then want to be able to write that variable back to a table using a sql task using the following expression:
"INSERT INTO CONTROL_BATCH_HEADER VALUES (
" + (DT_STR, 10, 1252) @[Control::intAdtBatchId] + ",
'" + @[Control::strSubjectArea] + "',
convert(datetime, '" + (DT_STR, 30, 1252) @[Control::dteRunDate] + "',131))"
The problem is the date is being written back in the wrong format. I've tried changing the mask to 121 but the month is being written as the day.
Help, how can I make this work regardless of the locals. In oracle I would just put a to_char(date,'dd-mm-yyyy') but not sure what to do here.
Thanks for your help
Stapsey
I'm writing a classic ASP application that records all logging of userlogins on our support site. The logging is a rolling window of how manypeople have logged in for a given month, i.e. tracked by a 'lastlogin'field so the tracking is done in a date range. So, for the month ofJanuary I would record lastlogin dates between January 1 and January31. My question is this...on the last day of the month (example beingJanauary)at 23:59:59PM I want to write the total number of users thathad a lastlogin date within the month of January and write that totalnumber to another table so I have a point-in-time figure for Januarylogins and how many users logged in in the month of January. How do Ido that? How can I have the sql server dynamically check the last dayof each month at 23:59:59PM and then run that query to obtain the totalnumbers users that logged in and then record that value to anothertable?? Do I use a SQL Job to do that?Any help would be greatly appreciated! Thanks!
View 1 Replies View RelatedI'd like to select the last unshipped order, or if none is open, the last shipped order, for each customer. I noticed that all the examples on line for OUTER APPLY involve table-valued functions, but I have read (in a book I don't have here) that a subquery can work. The following shows what I want, but it isn't valid syntax:
SELECT c.CustomerName, oa.OrderNumber, oa.Status
FROM Customers c
OUTER APPLY (
SELECT TOP 1 CustomerID, OrderNumber,
CASE WHEN ShipDate IS NULL
THEN 'due ' + CAST(DueDate AS VARCHAR)
ELSE 'shipped ' + CAST(ShipDate AS VARCHAR)
END AS Status,
CASE WHEN ShipDate IS NULL
THEN DueDate + 1000 -- Give open orders priority.
ELSE ShipDate
END AS SortOrder
ORDER BY SortOrder
) AS oa ON oa.CustomerID = c.CustomerID
Hi,
Is there a way to write to a log-table inside a transaction which is rolled back without rollback of this log-entry.
thanks in advance
Raimund
Hello there,How can i take data out of my database, put them into a textbox and then separate with a comma..An example:----------------------------------| column Email || mail1@email.com || mail2@email.com || mail3@email.com |----------------------------------Put them into a textbox and separate with a , (comma)-------------------------------------------------------------------------------------| mail1@email.com, mail2@email.com, mail3@email.com |-------------------------------------------------------------------------------------Anybody who know how I can do that? :S
View 4 Replies View RelatedDear expert, please show me some examples of how I can update an Microsoft SQL table data with an excel sheet. Meaning, when I upload an excel sheet using an ASPX page the data in the excel sheet will be transfer to a particular table in the database. Thank you in advance.
View 2 Replies View RelatedI'm trying to write data from a session into database but it won't work.
Could someone help me what is wrong with this.
cobjDT = Session("cart")
For each cobjDR in cobjDT.rows
Dim cmdInsert2 as New SQLCommand("order_details", loConn)
cmdInsert2.CommandType = CommandType.StoredProcedure
cmdInsert2.Parameters.Add(New SqlParameter("@Orders", SqlDbType.int, 4,
"OrderID"))
cmdInsert2.Parameters("@Orders").Value = Request("OrderIDtext")
cmdInsert2.Parameters.Add(New SqlParameter("@PID", SqlDbType.int, 4,
"PID"))
cmdInsert2.Parameters("@PID").Value = Request(cobjDR("PID"))
cmdInsert2.Parameters.Add(New SqlParameter("@PakID", SqlDbType.int, 4,
"PakID"))
cmdInsert2.Parameters("@PakID").Value = Request(cobjDR("PAKID"))
cmdInsert2.Parameters.Add(New SqlParameter("@StyckPris",
SqlDbType.decimal, 9, "StyckPris"))
cmdInsert2.Parameters("@StyckPris").Value = Request(cobjDR("StyckPris"))
cmdInsert2.Parameters.Add(New SqlParameter("@Mangd", SqlDbType.int, 4,
"Mangd"))
cmdInsert2.Parameters("@Mangd").Value = Request(cobjDR("Quantity"))
loConn.Open()
cmdInsert2.ExecuteNonQuery()
loConn.Close()
next
When I execute this an error message comes
Server Error in '/examen' Application.
Input string was not in a correct format.
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.FormatException: Input string was not in a correct format.
Source Error:
Line 78: cmdInsert2.Parameters("@Mangd").Value = Request(cobjDR("Quantity"))Line 79: loConn.Open()Line 80: cmdInsert2.ExecuteNonQuery()Line 81: loConn.Close()Line 82: next
Source File: D:lofa.mine.nuexamenmembersTMP3thkwo1d0w.aspx Line: 80
Stack Trace:
[FormatException: Input string was not in a correct format.] System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream) +723 System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +194 ASP.TMP3thkwo1d0w_aspx.Page_Load(Object Sender, EventArgs E) in D:lofa.mine.nuexamenmembersTMP3thkwo1d0w.aspx:80 System.Web.UI.Control.OnLoad(EventArgs e) +67 System.Web.UI.Control.LoadRecursive() +35 System.Web.UI.Page.ProcessRequestMain() +731
Version Information: Microsoft .NET Framework Version:1.1.4322.573; ASP.NET Version:1.1.4322.573
I use same character types in storedProcedure what they are in the
database and when I call the SP on my page the char types are same as
they are in the SP
I don't see what the problem is. Please Help
Hello All,
Thanks in advance for your help.
I am wondering if there is a correlation between the size of a database and the amount of RAM required in the server. Here's what's going on:
NT4.0 SP 6a, SQL 7.0
2-800 MHz Pentium III Processors
512 MB RAM
Swap Files: two, divided evenly between two partitions, min 512 per partition (dynamic)
3- 9.2 GB Drives, RAID 5
SQL Database size: 2.5 GB
The SQL administrators have a scheduled job set to dump the database every four hours, so they have 6 of these dump files per day. Seagate Backup Exec v 6.? backs up those files every evening. Of course, this is a mission-critical application.
As the NT/Hardware administrator, I was tasked to flash upgrade all the hardware that could be flashed. That happened about three weeks ago. Ever since I flashed it, getting the data dumps has been an inconsistent process. Sometimes it works, sometimes it errors out. The error message is " Lost Delayed - Write Data: The system was attempting to transfer file data from buffers to (path). The write operation failed, and only some to the data may have been written to the file."
On and off during the day, you can't even copy a small file on the server without receiving the same message.
Before I flashed the server, it was blue-screening and needing to be rebooted about once every two weeks. Since I flashed it, the server hasn't been rebooted, because it hasn't blue-screened.
The database dumps are about 2 GB. We were thinking that the way the dump process worked was that a snapshot of the data was put into memory before it was written to disk. We thought since the dump size was larger than Physical Memory + Virtual Memory, that that could be causing the error.
What do you guys think?
1. Is dumping the database first the preferred way to get a backup, or should a backup directly to tape be scheduled instead?
2. Do we have enough RAM for the database?
3. Are there special settings we should set for the RAID controller card to maximize throughput for SQL?
Thanks again. I am definitely not a SQL DBA and any information you can provide would be really helpful.
I'm trying to write a query to validate the data.
Here is the scenario:
1. The table has Three columns 1.ID, 2.Sqno, 3. Adj
2. The values for adj are (0,1,2)
Case1: The Sqno should start at '001000' for adj in (0,2) and increment by 2, i.e the next sqno would be '001002' and '001004' so on.
Case2: The sqno should start at '001001' for adj in (1) and increment by 2 i.e the next sqno would be '001003' and '001005' so on.
Finally when you do order by sqno and group by ID it will be a running sqno.
ID Sqno Adj
123A 001000 0
123A 001001 1
123A 001002 2
123A 001003 1
123A 001004 2
123A 001005 1
123A 001006 0
123A 001007 1
123A 001008 2
123A 001009 1
write a query that can validate this scenario.