Hello,

the examples from MSDN like the following, don't run on my computer. A System.Data.SqlServer don't exist. No SQLCommand Type, no SQLContext.GetCommand Method and so on...

Ist there something wrong with my installation or how do i execute commands
inside a CLR SQL Server Trigger?

Regards,
Markus
Imports System
Imports System.Data
Imports System.Data.Sql
Imports System.Data.SqlServer
Imports System.Data.SqlTypes
Imports System.Text.RegularExpressions

Public Class EmailTrigger

Public Shared Sub EmailAudit()
Dim triggContext As SqlTriggerContext = _
SqlContext.GetTriggerContext()
Dim userName As String
If triggContext.TriggerAction = TriggerAction.Insert Then
Dim sqlComm As SqlCommand = SqlContext.GetCommand()
Dim sqlP As SqlPipe = SqlContext.GetPipe()
sqlComm.CommandText = "SELECT Username from INSERTED"
Dim dataRecord As Object = sqlComm.ExecuteScalar
userName = dataRecord.ToString
If IsValidEMailAddress(userName) Then
sqlComm.CommandText = "INSERT Emails " & _
"VALUES ('" & userName & "')"
sqlP.Send(sqlComm.CommandText)
sqlP.Execute(sqlComm)
End If
End If
End Sub

Public Shared Function IsValidEMailAddress(ByVal email As String) _
As Boolean
Return Regex.IsMatch(email, _
"^([\w-]+\.)*?[\w-]+@.[\w-]+\.([\w-]+\.)*?[\w]+$")
End Function

End Class

It seems that something like this would work:

Dim pipe As SqlPipe = SqlContext.Pipe
Dim sqlcommand As New System.Data.SqlClient.SqlCommand

sqlcommand.CommandText = _
"Update Haushaltshilfe set Wochentag = 'Montag' where Wochentag = 'Dienstag'"
pipe.ExecuteAndSend(sqlcommand)

sqlcommand.CommandText = "Select * from inserted"
pipe.ExecuteAndSend(sqlcommand)

But how do i get the results?

Is it really so that the examples from msdn with System.Data.SQLServer don't work?|||The reason why it's not working for you is because the example you're trying is old, and they have now merged the old SqlServer provider for System.Data.SqlServer with the client side provider and created a new namespace: Microsoft.SqlServer.Server. In addition some of the methods on the SqlContext object have been replaced with properties.
Replace the Import System.Data.SqlServer with Import Microsoft.SqlServer.Server and do an extra import for System.Data.SqlClient. Then in the code where you say GetTriggerContext, use the propery TriggerContext instead. you also need to create a connection: Dim sqlConn as SqlConnection = new SqlConnection("Context Connection=true") and the open the connection. From the connection you can create the command: sqlConn.CreateCommand. Instead of GetPipe you use the Pipe property and finally you call ExecuteAndSend on the Pipe with your command as param.
Niels

|||Thank you very much Niels!
Are there web-pages where the latest changes in VS 2005 and SQL Server are documented?|||You can check Bob B's blog and mine. We're trying keep track of what's changing.

.SQL file type not recognized by SSMS

Good morning,

For some reason, I can no longer open .sql files in SQL Server Management Studio. I have checked the Windows file association, which still associates these files with SSMS. I also tried adding a static extension under Tools -> Options -> Text Editor -> File Extension, but still no luck.

Each time I try to open a .sql file, I receive the following error:

There is no editor available for <my file>. Make sure the application for the file type (.sql) is installed.

There have been no major changes on my system recently. Any help would be appreciated.

Thanks
Tim

It happened to me once whatI did is, re-associated those .SQL files to open in NOTEPAD and then again associated to SSMS to open, that worked.

HTH

.sql attachments will not open

Hi,
I have developed a web app where documents may be uploaded onto a server, and then opened of downloaded from the server by the users. Every type of file can be opened and downloaded apart from .sql. The main types of files that are uploaded are .txt, .dox, .pdf, .xls and .psd (photoshop), they all work apart from .sql
just wondering does somthing different have to be done for .sql files?

the uploading is working, it is not that the file doesnt exist on the server

Regards,
Frank

Its depends upon the client folder/file settings. You can't control from the Server.

If you need more information you can post this Q on ASP.NET forum.

*unsigned* 32-bit column type?

I need to stored lots of unsigned 32-bit values. (I'm actually storing IPv4 addresses). These values will frequently exceed 2^31 but never exceed 2^32

My options are:

Use bigint. This is undesirable since this wastes 32-bits of space for every value.
Use int and use the negative value range to get full 32-bits worth of data. In C, it is easy and fast to cast a signed -1 to an unsigned 2^32-1. In SQL, it will be more expensive: Must cast to 64-bit and if val < 0 then val = 2^32 + val.

Is there a better alternative?

Is there a fast (binary) way to cast a value > 2^31 to a negative signed value?

DECLARE @.value BIGINT
SET @.value = 3000000000

SELECT CONVERT(INT, @.value) -- causes error
SELECT CAST(@.value AS INT) -- causes errorIf it's IP addresses, why don't you break them into parts and store each part in TINYINT? For any sub-mask value you would take only 4 bytes.|||The quick and dirty answer is to do a C union to make both the UINT 32 and INT 32 values easily accessible.

-PatP|||If it's IP addresses, why don't you break them into parts and store each part in TINYINT? For any sub-mask value you would take only 4 bytes.

The quick and dirty answer is to do a C union to make both the UINT 32 and INT 32 values easily accessible.

Thanks guys. I'll stick with the signed 32-bit column and some ugly code to convert between unsigned 32-bit values.

Using 4 columns makes for uglier joins and queries. And Pat, I don't quite follow your approach.

CREATE FUNCTION parseIPv4(@.ipString VARCHAR(30))
RETURNS INT AS
BEGIN
DECLARE @.dotIndex1 INT
SET @.dotIndex1 = CHARINDEX('.', @.ipString)
DECLARE @.dotIndex2 INT
SET @.dotIndex2 = CHARINDEX('.', @.ipString, @.dotIndex1 + 1)
DECLARE @.dotIndex3 INT
SET @.dotIndex3 = CHARINDEX('.', @.ipString, @.dotIndex2 + 1)

IF @.dotIndex3 < 1 RETURN 0

DECLARE @.section1 BIGINT
SET @.section1 = CAST(SUBSTRING(@.ipString, 1, @.dotIndex1 - 1) AS BIGINT)
DECLARE @.section2 BIGINT
SET @.section2 = CAST(SUBSTRING(@.ipString, @.dotIndex1 + 1, @.dotIndex2 - @.dotIndex1 - 1) AS BIGINT)
DECLARE @.section3 BIGINT
SET @.section3 = CAST(SUBSTRING(@.ipString, @.dotIndex2 + 1, @.dotIndex3 - @.dotIndex2 - 1) AS BIGINT)
DECLARE @.section4 BIGINT
SET @.section4 = CAST(RIGHT(@.ipString, LEN(@.ipString) - @.dotIndex3) AS BIGINT)

DECLARE @.result BIGINT
SET @.result = (@.section1 * 0x1000000) | (@.section2 * 0x10000) | (@.section3 * 0x100) | (@.section4)

DECLARE @.t INT

-- If the value can fit in a signed 32-bit value
IF @.result < 2147483648 RETURN @.result

-- The value can't fit in a signed 32-bit value. Use negative space.
-- This is what would happen if the value was binary cast from unsigned to signed.
RETURN CAST(@.result - 4294967296 AS INT)
END|||What I meant was:// ptp 200408021738 demo for dbforums.com

#include <stdio.h>

void main()

{
union bar {
unsigned int u;
int s;
} foo;

foo.u = 4294967293;
printf("%d\n", foo.s); // outputs "-3"
}
-PatP|||What I meant was:

Ah, I see what you are saying. In C you can also just do a binary cast:

unsigned int value = 0xFFFFFFFF;
int signedValue = (int) value; // will be -1

In a T-SQL stored proc, I don't see an equivalent. I'm stuck using if statements and subtraction:

DECLARE @.value BIGINT
DECLARE @.signedValue INT

SET @.value = 4294967295

IF @.value < 2147483648 SET @.signedValue = @.value
ELSE SET @.signedValue = @.value - 4294967296

SELECT @.signedValue -- Will be -1

This works but I had a nagging feeling that there was a cleaner solution... Maybe not.|||Uglier joins and queries? Have you tried it?

begin tran
go
create function fn_RogerWilco(
@.section1 bigint ,
@.section2 bigint ,
@.section3 bigint ,
@.section4 bigint ) returns bigint
as begin
declare @.result bigint
set @.result = (@.section1 * 0x1000000) | (@.section2 * 0x10000) | (@.section3 * 0x100) | (@.section4)
return @.result
end
go
create table RogerWilco (
section1 tinyint not null,
section2 tinyint not null,
section3 tinyint not null,
section4 tinyint not null,
IPAddress as dbo.fn_RogerWilco(section1, section2, section3, section4) )
insert RogerWilco (section1, section2, section3, section4) values (192,168,1,1)
select * from RogerWilco
rollback tran|||In C you can also just do a binary cast:

unsigned int value = 0xFFFFFFFF;
int signedValue = (int) value; // will be -1

I think that you are making this orders of magnitude more difficult than it needs to be. Any flavor of four-byte int reaches the SQL Server as a signed int... It is the only way that they come. If you send any four-byte int down the wire, you'll get back the same bit pattern regardless of how you reference it within your client code. If you use either %d or %u to format it, you'll format the strings as signed or unsigned, regardless of how the int values are declared. The only reason that signed/unsigned makes any difference at all is if you want to do math on the server and have it match math done on the client, if you don't care about that, then nothing else matters!

-PatP|||i prefer the rdjabarov solution myself
i worked on a ip storage question where they had stored it as varchar...(yuck, first timers)
you can however write a pretty complex little function to break apart an ip address using substring and case.
either way i would be very interested to see the storage in a single column with a simple way to use the decimal storage inplace and in the context of an ip address not a decimal.

"The suspense is terrible. I hope it will last." Willy Wonka|||I'm confused. You see value in the database engine being able to address the octets of an IP address? So would a BINARY(4) be what you want, or four separate TINYINT columns, or something different?

I guess I'd never given real thought to allowing the database engine to manipulate the IP addresses directly. I know that it can be done, I'm just not sure that I can see the value in doing it.

-PatP|||I'm confused...I'm just not sure that I can see the value in doing it.Me neither. But maybe he knows something we don't? Do IP addresses actually mean something other than the fact that they belong to class A, B, or C and are unique for their sub-nets? Does converting them to signed/unsigned actually add value?|||Answering the question as given, you could use numeric (11, 0). In the database, it would not look like an IP address as such. Is your application going to decode the data?|||free at last, free at last...thank god almighty...

well you get the idea...

ahem...

anyone want to suggest that an ip address is NOT a number....|||Me neither. But maybe he knows something we don't? Do IP addresses actually mean something other than the fact that they belong to class A, B, or C and are unique for their sub-nets? Does converting them to signed/unsigned actually add value?There are ways you can manipulate IP addresses to obtain useful information if you know the network topology, such as knowing for any address A.B.C.D in your network, its router will be A.B.C.1 or similar operations. You might also know that for a device of class X, there will be a device of class Y at the IP address of X + 2 (for example in a cluster configuration).

I was thinking that RogerWilco wanted to ensure that the addresses computed would match between the client and the server. For that usage, since the server must use signed ints, it would help to be able to have signed ints available on the client too, but that is the only reason I can imagine.

Oh well, maybe RogerWilco will weigh in with some details that my feeble brain can't find at the moment!

-PatP|||anyone want to suggest that an ip address is NOT a number....What else is an IP address but a number? Over the years, folks have put some kind of funky spins on how it is used, but if you go back to Metcalfe's original writings you'll see that it really truly is just a number!

-PatP|||What else is an IP address but a number? Over the years, folks have put some kind of funky spins on how it is used, but if you go back to Metcalfe's original writings you'll see that it really truly is just a number!

-PatP

Do you do math on it?|||How else do you figure out which node is which in a cluster without doing math using the IP address? There might be a way to do it using some really arcane bit mapping dreck, but I'll stick with simple math myself.

-PatP|||What's the formula for it, Pat?|||In our configurations (which aren't standard), node N is the cluster IP + N. Granted that it isn't pretty, but it works.

-PatP|||Well, then why making it so hard when all you need to do is to increment a specific part? Or I missed your answer and it means to add to the signed representation of the IP address?|||Nah, you missed a bunch of stuff riddled throughout this thread. Brett was just freaking because I see an IP address as a UINT32 instead of an abstract data type. He couldn't imagine that I might ever do math on an IP address. Not only do I do simple arithmetic, but I do bitmasks and other fancy tricks too!

-PatP|||For what purpose?

How dynamic does this stuff have to be?|||I still don't see the formula...What do you add N to? UINT of the IP or a specific part of IP?|||If you think of the IP address as a 32 bit unsigned integer, that is what you manipulate. In nearly every case, that means that you'll only manipulate the low order octet. Very few clusters have more than four machines, and they almost never cross a subnet boundary (although in larger "server farms" they certainly could).

-PatP|||OK, I got it. So in regards to what I posted way up there, RogerWilco will have to have a translation back from signed to a 4-part number, huh?|||Most of the C routines that use IP addresses store them as an int or an unsigned int. The bit pattern itself is all that really matters, and ADO does the translation for me.

I suspect that we've made a mountain out of a mole hill. Unless RogerWilco has problems, I think we ought to let this thread die gracefully.

-PatP|||Yes, an IP address is just a number. But:

- There is frequently a need to convert the value in the database to/from the dotted notation that humans are accustomed to. When I do a query, I want to see the IP results in dotted notation format or I want to specify an IP in the WHERE clause in dotted notation. Obviously, a client app can do this kind of thing easily but I frequently need to write one-off SQL statements and run directly in Query Analyzer.
- I'd like to avoid strings since they are an inefficient use of space and are subject to various formatting errors.
- Frequently I want to join two tables together by IP, ORDER BY IP, or GROUP by IP. This is definitely possible but bulkier when the IP value is split across multiple columns: WHERE A.IP1 = B.IP1 AND A.IP2 = B.IP2 AND ...

I really never do anything with the numeric value of an IP other than equality tests. It would be nice to use the numeric value for "range" purposes, as in from 192.168.1.0 to 192.168.1.255 but that doesn't work quite right with signed values.

Thanks for all the answers, everyone! I don't think I've ever gotten this much of a response!

Here are the two TSQL functions I'm using to convert between dotted notation and 32-bit numeric:

CREATE FUNCTION parseIPv4(@.ipString VARCHAR(30))
RETURNS INT AS
BEGIN
DECLARE @.dotIndex1 INT
SET @.dotIndex1 = CHARINDEX('.', @.ipString)
DECLARE @.dotIndex2 INT
SET @.dotIndex2 = CHARINDEX('.', @.ipString, @.dotIndex1 + 1)
DECLARE @.dotIndex3 INT
SET @.dotIndex3 = CHARINDEX('.', @.ipString, @.dotIndex2 + 1)

IF @.dotIndex3 < 1 RETURN 0

DECLARE @.section1 BIGINT
SET @.section1 = CAST(SUBSTRING(@.ipString, 1, @.dotIndex1 - 1) AS BIGINT)
DECLARE @.section2 BIGINT
SET @.section2 = CAST(SUBSTRING(@.ipString, @.dotIndex1 + 1, @.dotIndex2 - @.dotIndex1 - 1) AS BIGINT)
DECLARE @.section3 BIGINT
SET @.section3 = CAST(SUBSTRING(@.ipString, @.dotIndex2 + 1, @.dotIndex3 - @.dotIndex2 - 1) AS BIGINT)
DECLARE @.section4 BIGINT
SET @.section4 = CAST(RIGHT(@.ipString, LEN(@.ipString) - @.dotIndex3) AS BIGINT)

DECLARE @.result BIGINT
SET @.result = (@.section1 * 0x1000000) | (@.section2 * 0x10000) | (@.section3 * 0x100) | (@.section4)

DECLARE @.t INT

IF @.result < 2147483648 RETURN @.result

RETURN CAST(@.result - 4294967296 AS INT)
END

CREATE FUNCTION formatIPv4(@.ipValue INT)
RETURNS VARCHAR(30) AS
BEGIN
DECLARE @.unsignedValue BIGINT
SET @.unsignedValue = @.ipValue
IF @.unsignedValue < 0 SET @.unsignedValue = @.unsignedValue + 4294967296

RETURN
CAST((@.unsignedValue / 0x1000000 & 0xFF) AS VARCHAR(4)) + '.'
+ CAST((@.unsignedValue / 0x0010000 & 0xFF) AS VARCHAR(4)) + '.'
+ CAST((@.unsignedValue / 0x0000100 & 0xFF) AS VARCHAR(4)) + '.'
+ CAST((@.unsignedValue / 0x0000001 & 0xFF) AS VARCHAR(4))
END|||I'd probably fall back to using BINARY(4) then, just because it is more efficient to convert and allows range comparisons. You could use something like:DROP FUNCTION dbo.f_b2s
GO
CREATE FUNCTION dbo.f_b2s(@.ipColumn BINARY(4)) RETURNS VARCHAR(15) AS
BEGIN
RETURN Convert(VARCHAR(3), Ascii(@.ipColumn)) + '.'
+ Convert(VARCHAR(3), Ascii(SubString(@.ipColumn, 2, 1))) + '.'
+ Convert(VARCHAR(3), Ascii(SubString(@.ipColumn, 3, 1))) + '.'
+ Convert(VARCHAR(3), Ascii(SubString(@.ipColumn, 4, 1)))
END
GO
DROP FUNCTION dbo.f_s2b
GO
CREATE FUNCTION dbo.f_s2b(@.pcDotted VARCHAR(20)) RETURNS BINARY(4) AS
BEGIN
DECLARE @.bWork VARBINARY(4)
, @.i1 INT
, @.i2 INT
, @.i3 INT

SET @.i1 = CharIndex('.', @.pcDotted, 1)
SET @.i2 = CharIndex('.', @.pcDotted, 1 + @.i1)
SET @.i3 = CharIndex('.', @.pcDotted, 1 + @.i2)

RETURN
CASE
WHEN @.pcDotted LIKE '%[^.0-9]%' THEN NULL
WHEN @.i3 < @.i2 OR @.i2 < @.i1 OR 0 = @.i1 THEN NULL
ELSE
CAST(Char(Convert(int, SubString(@.pcDotted, 1
, @.i1 - 1))) AS BINARY(1))
+ CAST(Char(Convert(int, SubString(@.pcDotted, @.i1 + 1
, @.i2 - @.i1 - 1))) AS BINARY(1))
+ CAST(Char(Convert(int, SubString(@.pcDotted, @.i2 + 1
, @.i3 - @.i2 - 1))) AS BINARY(1))
+ CAST(Char(Convert(int, SubString(@.pcDotted, @.i3 + 1
, 20))) AS BINARY(1))

END
END
GO-PatP|||free at last, free at last...thank god almighty...

well you get the idea...

ahem...

anyone want to suggest that an ip address is NOT a number....

OK i will byte.
I will suggest that an ip address is not a number..

it is a collection of 4 8bit sections (octets) that make up a network address. so each octet is a "number". because the ip address is not valuable without the subnet mask. 1921681100 as a number means something specific.
192.168.1.100 could be the 192 network or the 192.168 network or the 192.168.1 network etc ...
another problem is that with using numeric (11,0) each octet can contain a decimal equivalent of 1 through 255 so the periods (.) are necessary to decoding the address.

NOW technically if you broke each octet up into it's binary equivalent then each section would be an exact 8 character number consisting of ones and zeros.

rdjabarov,, becuase of cidr and vlsm the old classfull based system of ip addresses is a bit out of date. Classless addressing(Supernetting and subnetting) kind of make the classfull system a bit old fashioned and not the most effective.

My solution in this problem (considering all of the hassles) has always been to have 4 tinyint cols and display accordingly. not the most elegant solution, but it has always been the simplest and i am always one for bringing the app logic to the db

there , i think that about covers it.|||Ah brings tears to a data modelers heart

very nice...

And le the denormalization begin

And, which one are you?

http://www.techwise.com/techwise/index.cfm?ID=F66B888A-1D5D-4FAB-BE0BB5361EDC93BA|||Ah brings tears to a data modelers heart

very nice...

And le the denormalization begin

And, which one are you?

http://www.techwise.com/techwise/index.cfm?ID=F66B888A-1D5D-4FAB-BE0BB5361EDC93BA

front row third from the right.

i'm not in that picture.

as far as normalization goes, they are not rules but guidelines.
i am very dedicated to making my dbs as "normal" as possible but sometimes denormalizing can solve some certain problems but at the same time cause more**.

i realize that breaking up the ip address into 4 individual columns violates the entity rules but dammit it's so much easier than all the other work that has to be performed to treat the ip correctly.

**little known fact is that i invented the 2nd normal form. it was 1965 and i was bored with 1nf (i mean how many tables can you build??) so i tried to spice it up. i thought "Hey lets make more tables, it'l be cool" but shortly after i came out with it [i had ordered all the t shirts and the beer coozies and those arent cheap] codd and date come out with 3nf and steal all of my thunder. i had to eat it on the promotional 2nf pocket protectors and pens i even had my catchy saying and everything

"repeating groups, repeating groups, repeating groups, repeating groups"
catchy huh
"i'll admit it's not as good as the key the whole key and blah blah blah" but dammit those bastards ruined me... :D|||Hey, I'll buy some of your pocket protectors! They'll make great Christmas presents for all of my dba friends and co-workers!

-PatP

**longest text in SQL**

Hi
I'm working with SQL 2000 and I want to know what data type is suitable
for long texts and what's the limit for its length?
Any help would be thankful.You can use TEXT or NTEXT.
TEXT has a limit of 2GB, but does not support Unicode data. NTEXT has a
limit of 1 GB and can support Unicode data.
--
HTH,
SriSamp
Email: srisamp@.gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp
<R> wrote in message news:ops8av5uypmw7tkz@.system109.parskhazar.net...
> Hi
> I'm working with SQL 2000 and I want to know what data type is suitable
> for long texts and what's the limit for its length?
> Any help would be thankful.|||varchar(8000) can hold 8000 bytes and can be used in string functions
text can hold 2GB but can only be a variable by being a parameter on a
stored procedure/function and can only be used in certain string functions
and cannot be assigned.
nvarchar(4000) holds 4000 bytes and is unicode.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
<R> wrote in message news:ops8av5uypmw7tkz@.system109.parskhazar.net...
> Hi
> I'm working with SQL 2000 and I want to know what data type is suitable
> for long texts and what's the limit for its length?
> Any help would be thankful.|||text datatype takes 2GB of data. If 8k is enough go with the varchar or char
datatypes.
MC
<R> wrote in message news:ops8av5uypmw7tkz@.system109.parskhazar.net...
> Hi
> I'm working with SQL 2000 and I want to know what data type is suitable
> for long texts and what's the limit for its length?
> Any help would be thankful.

**** Error while exporting to excel rrRendering error *****

Exception of type
Microsoft.ReportingServices.ReportRendering.ReportRenderingException was
thrown. (rrRenderingError) Get Online Help
Exception of type
Microsoft.ReportingServices.ReportRendering.ReportRenderingException was
thrown.
Object reference not set to an instance of an object.
Hi:
I have been getting this error when i try to export to excel, exporting to
other formats works fine. My report has a matrix and a table. when i remove
the matrix the report exports fine to excel. By bringing back the matrix
exporting to excel error out. Am I missing somethng here.
Thanksoke so tht was a known bug in matrix. I removed the subtotals, and it exports
fine. Wierd thing is even though I removed the subtotals, and it doesnt show
when I see the report, but when I export to excel i am able to see the
subtotals as well. Weird !!!
"NI" wrote:
> Exception of type
> Microsoft.ReportingServices.ReportRendering.ReportRenderingException was
> thrown. (rrRenderingError) Get Online Help
> Exception of type
> Microsoft.ReportingServices.ReportRendering.ReportRenderingException was
> thrown.
> Object reference not set to an instance of an object.
> Hi:
> I have been getting this error when i try to export to excel, exporting to
> other formats works fine. My report has a matrix and a table. when i remove
> the matrix the report exports fine to excel. By bringing back the matrix
> exporting to excel error out. Am I missing somethng here.
> Thanks
>|||Does anyone know if there is a workaround for this. I have 2 row groups, and
when i include the subtotal for group 2 (inner group) it exports fine. The
problem occurs when I include the subtotal for the first row group
(outermost). Any workaorund to get the totals, and also export fine.
Thanks
"NI" wrote:
> oke so tht was a known bug in matrix. I removed the subtotals, and it exports
> fine. Wierd thing is even though I removed the subtotals, and it doesnt show
> when I see the report, but when I export to excel i am able to see the
> subtotals as well. Weird !!!
>
> "NI" wrote:
> > Exception of type
> > Microsoft.ReportingServices.ReportRendering.ReportRenderingException was
> > thrown. (rrRenderingError) Get Online Help
> > Exception of type
> > Microsoft.ReportingServices.ReportRendering.ReportRenderingException was
> > thrown.
> > Object reference not set to an instance of an object.
> >
> > Hi:
> >
> > I have been getting this error when i try to export to excel, exporting to
> > other formats works fine. My report has a matrix and a table. when i remove
> > the matrix the report exports fine to excel. By bringing back the matrix
> > exporting to excel error out. Am I missing somethng here.
> >
> > Thanks
> >|||Im experiencing the same error on an Excel 2002 SP2. I only have this
problem with subtotal for column groups. If i remove the subtotal i do not
get the error.
"NI" <NI@.discussions.microsoft.com> wrote in message
news:546BDFB7-F225-4D86-B79F-14EC42776508@.microsoft.com...
> Does anyone know if there is a workaround for this. I have 2 row groups,
and
> when i include the subtotal for group 2 (inner group) it exports fine. The
> problem occurs when I include the subtotal for the first row group
> (outermost). Any workaorund to get the totals, and also export fine.
> Thanks
> "NI" wrote:
> > oke so tht was a known bug in matrix. I removed the subtotals, and it
exports
> > fine. Wierd thing is even though I removed the subtotals, and it doesnt
show
> > when I see the report, but when I export to excel i am able to see the
> > subtotals as well. Weird !!!
> >
> >
> >
> > "NI" wrote:
> >
> > > Exception of type
> > > Microsoft.ReportingServices.ReportRendering.ReportRenderingException
was
> > > thrown. (rrRenderingError) Get Online Help
> > > Exception of type
> > > Microsoft.ReportingServices.ReportRendering.ReportRenderingException
was
> > > thrown.
> > > Object reference not set to an instance of an object.
> > >
> > > Hi:
> > >
> > > I have been getting this error when i try to export to excel,
exporting to
> > > other formats works fine. My report has a matrix and a table. when i
remove
> > > the matrix the report exports fine to excel. By bringing back the
matrix
> > > exporting to excel error out. Am I missing somethng here.
> > >
> > > Thanks
> > >

(Urgent)How can I specify the excel File''s column to import data as varchar instead of Float

Hi,

I have a excel file which i want to import the data to sql server... The sql server Data type for that particular column is

varchar and it has a contraint too like the data should be in this fashion 00000-0000 or 00000...

but when i try to import the data from the excel to sql server... 08545 just becomes 8545 (cause excel is treating it as a float) and so my insert fails...

what can i do to rectify the problem...

regards

Karen

Hi Karenros,

You need to put a data conversion task and make the datatype as varchar instead of float.

You need to place this task inbetween the excel task and destination sql server.

Thanks,

SVGP.

|||

SVGP,

thanks for your answer... can u please elaborate as to how can i create the data conversion task...

Regards

Karen

|||

In the dataflow tab ,on the left side you will find dataflow source,dataflow transformation and dataflow destinations.

Drag and drop the Data Conversion task from the dataflow transformation, inbetween your excel and oledb Destination.

edit the data conversion task,you need to select the column you want to convert and it will appear down automatically.

then you need to change the datatype(the drop down will lead you for that).

Remember to map the copy of the column you selected in the oledb destination.

Thanks,

SVGP

|||is the dataflow tab in Excel?|||

No Karen,iam mentioning about the Data flow in Business Intelligence development studio(BIDS)

You have Three tabs,control,dataflow and event handlers,from that u select dataflow and do as i said before.

Good Luck.

SVGP

|||but i am importing the data from an excel file to the database directly|||

What tool you are using to migrate the data?

|||going to sql server and importing the data directly

(Urgent)How can I specify the excel File''s column to import data as varchar instead of Float

Hi,

I have a excel file which i want to import the data to sql server... The sql server Data type for that particular column is

varchar and it has a contraint too like the data should be in this fashion 00000-0000 or 00000...

but when i try to import the data from the excel to sql server... 08545 just becomes 8545 (cause excel is treating it as a float) and so my insert fails...

what can i do to rectify the problem...

regards

Karen

Hi Karenros,

You need to put a data conversion task and make the datatype as varchar instead of float.

You need to place this task inbetween the excel task and destination sql server.

Thanks,

SVGP.

|||

SVGP,

thanks for your answer... can u please elaborate as to how can i create the data conversion task...

Regards

Karen

|||

In the dataflow tab ,on the left side you will find dataflow source,dataflow transformation and dataflow destinations.

Drag and drop the Data Conversion task from the dataflow transformation, inbetween your excel and oledb Destination.

edit the data conversion task,you need to select the column you want to convert and it will appear down automatically.

then you need to change the datatype(the drop down will lead you for that).

Remember to map the copy of the column you selected in the oledb destination.

Thanks,

SVGP

|||is the dataflow tab in Excel?|||

No Karen,iam mentioning about the Data flow in Business Intelligence development studio(BIDS)

You have Three tabs,control,dataflow and event handlers,from that u select dataflow and do as i said before.

Good Luck.

SVGP

|||but i am importing the data from an excel file to the database directly|||

What tool you are using to migrate the data?

|||going to sql server and importing the data directly

(Urgent)How can I specify the excel File''s column to import data as varchar instead of Float

Hi,

I have a excel file which i want to import the data to sql server... The sql server Data type for that particular column is

varchar and it has a contraint too like the data should be in this fashion 00000-0000 or 00000...

but when i try to import the data from the excel to sql server... 08545 just becomes 8545 (cause excel is treating it as a float) and so my insert fails...

what can i do to rectify the problem...

regards

Karen

Hi Karenros,

You need to put a data conversion task and make the datatype as varchar instead of float.

You need to place this task inbetween the excel task and destination sql server.

Thanks,

SVGP.

|||

SVGP,

thanks for your answer... can u please elaborate as to how can i create the data conversion task...

Regards

Karen

|||

In the dataflow tab ,on the left side you will find dataflow source,dataflow transformation and dataflow destinations.

Drag and drop the Data Conversion task from the dataflow transformation, inbetween your excel and oledb Destination.

edit the data conversion task,you need to select the column you want to convert and it will appear down automatically.

then you need to change the datatype(the drop down will lead you for that).

Remember to map the copy of the column you selected in the oledb destination.

Thanks,

SVGP

|||is the dataflow tab in Excel?|||

No Karen,iam mentioning about the Data flow in Business Intelligence development studio(BIDS)

You have Three tabs,control,dataflow and event handlers,from that u select dataflow and do as i said before.

Good Luck.

SVGP

|||but i am importing the data from an excel file to the database directly|||

What tool you are using to migrate the data?

|||going to sql server and importing the data directly

(Urgent)How can I specify the excel File''s column to import data as varchar instead of Float

Hi,

I have a excel file which i want to import the data to sql server... The sql server Data type for that particular column is

varchar and it has a contraint too like the data should be in this fashion 00000-0000 or 00000...

but when i try to import the data from the excel to sql server... 08545 just becomes 8545 (cause excel is treating it as a float) and so my insert fails...

what can i do to rectify the problem...

regards

Karen

Hi Karenros,

You need to put a data conversion task and make the datatype as varchar instead of float.

You need to place this task inbetween the excel task and destination sql server.

Thanks,

SVGP.

|||

SVGP,

thanks for your answer... can u please elaborate as to how can i create the data conversion task...

Regards

Karen

|||

In the dataflow tab ,on the left side you will find dataflow source,dataflow transformation and dataflow destinations.

Drag and drop the Data Conversion task from the dataflow transformation, inbetween your excel and oledb Destination.

edit the data conversion task,you need to select the column you want to convert and it will appear down automatically.

then you need to change the datatype(the drop down will lead you for that).

Remember to map the copy of the column you selected in the oledb destination.

Thanks,

SVGP

|||is the dataflow tab in Excel?|||

No Karen,iam mentioning about the Data flow in Business Intelligence development studio(BIDS)

You have Three tabs,control,dataflow and event handlers,from that u select dataflow and do as i said before.

Good Luck.

SVGP

|||but i am importing the data from an excel file to the database directly|||

What tool you are using to migrate the data?

|||going to sql server and importing the data directly

(Urgent)How can I specify the excel File''s column to import data as varchar instead of Float

Hi,

I have a excel file which i want to import the data to sql server... The sql server Data type for that particular column is

varchar and it has a contraint too like the data should be in this fashion 00000-0000 or 00000...

but when i try to import the data from the excel to sql server... 08545 just becomes 8545 (cause excel is treating it as a float) and so my insert fails...

what can i do to rectify the problem...

regards

Karen

Hi Karenros,

You need to put a data conversion task and make the datatype as varchar instead of float.

You need to place this task inbetween the excel task and destination sql server.

Thanks,

SVGP.

|||

SVGP,

thanks for your answer... can u please elaborate as to how can i create the data conversion task...

Regards

Karen

|||

In the dataflow tab ,on the left side you will find dataflow source,dataflow transformation and dataflow destinations.

Drag and drop the Data Conversion task from the dataflow transformation, inbetween your excel and oledb Destination.

edit the data conversion task,you need to select the column you want to convert and it will appear down automatically.

then you need to change the datatype(the drop down will lead you for that).

Remember to map the copy of the column you selected in the oledb destination.

Thanks,

SVGP

|||is the dataflow tab in Excel?|||

No Karen,iam mentioning about the Data flow in Business Intelligence development studio(BIDS)

You have Three tabs,control,dataflow and event handlers,from that u select dataflow and do as i said before.

Good Luck.

SVGP

|||but i am importing the data from an excel file to the database directly|||

What tool you are using to migrate the data?

|||going to sql server and importing the data directly

(Urgent)How can I specify the excel File''s column to import data as varchar instead of Float

Hi,

I have a excel file which i want to import the data to sql server... The sql server Data type for that particular column is

varchar and it has a contraint too like the data should be in this fashion 00000-0000 or 00000...

but when i try to import the data from the excel to sql server... 08545 just becomes 8545 (cause excel is treating it as a float) and so my insert fails...

what can i do to rectify the problem...

regards

Karen

Hi Karenros,

You need to put a data conversion task and make the datatype as varchar instead of float.

You need to place this task inbetween the excel task and destination sql server.

Thanks,

SVGP.

|||

SVGP,

thanks for your answer... can u please elaborate as to how can i create the data conversion task...

Regards

Karen

|||

In the dataflow tab ,on the left side you will find dataflow source,dataflow transformation and dataflow destinations.

Drag and drop the Data Conversion task from the dataflow transformation, inbetween your excel and oledb Destination.

edit the data conversion task,you need to select the column you want to convert and it will appear down automatically.

then you need to change the datatype(the drop down will lead you for that).

Remember to map the copy of the column you selected in the oledb destination.

Thanks,

SVGP

|||is the dataflow tab in Excel?|||

No Karen,iam mentioning about the Data flow in Business Intelligence development studio(BIDS)

You have Three tabs,control,dataflow and event handlers,from that u select dataflow and do as i said before.

Good Luck.

SVGP

|||but i am importing the data from an excel file to the database directly|||

What tool you are using to migrate the data?

|||going to sql server and importing the data directly

(Urgent)How can I specify the excel File''s column to import data as varchar instead of Float

Hi,

I have a excel file which i want to import the data to sql server... The sql server Data type for that particular column is

varchar and it has a contraint too like the data should be in this fashion 00000-0000 or 00000...

but when i try to import the data from the excel to sql server... 08545 just becomes 8545 (cause excel is treating it as a float) and so my insert fails...

what can i do to rectify the problem...

regards

Karen

Hi Karenros,

You need to put a data conversion task and make the datatype as varchar instead of float.

You need to place this task inbetween the excel task and destination sql server.

Thanks,

SVGP.

|||

SVGP,

thanks for your answer... can u please elaborate as to how can i create the data conversion task...

Regards

Karen

|||

In the dataflow tab ,on the left side you will find dataflow source,dataflow transformation and dataflow destinations.

Drag and drop the Data Conversion task from the dataflow transformation, inbetween your excel and oledb Destination.

edit the data conversion task,you need to select the column you want to convert and it will appear down automatically.

then you need to change the datatype(the drop down will lead you for that).

Remember to map the copy of the column you selected in the oledb destination.

Thanks,

SVGP

|||is the dataflow tab in Excel?|||

No Karen,iam mentioning about the Data flow in Business Intelligence development studio(BIDS)

You have Three tabs,control,dataflow and event handlers,from that u select dataflow and do as i said before.

Good Luck.

SVGP

|||but i am importing the data from an excel file to the database directly|||

What tool you are using to migrate the data?

|||going to sql server and importing the data directly

(Urgent)How can I specify the excel File''s column to import data as varchar instead of Float

Hi,

I have a excel file which i want to import the data to sql server... The sql server Data type for that particular column is

varchar and it has a contraint too like the data should be in this fashion 00000-0000 or 00000...

but when i try to import the data from the excel to sql server... 08545 just becomes 8545 (cause excel is treating it as a float) and so my insert fails...

what can i do to rectify the problem...

regards

Karen

Hi Karenros,

You need to put a data conversion task and make the datatype as varchar instead of float.

You need to place this task inbetween the excel task and destination sql server.

Thanks,

SVGP.

|||

SVGP,

thanks for your answer... can u please elaborate as to how can i create the data conversion task...

Regards

Karen

|||

In the dataflow tab ,on the left side you will find dataflow source,dataflow transformation and dataflow destinations.

Drag and drop the Data Conversion task from the dataflow transformation, inbetween your excel and oledb Destination.

edit the data conversion task,you need to select the column you want to convert and it will appear down automatically.

then you need to change the datatype(the drop down will lead you for that).

Remember to map the copy of the column you selected in the oledb destination.

Thanks,

SVGP

|||is the dataflow tab in Excel?|||

No Karen,iam mentioning about the Data flow in Business Intelligence development studio(BIDS)

You have Three tabs,control,dataflow and event handlers,from that u select dataflow and do as i said before.

Good Luck.

SVGP

|||but i am importing the data from an excel file to the database directly|||

What tool you are using to migrate the data?

|||going to sql server and importing the data directly

(Urgent)How can I specify the excel File''s column to import data as varchar instead of Float

Hi,

I have a excel file which i want to import the data to sql server... The sql server Data type for that particular column is

varchar and it has a contraint too like the data should be in this fashion 00000-0000 or 00000...

but when i try to import the data from the excel to sql server... 08545 just becomes 8545 (cause excel is treating it as a float) and so my insert fails...

what can i do to rectify the problem...

regards

Karen

Hi Karenros,

You need to put a data conversion task and make the datatype as varchar instead of float.

You need to place this task inbetween the excel task and destination sql server.

Thanks,

SVGP.

|||

SVGP,

thanks for your answer... can u please elaborate as to how can i create the data conversion task...

Regards

Karen

|||

In the dataflow tab ,on the left side you will find dataflow source,dataflow transformation and dataflow destinations.

Drag and drop the Data Conversion task from the dataflow transformation, inbetween your excel and oledb Destination.

edit the data conversion task,you need to select the column you want to convert and it will appear down automatically.

then you need to change the datatype(the drop down will lead you for that).

Remember to map the copy of the column you selected in the oledb destination.

Thanks,

SVGP

|||is the dataflow tab in Excel?|||

No Karen,iam mentioning about the Data flow in Business Intelligence development studio(BIDS)

You have Three tabs,control,dataflow and event handlers,from that u select dataflow and do as i said before.

Good Luck.

SVGP

|||but i am importing the data from an excel file to the database directly|||

What tool you are using to migrate the data?

|||going to sql server and importing the data directly