adding new queries to Server Management Studio

When ever I hit the CTRL+N to add a new query and then save it, why doesn't it get added to the current project I'm working on? Since it's not added, I have to open Windows Explorer, navigate to the right folder, then drag'n'drop it into the right project in my solution. A lot of work for something it should be doing by default.

What am I missing? Thanks.

This does not appear possible with SQL Server 2005 SP2. Could you file this on Microsoft Connect? http://connect.microsoft.com/SQLServer/Feedback/

Paul A. Mestemaker II

Program Manager

Microsoft SQL Server Manageability

http://blogs.msdn.com/sqlrem/

|||Done, id 275345.

adding new queries to Server Management Studio

When ever I hit the CTRL+N to add a new query and then save it, why doesn't it get added to the current project I'm working on? Since it's not added, I have to open Windows Explorer, navigate to the right folder, then drag'n'drop it into the right project in my solution. A lot of work for something it should be doing by default.

What am I missing? Thanks.

This does not appear possible with SQL Server 2005 SP2. Could you file this on Microsoft Connect? http://connect.microsoft.com/SQLServer/Feedback/

Paul A. Mestemaker II

Program Manager

Microsoft SQL Server Manageability

http://blogs.msdn.com/sqlrem/

|||Done, id 275345.

Adding Leading Zeros to a char value - TSQL Question

Hi all:

I have save stored procedure which does an insert and update. Before the insert or update statements, I have the following SQL to adding leading zeroes to in my input params:

DECLARE @.SchoolCode_Modified char(6)

SET @.SchoolCode_Modified = (SELECT Right(Replicate('0',6) + '123',6))

SELECT @.SchoolCode_Modified AS Col1

The problem is that this works on by itself on SSMS, but when incorporated in the SPROC, it doesnt add the leading zeros. The datatype for both of the colum is char(6) as well. If I run the above query in SQL Server Management Studio, it displays Col1 with 000123 in it, which is what I want, from the sproc, but that doesnt happen. Why? Can someone please help me? The following is how it is implemented in the SPROC:

CREATE PROCEDURE [LAF].[uspSaveLoanApplication]

...more params here

@.SchoolCode char(6),

@.BranchCode char(2),

@.PK int OUTPUT,

@.PreviousLoanApplicationStatus char(3) OUTPUT

AS

SET NOCOUNT OFF

SET @.PreviousLoanApplicationStatus = 0

IF EXISTS(SELECT [LoanApplicationStatusCode] FROM [LAF].[LoanApplication] WHERE [LoanApplicationID] = @.LoanApplicationID)

BEGIN

SET @.PreviousLoanApplicationStatus = (SELECT [LoanApplicationStatusCode] FROM [LAF].[LoanApplication] WHERE [LoanApplicationID] = @.LoanApplicationID)

SELECT @.PreviousLoanApplicationStatus PreviousLoanApplicationStatus

END

-- Add Leading Zeroes to School Code and Branch Code to conform with CLIPS Formatting

DECLARE @.SchoolCode_Modified char(6)

DECLARE @.BranchCode_Modified char(2)

SET @.SchoolCode_Modified = (SELECT Right(Replicate('0',6) + @.SchoolCode,6))

SET @.BranchCode_Modified = (SELECT Right(Replicate('0',2) + @.BranchCode,2))

IF EXISTS(SELECT [LoanApplicationID] FROM [LAF].[LoanApplication] WHERE [LoanApplicationID] = @.LoanApplicationID)

BEGIN

UPDATE [LAF].[LoanApplication] SET

....more here

[SchoolCode] = @.SchoolCode_Modified,

[BranchCode] = @.BranchCode_Modified,

...more here

WHERE

[LoanApplicationID] = @.LoanApplicationID

AND [UpdatedOn] = @.LastUpdated

SELECT @.LoanApplicationID PK

SET @.PK = @.LoanApplicationID

END

ELSE

BEGIN

INSERT INTO [LAF].[LoanApplication] (

.....more here

[SchoolCode],

[BranchCode],

.....more here

) VALUES (

....more here

@.SchoolCode_Modified,

@.BranchCode_Modified,

....more here

)

SET @.PK = SCOPE_IDENTITY()

SELECT @.PK PK

END

With a quick scan, your code appears correct.

How is it that you are finding that the values of @.SchoolCode_Modified and @.BranchCode_Modified do NOT have the leading zeros?

|||

It appears that your problem is that

@.SchoolCode is declared as char(6).

So it is NOT '123', but ' 123', i.e., 3 spaces followed by '123'.

It appears you must declare @.SchoolCode as varchar(6) if you want to get those leading zeroes in there.

Dan

|||

DanR1 wrote:

It appears that your problem is that

@.SchoolCode is declared as char(6).

So it is NOT '123', but ' 123', i.e., 3 spaces followed by '123'.

It appears you must declare @.SchoolCode as varchar(6) if you want to get those leading zeroes in there.

Dan

Good catch...

Actually, I don't think that it would be ' 123', but instead 'should' be '123 '. In either case though, adding preceding characters and then taking the rightmost 6 characters would not effect any change in the value.

|||

Arnie,

Yes, you are right about the trailing blanks, instead of leading blanks as I suggested. (I checked on it after I made my post, and figured it wasn't worth the trouble to make the edit.)

So it seems like "ltrim(rtrim(@.SchoolCode))" is what should be preceded by "000000" before taking the 6 rightmost characters.

Dan

|||

DECLARE @.SchoolCode_Modified char(6)

SET @.SchoolCode_Modified = '123'

SET @.SchoolCode_Modified = REPLICATE('0',6 -LEN(@.SchoolCode_Modified))+ @.SchoolCode_Modified

SELECT @.SchoolCode_Modified AS Col1

Adding Fields To A Table

I am new to SQL Server 2005 and I am trying to add two fields to an existing table. The table has 15 Million records in it and the save is not completing. How do I add the new fields?How are you doing it now?|||Through the Management Studio interface. Modify the table, add the fields, save the table. I get a timeout expired message.|||are you adding them to the end of the table, or inserting between 2 other fields, the latter taking a longer time.

look at Alter Table...|||Did you save the script?

And are you moving the columns into places not that are the last

It will make a temp copy of the table, copy all of the rows, rebuild the new table, then copy the data over, then do an sp_rename, then drop the original

Lot of overhead

just do this

CREATE TABLE myTable99(col1 int IDENTITY(1,1), Col2 datetime DEFAULT(GetDate()), Col3 Char(1))
GO

INSERT INTO myTable99(Col3)
SELECT 'a' UNION ALL SELECT 'b' UNION ALL SELECT 'c'
GO

SELECT * FROM myTable99
GO

ALTER TABLE myTable99 ADD Col4 binary
GO

SELECT * FROM myTable99
GO

DROP TABLE myTable99
GO|||I am adding the fields to a specific place in the table (not at the end). Why does this matter? I tried adding to the end and it saved fine. What do I need to do to add the fields where I would like them to be in the table. Some existing processes rely on the order of the fields.|||see the last line in Bretts signature. this violates relational theory. your application should not act like this.|||The reason we have it this way is to simplify our archival processes. By using the field indexes instead of field names the code does not have to change when the physical structure of the tables changes as long as both tables have the same structure.|||The problem you are facing is that Management studio is going to do the following to accomplish this:

1) Create a new table with the name tmp_yourtablename with all the columns in the order you want.
2) Transfer all of the data from the old table to the tmp_ table. Yes. all 15 million rows will be doubled up.
3) Drop the old table (usually with no error checking)
4) Rename the tmp_table as the original table name.

Contrast that with the alter table command which would append the two new columns on the end of the table, and takes a few seconds to run.

How can the archival process be simpler by using the field index? i would think that any of the columns "pushed out" by the insertion of new fields in the "middle" of the table to cause much larger problems.|||Some existing processes rely on the order of the fields.

Why would that be?

SELECT * perhaps?|||The reason we have it this way is to simplify our archival processes. By using the field indexes instead of field names the code does not have to change when the physical structure of the tables changes as long as both tables have the same structure.

OK, so what does that have to do with not adding them to the end?

In any case, The logging is what's going to kill you

I might bcp the data out
CREATE the new table
bcp the data back in, using a format card, into the new table|||The archival is being done in Visual Basic, using ADO recordsets. When you reference the table's Fields collection using the index, as long as field 1 is ID in one table and 1 is ID in the other table then it doesn't matter the names of the fields, but the order is important.|||The problem with adding them to the end is, the only difference in the table structure is the last field in the archive table. It is the datetime the record was archived.|||The archival is being done in Visual Basic, using ADO recordsets.

Shoot me now|||How many indexes are on this table? You might consider dropping them before you add the new columns, and then recreating them.|||I am adding the fields to a specific place in the table (not at the end). Why does this matter? I tried adding to the end and it saved fine. What do I need to do to add the fields where I would like them to be in the table. Some existing processes rely on the order of the fields.

I believe SQL need to create a temp table of the table, then insert the data back when you insert, as oppose to add fields to the end of the table. ColIDs change when inserting new fields, as opposed to adding.

Adding date to filename in report subscription

My company sends reports on a daily basis to our customers. Now I want to save all the sent reports on disc with the date in the filename. I have set up a subscription which daily saves the files where I want them. However, I haven't found a way to add the date easily. I already have a parameter when creating the report, it is called Date. Does anybody know if I can use a parameter or something else?

Thank's

Hello,

Sorry, I don't believe there is a way to modify the filename from a subscription, but you can specify a filename from a Data-Driven Subscription. Do a data-driven subscription to a file share, and just include an extra column in your subscription query to have something like this:

select 'Report or file name here ' + convert(varchar, getdate(), 101) as FileName, ...

Then, when you are setting the delivery extension settings, use this field as your File name.

Hope this helps.

Jarret

Adding Comments to a SQL2005 Query!

Hi everyone,

Has anyone else had problems trying to get comments to save in a view?

Every time you run or save the view, the comments disapear. Also all of the formatting / laying out of the query are lost also.

Anyone found a way to save them or keep your formatting?

Regards,

Steve

It depends on the program that you use to create the view. If you try to use the query design wizard (that automatically parses your SQL), then this will certainly be the case.

Try instead to create a new Query file (in SQL Management Studio). You'll notice that you get a different editor, and your code won't be modified as its parsed. In this mode, you'll also be able to add comments, and format your text as you wish, unheeded.

Adding a Word document to a sql db

I have a form that has a Word object. I want to save that object to the sql db. The original front and backend was an Access db and this all worked using an ole field. The backend data has been moved to a sql 2000 db. The reading of original Word objects works fine but I can't get any new objects stored. I have seen postings that there is no way to by-pass saving the object to a temp file but really I can't get anything to work. The attached code makes no complaints but when I try to access the new object I either get an empty object or an error that the ole server has a problem.

The code below includes simply saving an exisiting doc but that doc does not come back out of the db. I tried just for grins to store the form object in a temporary Access table and then save that field. Actually I got no complaints but then I got the same results. The use of the external file mimics examples I've seen even on this forum. any suggestions are appreciated.

Rick

'Now save word doc that is contained in the form entity
If Not IsNull(Me.oleSubSectionDetail) Then
Dim rst As ADODB.Recordset
Dim mstream As ADODB.Stream

'Tried to make it happen by essentially moving
'a database field to a db field
Dim oleRst As DAO.Recordset
Set oleRst = CurrentDb.OpenRecordset("tmpOLE", dbOpenDynaset)
oleRst.AddNew
'tempole is an ole defined field
oleRst!tempole = Me.oleSubSectionDetail
oleRst.Update
oleRst.MoveFirst

'Select record I want to update
Set rst = New ADODB.Recordset
rst.Open "Select * FROM [tbl-SOW Detail] WHERE RecID = " & RecID, ADOConnection.SQLDB_Connect, adOpenKeyset, adLockOptimistic

Set mstream = New ADODB.Stream
mstream.Type = adTypeBinary
mstream.Open

mstream.LoadFromFile "c:\\Documents and Settings\rburge\My Documents\Standard Contracts\-Cover Page.doc"

'or

'mstream.Write oleRst!tempole

rst.Fields("SubsectionDetail").Value = mstream.Read
rst.Update

rst.Close
Set rst = Nothing
Set mstream = Nothing
End If

I have more information: actually the above is working. It is that once you use that method to save the doc in the image field you must stream it out and to a file to properly read it.

I was hoping someone might share some light on this:

1) if you stream the document in and then stream it out the file is twice the size it was when input. If you open and save the word doc the size is restored. I have read some articles that suggest there is some overhead for every byte stored in the database image field.

2) Originally the backend was an access db which had many documents already stored as ole data type. It was easy enough using DAO to store and retrieve the document image with simple a=b type statements. I upsized the backend to SQL and those same records will allow me to retrieve the image and do a simple form field = db field, even using ADO. Now when I go through the streaming process to save a document I now have to always retrieve it the same way, via the stream. I'm going to have trouble with distiguishing between legacy records and new or I'm going to have to read and write every document record in the db to make sure everybody is on the same page so to speak.

Can anyone explain the storage differences?

Thanks,

Rick

|||

I came up with a solution:

I did create a local temp access table with an ole field. First I stored the document in the ms access field and then I could copy the ms access field to the sql image field. This helped preserve doc that were already in the sql db and preserved the basic document retrievial code. I think that this was a better solution (for me anyways) than reading and writing files.