hi everybody,
The problem is, I have a table with one column of varchar(8000) datatype. It has got 1000's of records. Through Query Analyzer, I am not able to view my records after 257 columns in my table, even I increased my textsize by using 'set textsize' command.
Any other setting is available to correct this. Any help is appreciated.
thanks,
Vasu
How to change column size dynamically depends on content of the column.
View 2 Replies View Relatedfor MS SQL 2000
table [Users] :
[id_Users] [int] NOT NULL ,
[Name] [varchar] (25) NOT NULL,
[Alias] [varchar] (25) NULL
how can I copy the content of [Name] into the column [Alias] only if [Alias] is Empty ?
thank you
Hi,
i have a table with a nvarchar column,i want to send this column value as unicode content to customer mail box , but when i send it a mail with '?' customer receive , how can i accomplish this?
thanks
I have a MS SQL table with a derived column, for date the records were imported, and need to delete records, based on the content of this column. What I need to do is delete all records from the table with a date of '2011-11-18'. Now this column is a datetime column, so it contains the time info after the date, i.e. 2011-11-18 09:29:38.000, but no matter what command I try for this:
-Delete from table where Date_Imported like '2011-11-18%'
-Delete from table where Date_Imported like '2011-11-18'
-Delete from table where Date_Imported = 2011-11-18
It comes back saying "0 rows affected", even though I know there are records with that date in the table.
I have a particular production database that on a few recent occasions has suddenly had one of it's tables empty of content. Now there are no jobs or triggers in place neither is any stored procedures that could perform a delete or alter table operation in place. The permissions have been set not to allow this either. The datatype is text only. What is intriguing is the QA server has an exact mirror and this dosen't happen. Has anyone come across this sort of phenomenon?
View 2 Replies View RelatedI know oracle uses UTL_FILE procedures lik putline, but microsoft?
Thank you for trying to help.
Hi.. anyone know wat the syntax to output the table content to a textfile? and copy back the content on the textfile back to the table?
Must use SQL command for MS SQL Server 2000 cannot use manual.. tks!
I have a table having XML column. I want to read a XML document and insert content of the XML file into this xml column.
CREATE TABLE [XMLTest](
[DataAsXML] [xml] NULL,
[CreatedDate] [datetime] NULL
)
I have tried the following query
declare @filepath varchar(100)
declare @filename varchar(100)
set @filename = 'Referred'
set @filepath = 'D:ReportOutput'+ @FileName +'.xml'
print @filepath
insert into XMLTest
SELECT xCol,getdate()
FROM (SELECT * FROM OPENROWSET
(BULK @filepath,SINGLE_BLOB) AS xCol) AS R(xCol)
Problem is that When I give complete file path for BULK instead of variable name i.e. BULK 'C:Test.xml', query runs fine. But when I try to use @filepath, I get error "Incorrect syntax near '@filepath'." What am I doing incorrect? Also,If some one can suggest a better approach to load content of XML document to table?
Regards
Hi all,need advice on the following task:copy the content of a big table from DB_A to DB_B in the same serverthe size of table:~ 7 million rows, ~ 9G in size, 1 clustered pk index, 13 nonclusteredindexcurrent practice:use DTS to copy the data, takes over 20 hours as-- first had to delete existing data of the table in DB_B-- then copy-- all these happen while all indexes are in place.I am trying to check what is the best or most efficient way to copythis kind of data and what wouldbe the expected time for such load.my machine: SQL 2000 Enterprise, 8-way P4, 12G RAM on a EMC Clarrion600 SAN.
View 2 Replies View RelatedHi,
In a project with SQL Servcer 2005 the customer is interested to save (archive) content of a table frequently. They want to have possibility to restore the table content as before. I have suggested to export the table every day to a text file and save the file in Visual source safe. If they need there will be possibility to import the text file later. Now I wonder, is there any other way to do archiving of the table content in SQL Server 2005?
Best regards
Is it possible in SQL express and SQL server mangement studio express to export table content to sql script, like form table
column1 column2
1 2 2
2 3 34
3 4 234
we get script
INSERT INTO [database].[dbo].[table] ([column1] ,[column2]) VALUES (2,2)
INSERT INTO [database].[dbo].[table] ([column1] ,[column2]) VALUES (3,34)
INSERT INTO [database].[dbo].[table] ([column1] ,[column2]) VALUES (4,234)
?
If it is not possible in express editions, is it at least available in developer editions?
I keep product name, id in Table1.
I keep Category name, id in Table2.
I keep relation between product and category (product_id, category_id) in Table3.
I have added some products to the table with proper category.
Work fine
But for some products I did not specified any category
(ie their id is not present in Table3)
But now I want all such products
(ie all products whose category is nothing)
To be associated with category_id 10
Can I do this simply with SQL queries?
Hope u can help me
sujith
I was able to view table content easily before, but after Ireinstalled everything, I cannot find the option to view table contentin MS SQL Server Express. I can define table with no problem. Checkout the screen snapshot below, from which you'll see that the popupmenu frrom right-clicking on the table name does not have the "Opentable" option. What is going on?http://farm1.static.flickr.com/167/...839620d0b_o.png
View 6 Replies View RelatedHi Guys,
What approach should I use to copy content of a text file.
Example of the text file's content:
Date, "20060101"
ST_Code, "101"
A_Code, P_Code, T_Code, amount, price
"0001", "1111", "0101", 550, 230
"0002", "1111", "0102", 345, 122
"2001", 0212", 0930", 410, 90
In the example above, I just want to copy the rows Date, "20060101" and ST_Code, "101" into a table.
Regards,
Lars
I have
Customer table, tblCust: ID; Name
1 Peter2 Mary
Product table, tblProduct: ID; Name
1 Banana2 Orange3 Apple
Order tblOrder, tblOrder: CustID; ProductID; Amount
1 ;2 ;$20 – means Peter ordered $20 oranges
How do I write the SQL query so that the values in tblProduct become column, currently I have 20 items in that table. So, it will return something like this according to the information that I provide above?
Name Banana Orange Apple
Peter 0 20 0
I want to export data in a table from one database to another database. And I would like it bo be clone of the source table, if it's possible. Is there any tools out there that can help me with this or do I have to write my own code?Is it possible to query the database to get all columns and datatypes for å requested table? If possible, can you give me an example?If I have to write a tool myself I guess I would do something like this: Export - Query the database for columns and datatype for the requested table. Dynamically generate a query to retrieve all rows from the table and save it to an xml file. Import - Turn off the Identity Increment, loop through the xml file and generate insert queries to insert a clone of each row, turn off the Identity Increment. Viola! If programming only could be that simple :-) Regards, Sigurd
View 5 Replies View RelatedHow to creating a Table Of Content with page number in ( SSRS )SQL Server Reporting Services or when exported to pdf report.
FYI, Page number should be there in TOC.
Third party component is not accepted.
Dynamic Document Map will not work, as it cant get the page number.
I understand that there is no buildin feature which support this, but there should be some work around in SSRS or when export to PDF.
Hi,I use view to join difference table together for some function. However,when the "real" table fields changed (e.g. add/delete/change field). Theview table still use the "old fields".Therefore everytimes when I change the real table, I also needed open theview table and save it by SQL enterprise manager manually for update theview table field.Can we use a SQL command or other method to update it directly?Regards,Silas
View 4 Replies View RelatedCode Snippet
Hi there,
I'm struggeling for more than a week now with this problem, without a finding the solution.
I have two databases, MS Access and SQL Server 2005 Express Edition
Using a procedure in Visual Studio i would like to copy all the records from one table in MS Access into an existing table in SQL Server (the tables have the same name and the same layout)
I tried to prepare one Dataset to copy from Access into SQL Server but when i run the command 'DaSQL.Update(DsSQL, "Tabella") nothing happens (not even an exeption has been raised), looking during debug, the DataSet seems filled though...
Please could anyone explain what's wrong and / or is there a more quicker way to copy data from a table to another?
Note i woul have as a final goal to get data from an AS400 database by ODBC, manage it, and put it on SQL Server for a 'data mining' scope (eliminating the use of MS Access, not suited for FE-BE).
the procedure goes like this;
' Create a connection to the MS Access Database
Dim connectionToAccess As New OleDbConnection(DBConnectionAccString)
strsql = "SELECT * FROM [TABELLA]"
connectionToAccess.Open()
Dim DaAccess As New OleDbDataAdapter(strsql, connectionToAccess)
Dim DsAccess As New DataSet("ACCESS")
DaAccess.FillSchema(DsAccess, SchemaType.Source, "Tabella")
DaAccess.Fill(DsAccess, "Tabella")
' Create a connection to the SQL Database
Dim connectionToSQL As New SqlConnection(DBConnectionSQLString)
connectionToSQL.Open()
Dim DaSQL As New SqlDataAdapter(strsql, connectionToSQL)
Dim DsSQL As New DataSet("SQL")
DaSQL.FillSchema(DsSQL, SchemaType.Source, "Tabella")
DaSQL.Fill(DsAccess, "Tabella")
DaSQL.Update(DsSQL, "Tabella")
Note I tried also the following, withou a result;
DsSQL = DsAccess.Copy
DaSQL.Update(DsSQL, "Tabella")
Please is there someone who could respond !!???
HI everybody need help on this..
I have two tables below
table1
country countryid
africa ___
usa ___
italy ___
Spain ___
table2
countryid country name
1 africa
2 germany
3 italy
4 usa
I need to write the countryid of table 2 to the field countryid in table1 using the criteria of the correspoinding country name table 2 to country of table 1 if it write countryid else 0..
THE RESULT WOULD BE
country countryid
africa 1
usa 4
italy 3
spain 0
thanks
For example,I have a table "authors" with a column "author_name",and it has three value "Anne Ringer,Ann Dull,Johnson White".Here I want to create a new table by using a select sentence,its columns come from the values of the column "author_name".
can you tell me how can I complete this with the SQL?
Hi all, I am not over familiar with SQL, I am a VB programmer, simply I need to achieve the following within Enterprise Manager.
I have 2 tables, different designs, different number of rows, I simply need to check whether the contents of a column in the first table is in a column in the second table, just simply a table/column to table/column data check for the same data content.
Easy Peasy for you guys, any help would be appreciated.
Hi, all experts here,
Thanks for your kind attention.
I want to use time series algorithm to mine data from my case table and nested table. Case table is Date table, while nested table is the fact table. E.g, I want to predict the monthly sales amount for different region (I have region table related to the fact table), how can I achieve this?
Thanks a lot and I hope it is clear for your help and I am looking forward to hearing from you shortly.
With best regards,
Yours sincerely,
Hi, all experts here,
Thank you very much for your kind attention.
I am confused on key column of case table and key time column of nested table by using Time Series algorithm.
In my case, the case table structure is as below:
Territory key text (the ID is actually dimrisk_key, in this case, I use the name column binding to combine the Territory column of case table Dimrisks),
While the nested table structure is as below:
Cal_month key time (in this case, actually the ID is dimdate_key, again, I used name column bining property to bind the Cal_month to the ID)
So my question is, as the key column of case table has been set to be Territory, as a result, does the model training still cover all the cases (rows) based on the ID of the table?
Also, in the nested table, as the key time column has been set to Cal_month rather than Dimdate_key of the nested table, as a result, would the single series based on the cal_month?
Hope it is clear for your advices and help.
And I am looking forward to hearing from you shortly.
With best regards,
Yours sincerely,
Hi guys,
If I have a temporary table called #CTE
With the columns
[Account]
[Name]
[RowID Table Level]
[RowID Data Level]
and I need to change the column type for the columns:
[RowID Table Level]
[RowID Data Level]
to integer, and set the column [RowID Table Level] as Identity (index) starting from 1, incrementing 1 each time.
What will be the right syntax using SQL SERVER 2000?
I am trying to solve the question in the link below:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2093921&SiteID=1
Thanks in advance,
Aldo.
I have tried the code below, but getting syntax error...
ALTER TABLE #CTE
ALTER COLUMN
[RowID Table Level] INT IDENTITY(1,1),
[RowID Data Level] INT;
I have also tried:
ALTER TABLE #CTE
MODIFY
[RowID Table Level] INT IDENTITY(1,1),
[RowID Data Level] INT;
Hi I’m trying to alter a table and delete a column I get the following error. The object 'DF__Morningst__LastU__19EB91BA' is dependent on column 'LastUpdated'.
ALTER TABLE DROP COLUMN LastUpdated failed because one or more objects access this column. I tried deleting the concerned constraint. But the next time I get the same error with a different constraint name. I want to find out if I can dynamically check the constraint name and delete it and then drop the column. Can anyone help.IF EXISTS(SELECT 1FROM sysobjects,syscolumnsWHERE sysobjects.id = syscolumns.idAND sysobjects.name = TablenameAND syscolumns.name = column name)BEGIN EXECUTE ('ALTER TABLE tablename DROP CONSTRAINT DF__SecurityM__DsegL__08C105B8')EXECUTE ('ALTER TABLE tablenameDrop column columnname)ENDGO
I am trying to exclude records from a table where the ID column is the same but the Mail code Column is multi-valued.For Example: (the table looks like....)
ID Mail_code
111111 XNT
111111 N11
111111 XNC
222222 XNC
222222 XNL
333333 XNC
So, if there is any ID that has a value of XNC, I want to exclude the ID all together from my output regardless of the other values.
I got a sales cost and cost amount table for my budget. the sales cost table is getting updated with FOBB items which makes the total incorrect . the FOBB values needs to be moved from the sales cost column to the cost amount column. how can i do it with an SQL script.
View 1 Replies View RelatedI receive this message when I try to run any report. The reportserver and reportservertempdb databases were upgraded using backup/restore from SQL2000 to SQL2005 on a separate server which is running RS2005 . Please help. Thanks
View 1 Replies View RelatedI have the following table
Table Name EmployeeInformation
EmployeeID EmployeeFirstName EmployeeLastName
1 |John |Baker
2 |Carl |Lennon
3 |Marion |Herbert
Table Name PeriodInformation
PeriodID PeriodStart PeriodEnd
1 |1/1/14 |12/30/14
2 |1/1/15 |12/30/15
[code]...
I want a query to join all this tables based on EmployeeID, PeriodID and LeaveTypeID sum of LeaveEntitlement.LeaveEntitlementDaysNumber based on LeaveTypeID AS EntitleAnnaul and AS EntitleSick and sum AssignedLeave.AssignedLeaveDaysNumber based on LeaveTypeID AS AssignedAnnaul and AS AssignedSick and subtract EntitleAnnaul from AssignedAnnual based on LeaveTypeID AS AnnualBalance and subtract EntitleSick from AssignedSick based on LeaveTypeID AS SickBalance
and the table should be shown as below after executing the query
EmployeeID, EmployeeFirstName, EmployeeLastName, PeriodID, PeriodStart, PeriodEnd, EntitleAnnual, AssignedAnnual, AnnualBalance, EntitleSick, AssignedSick, SickBalance
A column of a table has values in the format - 35106;#Grandbouche-Cropp, Amy.
I need to format the column data in such a way that only the text after # (Grandbouche-Cropp, Amy) remain in the column.
The text before ;# (35106) should be inserted in to another column of the same table.
Below is the table structure:
create table [HR_DEV_DM].[CFQ_TEST].sp_CFQ_Commercial_Referrals
(
ID int identity,
PromotionalCode nvarchar(4000),
QuoteNumber nvarchar(100),
CreatedBy nvarchar(100),
Created datetime,
ModifiedBy nvarchar(100),
Modified datetime,
CreatedBy_SalesRepSharePointID int,
ModifiedBy_ModBySharePointID int
)
when creating a new table. How can I set the default value of the column to equal the value of another column in the same table?
View 5 Replies View Related