Hi,
I am going to be writing an external stored procedure (my first) for SQL server 2000.
Has anyone out there written a DLL in Java (J++ or .NET) and then accessed the functions within the DLL as an external stored procedure in T/SQL?
I ask the question becaues I'm likely to get it done considerably faster if I write it in Java then VB ;-)
Any advice / suggestions most welcome.
Cheers,
EwanIt shouldn't really make a difference what language you write your dll in. As long as its compiled as a dll, you should be able to call it from a Stored Proc.
Just ensure that the dll is registered on the server that as executing the StoredProc (not the client).
As far as I know , there is no way to return values from the dll into the Stored Proc. Please let me know if there is.
I take no credit for the information below. I copied it from an old posting and saved it, and I cannot remember who posted it originally.
Good luck.
Lionel.
You can do it with the SP_OA* extened stored procedures, located in the
MASTER database (of ms-sql 7.0/2000). Look at this example (for sending
email
through jmail);
CREATE PROCEDURE sp_Send_JMail
@.fromName as char(50),
@.fromEmail as char(50),
@.toName as char(50),
@.toEmail as char(50),
@.subject as char(100),
@.Body as char(500)
AS
DECLARE @.ObjTok int
DECLARE @.RetVal int
EXEC @.RetVal=sp_OACreate'JMail.SMTPMail',@.ObjTok OUT
EXEC sp_OASetProperty @.ObjTok, 'ServerAddress','yourmailserver.com'
EXEC sp_OASetProperty @.ObjTok, 'SenderName', @.fromName
EXEC sp_OASetProperty @.ObjTok, 'Sender', @.fromEmail
EXEC sp_OASetProperty @.ObjTok,'Subject', @.Subject
EXEC @.RetVal = sp_OAMethod @.ObjTok, 'AddRecipient', Null,@.toEmail
EXEC sp_OASetProperty @.ObjTok, 'Body', @.Body
EXEC @.RetVal = sp_OAMethod @.ObjTok, 'Execute'
EXEC sp_OADestroy @.ObjTok GO
Showing posts with label net. Show all posts
Showing posts with label net. Show all posts
Monday, March 26, 2012
External DataSet on GoDaddy
Hello,
First, I tried to find the answet to this question, but no luck - so I decided to post it.
When I was creating my applications in asp.net - for the first time I decided to start using external DataSets. I think they are great and work very nice!
But once I pushed the application to GoDaddy, I got an error of this nature:
I was trying to access the exterbal dataSet like this:
Dim productsAdapter As NewNorthwindTableAdapters.ProductsTableAdapter()Dim products as Northwind.ProductsDataTable
...
And got an error saying that it couldn't find this type (but it worked fine on my local machine in visual studio).
So did I miss something that prevented this application working on GoDaddy? or there are some limitations on GoDaddy? ...or something else.
Thank you for looking into this for me.
Valera
I think Godaddy uses SQL 2000, so did you setup your connection strings in your web.config to connect to your Database on the GoDaddy server?
I'm pretty sure it uses SQL Server 2005. And the connection string was setup correctly, becuase I can get access to the data without using external dataSet methods.
If anyone with the access to Godaddy could create a sample page that uses external dataset data access method and prove me worng or right - that would be helpful.
Thank you
|||You are correct about 2005. When I first signed up in 2005, they were using SQL 2000.
Maybe I am a noob, but you say external dataset, how is that different from a regular dataset?
|||By external DataSet I mean that in your project you can add a new item - DataSet (just like a web form) that will be stored in App_Code folder. It is very well described here:http://www.asp.net/learn/dataaccess/tutorial01vb.aspx?tabid=63
|||
Oh, I am very fimilar with Scott's tutorials, they are excellent. I would not suggest using 'Strongly typed Dataset'. I didn't realize that is what you meant. Custom entities (A true database class) are far superior for scalability.
In Godaddy, is your ASP.NET runtime set to 2.0?
Yes ser - 2.0 it is :]
|||Could you take a screenshot of the error and and upload it to imageshack or some site and post it here. (block out sensitive data)
I am assuming you are trying to rebuild Scott's tutorial, correct?
Friday, March 23, 2012
Extending SQL Server 2005 Mgmt Studio?
Hi,
I've had requests for a version of my product ViEmu, a VS.NET 2003-VS2005 add-in, which will work within SQL Server 2005 Management Studio. I had a look at the Mgmt Studio, and it seems to be a version of the VS2005 environment customized for the product. There is a registry hive along the line of the one in Visual Studio, but located in HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\90\Tools\Shell.
I looked for any hint online, but it seems largely undocumented.
I tried plugging my software by writing the right 'Packages' and 'AutoloadPackages' keys, and running "SqlWb /setup", which seems to do something. Nothing happened afterwards, anyway, the DLL doesn't seem to be loaded into the process. I tried using the '/log' option, but an error message comes up (which doesn't describe the /setup option either).
Is there a way to load a VS package into SQL Server 2005 Mgmt Studio? Is this a problem with the PLK? Is it unsupported? Has anybody been successful in such a feat? Would it be ok if I hack some way, no matter how obscure? Is this deliberately turned off, as in the Express editions?
Best regards and thanks in advance,
- J
-
ViEmu - vi/vim emulation for Visual Studio
Try the SQL Tools forum.
-Jamie
Wednesday, March 21, 2012
Extended Stored Procedures developed in .NET?
Is it possible to create an extended sproc for SQL2K using .NET (ie C#,
VB.NET, etc)? All of the examples I've found focus on C++.
Cheers, JonNo, not in managed code. There's a KB describing that it is not supported fo
r SQL Server to host
managed code. Below is my "canned" answer:
It is not supported for extended stored procedures or sp_OA procedures to ca
ll .NET code in CLR;
hosted within SQL Server's address space..
See:
http://support.microsoft.com/defaul...kb;en-us;322884
Also, below is with permission from David Browne, explaining how you can hav
e SQL Server execute CLR
code executing in its own process:
"
Short answer: Don't do it.
Calling managed code inside a stored procedure is not supported.
http://support.microsoft.com/defaul...kb;en-us;322884
At least not directly. You need some sort of unmanaged proxy to communicate
with your component running in another process.
For instance, http, or, drum roll, a COM+ Server Application.
This will cause COM+ to load an unmanaged proxy object in the SqlServer
process and will load the CLR into a COM+ surrogate process (dllhost.exe).
Which somebody here mentioned last w
, and I just got around to testing.
It's all perfectly transparent to you, but you have to set up the COM+
server application.
Remember this is something different from .net remoting. With .NET remoting
you have a _managed_ proxy object in the local process, and so you load the
CLR in the local process as well as the remote process.
Anyway here's what I did:
I created this VB class
comTest.vb listing:
Imports System.Runtime.InteropServices
<ClassInterface(ClassInterfaceType.AutoDual),
ProgId("comTest.comTestClass")> _
Public Class comTest
Public Function Hello() As String
Return "hello"
End Function
End Class
build comTest.dll and registered it with
regasm /codebase comTest.dll /tlb:comTest.tlb
(complains that I haven't strong-named my assembly, which you should do.)
created an empty COM+ server application, set to run under a local
administrator account, and dragged comTest.dll into its components folder.
created an unmanaged host (vbscript will do), and invoked the component
using IDispach just like SQLServer.
test.vbs listing
Set d = CreateObject("comTest.comTestClass")
MsgBox d.Hello
Then I used the .net command line debugger cordbg.exe's 'pro' command to
list the processes hosting the CLR. And procexp.exe from
www.sysinternals.com to verify that the CLR's dll's were not loaded in my
unmanaged process. My unmanaged host did not load the CLR, although it
loaded "comsvcs.dll", and the CLR was loaded by the dllhost.exe process.
Then in sql I ran
declare @.object int
declare @.msg varchar(50)
declare @.rc int
declare @.hr int
declare @.source varchar(1000)
declare @.description varchar(1000)
exec @.rc = sp_oacreate 'comTest.comTestClass', @.object output
if @.rc <> 0
begin
EXEC @.hr = sp_OAGetErrorInfo @.object, @.source OUT, @.description OUT
print 'create failed ' + @.description
return
end
exec @.rc = sp_oamethod @.object, 'Hello', @.msg output
if @.rc <> 0
begin
EXEC @.hr = sp_OAGetErrorInfo @.object, @.source OUT, @.description OUT
print 'method failed ' + @.description
return
end
print 'return: ' + @.msg
exec @.rc = sp_oadestroy @.object
if @.rc <> 0
begin
EXEC @.hr = sp_OAGetErrorInfo @.object, @.source OUT, @.description OUT
print 'destroy failed ' + @.description
return
end
Ran fine, and still only one CLR loaded into dllhost.exe's process. So
think we can safely conclude that COM+ server applications do not violate
the prohibition against running managed code in SQLServer's process and
provide a convenient mechanism for interoperating with managed code from
TSQL.
David
"
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Jon Pope" <jon.pope@.healthlinkinc.com> wrote in message
news:u3nFUEQGFHA.2156@.TK2MSFTNGP10.phx.gbl...
> Is it possible to create an extended sproc for SQL2K using .NET (ie C#, VB
.NET, etc)? All of the
> examples I've found focus on C++.
> Cheers, Jon
>|||You rock!
Cheers, Jon
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23yum2SQGFHA.1140@.TK2MSFTNGP10.phx.gbl...
> No, not in managed code. There's a KB describing that it is not supported
> for SQL Server to host managed code. Below is my "canned" answer:
> It is not supported for extended stored procedures or sp_OA procedures to
> call .NET code in CLR; hosted within SQL Server's address space..
> See:
> http://support.microsoft.com/defaul...kb;en-us;322884
> Also, below is with permission from David Browne, explaining how you can
> have SQL Server execute CLR code executing in its own process:
> "
> Short answer: Don't do it.
> Calling managed code inside a stored procedure is not supported.
> http://support.microsoft.com/defaul...kb;en-us;322884
> At least not directly. You need some sort of unmanaged proxy to
> communicate
> with your component running in another process.
> For instance, http, or, drum roll, a COM+ Server Application.
> This will cause COM+ to load an unmanaged proxy object in the SqlServer
> process and will load the CLR into a COM+ surrogate process (dllhost.exe).
> Which somebody here mentioned last w, and I just got around to testing.
> It's all perfectly transparent to you, but you have to set up the COM+
> server application.
> Remember this is something different from .net remoting. With .NET
> remoting
> you have a _managed_ proxy object in the local process, and so you load
> the
> CLR in the local process as well as the remote process.
> Anyway here's what I did:
> I created this VB class
> comTest.vb listing:
> Imports System.Runtime.InteropServices
> <ClassInterface(ClassInterfaceType.AutoDual),
> ProgId("comTest.comTestClass")> _
> Public Class comTest
> Public Function Hello() As String
> Return "hello"
> End Function
> End Class
>
> build comTest.dll and registered it with
> regasm /codebase comTest.dll /tlb:comTest.tlb
> (complains that I haven't strong-named my assembly, which you should do.)
> created an empty COM+ server application, set to run under a local
> administrator account, and dragged comTest.dll into its components folder.
> created an unmanaged host (vbscript will do), and invoked the component
> using IDispach just like SQLServer.
> test.vbs listing
> Set d = CreateObject("comTest.comTestClass")
> MsgBox d.Hello
>
> Then I used the .net command line debugger cordbg.exe's 'pro' command to
> list the processes hosting the CLR. And procexp.exe from
> www.sysinternals.com to verify that the CLR's dll's were not loaded in my
> unmanaged process. My unmanaged host did not load the CLR, although it
> loaded "comsvcs.dll", and the CLR was loaded by the dllhost.exe process.
> Then in sql I ran
> declare @.object int
> declare @.msg varchar(50)
> declare @.rc int
> declare @.hr int
> declare @.source varchar(1000)
> declare @.description varchar(1000)
> exec @.rc = sp_oacreate 'comTest.comTestClass', @.object output
> if @.rc <> 0
> begin
> EXEC @.hr = sp_OAGetErrorInfo @.object, @.source OUT, @.description OUT
> print 'create failed ' + @.description
> return
> end
> exec @.rc = sp_oamethod @.object, 'Hello', @.msg output
> if @.rc <> 0
> begin
> EXEC @.hr = sp_OAGetErrorInfo @.object, @.source OUT, @.description OUT
> print 'method failed ' + @.description
> return
> end
> print 'return: ' + @.msg
> exec @.rc = sp_oadestroy @.object
> if @.rc <> 0
> begin
> EXEC @.hr = sp_OAGetErrorInfo @.object, @.source OUT, @.description OUT
> print 'destroy failed ' + @.description
> return
> end
>
> Ran fine, and still only one CLR loaded into dllhost.exe's process. So
> think we can safely conclude that COM+ server applications do not violate
> the prohibition against running managed code in SQLServer's process and
> provide a convenient mechanism for interoperating with managed code from
> TSQL.
>
> David
> "
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Jon Pope" <jon.pope@.healthlinkinc.com> wrote in message
> news:u3nFUEQGFHA.2156@.TK2MSFTNGP10.phx.gbl...
>|||> You rock!
In this case, I'd say that it is David who rock. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Jon Pope" <jon.pope@.healthlinkinc.com> wrote in message
news:%23kLhBsQGFHA.3376@.TK2MSFTNGP12.phx.gbl...
> You rock!
> Cheers, Jon
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:%23yum2SQGFHA.1140@.TK2MSFTNGP10.phx.gbl...
>sql
VB.NET, etc)? All of the examples I've found focus on C++.
Cheers, JonNo, not in managed code. There's a KB describing that it is not supported fo
r SQL Server to host
managed code. Below is my "canned" answer:
It is not supported for extended stored procedures or sp_OA procedures to ca
ll .NET code in CLR;
hosted within SQL Server's address space..
See:
http://support.microsoft.com/defaul...kb;en-us;322884
Also, below is with permission from David Browne, explaining how you can hav
e SQL Server execute CLR
code executing in its own process:
"
Short answer: Don't do it.
Calling managed code inside a stored procedure is not supported.
http://support.microsoft.com/defaul...kb;en-us;322884
At least not directly. You need some sort of unmanaged proxy to communicate
with your component running in another process.
For instance, http, or, drum roll, a COM+ Server Application.
This will cause COM+ to load an unmanaged proxy object in the SqlServer
process and will load the CLR into a COM+ surrogate process (dllhost.exe).
Which somebody here mentioned last w
, and I just got around to testing.It's all perfectly transparent to you, but you have to set up the COM+
server application.
Remember this is something different from .net remoting. With .NET remoting
you have a _managed_ proxy object in the local process, and so you load the
CLR in the local process as well as the remote process.
Anyway here's what I did:
I created this VB class
comTest.vb listing:
Imports System.Runtime.InteropServices
<ClassInterface(ClassInterfaceType.AutoDual),
ProgId("comTest.comTestClass")> _
Public Class comTest
Public Function Hello() As String
Return "hello"
End Function
End Class
build comTest.dll and registered it with
regasm /codebase comTest.dll /tlb:comTest.tlb
(complains that I haven't strong-named my assembly, which you should do.)
created an empty COM+ server application, set to run under a local
administrator account, and dragged comTest.dll into its components folder.
created an unmanaged host (vbscript will do), and invoked the component
using IDispach just like SQLServer.
test.vbs listing
Set d = CreateObject("comTest.comTestClass")
MsgBox d.Hello
Then I used the .net command line debugger cordbg.exe's 'pro' command to
list the processes hosting the CLR. And procexp.exe from
www.sysinternals.com to verify that the CLR's dll's were not loaded in my
unmanaged process. My unmanaged host did not load the CLR, although it
loaded "comsvcs.dll", and the CLR was loaded by the dllhost.exe process.
Then in sql I ran
declare @.object int
declare @.msg varchar(50)
declare @.rc int
declare @.hr int
declare @.source varchar(1000)
declare @.description varchar(1000)
exec @.rc = sp_oacreate 'comTest.comTestClass', @.object output
if @.rc <> 0
begin
EXEC @.hr = sp_OAGetErrorInfo @.object, @.source OUT, @.description OUT
print 'create failed ' + @.description
return
end
exec @.rc = sp_oamethod @.object, 'Hello', @.msg output
if @.rc <> 0
begin
EXEC @.hr = sp_OAGetErrorInfo @.object, @.source OUT, @.description OUT
print 'method failed ' + @.description
return
end
print 'return: ' + @.msg
exec @.rc = sp_oadestroy @.object
if @.rc <> 0
begin
EXEC @.hr = sp_OAGetErrorInfo @.object, @.source OUT, @.description OUT
print 'destroy failed ' + @.description
return
end
Ran fine, and still only one CLR loaded into dllhost.exe's process. So
think we can safely conclude that COM+ server applications do not violate
the prohibition against running managed code in SQLServer's process and
provide a convenient mechanism for interoperating with managed code from
TSQL.
David
"
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Jon Pope" <jon.pope@.healthlinkinc.com> wrote in message
news:u3nFUEQGFHA.2156@.TK2MSFTNGP10.phx.gbl...
> Is it possible to create an extended sproc for SQL2K using .NET (ie C#, VB
.NET, etc)? All of the
> examples I've found focus on C++.
> Cheers, Jon
>|||You rock!
Cheers, Jon
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23yum2SQGFHA.1140@.TK2MSFTNGP10.phx.gbl...
> No, not in managed code. There's a KB describing that it is not supported
> for SQL Server to host managed code. Below is my "canned" answer:
> It is not supported for extended stored procedures or sp_OA procedures to
> call .NET code in CLR; hosted within SQL Server's address space..
> See:
> http://support.microsoft.com/defaul...kb;en-us;322884
> Also, below is with permission from David Browne, explaining how you can
> have SQL Server execute CLR code executing in its own process:
> "
> Short answer: Don't do it.
> Calling managed code inside a stored procedure is not supported.
> http://support.microsoft.com/defaul...kb;en-us;322884
> At least not directly. You need some sort of unmanaged proxy to
> communicate
> with your component running in another process.
> For instance, http, or, drum roll, a COM+ Server Application.
> This will cause COM+ to load an unmanaged proxy object in the SqlServer
> process and will load the CLR into a COM+ surrogate process (dllhost.exe).
> Which somebody here mentioned last w
, and I just got around to testing.> It's all perfectly transparent to you, but you have to set up the COM+
> server application.
> Remember this is something different from .net remoting. With .NET
> remoting
> you have a _managed_ proxy object in the local process, and so you load
> the
> CLR in the local process as well as the remote process.
> Anyway here's what I did:
> I created this VB class
> comTest.vb listing:
> Imports System.Runtime.InteropServices
> <ClassInterface(ClassInterfaceType.AutoDual),
> ProgId("comTest.comTestClass")> _
> Public Class comTest
> Public Function Hello() As String
> Return "hello"
> End Function
> End Class
>
> build comTest.dll and registered it with
> regasm /codebase comTest.dll /tlb:comTest.tlb
> (complains that I haven't strong-named my assembly, which you should do.)
> created an empty COM+ server application, set to run under a local
> administrator account, and dragged comTest.dll into its components folder.
> created an unmanaged host (vbscript will do), and invoked the component
> using IDispach just like SQLServer.
> test.vbs listing
> Set d = CreateObject("comTest.comTestClass")
> MsgBox d.Hello
>
> Then I used the .net command line debugger cordbg.exe's 'pro' command to
> list the processes hosting the CLR. And procexp.exe from
> www.sysinternals.com to verify that the CLR's dll's were not loaded in my
> unmanaged process. My unmanaged host did not load the CLR, although it
> loaded "comsvcs.dll", and the CLR was loaded by the dllhost.exe process.
> Then in sql I ran
> declare @.object int
> declare @.msg varchar(50)
> declare @.rc int
> declare @.hr int
> declare @.source varchar(1000)
> declare @.description varchar(1000)
> exec @.rc = sp_oacreate 'comTest.comTestClass', @.object output
> if @.rc <> 0
> begin
> EXEC @.hr = sp_OAGetErrorInfo @.object, @.source OUT, @.description OUT
> print 'create failed ' + @.description
> return
> end
> exec @.rc = sp_oamethod @.object, 'Hello', @.msg output
> if @.rc <> 0
> begin
> EXEC @.hr = sp_OAGetErrorInfo @.object, @.source OUT, @.description OUT
> print 'method failed ' + @.description
> return
> end
> print 'return: ' + @.msg
> exec @.rc = sp_oadestroy @.object
> if @.rc <> 0
> begin
> EXEC @.hr = sp_OAGetErrorInfo @.object, @.source OUT, @.description OUT
> print 'destroy failed ' + @.description
> return
> end
>
> Ran fine, and still only one CLR loaded into dllhost.exe's process. So
> think we can safely conclude that COM+ server applications do not violate
> the prohibition against running managed code in SQLServer's process and
> provide a convenient mechanism for interoperating with managed code from
> TSQL.
>
> David
> "
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Jon Pope" <jon.pope@.healthlinkinc.com> wrote in message
> news:u3nFUEQGFHA.2156@.TK2MSFTNGP10.phx.gbl...
>|||> You rock!
In this case, I'd say that it is David who rock. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Jon Pope" <jon.pope@.healthlinkinc.com> wrote in message
news:%23kLhBsQGFHA.3376@.TK2MSFTNGP12.phx.gbl...
> You rock!
> Cheers, Jon
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:%23yum2SQGFHA.1140@.TK2MSFTNGP10.phx.gbl...
>sql
Extended stored procedures and UDP sockets
Hello,
I'm trying this question again; please excuse the repetition.
I have written an extended stored procedure using Visual C++ .Net
(unmanaged). The procedure is intended to send out a UDP multicast message
when new data arrives. The message doesn't seem to be sent.
I have verified that the ESP is getting called correctly (in response to an
INSERT trigger). I have verified that none of the Winsock API procedures
return errors when called from the ESP. I have verified the code that
actually sends the multicast message by running the exact same code from a
console application, where it works fine--including running it when logged i
n
under the account that SQL Server runs under.
There seems to be something unexpectedly different about the environment
that the ESP runs in. I'm at a loss to figure out what it is. Any clues
would be appreciated.
Thanks.
Ed Hoch
GEDDS Manager
Geophysical Institute
University of Alaska FairbanksI'll throw out some thoughts, though I don't have anything definite:
1. Are you initializing the Windows socket library right within the proc?
(I think it's WSAInitialize()). That definitely would need to be done.
2. If you can't get a solution, I know you could set a named Windows event
inside your extended proc (I've done that) and then have another process
waiting on that event and have it send the data gram.
3. Do you have fiber mode turned on in SQL Server? If so, that may affect
threading and sockets (just guessing...)
Mike
"Edward Hoch" <EdwardHoch@.discussions.microsoft.com> wrote in message
news:1A237FAF-EFD9-481B-99F8-6B05CCB3DA35@.microsoft.com...
> Hello,
> I'm trying this question again; please excuse the repetition.
> I have written an extended stored procedure using Visual C++ .Net
> (unmanaged). The procedure is intended to send out a UDP multicast
message
> when new data arrives. The message doesn't seem to be sent.
> I have verified that the ESP is getting called correctly (in response to
an
> INSERT trigger). I have verified that none of the Winsock API procedures
> return errors when called from the ESP. I have verified the code that
> actually sends the multicast message by running the exact same code from a
> console application, where it works fine--including running it when logged
in
> under the account that SQL Server runs under.
> There seems to be something unexpectedly different about the environment
> that the ESP runs in. I'm at a loss to figure out what it is. Any clues
> would be appreciated.
> Thanks.
> Ed Hoch
> --
> GEDDS Manager
> Geophysical Institute
> University of Alaska Fairbanks|||Hi Mike,
Thanks for the reply.
1. I am initializing the socket library with WSAStartup. It seems to work
from the console version of the program, so I think the initialization is
working properly.
2. Named events seem like a possibility. What's a good reference for
those?
3. I am not using the "Windows NT fibers" in my SQL Server. Would it be
more or less likely for the thing to work with fibers enabled?
Thanks again for the reply.
Ed Hoch
"Mike Jansen" wrote:
> I'll throw out some thoughts, though I don't have anything definite:
> 1. Are you initializing the Windows socket library right within the proc?
> (I think it's WSAInitialize()). That definitely would need to be done.
> 2. If you can't get a solution, I know you could set a named Windows event
> inside your extended proc (I've done that) and then have another process
> waiting on that event and have it send the data gram.
> 3. Do you have fiber mode turned on in SQL Server? If so, that may affect
> threading and sockets (just guessing...)
> Mike
> "Edward Hoch" <EdwardHoch@.discussions.microsoft.com> wrote in message
> news:1A237FAF-EFD9-481B-99F8-6B05CCB3DA35@.microsoft.com...
> message
> an
> in
>
>|||> Thanks for the reply.
> 1. I am initializing the socket library with WSAStartup. It seems to
work
> from the console version of the program, so I think the initialization is
> working properly.
WSAStartup, that was it :)
> 2. Named events seem like a possibility. What's a good reference for
> those?
In Windows API look at CreateEvent(), SetEvent(), OpenEvent(). I think
there's even an overview section on synchronization objects. The thing to
be aware of for named objects is that in order for them to work across
process boundaries you need to set up the SECURITY_ATTRIBUTES properly
(unless the other process happens to be running under the same account as
the SQL Server process). The named event won't pass any other context
information.
If you need to pass context information along with triggering an event, you
may want to do something like write out a file and have the process
monitoring a folder rather than an event. There are other options besides
files but file is simple and straightforward.
If you want more information, we can take this offline or to another group
since it's off-topic and can get fairly involved.
> 3. I am not using the "Windows NT fibers" in my SQL Server. Would it be
> more or less likely for the thing to work with fibers enabled?
Like I said, I was just guessing, but I'd guess you might have issues if
fibers were ENABLED a fiber is a "mutant" thread.
Also, perhaps you could try this (though I don't know if it's wise): In
your stored proc WinMain/on load, create a separate thread. Do your sockets
in that thread. That way it's still in the same process but on its own
thread. So your worker thread starts and waits for an item in a context
queue (something you create). When it receives an item in the queue, it
dequeues it and sends a datagram. In your exetended proc, just add items to
the queue. First, I'd do some scanning to see if secondary threads in
extended procs are OK. Regardless, the di
vantage of this is that its
starting to get complicated and complicated things mess up more often and
messing up inside the process space of SQL Server isn't the greatest thing.
So even if secondary threads are OK, BE VERY CAREFUL.
Mike|||What is the service account of SQL Server? Start the SQL Server service from
the command line, and execute the XP, see if that makes a difference.
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2005 All rights reserved.
"Edward Hoch" <EdwardHoch@.discussions.microsoft.com> wrote in message
news:1A237FAF-EFD9-481B-99F8-6B05CCB3DA35@.microsoft.com...
> Hello,
> I'm trying this question again; please excuse the repetition.
> I have written an extended stored procedure using Visual C++ .Net
> (unmanaged). The procedure is intended to send out a UDP multicast
> message
> when new data arrives. The message doesn't seem to be sent.
> I have verified that the ESP is getting called correctly (in response to
> an
> INSERT trigger). I have verified that none of the Winsock API procedures
> return errors when called from the ESP. I have verified the code that
> actually sends the multicast message by running the exact same code from a
> console application, where it works fine--including running it when logged
> in
> under the account that SQL Server runs under.
> There seems to be something unexpectedly different about the environment
> that the ESP runs in. I'm at a loss to figure out what it is. Any clues
> would be appreciated.
> Thanks.
> Ed Hoch
> --
> GEDDS Manager
> Geophysical Institute
> University of Alaska Fairbanks
I'm trying this question again; please excuse the repetition.
I have written an extended stored procedure using Visual C++ .Net
(unmanaged). The procedure is intended to send out a UDP multicast message
when new data arrives. The message doesn't seem to be sent.
I have verified that the ESP is getting called correctly (in response to an
INSERT trigger). I have verified that none of the Winsock API procedures
return errors when called from the ESP. I have verified the code that
actually sends the multicast message by running the exact same code from a
console application, where it works fine--including running it when logged i
n
under the account that SQL Server runs under.
There seems to be something unexpectedly different about the environment
that the ESP runs in. I'm at a loss to figure out what it is. Any clues
would be appreciated.
Thanks.
Ed Hoch
GEDDS Manager
Geophysical Institute
University of Alaska FairbanksI'll throw out some thoughts, though I don't have anything definite:
1. Are you initializing the Windows socket library right within the proc?
(I think it's WSAInitialize()). That definitely would need to be done.
2. If you can't get a solution, I know you could set a named Windows event
inside your extended proc (I've done that) and then have another process
waiting on that event and have it send the data gram.
3. Do you have fiber mode turned on in SQL Server? If so, that may affect
threading and sockets (just guessing...)
Mike
"Edward Hoch" <EdwardHoch@.discussions.microsoft.com> wrote in message
news:1A237FAF-EFD9-481B-99F8-6B05CCB3DA35@.microsoft.com...
> Hello,
> I'm trying this question again; please excuse the repetition.
> I have written an extended stored procedure using Visual C++ .Net
> (unmanaged). The procedure is intended to send out a UDP multicast
message
> when new data arrives. The message doesn't seem to be sent.
> I have verified that the ESP is getting called correctly (in response to
an
> INSERT trigger). I have verified that none of the Winsock API procedures
> return errors when called from the ESP. I have verified the code that
> actually sends the multicast message by running the exact same code from a
> console application, where it works fine--including running it when logged
in
> under the account that SQL Server runs under.
> There seems to be something unexpectedly different about the environment
> that the ESP runs in. I'm at a loss to figure out what it is. Any clues
> would be appreciated.
> Thanks.
> Ed Hoch
> --
> GEDDS Manager
> Geophysical Institute
> University of Alaska Fairbanks|||Hi Mike,
Thanks for the reply.
1. I am initializing the socket library with WSAStartup. It seems to work
from the console version of the program, so I think the initialization is
working properly.
2. Named events seem like a possibility. What's a good reference for
those?
3. I am not using the "Windows NT fibers" in my SQL Server. Would it be
more or less likely for the thing to work with fibers enabled?
Thanks again for the reply.
Ed Hoch
"Mike Jansen" wrote:
> I'll throw out some thoughts, though I don't have anything definite:
> 1. Are you initializing the Windows socket library right within the proc?
> (I think it's WSAInitialize()). That definitely would need to be done.
> 2. If you can't get a solution, I know you could set a named Windows event
> inside your extended proc (I've done that) and then have another process
> waiting on that event and have it send the data gram.
> 3. Do you have fiber mode turned on in SQL Server? If so, that may affect
> threading and sockets (just guessing...)
> Mike
> "Edward Hoch" <EdwardHoch@.discussions.microsoft.com> wrote in message
> news:1A237FAF-EFD9-481B-99F8-6B05CCB3DA35@.microsoft.com...
> message
> an
> in
>
>|||> Thanks for the reply.
> 1. I am initializing the socket library with WSAStartup. It seems to
work
> from the console version of the program, so I think the initialization is
> working properly.
WSAStartup, that was it :)
> 2. Named events seem like a possibility. What's a good reference for
> those?
In Windows API look at CreateEvent(), SetEvent(), OpenEvent(). I think
there's even an overview section on synchronization objects. The thing to
be aware of for named objects is that in order for them to work across
process boundaries you need to set up the SECURITY_ATTRIBUTES properly
(unless the other process happens to be running under the same account as
the SQL Server process). The named event won't pass any other context
information.
If you need to pass context information along with triggering an event, you
may want to do something like write out a file and have the process
monitoring a folder rather than an event. There are other options besides
files but file is simple and straightforward.
If you want more information, we can take this offline or to another group
since it's off-topic and can get fairly involved.
> 3. I am not using the "Windows NT fibers" in my SQL Server. Would it be
> more or less likely for the thing to work with fibers enabled?
Like I said, I was just guessing, but I'd guess you might have issues if
fibers were ENABLED a fiber is a "mutant" thread.
Also, perhaps you could try this (though I don't know if it's wise): In
your stored proc WinMain/on load, create a separate thread. Do your sockets
in that thread. That way it's still in the same process but on its own
thread. So your worker thread starts and waits for an item in a context
queue (something you create). When it receives an item in the queue, it
dequeues it and sends a datagram. In your exetended proc, just add items to
the queue. First, I'd do some scanning to see if secondary threads in
extended procs are OK. Regardless, the di
vantage of this is that itsstarting to get complicated and complicated things mess up more often and
messing up inside the process space of SQL Server isn't the greatest thing.
So even if secondary threads are OK, BE VERY CAREFUL.
Mike|||What is the service account of SQL Server? Start the SQL Server service from
the command line, and execute the XP, see if that makes a difference.
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2005 All rights reserved.
"Edward Hoch" <EdwardHoch@.discussions.microsoft.com> wrote in message
news:1A237FAF-EFD9-481B-99F8-6B05CCB3DA35@.microsoft.com...
> Hello,
> I'm trying this question again; please excuse the repetition.
> I have written an extended stored procedure using Visual C++ .Net
> (unmanaged). The procedure is intended to send out a UDP multicast
> message
> when new data arrives. The message doesn't seem to be sent.
> I have verified that the ESP is getting called correctly (in response to
> an
> INSERT trigger). I have verified that none of the Winsock API procedures
> return errors when called from the ESP. I have verified the code that
> actually sends the multicast message by running the exact same code from a
> console application, where it works fine--including running it when logged
> in
> under the account that SQL Server runs under.
> There seems to be something unexpectedly different about the environment
> that the ESP runs in. I'm at a loss to figure out what it is. Any clues
> would be appreciated.
> Thanks.
> Ed Hoch
> --
> GEDDS Manager
> Geophysical Institute
> University of Alaska Fairbanks
Extended Stored Procedure Permissions Problem
I have devloped a .NET windows service to manage
server-side traces for an ongoing database auditing effort
here at work. The service creates traces, rolls them over
periodically, and loads the trace file contents into a
reporting instance. This service logs into each audited
instance using its a domain user account. The service must
call the sp_trace_create, sp_setstatus, sp_setevent, and
sp_setfilter extented stored procedures. Addtionally, it
must access the fn_trace_getinfo and fn_trace_gettable
functions.
The service works very well when the domain account is a
member of the sysadmin role in an audited instances. The
service fails to work when it is just a regular db user
with EXECUTE permissions on the above stored procedures.
The service also does not work when configured as a dbo in
the master database of the audited instance.
I am guessing that this issue arises from the trace
procedures being extended stored procedures and perhaps
there is some underlying OS permission issue. Can anyone
offer some guidance that can help me avoid running this
service as a sysadmin?
Thanks in advance,
TimIn SQL2000 you have to be a sysadmin to run trace procs. This is a change
from SQL7 where you could grant exec on the extended stored procedures
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Tim Richardson" <anonymous@.discussions.microsoft.com> wrote in message
news:dce201c40ad0$2f8e3260$a101280a@.phx.gbl...
> I have devloped a .NET windows service to manage
> server-side traces for an ongoing database auditing effort
> here at work. The service creates traces, rolls them over
> periodically, and loads the trace file contents into a
> reporting instance. This service logs into each audited
> instance using its a domain user account. The service must
> call the sp_trace_create, sp_setstatus, sp_setevent, and
> sp_setfilter extented stored procedures. Addtionally, it
> must access the fn_trace_getinfo and fn_trace_gettable
> functions.
> The service works very well when the domain account is a
> member of the sysadmin role in an audited instances. The
> service fails to work when it is just a regular db user
> with EXECUTE permissions on the above stored procedures.
> The service also does not work when configured as a dbo in
> the master database of the audited instance.
> I am guessing that this issue arises from the trace
> procedures being extended stored procedures and perhaps
> there is some underlying OS permission issue. Can anyone
> offer some guidance that can help me avoid running this
> service as a sysadmin?
> Thanks in advance,
> Tim|||Thanks, Jasper.
>--Original Message--
>In SQL2000 you have to be a sysadmin to run trace procs.
This is a change
>from SQL7 where you could grant exec on the extended
stored procedures
>--
>HTH
>Jasper Smith (SQL Server MVP)
>I support PASS - the definitive, global
>community for SQL Server professionals -
>http://www.sqlpass.org
>
>"Tim Richardson" <anonymous@.discussions.microsoft.com>
wrote in message
>news:dce201c40ad0$2f8e3260$a101280a@.phx.gbl...
>
>.
>
server-side traces for an ongoing database auditing effort
here at work. The service creates traces, rolls them over
periodically, and loads the trace file contents into a
reporting instance. This service logs into each audited
instance using its a domain user account. The service must
call the sp_trace_create, sp_setstatus, sp_setevent, and
sp_setfilter extented stored procedures. Addtionally, it
must access the fn_trace_getinfo and fn_trace_gettable
functions.
The service works very well when the domain account is a
member of the sysadmin role in an audited instances. The
service fails to work when it is just a regular db user
with EXECUTE permissions on the above stored procedures.
The service also does not work when configured as a dbo in
the master database of the audited instance.
I am guessing that this issue arises from the trace
procedures being extended stored procedures and perhaps
there is some underlying OS permission issue. Can anyone
offer some guidance that can help me avoid running this
service as a sysadmin?
Thanks in advance,
TimIn SQL2000 you have to be a sysadmin to run trace procs. This is a change
from SQL7 where you could grant exec on the extended stored procedures
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Tim Richardson" <anonymous@.discussions.microsoft.com> wrote in message
news:dce201c40ad0$2f8e3260$a101280a@.phx.gbl...
> I have devloped a .NET windows service to manage
> server-side traces for an ongoing database auditing effort
> here at work. The service creates traces, rolls them over
> periodically, and loads the trace file contents into a
> reporting instance. This service logs into each audited
> instance using its a domain user account. The service must
> call the sp_trace_create, sp_setstatus, sp_setevent, and
> sp_setfilter extented stored procedures. Addtionally, it
> must access the fn_trace_getinfo and fn_trace_gettable
> functions.
> The service works very well when the domain account is a
> member of the sysadmin role in an audited instances. The
> service fails to work when it is just a regular db user
> with EXECUTE permissions on the above stored procedures.
> The service also does not work when configured as a dbo in
> the master database of the audited instance.
> I am guessing that this issue arises from the trace
> procedures being extended stored procedures and perhaps
> there is some underlying OS permission issue. Can anyone
> offer some guidance that can help me avoid running this
> service as a sysadmin?
> Thanks in advance,
> Tim|||Thanks, Jasper.
>--Original Message--
>In SQL2000 you have to be a sysadmin to run trace procs.
This is a change
>from SQL7 where you could grant exec on the extended
stored procedures
>--
>HTH
>Jasper Smith (SQL Server MVP)
>I support PASS - the definitive, global
>community for SQL Server professionals -
>http://www.sqlpass.org
>
>"Tim Richardson" <anonymous@.discussions.microsoft.com>
wrote in message
>news:dce201c40ad0$2f8e3260$a101280a@.phx.gbl...
>
>.
>
Monday, March 19, 2012
Extended Stored Procedure creation error
I've created an extended SP on my local machine (Win XP/ VS.Net 2003 / VC++)
.
It works fine when I tested on my local PC after I copied the created dll
file to binn directory and ran sp_addextendedproc.
I copied it to a Win 2000 Server/MS SQL's binn directory and
sp_addextendedproc completed successfully. However, it gives the following
error when I execute the stored procedure:
ODBC: Msg 0, Level 16, State 1
Cannot load the DLL "uxp_calc_sw_v1.dll", or one of the DLLs it references.
Reason: 126(The specified module could not be found.).
Is the dll rely on some other dlls? or should I use Visual C++ 6.0?I found the solution myself, I just copied the following two files to the
binn folder:
DLL Name: MSVCP70.dll
DLL Name: MSVCR70.dll
Or should I copy them to Winnt\system32?
"nick" wrote:
> I've created an extended SP on my local machine (Win XP/ VS.Net 2003 / VC+
+).
> It works fine when I tested on my local PC after I copied the created dll
> file to binn directory and ran sp_addextendedproc.
> I copied it to a Win 2000 Server/MS SQL's binn directory and
> sp_addextendedproc completed successfully. However, it gives the following
> error when I execute the stored procedure:
> ODBC: Msg 0, Level 16, State 1
> Cannot load the DLL "uxp_calc_sw_v1.dll", or one of the DLLs it references
.
> Reason: 126(The specified module could not be found.).
> Is the dll rely on some other dlls? or should I use Visual C++ 6.0?
>
>|||nick (nick@.discussions.microsoft.com) writes:
> I found the solution myself, I just copied the following two files to the
> binn folder:
> DLL Name: MSVCP70.dll
> DLL Name: MSVCR70.dll
> Or should I copy them to Winnt\system32?
There is some guidelines on this in MSDN Library. I had reason to
investigate the story about the Visual C DLL:s myself a while back. My
impressions is that the recommendation is that you should should distribute
the DLL:s with your code, and place in them together with your application.
Thus, you did put them into the right place.
If you want an answer from people who really understand this, you should
ask in a C++ newsgroup.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
.
It works fine when I tested on my local PC after I copied the created dll
file to binn directory and ran sp_addextendedproc.
I copied it to a Win 2000 Server/MS SQL's binn directory and
sp_addextendedproc completed successfully. However, it gives the following
error when I execute the stored procedure:
ODBC: Msg 0, Level 16, State 1
Cannot load the DLL "uxp_calc_sw_v1.dll", or one of the DLLs it references.
Reason: 126(The specified module could not be found.).
Is the dll rely on some other dlls? or should I use Visual C++ 6.0?I found the solution myself, I just copied the following two files to the
binn folder:
DLL Name: MSVCP70.dll
DLL Name: MSVCR70.dll
Or should I copy them to Winnt\system32?
"nick" wrote:
> I've created an extended SP on my local machine (Win XP/ VS.Net 2003 / VC+
+).
> It works fine when I tested on my local PC after I copied the created dll
> file to binn directory and ran sp_addextendedproc.
> I copied it to a Win 2000 Server/MS SQL's binn directory and
> sp_addextendedproc completed successfully. However, it gives the following
> error when I execute the stored procedure:
> ODBC: Msg 0, Level 16, State 1
> Cannot load the DLL "uxp_calc_sw_v1.dll", or one of the DLLs it references
.
> Reason: 126(The specified module could not be found.).
> Is the dll rely on some other dlls? or should I use Visual C++ 6.0?
>
>|||nick (nick@.discussions.microsoft.com) writes:
> I found the solution myself, I just copied the following two files to the
> binn folder:
> DLL Name: MSVCP70.dll
> DLL Name: MSVCR70.dll
> Or should I copy them to Winnt\system32?
There is some guidelines on this in MSDN Library. I had reason to
investigate the story about the Visual C DLL:s myself a while back. My
impressions is that the recommendation is that you should should distribute
the DLL:s with your code, and place in them together with your application.
Thus, you did put them into the right place.
If you want an answer from people who really understand this, you should
ask in a C++ newsgroup.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
Extended Stored Procedure
Could someone who has done it before be kind enough to post a short example of how to make a call from an extended stored procedure to a .NET DLL? Or even direct me to an example, or tell me that this is possible / not possible, it would help.
Thanks,
Brian
Theoffficial position from Microsoft is that they do not support the use of the CLR for extended stored procedures
At one point I had considered trying to use regular expressions withSQL Server 2000. Here'a a CodeProject article explaining how togo about it:xp_regex:Regular Expressions in SQL Server 2000. This also covers the topic of running managed code from an extendedstored procedure. Maybe you will find something in there to helpyou.
|||
Yes I was slowly finding this out through my research last night. Thanks for the validation, and I will check out your code.
I did find someone who did it anyway by wrapping a COM object:
http://sqljunkies.com/Article/C5A500EB-B8BE-42C0-B23B-258A342CAAAB.scuk
Who knows what kind of problems that could cause.
My solution is apparently around the bend in SQL Server and Visual Studio 2005 though. SQL 2005 natively supporting CLR.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sqlclrguidance.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnreal/html/realworld03112005.asp
Thanks for the feedback, if anyone has comments about the above please don't hold back.
Brian
|||Just to be clear, nothing I provided is my own code, just what I found when I researched the same subject.And indeed, SQL 2005 will be your answer!
|||
I have seen Jeff Prosise live use them in a Demo at our .NET user group but Jeff Prosise is the person to call if you want to know MFC(Microsoft Foundation Class). I would swing by BMC website to test drive their SQL Programmer tool because anything you can do in SQL Server their tool will do it. They bought the best Transact-SQL minds in 1999. I would also browse Ken Henderson books at my local book store, there are three of them on Transact-SQL because he covers undocumented DBCCs and functions that may help you. Hope this helps.
http://www.wintellect.com
http://www.bmc.com/products/proddocview/0,,0_0_0_8739,00.html
Kind regards,
Gift Peddie
Friday, March 9, 2012
Expression Too Complex error - mdb
DataAdapter.update(dataSet) exception Error:
"Changes not saved to database. Expression Too Complex"
Using: Visual Studio, C#, ADO.Net interface and MS-Access (OLE DB Jet)
Without diving into the details on the one-to-many 99 column Access (Main)
table , can someone tell me the reason for the error? Is it really the
complexity of the SQL statement? Would SQL server help me? Does SQL Server
allow update() of a queries. StevePlease send us the text of the query you are trying. Based on the little
data we have here, UPDATE() is likely not usable in your situation, since
this is only for use in triggers.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Steve B." <SteveB@.discussions.microsoft.com> wrote in message
news:6284FB15-F8CE-4BDA-9060-B4B09428F4B6@.microsoft.com...
> DataAdapter.update(dataSet) exception Error:
> "Changes not saved to database. Expression Too Complex"
> Using: Visual Studio, C#, ADO.Net interface and MS-Access (OLE DB Jet)
> Without diving into the details on the one-to-many 99 column Access (Main)
> table , can someone tell me the reason for the error? Is it really the
> complexity of the SQL statement? Would SQL server help me? Does SQL
> Server
> allow update() of a queries. Steve|||hi steve
SQL Server allows updates. there might be an error in the sql string that u
were trying to pass.
please check the query and revert back.
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"Steve B." wrote:
> DataAdapter.update(dataSet) exception Error:
> "Changes not saved to database. Expression Too Complex"
> Using: Visual Studio, C#, ADO.Net interface and MS-Access (OLE DB Jet)
> Without diving into the details on the one-to-many 99 column Access (Main)
> table , can someone tell me the reason for the error? Is it really the
> complexity of the SQL statement? Would SQL server help me? Does SQL Serve
r
> allow update() of a queries. Steve|||> DataAdapter.update(dataSet) exception Error:
> "Changes not saved to database. Expression Too Complex"
> Using: Visual Studio, C#, ADO.Net interface and MS-Access (OLE DB Jet)
> Without diving into the details on the one-to-many 99 column Access (Main)
> table , can someone tell me the reason for the error? Is it really the
> complexity of the SQL statement? Would SQL server help me? Does SQL
> Server
> allow update() of a queries.
Just off the top of my head, each version of Access has a different maximum
limit on how long an SQL statement is allowed to be. I have actually gotten
that error. That's all I can think of though.
Peace & happy computing,
Mike Labosh, MCSD
"Musha ring dum a doo dum a da!" -- James Hetfield|||I hope this is what your looking for (see below). In the mean time, I found
this (please see first few paragraphs):
http://support.microsoft.com/defaul...kb;en-us;192716
I have 99 columns and I'm using a Jet 4.0 OLE DB for Access. I'm now
thinking of dividing my Main table into 4 or 5 tables indexed on part number
(part data table, Chara. 1-10 table, chara. 11-20 data table, etc) as a
workaround. What a mess..
The CharacteristicCodes, ProcessCodes and LocationCodes fields are indexed
(dropdown) from their own table in the "Main" table but, every Characteristi
c
field is different (blueprint dimensions, note number, etc) . I really nee
d
30 characteristics not 21. Can't get there from here.
I don't know if a commercial database program like SQL server will help me.
Thats why I'm asking. Comments?
(copied from VS Dataadapter wizard)
SELECT
MainID,
PartNumber,
Nomenclature,
PartStatus,
CoverageBy,
CoverageByDate,
Engine,
Service,
Originator,
RequirementSourceCodes,
PartPointOfContact,
VerificationDate,
CharacteristicCodes1,
ProcessCodes1,
LocationCodes1,
Characteristic1,
CharacteristicCodes2,
LocationCodes2,
ProcessCodes2,
Characteristic2,
CharacteristicCodes3,
LocationCodes3,
ProcessCodes3,
Characteristic3,
CharacteristicCodes4,
LocationCodes4,
ProcessCodes4,
Characteristic4,
CharacteristicCodes5,
LocationCodes5,
ProcessCodes5,
Characteristic5,
CharacteristicCodes6,
LocationCodes6,
ProcessCodes6,
Characteristic6,
CharacteristicCodes7,
LocationCodes7,
ProcessCodes7,
Characteristic7,
CharacteristicCodes8,
LocationCodes8,
ProcessCodes8,
Characteristic8,
CharacteristicCodes9,
LocationCodes9,
ProcessCodes9,
Characteristic9,
CharacteristicCodes10,
LocationCodes10,
ProcessCodes10,
Characteristic10,
CharacteristicCodes11,
LocationCodes11,
ProcessCodes11,
Characteristic11,
CharacteristicCodes12,
LocationCodes12,
ProcessCodes12,
Characteristic12,
CharacteristicCodes13,
LocationCodes13,
ProcessCodes13,
Characteristic13,
CharacteristicCodes14,
LocationCodes14,
ProcessCodes14,
Characteristic14,
CharacteristicCodes15,
LocationCodes15,
ProcessCodes15,
Characteristic15,
CharacteristicCodes16,
LocationCodes16,
ProcessCodes16,
Characteristic16,
CharacteristicCodes17,
LocationCodes17,
ProcessCodes17,
Characteristic17,
CharacteristicCodes18,
LocationCodes18,
ProcessCodes18,
Characteristic18,
CharacteristicCodes19,
LocationCodes19,
ProcessCodes19,
Characteristic19,
CharacteristicCodes20,
LocationCodes20,
ProcessCodes20,
Characteristic20,
CharacteristicCodes21,
LocationCodes21,
ProcessCodes21,
Characteristic21,
Comments,
AnnualCSIConfirmation,
DataRowState
FROM
Main
WHERE
(AnnualCSIConfirmation = 'Yes') AND
(PartStatus = 'Active (buy part)') OR (PartStatus = 'Active (make
part)') OR (PartStatus = 'Active (buy/make part)') OR (PartStatus = 'None On
Order (Inactive)') ORDER BY MainID
"Chandra" wrote:
> hi steve
> SQL Server allows updates. there might be an error in the sql string that
u
> were trying to pass.
> please check the query and revert back.
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://www.SQLResource.com/
> ---
>
> "Steve B." wrote:
>|||Yes!
i agree with you. The message mentions that, the query must be optimized.
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"Mike Labosh" wrote:
> Just off the top of my head, each version of Access has a different maximu
m
> limit on how long an SQL statement is allowed to be. I have actually gott
en
> that error. That's all I can think of though.
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "Musha ring dum a doo dum a da!" -- James Hetfield
>
>|||hi
looks like u need to normalize your table:
something like:
MainID,
Charid,
CharacteristicCodes,
ProcessCodes,
LocationCodes,
Characteristic,
but, this is out of scope now:
just try to remove "ORDER BY MainID" and check, it might speed up the
process and u can escape from the error.
or
where part:
AnnualCSIConfirmation = 'Yes' AND
PartStatus IN ( 'Active (buy part)', 'Active (make part)', 'Active
(buy/make part)', 'None On Order (Inactive)' )
please revert back with the results
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"Steve B." wrote:
> I hope this is what your looking for (see below). In the mean time, I fou
nd
> this (please see first few paragraphs):
> http://support.microsoft.com/defaul...kb;en-us;192716
> I have 99 columns and I'm using a Jet 4.0 OLE DB for Access. I'm now
> thinking of dividing my Main table into 4 or 5 tables indexed on part numb
er
> (part data table, Chara. 1-10 table, chara. 11-20 data table, etc) as a
> workaround. What a mess..
> The CharacteristicCodes, ProcessCodes and LocationCodes fields are indexe
d
> (dropdown) from their own table in the "Main" table but, every Characteris
tic
> field is different (blueprint dimensions, note number, etc) . I really n
eed
> 30 characteristics not 21. Can't get there from here.
> I don't know if a commercial database program like SQL server will help me
.
> Thats why I'm asking. Comments?
> (copied from VS Dataadapter wizard)
> SELECT
> MainID,
> PartNumber,
> Nomenclature,
> PartStatus,
> CoverageBy,
> CoverageByDate,
> Engine,
> Service,
> Originator,
> RequirementSourceCodes,
> PartPointOfContact,
> VerificationDate,
> CharacteristicCodes1,
> ProcessCodes1,
> LocationCodes1,
> Characteristic1,
> CharacteristicCodes2,
> LocationCodes2,
> ProcessCodes2,
> Characteristic2,
> CharacteristicCodes3,
> LocationCodes3,
> ProcessCodes3,
> Characteristic3,
> CharacteristicCodes4,
> LocationCodes4,
> ProcessCodes4,
> Characteristic4,
> CharacteristicCodes5,
> LocationCodes5,
> ProcessCodes5,
> Characteristic5,
> CharacteristicCodes6,
> LocationCodes6,
> ProcessCodes6,
> Characteristic6,
> CharacteristicCodes7,
> LocationCodes7,
> ProcessCodes7,
> Characteristic7,
> CharacteristicCodes8,
> LocationCodes8,
> ProcessCodes8,
> Characteristic8,
> CharacteristicCodes9,
> LocationCodes9,
> ProcessCodes9,
> Characteristic9,
> CharacteristicCodes10,
> LocationCodes10,
> ProcessCodes10,
> Characteristic10,
> CharacteristicCodes11,
> LocationCodes11,
> ProcessCodes11,
> Characteristic11,
> CharacteristicCodes12,
> LocationCodes12,
> ProcessCodes12,
> Characteristic12,
> CharacteristicCodes13,
> LocationCodes13,
> ProcessCodes13,
> Characteristic13,
> CharacteristicCodes14,
> LocationCodes14,
> ProcessCodes14,
> Characteristic14,
> CharacteristicCodes15,
> LocationCodes15,
> ProcessCodes15,
> Characteristic15,
> CharacteristicCodes16,
> LocationCodes16,
> ProcessCodes16,
> Characteristic16,
> CharacteristicCodes17,
> LocationCodes17,
> ProcessCodes17,
> Characteristic17,
> CharacteristicCodes18,
> LocationCodes18,
> ProcessCodes18,
> Characteristic18,
> CharacteristicCodes19,
> LocationCodes19,
> ProcessCodes19,
> Characteristic19,
> CharacteristicCodes20,
> LocationCodes20,
> ProcessCodes20,
> Characteristic20,
> CharacteristicCodes21,
> LocationCodes21,
> ProcessCodes21,
> Characteristic21,
> Comments,
> AnnualCSIConfirmation,
> DataRowState
> FROM
> Main
> WHERE
> (AnnualCSIConfirmation = 'Yes') AND
> (PartStatus = 'Active (buy part)') OR (PartStatus = 'Active (make
> part)') OR (PartStatus = 'Active (buy/make part)') OR (PartStatus = 'None
On
> Order (Inactive)') ORDER BY MainID
>
> "Chandra" wrote:
>|||>> I have 99 columns and I'm using a Jet 4.0 OLE DB for Access. I'm now
You have a poorly designed table. Any shuffling you do without a thorough
analysis of your business model and logic rules, might still leave the
schema unmaintainable and make the queries complex.
Instead of splitting up a single set of attribute into groups of {1st set of
cols}, {2nd of cols}, ... {n-th set of cols}, consider redesigning the table
better. Understand and analyze the entities in question and identify each of
the attributes explicitly. Identify the dependencies among these attributes
and consider decomposing them into multiple tables appropriately, if
required.
Even, without considering any ill-effects of under normalized schemas, with
certain assumptions based on the query you posted, you might benefit from
having your table structured like:
CREATE TABLE Parts (
Part_nbr INT NOT NULL PRIMARY KEY,
.. ) ;
CREATE TABLE Characteristics (
Character_id INT NOT NULL ,
Part_nbr INT NOT NULL
REFERENCES Parts ( Part_nbr ) ON UPDATE...ON DELETE...
Code ...
Characteristic...
PRIMARY KEY ( Character_id, Part_nbr ) );
CREATE TABLE Processes (
Process_id INT NOT NULL,
Part_nbr INT NOT NULL
REFERENCES Parts ( Part_nbr ) ON UPDATE...ON DELETE...
Process_code ...
PRIMARY KEY ( Process_id, Part_nbr ) );
CREATE TABLE Locations (
Location_id INT NOT NULL,
Part_nbr INT NOT NULL
REFERENCES Parts ( Part_nbr ) ON UPDATE...ON DELETE...
Location_code ...
PRIMARY KEY ( Location_id, Part_nbr );
Now the query is as simple as:
SELECT p1.Part_nbr,...
c1.code, ...
p2.process_code,...
l1.location_code...
FROM Parts p1
INNER JOIN Characteristics c1 ON p1.Part_nbr = c1.Part_nbr
INNER JOIN Processes p2 ON p1.Part_nbr = p2.Part_nbr
INNER JOIN Locations l1 ON p1.Part_nbr = l1.Part_nbr
...
This could be your comparable resultset which you might consider using for a
variety of purposes. For the specific case you mentioned in your post, you
can simply pivot this data, either using your client programming language or
if done within the server, using the popular pivoting technique detailed in
MSKB ( support.microsoft.com ) : 175574
Anith|||Thank You Chandra. That was an easy fix but I regret it didn't work. It wa
s
definitly worth a try in order to escape. I agree. Thanks Steve
"Chandra" wrote:
> hi
> looks like u need to normalize your table:
> something like:
> MainID,
> Charid,
> CharacteristicCodes,
> ProcessCodes,
> LocationCodes,
> Characteristic,
> but, this is out of scope now:
> just try to remove "ORDER BY MainID" and check, it might speed up the
> process and u can escape from the error.
> or
> where part:
> AnnualCSIConfirmation = 'Yes' AND
> PartStatus IN ( 'Active (buy part)', 'Active (make part)', 'Active
> (buy/make part)', 'None On Order (Inactive)' )
> please revert back with the results
>
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://www.SQLResource.com/
> ---
>
> "Steve B." wrote:
>|||Anith,
Thank You for your input. I've sent a copy of your post to a coworker who
understands database design more then I - such is the problem with using
wizards. A few questions so I (we) can better understand the design/context
.
Assumptions:
1. You appear to be dividing the fields into 4 tables – Parts,
CharacteristicsCode (identified in post as Characteristics), Process and
Locations. There is an additional field Characteristic that varies with eac
h
of the other three fields. So for each of the [possible] 30 Characteristic’s
each of them have an associated CharacteristicsCode , Process and Location.
I guess the structure of all 4 tables (except Parts) are exactly the same an
d
doesn’t matter. With that said, the CharacteristicsCode, Process, Locatio
ns
and Characteristic tables will have the exact same table structure and kind
of relationship.
2. It’s assumed the Parts table will contain the part numbers and
associated part data fields like t nomenclatures, etc
3. Does PRIMARY KEY (…) mean you somehow combine the both fields? Is thi
s
the actual relationship, for example, the join from Process_id to Part_nbr ?
4. SQL statement: what’s p1, c1, etc. and/or what’s p1.Part_nbr, c1.cod
e, etc
I think I’m going to have to create it this before I fully understand it.
However, I’m not sure the Visual Studio (VS) Query Builder inside the VS
environment will allow me to create such an Access SQL statement as
described. The DataAdapter might just expand the SQL statement again in the
DataSet so, I might be right back to the beginning again. I can’t use
external Access queries for SELECT, UPDATE, DELETE etc. Please keep the
post open. Thank You.
Steve
"Anith Sen" wrote:
> You have a poorly designed table. Any shuffling you do without a thorough
> analysis of your business model and logic rules, might still leave the
> schema unmaintainable and make the queries complex.
> Instead of splitting up a single set of attribute into groups of {1st set
of
> cols}, {2nd of cols}, ... {n-th set of cols}, consider redesigning the tab
le
> better. Understand and analyze the entities in question and identify each
of
> the attributes explicitly. Identify the dependencies among these attribute
s
> and consider decomposing them into multiple tables appropriately, if
> required.
> Even, without considering any ill-effects of under normalized schemas, wit
h
> certain assumptions based on the query you posted, you might benefit from
> having your table structured like:
> CREATE TABLE Parts (
> Part_nbr INT NOT NULL PRIMARY KEY,
> ... ) ;
> CREATE TABLE Characteristics (
> Character_id INT NOT NULL ,
> Part_nbr INT NOT NULL
> REFERENCES Parts ( Part_nbr ) ON UPDATE...ON DELETE...
> Code ...
> Characteristic...
> PRIMARY KEY ( Character_id, Part_nbr ) );
> CREATE TABLE Processes (
> Process_id INT NOT NULL,
> Part_nbr INT NOT NULL
> REFERENCES Parts ( Part_nbr ) ON UPDATE...ON DELETE...
> Process_code ...
> PRIMARY KEY ( Process_id, Part_nbr ) );
> CREATE TABLE Locations (
> Location_id INT NOT NULL,
> Part_nbr INT NOT NULL
> REFERENCES Parts ( Part_nbr ) ON UPDATE...ON DELETE...
> Location_code ...
> PRIMARY KEY ( Location_id, Part_nbr );
> Now the query is as simple as:
> SELECT p1.Part_nbr,...
> c1.code, ...
> p2.process_code,...
> l1.location_code...
> FROM Parts p1
> INNER JOIN Characteristics c1 ON p1.Part_nbr = c1.Part_nbr
> INNER JOIN Processes p2 ON p1.Part_nbr = p2.Part_nbr
> INNER JOIN Locations l1 ON p1.Part_nbr = l1.Part_nbr
> ...
> This could be your comparable resultset which you might consider using for
a
> variety of purposes. For the specific case you mentioned in your post, you
> can simply pivot this data, either using your client programming language
or
> if done within the server, using the popular pivoting technique detailed i
n
> MSKB ( support.microsoft.com ) : 175574
> --
> Anith
>
>
"Changes not saved to database. Expression Too Complex"
Using: Visual Studio, C#, ADO.Net interface and MS-Access (OLE DB Jet)
Without diving into the details on the one-to-many 99 column Access (Main)
table , can someone tell me the reason for the error? Is it really the
complexity of the SQL statement? Would SQL server help me? Does SQL Server
allow update() of a queries. StevePlease send us the text of the query you are trying. Based on the little
data we have here, UPDATE() is likely not usable in your situation, since
this is only for use in triggers.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Steve B." <SteveB@.discussions.microsoft.com> wrote in message
news:6284FB15-F8CE-4BDA-9060-B4B09428F4B6@.microsoft.com...
> DataAdapter.update(dataSet) exception Error:
> "Changes not saved to database. Expression Too Complex"
> Using: Visual Studio, C#, ADO.Net interface and MS-Access (OLE DB Jet)
> Without diving into the details on the one-to-many 99 column Access (Main)
> table , can someone tell me the reason for the error? Is it really the
> complexity of the SQL statement? Would SQL server help me? Does SQL
> Server
> allow update() of a queries. Steve|||hi steve
SQL Server allows updates. there might be an error in the sql string that u
were trying to pass.
please check the query and revert back.
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"Steve B." wrote:
> DataAdapter.update(dataSet) exception Error:
> "Changes not saved to database. Expression Too Complex"
> Using: Visual Studio, C#, ADO.Net interface and MS-Access (OLE DB Jet)
> Without diving into the details on the one-to-many 99 column Access (Main)
> table , can someone tell me the reason for the error? Is it really the
> complexity of the SQL statement? Would SQL server help me? Does SQL Serve
r
> allow update() of a queries. Steve|||> DataAdapter.update(dataSet) exception Error:
> "Changes not saved to database. Expression Too Complex"
> Using: Visual Studio, C#, ADO.Net interface and MS-Access (OLE DB Jet)
> Without diving into the details on the one-to-many 99 column Access (Main)
> table , can someone tell me the reason for the error? Is it really the
> complexity of the SQL statement? Would SQL server help me? Does SQL
> Server
> allow update() of a queries.
Just off the top of my head, each version of Access has a different maximum
limit on how long an SQL statement is allowed to be. I have actually gotten
that error. That's all I can think of though.
Peace & happy computing,
Mike Labosh, MCSD
"Musha ring dum a doo dum a da!" -- James Hetfield|||I hope this is what your looking for (see below). In the mean time, I found
this (please see first few paragraphs):
http://support.microsoft.com/defaul...kb;en-us;192716
I have 99 columns and I'm using a Jet 4.0 OLE DB for Access. I'm now
thinking of dividing my Main table into 4 or 5 tables indexed on part number
(part data table, Chara. 1-10 table, chara. 11-20 data table, etc) as a
workaround. What a mess..
The CharacteristicCodes, ProcessCodes and LocationCodes fields are indexed
(dropdown) from their own table in the "Main" table but, every Characteristi
c
field is different (blueprint dimensions, note number, etc) . I really nee
d
30 characteristics not 21. Can't get there from here.
I don't know if a commercial database program like SQL server will help me.
Thats why I'm asking. Comments?
(copied from VS Dataadapter wizard)
SELECT
MainID,
PartNumber,
Nomenclature,
PartStatus,
CoverageBy,
CoverageByDate,
Engine,
Service,
Originator,
RequirementSourceCodes,
PartPointOfContact,
VerificationDate,
CharacteristicCodes1,
ProcessCodes1,
LocationCodes1,
Characteristic1,
CharacteristicCodes2,
LocationCodes2,
ProcessCodes2,
Characteristic2,
CharacteristicCodes3,
LocationCodes3,
ProcessCodes3,
Characteristic3,
CharacteristicCodes4,
LocationCodes4,
ProcessCodes4,
Characteristic4,
CharacteristicCodes5,
LocationCodes5,
ProcessCodes5,
Characteristic5,
CharacteristicCodes6,
LocationCodes6,
ProcessCodes6,
Characteristic6,
CharacteristicCodes7,
LocationCodes7,
ProcessCodes7,
Characteristic7,
CharacteristicCodes8,
LocationCodes8,
ProcessCodes8,
Characteristic8,
CharacteristicCodes9,
LocationCodes9,
ProcessCodes9,
Characteristic9,
CharacteristicCodes10,
LocationCodes10,
ProcessCodes10,
Characteristic10,
CharacteristicCodes11,
LocationCodes11,
ProcessCodes11,
Characteristic11,
CharacteristicCodes12,
LocationCodes12,
ProcessCodes12,
Characteristic12,
CharacteristicCodes13,
LocationCodes13,
ProcessCodes13,
Characteristic13,
CharacteristicCodes14,
LocationCodes14,
ProcessCodes14,
Characteristic14,
CharacteristicCodes15,
LocationCodes15,
ProcessCodes15,
Characteristic15,
CharacteristicCodes16,
LocationCodes16,
ProcessCodes16,
Characteristic16,
CharacteristicCodes17,
LocationCodes17,
ProcessCodes17,
Characteristic17,
CharacteristicCodes18,
LocationCodes18,
ProcessCodes18,
Characteristic18,
CharacteristicCodes19,
LocationCodes19,
ProcessCodes19,
Characteristic19,
CharacteristicCodes20,
LocationCodes20,
ProcessCodes20,
Characteristic20,
CharacteristicCodes21,
LocationCodes21,
ProcessCodes21,
Characteristic21,
Comments,
AnnualCSIConfirmation,
DataRowState
FROM
Main
WHERE
(AnnualCSIConfirmation = 'Yes') AND
(PartStatus = 'Active (buy part)') OR (PartStatus = 'Active (make
part)') OR (PartStatus = 'Active (buy/make part)') OR (PartStatus = 'None On
Order (Inactive)') ORDER BY MainID
"Chandra" wrote:
> hi steve
> SQL Server allows updates. there might be an error in the sql string that
u
> were trying to pass.
> please check the query and revert back.
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://www.SQLResource.com/
> ---
>
> "Steve B." wrote:
>|||Yes!
i agree with you. The message mentions that, the query must be optimized.
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"Mike Labosh" wrote:
> Just off the top of my head, each version of Access has a different maximu
m
> limit on how long an SQL statement is allowed to be. I have actually gott
en
> that error. That's all I can think of though.
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "Musha ring dum a doo dum a da!" -- James Hetfield
>
>|||hi
looks like u need to normalize your table:
something like:
MainID,
Charid,
CharacteristicCodes,
ProcessCodes,
LocationCodes,
Characteristic,
but, this is out of scope now:
just try to remove "ORDER BY MainID" and check, it might speed up the
process and u can escape from the error.
or
where part:
AnnualCSIConfirmation = 'Yes' AND
PartStatus IN ( 'Active (buy part)', 'Active (make part)', 'Active
(buy/make part)', 'None On Order (Inactive)' )
please revert back with the results
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"Steve B." wrote:
> I hope this is what your looking for (see below). In the mean time, I fou
nd
> this (please see first few paragraphs):
> http://support.microsoft.com/defaul...kb;en-us;192716
> I have 99 columns and I'm using a Jet 4.0 OLE DB for Access. I'm now
> thinking of dividing my Main table into 4 or 5 tables indexed on part numb
er
> (part data table, Chara. 1-10 table, chara. 11-20 data table, etc) as a
> workaround. What a mess..
> The CharacteristicCodes, ProcessCodes and LocationCodes fields are indexe
d
> (dropdown) from their own table in the "Main" table but, every Characteris
tic
> field is different (blueprint dimensions, note number, etc) . I really n
eed
> 30 characteristics not 21. Can't get there from here.
> I don't know if a commercial database program like SQL server will help me
.
> Thats why I'm asking. Comments?
> (copied from VS Dataadapter wizard)
> SELECT
> MainID,
> PartNumber,
> Nomenclature,
> PartStatus,
> CoverageBy,
> CoverageByDate,
> Engine,
> Service,
> Originator,
> RequirementSourceCodes,
> PartPointOfContact,
> VerificationDate,
> CharacteristicCodes1,
> ProcessCodes1,
> LocationCodes1,
> Characteristic1,
> CharacteristicCodes2,
> LocationCodes2,
> ProcessCodes2,
> Characteristic2,
> CharacteristicCodes3,
> LocationCodes3,
> ProcessCodes3,
> Characteristic3,
> CharacteristicCodes4,
> LocationCodes4,
> ProcessCodes4,
> Characteristic4,
> CharacteristicCodes5,
> LocationCodes5,
> ProcessCodes5,
> Characteristic5,
> CharacteristicCodes6,
> LocationCodes6,
> ProcessCodes6,
> Characteristic6,
> CharacteristicCodes7,
> LocationCodes7,
> ProcessCodes7,
> Characteristic7,
> CharacteristicCodes8,
> LocationCodes8,
> ProcessCodes8,
> Characteristic8,
> CharacteristicCodes9,
> LocationCodes9,
> ProcessCodes9,
> Characteristic9,
> CharacteristicCodes10,
> LocationCodes10,
> ProcessCodes10,
> Characteristic10,
> CharacteristicCodes11,
> LocationCodes11,
> ProcessCodes11,
> Characteristic11,
> CharacteristicCodes12,
> LocationCodes12,
> ProcessCodes12,
> Characteristic12,
> CharacteristicCodes13,
> LocationCodes13,
> ProcessCodes13,
> Characteristic13,
> CharacteristicCodes14,
> LocationCodes14,
> ProcessCodes14,
> Characteristic14,
> CharacteristicCodes15,
> LocationCodes15,
> ProcessCodes15,
> Characteristic15,
> CharacteristicCodes16,
> LocationCodes16,
> ProcessCodes16,
> Characteristic16,
> CharacteristicCodes17,
> LocationCodes17,
> ProcessCodes17,
> Characteristic17,
> CharacteristicCodes18,
> LocationCodes18,
> ProcessCodes18,
> Characteristic18,
> CharacteristicCodes19,
> LocationCodes19,
> ProcessCodes19,
> Characteristic19,
> CharacteristicCodes20,
> LocationCodes20,
> ProcessCodes20,
> Characteristic20,
> CharacteristicCodes21,
> LocationCodes21,
> ProcessCodes21,
> Characteristic21,
> Comments,
> AnnualCSIConfirmation,
> DataRowState
> FROM
> Main
> WHERE
> (AnnualCSIConfirmation = 'Yes') AND
> (PartStatus = 'Active (buy part)') OR (PartStatus = 'Active (make
> part)') OR (PartStatus = 'Active (buy/make part)') OR (PartStatus = 'None
On
> Order (Inactive)') ORDER BY MainID
>
> "Chandra" wrote:
>|||>> I have 99 columns and I'm using a Jet 4.0 OLE DB for Access. I'm now
You have a poorly designed table. Any shuffling you do without a thorough
analysis of your business model and logic rules, might still leave the
schema unmaintainable and make the queries complex.
Instead of splitting up a single set of attribute into groups of {1st set of
cols}, {2nd of cols}, ... {n-th set of cols}, consider redesigning the table
better. Understand and analyze the entities in question and identify each of
the attributes explicitly. Identify the dependencies among these attributes
and consider decomposing them into multiple tables appropriately, if
required.
Even, without considering any ill-effects of under normalized schemas, with
certain assumptions based on the query you posted, you might benefit from
having your table structured like:
CREATE TABLE Parts (
Part_nbr INT NOT NULL PRIMARY KEY,
.. ) ;
CREATE TABLE Characteristics (
Character_id INT NOT NULL ,
Part_nbr INT NOT NULL
REFERENCES Parts ( Part_nbr ) ON UPDATE...ON DELETE...
Code ...
Characteristic...
PRIMARY KEY ( Character_id, Part_nbr ) );
CREATE TABLE Processes (
Process_id INT NOT NULL,
Part_nbr INT NOT NULL
REFERENCES Parts ( Part_nbr ) ON UPDATE...ON DELETE...
Process_code ...
PRIMARY KEY ( Process_id, Part_nbr ) );
CREATE TABLE Locations (
Location_id INT NOT NULL,
Part_nbr INT NOT NULL
REFERENCES Parts ( Part_nbr ) ON UPDATE...ON DELETE...
Location_code ...
PRIMARY KEY ( Location_id, Part_nbr );
Now the query is as simple as:
SELECT p1.Part_nbr,...
c1.code, ...
p2.process_code,...
l1.location_code...
FROM Parts p1
INNER JOIN Characteristics c1 ON p1.Part_nbr = c1.Part_nbr
INNER JOIN Processes p2 ON p1.Part_nbr = p2.Part_nbr
INNER JOIN Locations l1 ON p1.Part_nbr = l1.Part_nbr
...
This could be your comparable resultset which you might consider using for a
variety of purposes. For the specific case you mentioned in your post, you
can simply pivot this data, either using your client programming language or
if done within the server, using the popular pivoting technique detailed in
MSKB ( support.microsoft.com ) : 175574
Anith|||Thank You Chandra. That was an easy fix but I regret it didn't work. It wa
s
definitly worth a try in order to escape. I agree. Thanks Steve
"Chandra" wrote:
> hi
> looks like u need to normalize your table:
> something like:
> MainID,
> Charid,
> CharacteristicCodes,
> ProcessCodes,
> LocationCodes,
> Characteristic,
> but, this is out of scope now:
> just try to remove "ORDER BY MainID" and check, it might speed up the
> process and u can escape from the error.
> or
> where part:
> AnnualCSIConfirmation = 'Yes' AND
> PartStatus IN ( 'Active (buy part)', 'Active (make part)', 'Active
> (buy/make part)', 'None On Order (Inactive)' )
> please revert back with the results
>
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://www.SQLResource.com/
> ---
>
> "Steve B." wrote:
>|||Anith,
Thank You for your input. I've sent a copy of your post to a coworker who
understands database design more then I - such is the problem with using
wizards. A few questions so I (we) can better understand the design/context
.
Assumptions:
1. You appear to be dividing the fields into 4 tables – Parts,
CharacteristicsCode (identified in post as Characteristics), Process and
Locations. There is an additional field Characteristic that varies with eac
h
of the other three fields. So for each of the [possible] 30 Characteristic’s
each of them have an associated CharacteristicsCode , Process and Location.
I guess the structure of all 4 tables (except Parts) are exactly the same an
d
doesn’t matter. With that said, the CharacteristicsCode, Process, Locatio
ns
and Characteristic tables will have the exact same table structure and kind
of relationship.
2. It’s assumed the Parts table will contain the part numbers and
associated part data fields like t nomenclatures, etc
3. Does PRIMARY KEY (…) mean you somehow combine the both fields? Is thi
s
the actual relationship, for example, the join from Process_id to Part_nbr ?
4. SQL statement: what’s p1, c1, etc. and/or what’s p1.Part_nbr, c1.cod
e, etc
I think I’m going to have to create it this before I fully understand it.
However, I’m not sure the Visual Studio (VS) Query Builder inside the VS
environment will allow me to create such an Access SQL statement as
described. The DataAdapter might just expand the SQL statement again in the
DataSet so, I might be right back to the beginning again. I can’t use
external Access queries for SELECT, UPDATE, DELETE etc. Please keep the
post open. Thank You.
Steve
"Anith Sen" wrote:
> You have a poorly designed table. Any shuffling you do without a thorough
> analysis of your business model and logic rules, might still leave the
> schema unmaintainable and make the queries complex.
> Instead of splitting up a single set of attribute into groups of {1st set
of
> cols}, {2nd of cols}, ... {n-th set of cols}, consider redesigning the tab
le
> better. Understand and analyze the entities in question and identify each
of
> the attributes explicitly. Identify the dependencies among these attribute
s
> and consider decomposing them into multiple tables appropriately, if
> required.
> Even, without considering any ill-effects of under normalized schemas, wit
h
> certain assumptions based on the query you posted, you might benefit from
> having your table structured like:
> CREATE TABLE Parts (
> Part_nbr INT NOT NULL PRIMARY KEY,
> ... ) ;
> CREATE TABLE Characteristics (
> Character_id INT NOT NULL ,
> Part_nbr INT NOT NULL
> REFERENCES Parts ( Part_nbr ) ON UPDATE...ON DELETE...
> Code ...
> Characteristic...
> PRIMARY KEY ( Character_id, Part_nbr ) );
> CREATE TABLE Processes (
> Process_id INT NOT NULL,
> Part_nbr INT NOT NULL
> REFERENCES Parts ( Part_nbr ) ON UPDATE...ON DELETE...
> Process_code ...
> PRIMARY KEY ( Process_id, Part_nbr ) );
> CREATE TABLE Locations (
> Location_id INT NOT NULL,
> Part_nbr INT NOT NULL
> REFERENCES Parts ( Part_nbr ) ON UPDATE...ON DELETE...
> Location_code ...
> PRIMARY KEY ( Location_id, Part_nbr );
> Now the query is as simple as:
> SELECT p1.Part_nbr,...
> c1.code, ...
> p2.process_code,...
> l1.location_code...
> FROM Parts p1
> INNER JOIN Characteristics c1 ON p1.Part_nbr = c1.Part_nbr
> INNER JOIN Processes p2 ON p1.Part_nbr = p2.Part_nbr
> INNER JOIN Locations l1 ON p1.Part_nbr = l1.Part_nbr
> ...
> This could be your comparable resultset which you might consider using for
a
> variety of purposes. For the specific case you mentioned in your post, you
> can simply pivot this data, either using your client programming language
or
> if done within the server, using the popular pivoting technique detailed i
n
> MSKB ( support.microsoft.com ) : 175574
> --
> Anith
>
>
Sunday, February 26, 2012
Express SP2 install error
Hi, try to install (not an upgrade) SQLExpressSP2 on WinXPSP2 from SQLEXPR32.exe package.
.NET version 2.0 is already installed however get error below before last step of Server setup ( Excerpt from C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Files\SQLSetup0001_ISD02_Core(Local) ):
</- START OF Excerpt >
<Func Name='PerformDetections'>
0
<EndFunc Name='PerformDetections' Return='0' GetLastError='0'>
<Func Name='DisplaySCCWizard'>
CSetupBootstrapWizard returned 1
<EndFunc Name='DisplaySCCWizard' Return='0' GetLastError='183'>
Loaded DLL:C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\sqlsval.dll Version:2005.90.3042.0
<EndFunc Name='DwLaunchMsiExec' Return='1603' GetLastError='0'>
Complete: InvokeSqlSetupDllAction at: 2007/2/14 16:58:23, returned false
Error: Action "InvokeSqlSetupDllAction" failed during execution.
Action "AnalyzeInstallStage" will be skipped due to the following restrictions:
Condition "Action: InvokeSqlSetupDllAction has finished and passed." returned false.
Action "PrepareForChainingStage" will be skipped due to the following restrictions:
Condition "Action: InvokeSqlSetupDllAction has finished and passed." returned false.
Action "SequenceChainingActionsStage" will be skipped due to the following restrictions:
Condition "Action: InvokeSqlSetupDllAction has finished and passed." returned false.
Action "InstallChainedPackagesStage" will be skipped due to the following restrictions:
Condition "Action: InvokeSqlSetupDllAction has finished and passed." returned false.
Error: Action "ExposeVistaClusteredResources" failed during execution. Error information reported during run:
Action: "ExposeVistaClusteredResources" will be marked as failed due to the following condition:
Condition "The Clustered SQL Server instance that hosts is installed." returned false.
Running: ReportChainingResults at: 2007/2/14 16:58:23
Error: Action "ReportChainingResults" threw an exception during execution.
DwLaunchMsiExec() returned : 1603
Error Code: 0x80070643 (1603)
Windows Error Text: Fatal error during installation.
Source File Name: sqlchaining\sqlchainingactions.cpp
Compiler Timestamp: Thu Nov 16 20:31:57 2006
Function Name: sqls::ReportChainingResults::perform
Source Line Number: 3416
Error: Failed to add file :"C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Files\SQLSetup0001_ISD02_.NET Framework 2.0.log" to cab file : "C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\SqlSetup0001.cab" Error Code : 2
Running: UploadDrWatsonLogAction at: 2007/2/14 16:58:23
Message pump returning: 1603
</- END OF Excerpt >
Cud u please advise workaround?
cheers,
Fred
N.B.
Also have tried to register my installation of SQL Express SP2 but the registration web page has a bug and
does not enable me to register at https://profile.microsoft.com/ProductActivation/ProductActivation.aspx?lcid=1033&WizId=0569136c-78c8-454b-bf27-cb7d7863de63
This message appears in "Red' even when legitimate email-address was entered:
"*Required: E-Mail Address"
cheers,
Fred
|||Tyodotnet wrote:
Hi, try to install (not an upgrade) SQLExpressSP2 on WinXPSP2 from SQLEXPR32.exe package.
.NET version 2.0 is already installed however get error below before last step of Server setup ( Excerpt from C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Files\SQLSetup0001_ISD02_Core(Local) ):
</- START OF Excerpt >
<Func Name='PerformDetections'>
0
<EndFunc Name='PerformDetections' Return='0' GetLastError='0'>
<Func Name='DisplaySCCWizard'>
CSetupBootstrapWizard returned 1
<EndFunc Name='DisplaySCCWizard' Return='0' GetLastError='183'>
Loaded DLL:C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\sqlsval.dll Version:2005.90.3042.0
<EndFunc Name='DwLaunchMsiExec' Return='1603' GetLastError='0'>
Complete: InvokeSqlSetupDllAction at: 2007/2/14 16:58:23, returned false
Error: Action "InvokeSqlSetupDllAction" failed during execution.
Action "AnalyzeInstallStage" will be skipped due to the following restrictions:
Condition "Action: InvokeSqlSetupDllAction has finished and passed." returned false.
Action "PrepareForChainingStage" will be skipped due to the following restrictions:
Condition "Action: InvokeSqlSetupDllAction has finished and passed." returned false.
Action "SequenceChainingActionsStage" will be skipped due to the following restrictions:
Condition "Action: InvokeSqlSetupDllAction has finished and passed." returned false.
Action "InstallChainedPackagesStage" will be skipped due to the following restrictions:
Condition "Action: InvokeSqlSetupDllAction has finished and passed." returned false.
Error: Action "ExposeVistaClusteredResources" failed during execution. Error information reported during run:
Action: "ExposeVistaClusteredResources" will be marked as failed due to the following condition:
Condition "The Clustered SQL Server instance that hosts is installed." returned false.
Running: ReportChainingResults at: 2007/2/14 16:58:23
Error: Action "ReportChainingResults" threw an exception during execution.
DwLaunchMsiExec() returned : 1603
Error Code: 0x80070643 (1603)
Windows Error Text: Fatal error during installation.Source File Name: sqlchaining\sqlchainingactions.cpp
Compiler Timestamp: Thu Nov 16 20:31:57 2006
Function Name: sqls::ReportChainingResults::perform
Source Line Number: 3416Error: Failed to add file :"C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Files\SQLSetup0001_ISD02_.NET Framework 2.0.log" to cab file : "C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\SqlSetup0001.cab" Error Code : 2
Running: UploadDrWatsonLogAction at: 2007/2/14 16:58:23
Message pump returning: 1603
</- END OF Excerpt >Cud u please advise workaround?
cheers,
Fred
Not finding much on this one, I'm going to move this to the setup forum so the experts can take a look.
Mike
|||Hi Mike,
Thanks for your assistance.
Meanwhile I have found out the problem i.e:
1. Instead of running SQLEXPR32.EXE I have extracted the contents
into a new folder using the commandline SQLEXPR32.EXE -X
2. I have created an autostart CD from the extracted data.
3. Run the CD, autorun will open splash.hta automatically.
This installation menu will appear:
Prepare
Review hardware and software requirements
Read the release notes
Install
Run the SQL Server Installation Wizard
Run the SQL Native Client Installation Wizard
Other Information
Browse this CD
Visit the SQL Server website
Read the SQL Server license agreement
4. Run the SQL Native Client Installation Wizard, with success,
installation closes all screens.
5. Run CD again and run the SQL Server Installation Wizard
got into error: current user (the Administrator!)
has not enough access rights to write in "C:\Program Files\Microsoft SQL Server"
6. Check and change access control with Explorer "C:\Program Files\Microsoft SQL Server"
Indeed it was totally unwritable by Admin!
7. Run CD again and the SQL Server Installation Wizard is running flawless.
All steps can be finished.
cheers,
Fred
Express SP2 install error
Hi, try to install (not an upgrade) SQLExpressSP2 on WinXPSP2 from SQLEXPR32.exe package.
.NET version 2.0 is already installed however get error below before last step of Server setup ( Excerpt from C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Files\SQLSetup0001_ISD02_Core(Local) ):
</- START OF Excerpt >
<Func Name='PerformDetections'>
0
<EndFunc Name='PerformDetections' Return='0' GetLastError='0'>
<Func Name='DisplaySCCWizard'>
CSetupBootstrapWizard returned 1
<EndFunc Name='DisplaySCCWizard' Return='0' GetLastError='183'>
Loaded DLL:C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\sqlsval.dll Version:2005.90.3042.0
<EndFunc Name='DwLaunchMsiExec' Return='1603' GetLastError='0'>
Complete: InvokeSqlSetupDllAction at: 2007/2/14 16:58:23, returned false
Error: Action "InvokeSqlSetupDllAction" failed during execution.
Action "AnalyzeInstallStage" will be skipped due to the following restrictions:
Condition "Action: InvokeSqlSetupDllAction has finished and passed." returned false.
Action "PrepareForChainingStage" will be skipped due to the following restrictions:
Condition "Action: InvokeSqlSetupDllAction has finished and passed." returned false.
Action "SequenceChainingActionsStage" will be skipped due to the following restrictions:
Condition "Action: InvokeSqlSetupDllAction has finished and passed." returned false.
Action "InstallChainedPackagesStage" will be skipped due to the following restrictions:
Condition "Action: InvokeSqlSetupDllAction has finished and passed." returned false.
Error: Action "ExposeVistaClusteredResources" failed during execution. Error information reported during run:
Action: "ExposeVistaClusteredResources" will be marked as failed due to the following condition:
Condition "The Clustered SQL Server instance that hosts is installed." returned false.
Running: ReportChainingResults at: 2007/2/14 16:58:23
Error: Action "ReportChainingResults" threw an exception during execution.
DwLaunchMsiExec() returned : 1603
Error Code: 0x80070643 (1603)
Windows Error Text: Fatal error during installation.
Source File Name: sqlchaining\sqlchainingactions.cpp
Compiler Timestamp: Thu Nov 16 20:31:57 2006
Function Name: sqls::ReportChainingResults::perform
Source Line Number: 3416
Error: Failed to add file :"C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Files\SQLSetup0001_ISD02_.NET Framework 2.0.log" to cab file : "C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\SqlSetup0001.cab" Error Code : 2
Running: UploadDrWatsonLogAction at: 2007/2/14 16:58:23
Message pump returning: 1603
</- END OF Excerpt >
Cud u please advise workaround?
cheers,
Fred
N.B.
Also have tried to register my installation of SQL Express SP2 but the registration web page has a bug and
does not enable me to register at https://profile.microsoft.com/ProductActivation/ProductActivation.aspx?lcid=1033&WizId=0569136c-78c8-454b-bf27-cb7d7863de63
This message appears in "Red' even when legitimate email-address was entered:
"*Required: E-Mail Address"
cheers,
Fred
|||Hi, try to install (not an upgrade) SQLExpressSP2 on WinXPSP2 from SQLEXPR32.exe package.
.NET version 2.0 is already installed however get error below before last step of Server setup ( Excerpt from C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Files\SQLSetup0001_ISD02_Core(Local) ):
</- START OF Excerpt >
<Func Name='PerformDetections'>
0
<EndFunc Name='PerformDetections' Return='0' GetLastError='0'>
<Func Name='DisplaySCCWizard'>
CSetupBootstrapWizard returned 1
<EndFunc Name='DisplaySCCWizard' Return='0' GetLastError='183'>
Loaded DLL:C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\sqlsval.dll Version:2005.90.3042.0
<EndFunc Name='DwLaunchMsiExec' Return='1603' GetLastError='0'>
Complete: InvokeSqlSetupDllAction at: 2007/2/14 16:58:23, returned false
Error: Action "InvokeSqlSetupDllAction" failed during execution.
Action "AnalyzeInstallStage" will be skipped due to the following restrictions:
Condition "Action: InvokeSqlSetupDllAction has finished and passed." returned false.
Action "PrepareForChainingStage" will be skipped due to the following restrictions:
Condition "Action: InvokeSqlSetupDllAction has finished and passed." returned false.
Action "SequenceChainingActionsStage" will be skipped due to the following restrictions:
Condition "Action: InvokeSqlSetupDllAction has finished and passed." returned false.
Action "InstallChainedPackagesStage" will be skipped due to the following restrictions:
Condition "Action: InvokeSqlSetupDllAction has finished and passed." returned false.
Error: Action "ExposeVistaClusteredResources" failed during execution. Error information reported during run:
Action: "ExposeVistaClusteredResources" will be marked as failed due to the following condition:
Condition "The Clustered SQL Server instance that hosts is installed." returned false.
Running: ReportChainingResults at: 2007/2/14 16:58:23
Error: Action "ReportChainingResults" threw an exception during execution.
DwLaunchMsiExec() returned : 1603
Error Code: 0x80070643 (1603)
Windows Error Text: Fatal error during installation.Source File Name: sqlchaining\sqlchainingactions.cpp
Compiler Timestamp: Thu Nov 16 20:31:57 2006
Function Name: sqls::ReportChainingResults::perform
Source Line Number: 3416Error: Failed to add file :"C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Files\SQLSetup0001_ISD02_.NET Framework 2.0.log" to cab file : "C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\SqlSetup0001.cab" Error Code : 2
Running: UploadDrWatsonLogAction at: 2007/2/14 16:58:23
Message pump returning: 1603
</- END OF Excerpt >Cud u please advise workaround?
cheers,
Fred
Not finding much on this one, I'm going to move this to the setup forum so the experts can take a look.
Mike
|||Hi Mike,
Thanks for your assistance.
Meanwhile I have found out the problem i.e:
1. Instead of running SQLEXPR32.EXE I have extracted the contents
into a new folder using the commandline SQLEXPR32.EXE -X
2. I have created an autostart CD from the extracted data.
3. Run the CD, autorun will open splash.hta automatically.
This installation menu will appear:
Prepare
Review hardware and software requirements
Read the release notes
Install
Run the SQL Server Installation Wizard
Run the SQL Native Client Installation Wizard
Other Information
Browse this CD
Visit the SQL Server website
Read the SQL Server license agreement
4. Run the SQL Native Client Installation Wizard, with success,
installation closes all screens.
5. Run CD again and run the SQL Server Installation Wizard
got into error: current user (the Administrator!)
has not enough access rights to write in "C:\Program Files\Microsoft SQL Server"
6. Check and change access control with Explorer "C:\Program Files\Microsoft SQL Server"
Indeed it was totally unwritable by Admin!
7. Run CD again and the SQL Server Installation Wizard is running flawless.
All steps can be finished.
cheers,
Fred
Friday, February 24, 2012
Express and AspNet_RegSql.exe
Can the Asp Net exe sql script be used to add the aspnetdb database to an Sq
l
Express?
I am doing this for running the App Blocks.
The error that comes back is "can not connect".
Thaks,
MarcCheck to see if you need to specify the instance name for the connection. I
know that when I installed a CTP version of 2005, the "standard" installatio
n
created a non-default instance.
"Marc" wrote:
> Can the Asp Net exe sql script be used to add the aspnetdb database to an
Sql
> Express?
> I am doing this for running the App Blocks.
> The error that comes back is "can not connect".
> Thaks,
> Marc
>
l
Express?
I am doing this for running the App Blocks.
The error that comes back is "can not connect".
Thaks,
MarcCheck to see if you need to specify the instance name for the connection. I
know that when I installed a CTP version of 2005, the "standard" installatio
n
created a non-default instance.
"Marc" wrote:
> Can the Asp Net exe sql script be used to add the aspnetdb database to an
Sql
> Express?
> I am doing this for running the App Blocks.
> The error that comes back is "can not connect".
> Thaks,
> Marc
>
expose 1433 on net
I need to exposed 1433 on the net to enable a IIS6 pages to query remote SQL
servers.
Im looking for information on how to do this as securely as possible
(although im sure this is not ideal) ?
At the moment im using SQL authentication only.
Thanks for any information
ScottWhy not install SQLXML and take advantage of the easy stored procedure to
web services tool. Then you can apply an SSL certificate and have your web
site only talk to SQL Server over TCP 443. It's safer, it's encrypted, it's
extensible, and it's more portable. It's SOA!
hth,
Eric
scott wrote:
> I need to exposed 1433 on the net to enable a IIS6 pages to query
> remote SQL servers.
> Im looking for information on how to do this as securely as possible
> (although im sure this is not ideal) ?
> At the moment im using SQL authentication only.
> Thanks for any information
> Scott|||sounds excellent but how do you set it up ?
Currently my aspx app on IIS6 queries SQL on an IP addresson port 1433 and
pulls back XML.
How do i install SQLXML ?
Use the web service tool ?
Setup SSL ?
(guess I just forward port 443)
Sorry for all the questions
Scott|||you should start by reading about sqlxml at
http://www.microsoft.com/sql/techinfo/xml/default.asp
scott wrote:
> sounds excellent but how do you set it up ?
> Currently my aspx app on IIS6 queries SQL on an IP addresson port
> 1433 and pulls back XML.
> How do i install SQLXML ?
> Use the web service tool ?
> Setup SSL ?
> (guess I just forward port 443)
> Sorry for all the questions
> Scott
servers.
Im looking for information on how to do this as securely as possible
(although im sure this is not ideal) ?
At the moment im using SQL authentication only.
Thanks for any information
ScottWhy not install SQLXML and take advantage of the easy stored procedure to
web services tool. Then you can apply an SSL certificate and have your web
site only talk to SQL Server over TCP 443. It's safer, it's encrypted, it's
extensible, and it's more portable. It's SOA!
hth,
Eric
scott wrote:
> I need to exposed 1433 on the net to enable a IIS6 pages to query
> remote SQL servers.
> Im looking for information on how to do this as securely as possible
> (although im sure this is not ideal) ?
> At the moment im using SQL authentication only.
> Thanks for any information
> Scott|||sounds excellent but how do you set it up ?
Currently my aspx app on IIS6 queries SQL on an IP addresson port 1433 and
pulls back XML.
How do i install SQLXML ?
Use the web service tool ?
Setup SSL ?
(guess I just forward port 443)
Sorry for all the questions
Scott|||you should start by reading about sqlxml at
http://www.microsoft.com/sql/techinfo/xml/default.asp
scott wrote:
> sounds excellent but how do you set it up ?
> Currently my aspx app on IIS6 queries SQL on an IP addresson port
> 1433 and pulls back XML.
> How do i install SQLXML ?
> Use the web service tool ?
> Setup SSL ?
> (guess I just forward port 443)
> Sorry for all the questions
> Scott
expose 1433 on net
I need to exposed 1433 on the net to enable a IIS6 pages to query remote SQL
servers.
Im looking for information on how to do this as securely as possible
(although im sure this is not ideal) ?
At the moment im using SQL authentication only.
Thanks for any information
Scott
Why not install SQLXML and take advantage of the easy stored procedure to
web services tool. Then you can apply an SSL certificate and have your web
site only talk to SQL Server over TCP 443. It's safer, it's encrypted, it's
extensible, and it's more portable. It's SOA!
hth,
Eric
scott wrote:
> I need to exposed 1433 on the net to enable a IIS6 pages to query
> remote SQL servers.
> Im looking for information on how to do this as securely as possible
> (although im sure this is not ideal) ?
> At the moment im using SQL authentication only.
> Thanks for any information
> Scott
|||sounds excellent but how do you set it up ?
Currently my aspx app on IIS6 queries SQL on an IP addresson port 1433 and
pulls back XML.
How do i install SQLXML ?
Use the web service tool ?
Setup SSL ?
(guess I just forward port 443)
Sorry for all the questions
Scott
|||you should start by reading about sqlxml at
http://www.microsoft.com/sql/techinfo/xml/default.asp
scott wrote:
> sounds excellent but how do you set it up ?
> Currently my aspx app on IIS6 queries SQL on an IP addresson port
> 1433 and pulls back XML.
> How do i install SQLXML ?
> Use the web service tool ?
> Setup SSL ?
> (guess I just forward port 443)
> Sorry for all the questions
> Scott
servers.
Im looking for information on how to do this as securely as possible
(although im sure this is not ideal) ?
At the moment im using SQL authentication only.
Thanks for any information
Scott
Why not install SQLXML and take advantage of the easy stored procedure to
web services tool. Then you can apply an SSL certificate and have your web
site only talk to SQL Server over TCP 443. It's safer, it's encrypted, it's
extensible, and it's more portable. It's SOA!
hth,
Eric
scott wrote:
> I need to exposed 1433 on the net to enable a IIS6 pages to query
> remote SQL servers.
> Im looking for information on how to do this as securely as possible
> (although im sure this is not ideal) ?
> At the moment im using SQL authentication only.
> Thanks for any information
> Scott
|||sounds excellent but how do you set it up ?
Currently my aspx app on IIS6 queries SQL on an IP addresson port 1433 and
pulls back XML.
How do i install SQLXML ?
Use the web service tool ?
Setup SSL ?
(guess I just forward port 443)
Sorry for all the questions
Scott
|||you should start by reading about sqlxml at
http://www.microsoft.com/sql/techinfo/xml/default.asp
scott wrote:
> sounds excellent but how do you set it up ?
> Currently my aspx app on IIS6 queries SQL on an IP addresson port
> 1433 and pulls back XML.
> How do i install SQLXML ?
> Use the web service tool ?
> Setup SSL ?
> (guess I just forward port 443)
> Sorry for all the questions
> Scott
Exporting to word
Hi,
I'm using Crystal Reports in Microsoft Visual C# .NET version 1.1.
I've never used Crystal reports before, so I'm just finding my way around. I have created a very simply report embedded in a windows form which just displays a few columns from a database table.
My problem is this: When I click the "export" button I just get an egg timer.
I gather that there may be some dll's requirered for the exporting functionality to work. Could this be the problem? If so, where do I get then from? Or do I have to implement some code myself which will enable the export dialog to appear?
many thanks for any help offered.
update: I've just tried the exe on someone elses machine and it worked ok, so I think this is a problem with my .NET installation.http://support.businessobjects.com/library/kbase/articles/c2010787.asp
Hi,
I'm using Crystal Reports in Microsoft Visual C# .NET version 1.1.
I've never used Crystal reports before, so I'm just finding my way around. I have created a very simply report embedded in a windows form which just displays a few columns from a database table.
My problem is this: When I click the "export" button I just get an egg timer.
I gather that there may be some dll's requirered for the exporting functionality to work. Could this be the problem? If so, where do I get then from? Or do I have to implement some code myself which will enable the export dialog to appear?
many thanks for any help offered.
update: I've just tried the exe on someone elses machine and it worked ok, so I think this is a problem with my .NET installation.|||http://support.businessobjects.com/library/kbase/articles/c2010787.asp
Many thanks for the reply, but that isn't my problem.
When I click the "export" button I just get an egg timer. I am not trying to export a TIF, and I don't get any error message.
I'm using Crystal Reports in Microsoft Visual C# .NET version 1.1.
I've never used Crystal reports before, so I'm just finding my way around. I have created a very simply report embedded in a windows form which just displays a few columns from a database table.
My problem is this: When I click the "export" button I just get an egg timer.
I gather that there may be some dll's requirered for the exporting functionality to work. Could this be the problem? If so, where do I get then from? Or do I have to implement some code myself which will enable the export dialog to appear?
many thanks for any help offered.
update: I've just tried the exe on someone elses machine and it worked ok, so I think this is a problem with my .NET installation.http://support.businessobjects.com/library/kbase/articles/c2010787.asp
Hi,
I'm using Crystal Reports in Microsoft Visual C# .NET version 1.1.
I've never used Crystal reports before, so I'm just finding my way around. I have created a very simply report embedded in a windows form which just displays a few columns from a database table.
My problem is this: When I click the "export" button I just get an egg timer.
I gather that there may be some dll's requirered for the exporting functionality to work. Could this be the problem? If so, where do I get then from? Or do I have to implement some code myself which will enable the export dialog to appear?
many thanks for any help offered.
update: I've just tried the exe on someone elses machine and it worked ok, so I think this is a problem with my .NET installation.|||http://support.businessobjects.com/library/kbase/articles/c2010787.asp
Many thanks for the reply, but that isn't my problem.
When I click the "export" button I just get an egg timer. I am not trying to export a TIF, and I don't get any error message.
Sunday, February 19, 2012
Exporting to Excel; timeout
Exporting to Excel; timeout
I have 8000 rows in the report and trying to export excel in asp.net code, it does not export in the Report manager and it give exception saying “The underlying connection was closed: An unexpected error occurred on a receive.”
Small number of rows are exported correctly. Is there any setting I can change sin RS2005 web service
Follow this KB http://support.microsoft.com/default.aspx/kb/909678Friday, February 17, 2012
Exporting text files
Hi,
Is there any way to export .rpt files as text files. I am using crystal reports 9.0 and .Net framework..
I want to export them dynamically in the code.
I could able to export to pdf,doc and html..But not to text.
Thanks,
RameshYou have to include the right dlls in order for the export to work. I use Crystal Reports Version 8.5 and there's a list of the required files in a file called runtime.hlp. (The file may be called something different with other versions of Crystal)
There are 2 types of dlls to include when you want to export: Export Destinations (Application, Disk, MAPI, Lotus Domino, etc... ) and Export Formats (HTML, pdf, doc, text, etc... ). In order to be able to export to text, I had to include U2FTEXT.DLL (from the Export Formats, this dll exports to text format) and U2DDISK.DLL (from the Export Destinations, this dll exports to disk instead of application). I couldn't get it to work if I just included the file to output to application.
Just remember that this is for CR 8.5 and VB 6, so you may or may not be able to use it, but I hope it helps anyway!|||I want to export multiple crystal reports in one file.. i.e. only one text file / pdf file or any other format... in short i want to merge the reports.
can anyone help me in that.. ?|||Hi! i too face the same prob but the differance if i want to export multipal subreport with main report in one pdf file.
Any one who can help me out plz plz do reply its urgent.
Thanks in advance|||aj_patil: When you export the main report, the subreports will be exported with it. No need to do anything else.
dhmshah: Perhaps you can set each of your reports as subreports in one main report. Other than that, I'm not sure if multiple reports can be exported into 1 file.
Also, if you have a new question, you should post it in a new thread instead of onto an old thread. That way, other people who have the same question as you can find the answer easier.|||aj_patil: When you export the main report, the subreports will be exported with it. No need to do anything else.
dhmshah: Perhaps you can set each of your reports as subreports in one main report. Other than that, I'm not sure if multiple reports can be exported into 1 file.
Also, if you have a new question, you should post it in a new thread instead of onto an old thread. That way, other people who have the same question as you can find the answer easier.
Thanks for the advice but i hv tried, any way if u get any link hear after do write to me.|||I want to export multiple crystal reports in one file.. i.e. only one text file / pdf file or any other format... in short i want to merge the reports.
can anyone help me in that.. ?
thanks for the advice. but if u get any infomation related do write- aj
Is there any way to export .rpt files as text files. I am using crystal reports 9.0 and .Net framework..
I want to export them dynamically in the code.
I could able to export to pdf,doc and html..But not to text.
Thanks,
RameshYou have to include the right dlls in order for the export to work. I use Crystal Reports Version 8.5 and there's a list of the required files in a file called runtime.hlp. (The file may be called something different with other versions of Crystal)
There are 2 types of dlls to include when you want to export: Export Destinations (Application, Disk, MAPI, Lotus Domino, etc... ) and Export Formats (HTML, pdf, doc, text, etc... ). In order to be able to export to text, I had to include U2FTEXT.DLL (from the Export Formats, this dll exports to text format) and U2DDISK.DLL (from the Export Destinations, this dll exports to disk instead of application). I couldn't get it to work if I just included the file to output to application.
Just remember that this is for CR 8.5 and VB 6, so you may or may not be able to use it, but I hope it helps anyway!|||I want to export multiple crystal reports in one file.. i.e. only one text file / pdf file or any other format... in short i want to merge the reports.
can anyone help me in that.. ?|||Hi! i too face the same prob but the differance if i want to export multipal subreport with main report in one pdf file.
Any one who can help me out plz plz do reply its urgent.
Thanks in advance|||aj_patil: When you export the main report, the subreports will be exported with it. No need to do anything else.
dhmshah: Perhaps you can set each of your reports as subreports in one main report. Other than that, I'm not sure if multiple reports can be exported into 1 file.
Also, if you have a new question, you should post it in a new thread instead of onto an old thread. That way, other people who have the same question as you can find the answer easier.|||aj_patil: When you export the main report, the subreports will be exported with it. No need to do anything else.
dhmshah: Perhaps you can set each of your reports as subreports in one main report. Other than that, I'm not sure if multiple reports can be exported into 1 file.
Also, if you have a new question, you should post it in a new thread instead of onto an old thread. That way, other people who have the same question as you can find the answer easier.
Thanks for the advice but i hv tried, any way if u get any link hear after do write to me.|||I want to export multiple crystal reports in one file.. i.e. only one text file / pdf file or any other format... in short i want to merge the reports.
can anyone help me in that.. ?
thanks for the advice. but if u get any infomation related do write- aj
Subscribe to:
Posts (Atom)