Hi.
I have a long TEXT column and it cuts off at a certain point
in the Query Analyzer. I tried copying and pasting to notepad
but it is still half the actual string. How do i get the whole
TEXT string from a given column?
Thanks!
How do I "ltrim" an enire colum?
Thanks,
I'm using the following query to look in a log file and show somestatistics. It counts the total number of views and the total numberof unique users who have viewed a particular item (the "id" and"title" fields are associated with the item).SELECT title, COUNT(id) AS NumberViews, COUNT(DISTINCT UID) ASNumberUniqueUsers, Type, idFROM ActivityLogWHERE dtTime >= 'Nov 1 2004 12:00AM' AND DtTime <= 'Nov 1 200512:00AM'GROUP BY Title, Type, hdnIdORDER BY TopViewed descThis works fine on SQL Server 2000 (our development machine), but onSQL Server 7 (our production machine), the title column has the samevalue for every row. The other columns show the correct values.If I remove the "ORDER BY" clause, then it works fine. If I removethe "COUNT(DISTINCT UID)" column, it works fine. If I totally removethe WHERE clause, it works fine.Any ideas? It seems like a bug since it works fine on sql2k. I'vetried adding OPTION (MAXDOP 1) to the end of the query, but thatdidn't help.We're using SQL Server 7.0 sp1, and my boss doesn't want to riskupgrading to sp4 because it might screw up all of our otherapplications. I looked through all of the sp2 through sp4 bug fixes,and I didn't see anything specifically mentioning this.Thanks.
View 2 Replies View RelatedI convert EBCDIC file to text file at C:orderorder.txt. There are about 1400000 rows. The length of line is 1500.
How to insert entire line into one column of the table? (only one column, named as [LineDetail])
I have a table and one of the column have tab delimited value and I need to separate the tab delimited values and keep them in separate rows.
ID Name State Country
1 Scott, Ricky NSW AUS
2 Martin VIC AUS
3 James, Peter,John WA AUS
ID column is not a primary key. I want the output columns to identify the comma (,) and put them in separate rows as below
ID Name State Country
1 Scott NSW AUS
1 Ricky NSW AUS
2 Martin VIC AUS
3 James WA AUS
3 Peter WA AUS
3 John WA AUS
How to strip the camma(,) in SQL?
Hi, Could you tell me if this is possible? How do I return results using an entire column as part of the search parameter? I need to do this in the sql rather than selecting the contents and iterating through it as it would take too long.
eg.
CREATE TABLE [dbo].[tPopupKeywords](
[id] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[title] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[description] [nvarchar](2000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
INSERT INTO dbo.tPopupKeywords(title, description)
SELECT 'check', 'desc' UNION ALL
SELECT 'for', 'desc' UNION ALL
SELECT 'keywords', 'desc'
select dbo.tpopupkeywords.title
where 'This is an example of a passed in string to check if any keywords are returned.'
LIKE '% ' + dbo.tpopupkeywords.title + ' %' --Does this bit need to do a select??
expected results.....:
check
keywords
I have created a DTS package that pulls data from one column (varchar,600) in a table and exports it to a text file. The max length in this field is only 285 characters long, however, the end of one of the records is being truncated in the text file. Data is tab delimited in the table and is being exported that way.
Any tips would be greatly appreciated.
I have 2 higher level column groupings of month name and year above my actual date groups. It looks a little weird aligning them left but there is no guarantee that centering them will even allow them to show until I've scrolled right to the middle of the cell width that they occupy.
Is there a feature that comes with, or a well known trick for making them center in the area that is being viewed instead of the potentially very wide cell that they occupy?
Hi Gents,
I have a file that contains internet addresses accessed by users.
When I load this file into DTS I only want to insert into a table the first part of the address, ie,
http://www.microsoft.com.au/something/something.asp
I only want to insert the http://www.microsoft.com.au part of the address. Each address will be of varying lengths depending on the site visited, but still only want the first part. Up to the third instance of a /.
Any help???
Tony
I use this code to get Tiff files from db , but output file seems to be corrupted :
Dim pubsConn As SqlConnection = New SqlConnection("Server=(local);uid=sa2;pwd=sony;database=pubs;") Dim logoCMD As SqlCommand = New SqlCommand("SELECT pub_id, logo FROM pub_info", pubsConn)
Dim fs As FileStream ' Writes the BLOB to a file (*.bmp). Dim bw As BinaryWriter ' Streams the binary data to the FileStream object.
Dim bufferSize As Integer = 100 ' The size of the BLOB buffer. Dim outbyte(bufferSize - 1) As Byte ' The BLOB byte() buffer to be filled by GetBytes. Dim retval As Long ' The bytes returned from GetBytes. Dim startIndex As Long = 0 ' The starting position in the BLOB output.
Dim pub_id As String = "" ' The publisher id to use in the file name.
' Open the connection and read data into the DataReader. pubsConn.Open() Dim myReader As SqlDataReader = logoCMD.ExecuteReader(CommandBehavior.SequentialAccess)
Do While myReader.Read() ' Get the publisher id, which must occur before getting the logo. pub_id = myReader.GetString(0) ' Create a file to hold the output. fs = New FileStream("\Server1shared1logo" & "KK" & ".tiff", FileMode.OpenOrCreate, FileAccess.Write) bw = New BinaryWriter(fs)
' Reset the starting byte for a new BLOB. startIndex = 0
' Read bytes into outbyte() and retain the number of bytes returned. retval = myReader.GetBytes(1, startIndex, outbyte, 0, bufferSize)
' Continue reading and writing while there are bytes beyond the size of the buffer. Do While retval = bufferSize bw.Write(outbyte) bw.Flush()
' Reposition the start index to the end of the last buffer and fill the buffer. startIndex += bufferSize retval = myReader.GetBytes(1, startIndex, outbyte, 0, bufferSize) Loop
' Write the remaining buffer. bw.Write(outbyte, 0, retval - 1) bw.Flush() ' Close the output file. bw.Close() fs.Close() Loop
' Close the reader and the connection. myReader.Close() pubsConn.Close()
Me.Div1.InnerHtml = ("<embed height=650 width=100% toolbar='' src= '" & "\Server1shared1logoKK.tiff" & "' type='application/x-alternatiff'>")
Hi,
I would like to read from a Text File using SSIS Integration Package.
The file has a fixed number of columns, let's say 3 columns.
There is no row header and each columns length is fixed. There is no delimiter as well.
Here is the sample of the file contents:
John Doe USA
Mary Monroe UK
Andy Archibald Singapore
Here is the hints to read the file contents
123456789
0123456789
0123456789
==============================
John Doe USA
Mary Monroe UK
Andy Archibald Singapore
If you notice, from the 1st column until the 9th column, it's reserved for the first name.
The 10-th column until the 19th column, it's reserved for the last name. Finally the 20-th column until the 29th column is reserved for the Origin Country.
Since there's no delimiter inside the flat file contents, i have difficulty in parsing this text using SSIS Package.
Please let me know if you need any necessary information.
Thanks for all your help.
Regards,
Hadi Teo.
I have a subselect that should be working but doesn't. Been at it too long today.
DECLARE @Calendar1 AS DateTime
SET @Calendar1 = '{{{ Please choose a start date. }}}'
SELECT
('0' + CONVERT (varchar (10), W.WorkerID)) AS VendorID,
(W.FirstName + ' ' + W.LastName) AS VendorName,
(W.FirstName + ' ' + W.LastName) AS Contact,
[code].....
Where did I go wrong?
Hello,
i have a problem that i don't understand why it occurs: i have a package like this (resumed and ordered by its correspondingdata flow):
1. move data files to a path through by a file system task
2. import data of data files to a sqlserver database (each file through its own data flow). The origin of data is in files that have been moved to the path of step 1
3. execute a procedure of database (sql task) to manipulate data and finally export resume (data flow) to a new file in the path (moved by other file system task)
PROBLEM: if i execute the package task by task (one by one), it runs ok, but then when i build and run package as a whole (debug), it produces an error in the data flow such as it doesnt' find the path to take data of data flow (it doesn't find the source). Really it doesn't find source because the data files aren't in its correct path (like step 1 had failed). In summary, it looks like if the order of the task of package aren't correct when i execute the entire package, because the log doesn't thread any error about step 1 (file system task).
ERROR MESSAGE: an OLE DB error has occurred. Error code: 0x80040E37
Thanks for any help.
Gema
I need to search all of the tables in a DB for a specific number (say, 123456). I am unsure of which tables may have this number in it. Is there a way to search an entire DB for a specific criteria?
This is what I want:
Select *
From [db]
where [column] like '%123456%'
I understand why this dosen't work (the DB doesn't have column names), but is there a way to display table names where this number exists? If I had that, then I could search the tables individually.
What is the best way to create a blank copy of my database? I really want some of the tables to keep the data in them, these are some of the lookup table.
I appreciate your help.
Hi, I'm a noob who sucks at programming, and sucks even more when it comes to database.
I'm operating a small website with an SQL Server database that drives my shopping cart. I want to close this website down, but I don't want to lose all the data in the database in case I want to do something in the future.
How do you backup the entire database into a file so i might be able to import it at later times? Do I have to do this with SQL, T-SQL, or the manager?
Hi All,
Could anybody tell me how to make a copy of an entire database on the same server? The copy wizard won't allow the a copy to be made on the same server.
And I believe I won't be able to detach the database and re-attach it to a newly created database with a different name.
Thanks in advance!
I have built a template database which I'm finally pleased with, however I want to periodically duplicate the design - not data into a new database. How can I duplicate a database? I was hoping to right mouse, copy, then right mouse, paste, and then be prompted for the new name but no such luck.
View 6 Replies View RelatedIs there any way to do a complete database search in SQL server? For instance, if I have a criteria "DBFORUMS", I would like to scan through all user tables in my database to get all records with the word "DBFORUMS" stored, just like want we are doing in "Quick Search" in dbforums site.
Any ideas?
Thanks in advance.
Hi friends
Suppose i have a table of 100 cols and 10000 rows i want to search a particular field called 'Newyork' . I dont no what the col is ?
Can anyone tell me how can i search that
Vicky
Hi, this is my first post on these forums, so please excuse me if this topic has already been covered.
I'm currently working in a power station for student vacation work placement. I need to export data from a database that gets it's data from machinery and inputs out in the plant. The machines that provide this input put it into a database, and I need to find the relevant data to export.
My problem is that, in some cases, the sample data that i'm given may be under different field names, in a completely unrelated table. I was looking for a way to search the entire database (250+ tables) for a certain string, so I can find where it is in the database, and run queries on the table it originates from. For example:
My sample data shows me that I have an object with the ID Y03A3DEA_TH1. I know this ID will occur somewhere else in the database, but i'm just not sure where.
If anyone knows of any way that I can search the entire database for specific data, either using tools in MS SQL 2000, or 3rd party apps, i would greatly appreciate their help.
Thanks a lot,
Jack Smith
Hi! I've some data in a table, and I want to add these records to another table, but I tried yet with select ... Into ... and I lose all the data from this one. I need to ADD the data from table to table. Any suggestion?
Thx
Hi,I need to lock a table so that Inserts are prevented as well as deleted andupdates. At present I'm thinking this might do it:SELECT * FROM myTable WITH(UPLOCK)but then again I'm not sure whether this will cover the insert case.Thanks,Robin
View 5 Replies View RelatedRather than setting by session I would like to configure the DB as readuncommitted.Thanx Advance.
View 5 Replies View Related
Hi,
Is it possible to copy a database from the existing database. I mean to say creating a duplicating database.
Note: I can do it by using "Generate SQL Server Script Wizard" or "Database Publishing Wizard" which saves a some filename.sql and later executing this will create another database by assigning some xyzdatabase name.
My main motive is copying a database in a single line script without using this lengthy sciprt.
Example
FirstDatabase (contains 10 tables 10 stored procedures)
SecondDatabase (also contains 10 tables 10 stored procedures)
If possible, can you write a script that attach a database from the C:MyTemporaryDatabase
Because this i need through programming Visual Basic using SQL Server. I want to create some temporary database.
Waiting for your reply.
Thanks.
Best Regards
Kashif Chotu
Im building a site on my pc using SQLExpress and i have a question about something. The database im working on has data inside the tables and i was wondering how i would move that to a different server though a script with all the data in the table.
View 3 Replies View RelatedHow can I do in order that a table show all its rows in a page?And if the table can only show a number of rows cause arrive to the end of the page show the table in the next page?
Thanks!
I am giving the following statement outside the begin tran block in a stored procedure:
select * from table1 with (holdlock) where column1 = @colValue
I have the following two questions regarding above situation:
Being outside the begin tran block, will the select statement cause a lock to be held or it has to be within the begin tran block for the lock to be held?
Will the select statement hold a lock only on the row or rows matching the where clause, or it may lock a page or even the entire table in this situation?
Thanks
How can I instruct SQL Server to keep entire table in memory? ie the memory pages should not be swapped to HD.
View 2 Replies View RelatedI need to move everything from one 6.5 server to another. I have already installed 6.5 to the new server. (The new server has a new drive configuration) How can I move the Master Database and all other databases including sp's and logins?
Please Help ASAP!!!!
Thanks,
Jason
I'm running the below query for listing all the primary key in a database but it is retiving 0 rows.
DECLARE @cmdstr varchar(100)
CREATE TABLE dbo.TempTable
(TABLE_QUALIFIER varchar(10),
TABLE_OWNER varchar(5),
Table_Name varchar(10),
COLUMN_NAME varchar(15),
KEY_SEQ int,
PK_NAME varchar(50),
)
--Create Stored Procedure String
SELECT @cmdstr = 'sp_msforeachtable ''sp_pkeys "?"'''
INSERT INTO TempTable EXEC(@cmdstr)
SELECT Table_Name,COLUMN_NAME,PK_NAME FROM TempTable ORDER BY Table_Name
DROP TABLE TempTable
Any body can give advice regarding this.
Thanks,
Ravi
I have tried searching all over and have not been able to find what I need, but this is what I have pieced together.
Background:
I need to move an entire instance of SQL Server 2000 SP3 running on Windows Server 2000 to a new physical server running SQL Server 2000 SP4 running on Windows Server 2003. I guess my biggest question is how do I move system databases (master,msdb,tempdb,model), I don't particularly like the idea of using sp_detach/sp_attach on system databases?
This is what I had in mind.
-Install SP4 on the source server
-Backup source to tape
-Restore from tape to destination server (restore system dbs as re_master,re_msdb)
-Shut SQL services down on destination server.
-Replace the system dbs .mdf and .ldf with the re_master & re_msdb .mdf and .ldf
-Restart SQL services
-Restore User databases from tape.
Ultimately, I'm asking can you change the undlying .mdf & .ldf files for system databases?
Also, are there any issues going from Windows Server 2000 to Windows Server 2003?
Thanks in advance.
Has anyone here been having issues where SQL Server Management Server will sometimes ignore selected text and run all scripts on the current tab?
This has been happening most recently after a connection times out or is forced shut. From a disconnected tab, if I add "Use Master" at the bottom of a page full of script, it will often run ALL statements starting from the first line.
I have SP2 installed which rolls management server to 9.00.2047.00 - but this has been an issue for me even before SP1 & SP2.
I can find nothing in the knowledge base regarding this, but perhaps my search string wasn't very thorough.
I'd just like to know if others are experience this issue so I can report to MS.
Thanks.