|
that really didnt answer his question, he was asking about logging, if you issue a commit the log should get truncated so it wont just keep growing. however i've only had problems with that makeing a system low on disk space not virtual memory or ram. if you are running SQL Server 2k it wants 2gb of memory and will take all it can find..the 64bit version wants 4GB..and i imagine the newer versions take even more.
Please remember to rate helpful or unhelpful answers, it lets us and people reading the forums know if our answers are any good.
|
|
|
|
|
no iam not doing any logging.
|
|
|
|
|
you might not be logging, but did you turn it off for the database on the server? it will automatically log inserts and updates, so that if you or something cancels the query that is bieng run it can roll back any changes. you can disable it but i've never done so and was told that it was a pain, and can cause issues. If you have any indecies on those tables you'll have to rebuild them unless your data is in order when its bieng inserted, either way the indecies will slow down inserts and if they've been corrupted will slow down any table scan, select, update etc..
Please remember to rate helpful or unhelpful answers, it lets us and people reading the forums know if our answers are any good.
|
|
|
|
|
Logging, logging, what logging, BBbbrrrrr [fires up the chain saw] wheres the log I'll truncate the bloody log for you....
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
i would have loved to use a chain saw on our damn dev server the other day..my boss was updating 3 columns based on a 4th column in a table with over 400M rows, and the database was 23gb w/ a logfile 36gb and the array it was sitting on had 8MB left on it, we were wondering why the command wouldnt finish...but thats on SQL Server 2k, hopefully the new ones do it differently. cant wait for the new dev server 2TB of storage.
Please remember to rate helpful or unhelpful answers, it lets us and people reading the forums know if our answers are any good.
|
|
|
|
|
Hello All,
I have a table with some Columns. I am inserting the records in this table by bulk insert query. The problem is that in the last column the data is inserted by name with FIELDTERMINATOR $. I just need to wirte a query by which I can remove this $ and just have name in that column.
Any suggestions...
Thanks in advance...
|
|
|
|
|
To this and your other question below.
What is the row delimiter used in the file? If it's a newline, just add
BULK INSERT dbo.tb_DEMO
FROM 'C:\DEMO08Q1.TXT'
WITH
(
FIELDTERMINATOR ='$'
ROWTERMINATOR = '$\n'
)
BTW, does your file have only 1 row?
|
|
|
|
|
hello SimulationofSai,
Thanks for the reply. Its working now. File contains almost 104411 rows.
I did this :
BULK INSERT dbo.tb_DEMO
FROM 'C:\DEMO08Q1.TXT'
WITH
(
FIRSTROW = 2
,FIELDTERMINATOR ='$'
,ROWTERMINATOR = '$\n'
)
FirstRow = 2 solved my first problem and ur suggestion helped me to solve my 2nd problem.
Thanks again... 
|
|
|
|
|
Anyone know or use a tool that let's you point it at a table in a DB and it will then generate a SQL script for you that drops/creates all the indexes on the table? Thanks. 
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
|
|
|
|
|
Chris Meech wrote: Anyone know or use a tool that let's you point it at a table in a DB and it will then generate a SQL script for you that drops/creates all the indexes on the table? Thanks.
SQL Management Studio?
You've to script twice, once for drop and once for create. And when you script a table, it'll also script CREATE TABLE which you've to remove manually.
Yeah, it can't be done in one step. But it's not too complicated either...
|
|
|
|
|
Sorry, I should have mentioned that it's not for SQL Server, but for Oracle specifically, though I was hoping for a tool that would be database 'agnostic'.
I done lots of Google searching and found lots of scripts that will tell me how to determine what indexes are defined, but I'm searching for something that goes a step further and puts a drop/create script together for me. Though I also know that may be kind of tough when it comes to unique/primary keys for a table. Thanks, though.
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
|
|
|
|
|
There are a number of tools out there, TOAD is one, used to be for Oracle only but I think it is agnostic now. A goole search should turn up plenty (paid), I don't know of any free ones.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Mycroft Holmes wrote: TOAD is one, used to be for Oracle only but I think it is agnostic now.
Yes, it also works with MS SQL Server now. A nice tool to have around
"The clue train passed his station without stopping." - John Simmons / outlaw programmer
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
"Not only do you continue to babble nonsense, you can't even correctly remember the nonsense you babbled just minutes ago." - Rob Graham
|
|
|
|
|
Hello All,
I am developing a Windows Service in which I download some data from net. Data which I download is in text file. Text file contains the data and column name of the Table and is seperated by $.
Something like this :
C1$C2$C3$C4$C5$C6$C7$C8$C9$C10$C11$C12$C13$C14$C15$C16$C17$C18$C19$C20$C21$C22$C23$
5476771$6638853$I$$5476771-2$$$20071003$DIR$$$$$$N$$$20071003$OT$$N$N$UNITED STATES$
5476957$6689344$I$$5476957-7$20070927$$20071002$DIR$$$59$YR$F$N$142$LBS$20070930$OT$$N$Y$UNITED STATES$
I worte the following Query for Bulk Insert:
BULK INSERT dbo.tb_DEMO
FROM 'C:\DEMO08Q1.TXT'
WITH
(
FIELDTERMINATOR ='$'
)
I am facing 2 problems in this:
1: I have to delete the column Names from the file and then fire this query.
2: If I delete the column Names then it works but in the last column the data is inserted with $. example: UNITED STATES$
Please help me....
Thanks in Advance.
|
|
|
|
|
How to write Query to get the datatype of Fields or Columns of a table.
and How to write query to get all the table names in a database.
Thanks,
Preethi.
|
|
|
|
|
How to write query to get all the table names in a database.
Ans 1><code>select name as [Tablename] from sysobjects where xtype='u'</code>
How to write Query to get the datatype of Fields or Columns of a table
Ans 2> <code>SELECT COLUMN_NAME,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH,NUMERIC_PRECISION,NUMERIC_SCALE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @tableName ORDER BY ORDINAL_POSITION</code>
Reasons are not Important but Results are Important.
Swati
|
|
|
|
|
Hi,
Getting table names from database is giving results. Thanks For Great support.
But, The next Query is giving problems Please look at this
i have a table name Customers in northwind, So i modified the Query which you given like this
SELECT COLUMN_NAME,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH,NUMERIC_PRECISION,NUMERIC_SCALE FROM INFORMATION_SCHEMA.COLUMNS
WHERE <b>TABLE_NAME = Customers </b>ORDER BY ORDINAL_POSITION
But it is giving Error
Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'Customers'.
Thanks,
Preethi
|
|
|
|
|
hey please look
SELECT COLUMN_NAME,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH,NUMERIC_PRECISION,NUMERIC_SCALE FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Customers' ORDER BY ORDINAL_POSITION
OR
declare @tablename varchar(20)
set @tablename = 'Customers'
SELECT COLUMN_NAME,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH,NUMERIC_PRECISION,NUMERIC_SCALE FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @tablename ORDER BY ORDINAL_POSITION
Reasons are not Important but Results are Important.
Swati
|
|
|
|
|
Hi ,
I applied
declare @tablename varchar(20)
set @tablename = 'Customers'
SELECT COLUMN_NAME,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH,NUMERIC_PRECISION,NUMERIC_SCALE FROM INFORMATION_SCHEMA.COLUMNSWHERE TABLE_NAME = 'Customers' ORDER BY ORDINAL_POSITION
But still iam getting Error in execution
Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near '='.
Thanks Swati for your Kind support.
|
|
|
|
|
Preethi in your query there is error please put space between
INFORMATION_SCHEMA.COLUMNSWHERE
thats why you getting error of '=' sign
declare @tablename varchar(20)
set @tablename = 'Customers'
SELECT COLUMN_NAME,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH,NUMERIC_PRECISION,NUMERIC_SCALE FROM
INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = tablename ORDER BY ORDINAL_POSITION
Reasons are not Important but Results are Important.
Swati Tripathi
|
|
|
|
|
Thanks Swati,
Iam feeling shame to not caught this Synax error.
Once again Thanks for support.
Bye,
Preethi
|
|
|
|
|
I would assume you have SQL 2005, look into the information schema views, in management studio under views|system views
If you are in an earlier version you are going to have to make them up using sysobjects and syscolumns.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
iam using Sql server 2000.
|
|
|
|
|
Hi
I have result set as
table name=ID
ID1 ID2 ID3
1 3 1
2 6 1
1 1 1
I want output as
Name1 Name2 Name3
A C A
B D A
A A A
I have one table for name is Name field of this table are:Id,Name.
I m trying query as
Select Name.Name as Name1 Name ,Name.Name as Name2 from Name,Name.Name as Name3
from Name
inner join ID on Name.ID=ID1
Inner join ID on Name.ID=ID2
inner join ID on Name.ID=ID3
above query is giving error. Plz help in this
thanx
Care Carrer
|
|
|
|
|
Man thats ugly, got to be one on the lousiest data structures around.
Joins are not going to do it. You will need to use sub selects.
select <br />
(Select name from name where ID = ID1) name1,<br />
(Select name from name where ID = ID2) name2, <br />
(Select name from name where ID = ID3) name3<br />
from ID
Never underestimate the power of human stupidity
RAH
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
