Is there any utility, command, or query in SQL Server, that I can execute
from a batch mode to script out all of my database definitions (tables,
columns, sp, functions, indexes, constraints, triggers, etc.) so I can create
an automated routine to check database definitions?
--
Jason"JasonDWilson" <JasonDWilson@.discussions.microsoft.com> wrote in message
news:7B571D73-00F3-425B-BB96-555A028AC91F@.microsoft.com...
> Is there any utility, command, or query in SQL Server, that I can execute
> from a batch mode to script out all of my database definitions (tables,
> columns, sp, functions, indexes, constraints, triggers, etc.) so I can
> create
> an automated routine to check database definitions?
I wrote one some time ago, using DMO. I can provide compiled exe or sources
if you want. AFAIR works only with integrated security (kind of bug I never
really needed to fix). Works fine if you need to monitor database (or all
databases) schema.
Cheers,
Wojtek|||Yes, that would be great. Can I get both? I will need to modify to work
with sql authentication.
Thanks,
--
Jason
"Wojtek Garwol" wrote:
> "JasonDWilson" <JasonDWilson@.discussions.microsoft.com> wrote in message
> news:7B571D73-00F3-425B-BB96-555A028AC91F@.microsoft.com...
> > Is there any utility, command, or query in SQL Server, that I can execute
> > from a batch mode to script out all of my database definitions (tables,
> > columns, sp, functions, indexes, constraints, triggers, etc.) so I can
> > create
> > an automated routine to check database definitions?
> I wrote one some time ago, using DMO. I can provide compiled exe or sources
> if you want. AFAIR works only with integrated security (kind of bug I never
> really needed to fix). Works fine if you need to monitor database (or all
> databases) schema.
> Cheers,
> Wojtek
>
>|||Sure:
http://www.garwol.net/DbCompare.src.zip
http://www.garwol.net/DbCompare.exe.zip
Enjoy :)
Cheers,
Wojtek
"JasonDWilson" <JasonDWilson@.discussions.microsoft.com> wrote in message
news:45287186-E6D8-47E8-8194-DB2523FDF881@.microsoft.com...
> Yes, that would be great. Can I get both? I will need to modify to work
> with sql authentication.
> Thanks,
> --
> Jason
>
> "Wojtek Garwol" wrote:
>> "JasonDWilson" <JasonDWilson@.discussions.microsoft.com> wrote in message
>> news:7B571D73-00F3-425B-BB96-555A028AC91F@.microsoft.com...
>> > Is there any utility, command, or query in SQL Server, that I can
>> > execute
>> > from a batch mode to script out all of my database definitions (tables,
>> > columns, sp, functions, indexes, constraints, triggers, etc.) so I can
>> > create
>> > an automated routine to check database definitions?
>> I wrote one some time ago, using DMO. I can provide compiled exe or
>> sources
>> if you want. AFAIR works only with integrated security (kind of bug I
>> never
>> really needed to fix). Works fine if you need to monitor database (or all
>> databases) schema.
>> Cheers,
>> Wojtek
>>
Showing posts with label execute. Show all posts
Showing posts with label execute. Show all posts
Thursday, March 29, 2012
Sunday, March 25, 2012
Create a sql stement from existing tables
Is there any way to automatically create a "CREATE TABLE" statment using an
existing table?
I would simply want to execute this statement and it would create, in
another database, a table with the same structure (no data).
Is there a way to do this?
cheers
nathan
hi Nathan,
Nathan wrote:
> Is there any way to automatically create a "CREATE TABLE" statment
> using an existing table?
> I would simply want to execute this statement and it would create, in
> another database, a table with the same structure (no data).
> Is there a way to do this?
not directly... you can use tools like Enterlirse Manager (if you are
licensed to) or even free tools like QALite
(http://www.rac4sql.net/qalite_main.asp) to generate thos kind of DDL...
but with some efforts you can dig into the INFORMATION_SCHEMA views to
output the desired result...
start with
http://msdn.microsoft.com/library/de...a-iz_87w3.asp,
INFORMATION_SCHEMA.COLUMNS..
this is a 5 minutes sample... if you like more deep details such as
constraints, keys, index and the like you have to expand your search on the
other INFORMATION_SCHEMA views..
SET NOCOUNT ON
USE tempdb
GO
CREATE TABLE dbo.table1 (
Id int NOT NULL PRIMARY KEY ,
vc varchar(10) NULL ,
c char(1) NOT NULL DEFAULT ('a') ,
i int ,
d decimal(18,4) NOT NULL DEFAULT 0
)
GO
DECLARE @.COLUMN_NAME nvarchar(128),
@.COLUMN_DEFAULT nvarchar(4000),
@.IS_NULLABLE varchar(3),
@.DATA_TYPE nvarchar(128),
@.NUMERIC_PRECISION tinyint,
@.NUMERIC_SCALE int,
@.COLLATION_NAME nvarchar(128),
@.CHARACTER_MAXIMUM_LENGTH smallint
DECLARE @.cmd varchar(8000)
SET @.cmd = ''
DECLARE t CURSOR FOR
SELECT c.COLUMN_NAME, c.COLUMN_DEFAULT, c.IS_NULLABLE, c.DATA_TYPE,
c.NUMERIC_PRECISION, c.NUMERIC_SCALE, c.COLLATION_NAME,
c.CHARACTER_MAXIMUM_LENGTH
FROM INFORMATION_SCHEMA.COLUMNS c
WHERE c.TABLE_SCHEMA = 'dbo'
AND c.TABLE_NAME = 'table1'
ORDER BY c.ORDINAL_POSITION
OPEN t
FETCH NEXT FROM t
INTO @.COLUMN_NAME, @.COLUMN_DEFAULT, @.IS_NULLABLE, @.DATA_TYPE,
@.NUMERIC_PRECISION, @.NUMERIC_SCALE, @.COLLATION_NAME,
@.CHARACTER_MAXIMUM_LENGTH
WHILE @.@.FETCH_STATUS = 0 BEGIN
IF DATALENGTH(@.cmd) <> 0 SET @.cmd = @.cmd + ' ,' + CHAR(10)
SET @.cmd = @.cmd + CHAR(9) + QUOTENAME( @.COLUMN_NAME )+ ' ' + @.DATA_TYPE + '
'
IF @.DATA_TYPE IN ('varchar', 'nvarchar', 'char', 'nchar')
SET @.cmd = @.cmd + '(' + CONVERT(varchar, @.CHARACTER_MAXIMUM_LENGTH) + ') '
IF @.DATA_TYPE = 'decimal' OR @.DATA_TYPE = 'numeric'
SET @.cmd = @.cmd + '(' + CONVERT(varchar, @.NUMERIC_PRECISION) + ', ' +
CONVERT(varchar, @.NUMERIC_SCALE) + ') '
IF NOT @.COLUMN_DEFAULT IS NULL
SET @.cmd = @.cmd + 'DEFAULT ' + @.COLUMN_DEFAULT + ' '
IF NOT @.COLLATION_NAME IS NULL
SET @.cmd = @.cmd + 'COLLATE ' + @.COLLATION_NAME + ' '
IF @.IS_NULLABLE = 'YES'
SET @.cmd = @.cmd + 'NULL '
ELSE
SET @.cmd = @.cmd + 'NOT NULL '
FETCH NEXT FROM t
INTO @.COLUMN_NAME, @.COLUMN_DEFAULT, @.IS_NULLABLE, @.DATA_TYPE,
@.NUMERIC_PRECISION, @.NUMERIC_SCALE, @.COLLATION_NAME,
@.CHARACTER_MAXIMUM_LENGTH
END
CLOSE t
DEALLOCATE t
SET @.cmd = 'CREATE TABLE ' + QUOTENAME('dbo') + '.' + QUOTENAME('table1') +
' (' + CHAR(10)
+ @.cmd + CHAR(10)
+ CHAR(9) + ')'
SELECT @.cmd
GO
DROP TABLE dbo.table1
--<--
CREATE TABLE [dbo].[table1] (
[Id] int NOT NULL ,
[vc] varchar (10) COLLATE Latin1_General_CI_AS NULL ,
[c] char (1) DEFAULT ('a') COLLATE Latin1_General_CI_AS NOT NULL ,
[i] int NULL ,
[d] decimal (18, 4) DEFAULT (0) NOT NULL
)
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.16.0 - DbaMgr ver 0.61.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
existing table?
I would simply want to execute this statement and it would create, in
another database, a table with the same structure (no data).
Is there a way to do this?
cheers
nathan
hi Nathan,
Nathan wrote:
> Is there any way to automatically create a "CREATE TABLE" statment
> using an existing table?
> I would simply want to execute this statement and it would create, in
> another database, a table with the same structure (no data).
> Is there a way to do this?
not directly... you can use tools like Enterlirse Manager (if you are
licensed to) or even free tools like QALite
(http://www.rac4sql.net/qalite_main.asp) to generate thos kind of DDL...
but with some efforts you can dig into the INFORMATION_SCHEMA views to
output the desired result...
start with
http://msdn.microsoft.com/library/de...a-iz_87w3.asp,
INFORMATION_SCHEMA.COLUMNS..
this is a 5 minutes sample... if you like more deep details such as
constraints, keys, index and the like you have to expand your search on the
other INFORMATION_SCHEMA views..
SET NOCOUNT ON
USE tempdb
GO
CREATE TABLE dbo.table1 (
Id int NOT NULL PRIMARY KEY ,
vc varchar(10) NULL ,
c char(1) NOT NULL DEFAULT ('a') ,
i int ,
d decimal(18,4) NOT NULL DEFAULT 0
)
GO
DECLARE @.COLUMN_NAME nvarchar(128),
@.COLUMN_DEFAULT nvarchar(4000),
@.IS_NULLABLE varchar(3),
@.DATA_TYPE nvarchar(128),
@.NUMERIC_PRECISION tinyint,
@.NUMERIC_SCALE int,
@.COLLATION_NAME nvarchar(128),
@.CHARACTER_MAXIMUM_LENGTH smallint
DECLARE @.cmd varchar(8000)
SET @.cmd = ''
DECLARE t CURSOR FOR
SELECT c.COLUMN_NAME, c.COLUMN_DEFAULT, c.IS_NULLABLE, c.DATA_TYPE,
c.NUMERIC_PRECISION, c.NUMERIC_SCALE, c.COLLATION_NAME,
c.CHARACTER_MAXIMUM_LENGTH
FROM INFORMATION_SCHEMA.COLUMNS c
WHERE c.TABLE_SCHEMA = 'dbo'
AND c.TABLE_NAME = 'table1'
ORDER BY c.ORDINAL_POSITION
OPEN t
FETCH NEXT FROM t
INTO @.COLUMN_NAME, @.COLUMN_DEFAULT, @.IS_NULLABLE, @.DATA_TYPE,
@.NUMERIC_PRECISION, @.NUMERIC_SCALE, @.COLLATION_NAME,
@.CHARACTER_MAXIMUM_LENGTH
WHILE @.@.FETCH_STATUS = 0 BEGIN
IF DATALENGTH(@.cmd) <> 0 SET @.cmd = @.cmd + ' ,' + CHAR(10)
SET @.cmd = @.cmd + CHAR(9) + QUOTENAME( @.COLUMN_NAME )+ ' ' + @.DATA_TYPE + '
'
IF @.DATA_TYPE IN ('varchar', 'nvarchar', 'char', 'nchar')
SET @.cmd = @.cmd + '(' + CONVERT(varchar, @.CHARACTER_MAXIMUM_LENGTH) + ') '
IF @.DATA_TYPE = 'decimal' OR @.DATA_TYPE = 'numeric'
SET @.cmd = @.cmd + '(' + CONVERT(varchar, @.NUMERIC_PRECISION) + ', ' +
CONVERT(varchar, @.NUMERIC_SCALE) + ') '
IF NOT @.COLUMN_DEFAULT IS NULL
SET @.cmd = @.cmd + 'DEFAULT ' + @.COLUMN_DEFAULT + ' '
IF NOT @.COLLATION_NAME IS NULL
SET @.cmd = @.cmd + 'COLLATE ' + @.COLLATION_NAME + ' '
IF @.IS_NULLABLE = 'YES'
SET @.cmd = @.cmd + 'NULL '
ELSE
SET @.cmd = @.cmd + 'NOT NULL '
FETCH NEXT FROM t
INTO @.COLUMN_NAME, @.COLUMN_DEFAULT, @.IS_NULLABLE, @.DATA_TYPE,
@.NUMERIC_PRECISION, @.NUMERIC_SCALE, @.COLLATION_NAME,
@.CHARACTER_MAXIMUM_LENGTH
END
CLOSE t
DEALLOCATE t
SET @.cmd = 'CREATE TABLE ' + QUOTENAME('dbo') + '.' + QUOTENAME('table1') +
' (' + CHAR(10)
+ @.cmd + CHAR(10)
+ CHAR(9) + ')'
SELECT @.cmd
GO
DROP TABLE dbo.table1
--<--
CREATE TABLE [dbo].[table1] (
[Id] int NOT NULL ,
[vc] varchar (10) COLLATE Latin1_General_CI_AS NULL ,
[c] char (1) DEFAULT ('a') COLLATE Latin1_General_CI_AS NOT NULL ,
[i] int NULL ,
[d] decimal (18, 4) DEFAULT (0) NOT NULL
)
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.16.0 - DbaMgr ver 0.61.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
Tuesday, March 20, 2012
Create 1 connection to execute multiple Packages
I want to create 1 connection because I have to execute multiple packages (2005). But when I go into the execute package task editor, the dropdown only ever has "New Connection". Is this a bug?Connections are package local. One package cannot see another package's connection managers.
We, unfortunately, don't have the notion of a file data source which it sounds like you're looking for. Basically, a way to create a connection that you can share across multiple packages.
You'll need to create a new connection per package.
K|||Kirk, not sure if I explained correctly. I'm trying to have 1 master package that executes child packages all on the same "ETL server". So I want 1 connection to be the "ETL Server" such that it can be changed in 1 place (actually, I would just have a runtime setting change it based on a config) when moving from DEV (each developer would have their own instance) to QA to PROD.
I'm coming into this late and I think I may be misunderstanding it all but let me have a punt anyway.
We, unfortunately, don't have the notion of a file data source which it sounds like you're looking for. Basically, a way to create a connection that you can share across multiple packages.
You'll need to create a new connection per package.
K|||Kirk, not sure if I explained correctly. I'm trying to have 1 master package that executes child packages all on the same "ETL server". So I want 1 connection to be the "ETL Server" such that it can be changed in 1 place (actually, I would just have a runtime setting change it based on a config) when moving from DEV (each developer would have their own instance) to QA to PROD.
The reason I want many packages is so that multiple ETL developers can work on a component and build it without having to wait. Additionally, if everything was in 1 package, it would be huge.
Is the only way to accomplish this without having to change each package's sql server location is to reference it by its file location?|||OK, a few questions to clarify.
What kind of connection do you want the one connection to be SQL, file?
You have one package that you want to call multiple subpackages?
You want the subpackages to use the one connection?
I'm not sure what you mean by "1 connection to be the 'ETL Server' "
"Is the only way to accomplish this without having to change each package's sql server location is to reference it by its file location?"
I don't understand why you need to change the package's sql server location. Which package's SQL Server location? The parent package or the sub-packages?
I think what's confusing me here is that your description has a lot of moving parts. Can you simplify the problem into two or three root issues. I have a simple mind and can't deal with all the complexity. :)
|||Let me offer a scenario and you can tell me how it doesn't meet your needs.
You can create a parent package. It can call all the other sub-packages. When the subpackages execute, the parent packages connection is available to them. However, not at design time. I think this is the issue you're having. The sub-package doesn't have areference to the parent package at design time, so it doesn't have the connections either.
What I'd suggest is that you create a Data source per machine and have all the connections reference it. You'll still need to create a connection manager in each package, but they will all reference the same data source. Moving to different machines is no problem because you can have a "machine resident" data source file that properly points to your "ETL Server".
Is this what you're after?|||see responses below, thanks for taking the time to respond.
Q. What kind of connection do you want the one connection to be SQL, file?
A. The connection of the SSIS server where the subpackages reside.
Q. You have one package that you want to call multiple subpackages?
A. YES
Q. You want the subpackages to use the one connection?
A. YES
Q. I'm not sure what you mean by "1 connection to be the 'ETL Server' "
A. If I store all the subpackages on SQL Server, I'll bound to have different servers for different environments, etc.
Q.I don't understand why you need to change the package's sql server location. Which package's SQL Server location? The parent package or the sub-packages?
A. sub-packages
I'm not after the connections inside the subpackage from the parent packaeg, nor the connections in the parent package from the subpackage.
What I am looking for is to create 1 connection and tie it to where the subpackages are stored since it needs to be dynamic without having to spin through every execute package task in the master package and change the sql server it resides on at runtime.
Alternatively, I could create a new connection for every execute child package in my master package all pointing to the same SQL Server that is storing them, but its extremely redundant and would only be useful if I'm pointing to (LOCAL) and the user and password never changes.
Why can't the EXECUTE PACKAGE task just use one of the connections available in the "connections managers" collection? I thought that was the reason for the "connection manager" abstraction layer, so that a physical connection can be changed in one place, and all logical connections are automatically redirected?
Any chance of implementing this? Alternatively, implementing a FOR EACH connection enumerator (although, this would be hokey)?
Followup question would be this: If I'm executing a package from another package, what is the performance difference (if any) of executing a package in general stored in the file system VS. stored in sql server? I'm assuming that if its not stored in sql server, it needs to be brought in from the file system and compiled on its 1st use?
My goal is to create an ETL where multiple developers can work on different components at the same time. The easiest way to do this would be to create 1 package per table. A master package would call all the child packages, but the master package needs to know which ETL server all the child packages are stored in, which changes from environment to environment.
|||
"Why can't the EXECUTE PACKAGE task just use one of the connections available in the "connections managers" collection?"
It can, If all your execute package tasks are in the same package, yes, they can all use the same connection manager. Is this not working for you?
"what is the performance difference (if any) of executing a package in general stored in the file system VS. stored in sql server"
No performance difference between the two storage methods once loaded. Packages aren't compiled. They're read into the object model.
"The easiest way to do this would be to create 1 package per table. "
Really? Why is this easiest. Why limit it to one package per table. Why not just have developers develop package files and check them into sourcesafe? Then when they're done developing them, deploy to SQL Server? Not sure I follow this logic.
Please keep the threads cogent by only asking only one question per thread.
K
|||Hi,I'm coming into this late and I think I may be misunderstanding it all but let me have a punt anyway.
You have 1 master package which is intended to call multiple child packages.
You want 1 connection manager in the master package to be used to call all child packages.
You want to cycle through a list of all the child packages and execute them using 1 Execute Package Task
If that is all correct then I think what you want to do is possible using property expressions.
Have I understood correctly?
-Jamie|||"Why can't the EXECUTE PACKAGE task just use one of the connections available in the "connections managers" collection?"
It can, If all your execute package tasks are in the same package, yes, they can all use the same connection manager. Is this not working for you?
Whew, thats all i need, maybe its a bug - using June CTP. I create a new execute package task and then create a new connection for it. Then I create a 2nd execute package task, and click on the connection dropdown, but all that is available is New Connection.
|||
Ah! You're using SQL Connections. Dah!
Yes, this is a bug!
Thanks, great find. Please file a bug.
K
CREATE / EXECUTE Stored Procedure (SQL Server 7.0)
Hi,
I create one stored procedure to return some objects, this procedure accepts
two parameters and this parameters have a default value.
When i execute the procedure with no values the default parameters are used
and every thing goes fine, when i need to input these parameters the
following error appears.
--exec procedure_name db_name, user_name
Error:
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'user_name'.
Can you help me.
Best regardsI guess it should be
exec procedure_name 'db_name', 'user_name'
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:4A3D9388-DB65-4CAA-9F29-13C2D5B735AE@.microsoft.com...
> Hi,
> I create one stored procedure to return some objects, this procedure
> accepts
> two parameters and this parameters have a default value.
> When i execute the procedure with no values the default parameters are
> used
> and every thing goes fine, when i need to input these parameters the
> following error appears.
> --exec procedure_name db_name, user_name
> Error:
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'user_name'.
> Can you help me.
> Best regards
>|||If quoting the parameters doesn't fix it, then post the SP source so we can
see.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:4A3D9388-DB65-4CAA-9F29-13C2D5B735AE@.microsoft.com...
> Hi,
> I create one stored procedure to return some objects, this procedure
accepts
> two parameters and this parameters have a default value.
> When i execute the procedure with no values the default parameters are
used
> and every thing goes fine, when i need to input these parameters the
> following error appears.
> --exec procedure_name db_name, user_name
> Error:
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'user_name'.
> Can you help me.
> Best regards
>
I create one stored procedure to return some objects, this procedure accepts
two parameters and this parameters have a default value.
When i execute the procedure with no values the default parameters are used
and every thing goes fine, when i need to input these parameters the
following error appears.
--exec procedure_name db_name, user_name
Error:
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'user_name'.
Can you help me.
Best regardsI guess it should be
exec procedure_name 'db_name', 'user_name'
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:4A3D9388-DB65-4CAA-9F29-13C2D5B735AE@.microsoft.com...
> Hi,
> I create one stored procedure to return some objects, this procedure
> accepts
> two parameters and this parameters have a default value.
> When i execute the procedure with no values the default parameters are
> used
> and every thing goes fine, when i need to input these parameters the
> following error appears.
> --exec procedure_name db_name, user_name
> Error:
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'user_name'.
> Can you help me.
> Best regards
>|||If quoting the parameters doesn't fix it, then post the SP source so we can
see.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:4A3D9388-DB65-4CAA-9F29-13C2D5B735AE@.microsoft.com...
> Hi,
> I create one stored procedure to return some objects, this procedure
accepts
> two parameters and this parameters have a default value.
> When i execute the procedure with no values the default parameters are
used
> and every thing goes fine, when i need to input these parameters the
> following error appears.
> --exec procedure_name db_name, user_name
> Error:
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'user_name'.
> Can you help me.
> Best regards
>
CREATE / EXECUTE Stored Procedure (SQL Server 7.0)
Hi,
I create one stored procedure to return some objects, this procedure accepts
two parameters and this parameters have a default value.
When i execute the procedure with no values the default parameters are used
and every thing goes fine, when i need to input these parameters the
following error appears.
--exec procedure_name db_name, user_name
Error:
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'user_name'.
Can you help me.
Best regardsI guess it should be
exec procedure_name 'db_name', 'user_name'
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:4A3D9388-DB65-4CAA-9F29-13C2D5B735AE@.microsoft.com...
> Hi,
> I create one stored procedure to return some objects, this procedure
> accepts
> two parameters and this parameters have a default value.
> When i execute the procedure with no values the default parameters are
> used
> and every thing goes fine, when i need to input these parameters the
> following error appears.
> --exec procedure_name db_name, user_name
> Error:
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'user_name'.
> Can you help me.
> Best regards
>|||If quoting the parameters doesn't fix it, then post the SP source so we can
see.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:4A3D9388-DB65-4CAA-9F29-13C2D5B735AE@.microsoft.com...
> Hi,
> I create one stored procedure to return some objects, this procedure
accepts
> two parameters and this parameters have a default value.
> When i execute the procedure with no values the default parameters are
used
> and every thing goes fine, when i need to input these parameters the
> following error appears.
> --exec procedure_name db_name, user_name
> Error:
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'user_name'.
> Can you help me.
> Best regards
>sql
I create one stored procedure to return some objects, this procedure accepts
two parameters and this parameters have a default value.
When i execute the procedure with no values the default parameters are used
and every thing goes fine, when i need to input these parameters the
following error appears.
--exec procedure_name db_name, user_name
Error:
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'user_name'.
Can you help me.
Best regardsI guess it should be
exec procedure_name 'db_name', 'user_name'
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:4A3D9388-DB65-4CAA-9F29-13C2D5B735AE@.microsoft.com...
> Hi,
> I create one stored procedure to return some objects, this procedure
> accepts
> two parameters and this parameters have a default value.
> When i execute the procedure with no values the default parameters are
> used
> and every thing goes fine, when i need to input these parameters the
> following error appears.
> --exec procedure_name db_name, user_name
> Error:
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'user_name'.
> Can you help me.
> Best regards
>|||If quoting the parameters doesn't fix it, then post the SP source so we can
see.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:4A3D9388-DB65-4CAA-9F29-13C2D5B735AE@.microsoft.com...
> Hi,
> I create one stored procedure to return some objects, this procedure
accepts
> two parameters and this parameters have a default value.
> When i execute the procedure with no values the default parameters are
used
> and every thing goes fine, when i need to input these parameters the
> following error appears.
> --exec procedure_name db_name, user_name
> Error:
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'user_name'.
> Can you help me.
> Best regards
>sql
CREATE / EXECUTE Stored Procedure (SQL Server 7.0)
Hi,
I create one stored procedure to return some objects, this procedure accepts
two parameters and this parameters have a default value.
When i execute the procedure with no values the default parameters are used
and every thing goes fine, when i need to input these parameters the
following error appears.
--exec procedure_name db_name, user_name
Error:
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'user_name'.
Can you help me.
Best regards
I guess it should be
exec procedure_name 'db_name', 'user_name'
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:4A3D9388-DB65-4CAA-9F29-13C2D5B735AE@.microsoft.com...
> Hi,
> I create one stored procedure to return some objects, this procedure
> accepts
> two parameters and this parameters have a default value.
> When i execute the procedure with no values the default parameters are
> used
> and every thing goes fine, when i need to input these parameters the
> following error appears.
> --exec procedure_name db_name, user_name
> Error:
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'user_name'.
> Can you help me.
> Best regards
>
|||If quoting the parameters doesn't fix it, then post the SP source so we can
see.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:4A3D9388-DB65-4CAA-9F29-13C2D5B735AE@.microsoft.com...
> Hi,
> I create one stored procedure to return some objects, this procedure
accepts
> two parameters and this parameters have a default value.
> When i execute the procedure with no values the default parameters are
used
> and every thing goes fine, when i need to input these parameters the
> following error appears.
> --exec procedure_name db_name, user_name
> Error:
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'user_name'.
> Can you help me.
> Best regards
>
I create one stored procedure to return some objects, this procedure accepts
two parameters and this parameters have a default value.
When i execute the procedure with no values the default parameters are used
and every thing goes fine, when i need to input these parameters the
following error appears.
--exec procedure_name db_name, user_name
Error:
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'user_name'.
Can you help me.
Best regards
I guess it should be
exec procedure_name 'db_name', 'user_name'
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:4A3D9388-DB65-4CAA-9F29-13C2D5B735AE@.microsoft.com...
> Hi,
> I create one stored procedure to return some objects, this procedure
> accepts
> two parameters and this parameters have a default value.
> When i execute the procedure with no values the default parameters are
> used
> and every thing goes fine, when i need to input these parameters the
> following error appears.
> --exec procedure_name db_name, user_name
> Error:
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'user_name'.
> Can you help me.
> Best regards
>
|||If quoting the parameters doesn't fix it, then post the SP source so we can
see.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:4A3D9388-DB65-4CAA-9F29-13C2D5B735AE@.microsoft.com...
> Hi,
> I create one stored procedure to return some objects, this procedure
accepts
> two parameters and this parameters have a default value.
> When i execute the procedure with no values the default parameters are
used
> and every thing goes fine, when i need to input these parameters the
> following error appears.
> --exec procedure_name db_name, user_name
> Error:
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'user_name'.
> Can you help me.
> Best regards
>
Monday, March 19, 2012
Crearting a new table via SPROCS
I supply a parameter @.TEMPTABLE for the table name. When I execute my SPROC with the parameter, execution goes thru by I always end up getting "@.TEMPTABLE" as the name of the table instead of the value of the parameter.
Any ideas would be appreciated.
Thank you.
CREATE PROCEDURE sp_CREATE_TEMP_TABLE
(@.TEMPTABLE varchar(30))
AS
CREATE TABLE @.TEMPTABLE (
[WOTempID] [int] IDENTITY (1, 1) NOT NULL ,
[OrderScheduleID] [int] NULL ,
[OrderID] [int] NULL ,
[FormCode] [varchar] (4) NULL ,
[AcctName] [varchar] (50) NULL ,
[AcctRetailer] [varchar] (50) NULL ,
[StoreCode] [varchar] (12) NULL ,
[RankCode] [varchar] (3) NULL ,
[RankID] [tinyint] NULL ,
[DeptCode] [int] NULL ,
[WeekNo] [tinyint] NULL ,
[StartDate] [smalldatetime] NULL ,
[EndDate] [smalldatetime] NULL
) ON [PRIMARY]Hi,
your tablename is "@.temptable" ... the server will not use the var, instead it will name the table...
you need to build an cmd-string and execute it. something like this:
declare @.S nvarchar(1000)
select @.S=
'CREATE TABLE'+@.TEMPTABLE+' (
[WOTempID] [int] IDENTITY (1, 1) NOT NULL ,
[OrderScheduleID] [int] NULL ,
[OrderID] [int] NULL ,
[FormCode] [varchar] (4) NULL ,
[AcctName] [varchar] (50) NULL ,
[AcctRetailer] [varchar] (50) NULL ,
[StoreCode] [varchar] (12) NULL ,
[RankCode] [varchar] (3) NULL ,
[RankID] [tinyint] NULL ,
[DeptCode] [int] NULL ,
[WeekNo] [tinyint] NULL ,
[StartDate] [smalldatetime] NULL ,
[EndDate] [smalldatetime] NULL
) ON [PRIMARY] '
exec sp_executesql @.S
markus
Any ideas would be appreciated.
Thank you.
CREATE PROCEDURE sp_CREATE_TEMP_TABLE
(@.TEMPTABLE varchar(30))
AS
CREATE TABLE @.TEMPTABLE (
[WOTempID] [int] IDENTITY (1, 1) NOT NULL ,
[OrderScheduleID] [int] NULL ,
[OrderID] [int] NULL ,
[FormCode] [varchar] (4) NULL ,
[AcctName] [varchar] (50) NULL ,
[AcctRetailer] [varchar] (50) NULL ,
[StoreCode] [varchar] (12) NULL ,
[RankCode] [varchar] (3) NULL ,
[RankID] [tinyint] NULL ,
[DeptCode] [int] NULL ,
[WeekNo] [tinyint] NULL ,
[StartDate] [smalldatetime] NULL ,
[EndDate] [smalldatetime] NULL
) ON [PRIMARY]Hi,
your tablename is "@.temptable" ... the server will not use the var, instead it will name the table...
you need to build an cmd-string and execute it. something like this:
declare @.S nvarchar(1000)
select @.S=
'CREATE TABLE'+@.TEMPTABLE+' (
[WOTempID] [int] IDENTITY (1, 1) NOT NULL ,
[OrderScheduleID] [int] NULL ,
[OrderID] [int] NULL ,
[FormCode] [varchar] (4) NULL ,
[AcctName] [varchar] (50) NULL ,
[AcctRetailer] [varchar] (50) NULL ,
[StoreCode] [varchar] (12) NULL ,
[RankCode] [varchar] (3) NULL ,
[RankID] [tinyint] NULL ,
[DeptCode] [int] NULL ,
[WeekNo] [tinyint] NULL ,
[StartDate] [smalldatetime] NULL ,
[EndDate] [smalldatetime] NULL
) ON [PRIMARY] '
exec sp_executesql @.S
markus
Crash in Execute() method of _ConnectionPtr
Hi All,
I have a piece of code where object of _ConnectionPtr is used. When I
call the Execute() method for this object I get an debug error stating
that "The Value of ESP could be saved...".
If I ignore the message this leads to crash with "Access violation
error".
Following is the code snippet i use.
void ExecuteSQLCmd( unsigned short *sqlCmd)
{
_ConnectionPtr ConnPtr ;
_bstr_t cmd ;
_RecordsetPtr ptrRS("ADODB.Recordset");Ashish choudhari (ashishtchaudhari@.gmail.com) writes:
> I have a piece of code where object of _ConnectionPtr is used. When I
> call the Execute() method for this object I get an debug error stating
> that "The Value of ESP could be saved...".
> If I ignore the message this leads to crash with "Access violation
> error".
> Following is the code snippet i use.
> void ExecuteSQLCmd( unsigned short *sqlCmd)
> {
> _ConnectionPtr ConnPtr ;
> _bstr_t cmd ;
> _RecordsetPtr ptrRS("ADODB.Recordset");
> .
> .
> cmd = sqlCmd ;
> //occassionally crash here
> pRS = ConnPtr->Execute(cmd,NULL,enumtextCmd) ; //enumtextCmd = 1
> }
> The crash at the above mentioned statement occurs sometimes, I am not
> sure why the error is occuring I am using m
o.dll version 2.50
Extremely stupid question: should you not assign ConnPtr some value
before using it?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi,
I did forgot to mention in the above code.
I have used CreateInstance() and Open() method of _ConnectionPtr before
actually calling Execute().
The code looks as follows
void ExecuteSQLCmd( unsigned short *sqlCmd, char *ConnStr)
{
_ConnectionPtr ConnPtr ;
_bstr_t cmd ;
_RecordsetPtr ptrRS("ADODB.Recordset");
CoInitialize(NULL) ;
ConnPtr.CreateInstance(__uuidof( Connection ));
ConnPtr.Open(ConnStr,"","",adoConnectUnSpecified) ; //
adoConnectUnspecified = -1|||
I can't see anything obviously wrong with your code, although
the CoInitialize should be called only once per thread and
before any COM calls, including the line
_RecordsetPtr ptrRS("ADODB.Recordset");
When debugging what does the call stack tell you when you
get the "The Value of ESP could be saved..." message?
Occasional crashes may indicate memory overwrites/array bounds
exceeded etc. Is you app multi-threaded? If so are there thread
safety related issues? There are 3rd party tools available
to help with these kinds of problems (e.g. BoundsChecker).|||Hi,
First, I would like to let you know actually how my code is
implemented.
I have a C++ class "CRdbms". The public member variable "ptrConn" in
class "CRdbms" is an object of class "_ConnectionPtr. The object of
these class "CRdbms" is passed to function "ExecuteSQLCmd()". Before
calling ExecuteSQLCmd(), _ConnectionPtr is initialized. Then the
function ExecuteSQLCmd () is called. ExecuteSQLCmd() looks as follows:
void ExecuteSQLCmd(CRdbms &objRdbms, unsigned short *sqlCmd)
{
bstr_t cmd;
cmd = sqlCmd;
CoInitialize(NULL);
try
{
_RecordsetPtr ptrRs("ADODB.Recordset");
if ( objRdbms.RdbmsCmdTimeOut() != -1 )
{
objRdbms.ptrConn->CommandTimeout = objRdbms.RdbmsCmdTimeOut();
}
//Occassionally crash here
ptrRs = objRdbms.ptrConn->Execute(cmd,
NULL, enumtextCmd) ; //enumtextCmd = 1
}
catch (_com_error &e)
{
status = false;
wsprintf("Code = %08lx\n", e.Error());
wsprintf("Code meaning = %s\n", (char*) e.ErrorMessage());
wsprintf("Source = %s\n", (char*) e.Source());
wsprintf("Description = %s\n", (char*) e.Description());
wsprintf ( objRdbms.errMsg, L"%s", (unsigned short *)
e.Description());
}
CoUninitialize();
}
The application is running in multi-threaded environment. The debug
error is coming will executing Execute() function of "_ConnectionPtr"
and if we ignore the message the crash occurs while results are
returned and assigned to ptrRs. i.e. statement
ptrRs = objRdbms.ptrConn->Execute(cmd, NULL, enumtextCmd) ;
//enumtextCmd = 1
It seems that the memory overwrite is taking place in this situation
but not sure why is it happening so.
Please help me to solve the problem.
Thanks and Regards,
Ashish Choudhari|||
A couple of things to try...
Do you have a single instance of your "CRdbms" class that
is shared between all threads? If so, then you may need
to serialise any access to "objRdbms.ptrConn" (use a
critical section inside your ExecuteSQLCmd function)
or create a new connection per thread.
Try moving the CoInitialize/CoUninitialize so that
they happen once per thread.|||I do not have single instance of "CRdbms" object. It is created one per
thread and not shared between any other thread.
There are multiple functions called by each thread and each function
have CoInitialize and CoUnitilize.
I will try moving them so that it occurs only one per thread.
Thanks for your comments.
Regards,
Ashish Choudhary|||Ashish choudhari (ashishtchaudhari@.gmail.com) writes:
> I do not have single instance of "CRdbms" object. It is created one per
> thread and not shared between any other thread.
> There are multiple functions called by each thread and each function
> have CoInitialize and CoUnitilize.
> I will try moving them so that it occurs only one per thread.
I would not say that I know this stuff too well, but I've written
a DLL in C++ that implements a general interface for running queries
from Perl. When the DLL is loaded the first time, I do:
CoInitializeEx(NULL, COINIT_MULTITHREADED);
And I, eh, never call CoUnitialize.
But I would like to stress that I'm a not regular programmer of COM
application, so the above may very well be an example of poor practice.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I modified my code so that CoInitialize is called only once per thread.
Doing this reduced the number of crash locations. But the code still
crash in Open() function of _ConnectionPtr. My Code looks as follows.
bool AdoConnect (CRdbms &objRdbms )
{
bool status = true;
if(FAILED(CoInitialize(NULL)))
{
status = false ;
return status ;
}
try
{
HRESULT hR = objRdbms.Pcn.CreateInstance(__uuidof( Connection ));
if (!SUCCEEDED(hR))
{
_com_issue_error(hR);
status = false;
}
else
{
char *sqlCmd ;
sqlCmd = rdbms.RdbmsConnStr() ;
if (sqlCmd == NULL)
{
printf("Error: No Connection String present") ;
status = false ;
}
else
{
bstr_t cmd(sqlCmd) ;
objRdbms.Pcn->Open ( cmd, "", "", adConnectUnspecified);
status = true;
}
}
}
catch (_com_error &e)
{
if ( status )
{
objRdbms.Pcn.Release();
}
status = false;
printf("Error:\n");
printf("Code = %08lx\n", e.Error());
printf("Code meaning = %s\n", (char*) e.ErrorMessage());
printf("Source = %s\n", (char*) e.Source());
printf("Description = %s\n", (char*) e.Description());
//wcscpy ( rdbms.errMsg, ERR_CONN_FAILED );
wsprintf ( rdbms.errMsg, L"%s", (unsigned short *) e.Description());
}
return status;
}
In the above code SOMETIMES when the statement objRdbms.Pcn->Open() is
execute the application crashes here. Internally, when the function
Open() returns it destroys the object of _bstr_t and it is here it
crashes.
Please do let me know if you have any suggestions.
Thanks and Regards,
Ashish Choudhary
I have a piece of code where object of _ConnectionPtr is used. When I
call the Execute() method for this object I get an debug error stating
that "The Value of ESP could be saved...".
If I ignore the message this leads to crash with "Access violation
error".
Following is the code snippet i use.
void ExecuteSQLCmd( unsigned short *sqlCmd)
{
_ConnectionPtr ConnPtr ;
_bstr_t cmd ;
_RecordsetPtr ptrRS("ADODB.Recordset");Ashish choudhari (ashishtchaudhari@.gmail.com) writes:
> I have a piece of code where object of _ConnectionPtr is used. When I
> call the Execute() method for this object I get an debug error stating
> that "The Value of ESP could be saved...".
> If I ignore the message this leads to crash with "Access violation
> error".
> Following is the code snippet i use.
> void ExecuteSQLCmd( unsigned short *sqlCmd)
> {
> _ConnectionPtr ConnPtr ;
> _bstr_t cmd ;
> _RecordsetPtr ptrRS("ADODB.Recordset");
> .
> .
> cmd = sqlCmd ;
> //occassionally crash here
> pRS = ConnPtr->Execute(cmd,NULL,enumtextCmd) ; //enumtextCmd = 1
> }
> The crash at the above mentioned statement occurs sometimes, I am not
> sure why the error is occuring I am using m
o.dll version 2.50Extremely stupid question: should you not assign ConnPtr some value
before using it?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi,
I did forgot to mention in the above code.
I have used CreateInstance() and Open() method of _ConnectionPtr before
actually calling Execute().
The code looks as follows
void ExecuteSQLCmd( unsigned short *sqlCmd, char *ConnStr)
{
_ConnectionPtr ConnPtr ;
_bstr_t cmd ;
_RecordsetPtr ptrRS("ADODB.Recordset");
CoInitialize(NULL) ;
ConnPtr.CreateInstance(__uuidof( Connection ));
ConnPtr.Open(ConnStr,"","",adoConnectUnSpecified) ; //
adoConnectUnspecified = -1|||
I can't see anything obviously wrong with your code, although
the CoInitialize should be called only once per thread and
before any COM calls, including the line
_RecordsetPtr ptrRS("ADODB.Recordset");
When debugging what does the call stack tell you when you
get the "The Value of ESP could be saved..." message?
Occasional crashes may indicate memory overwrites/array bounds
exceeded etc. Is you app multi-threaded? If so are there thread
safety related issues? There are 3rd party tools available
to help with these kinds of problems (e.g. BoundsChecker).|||Hi,
First, I would like to let you know actually how my code is
implemented.
I have a C++ class "CRdbms". The public member variable "ptrConn" in
class "CRdbms" is an object of class "_ConnectionPtr. The object of
these class "CRdbms" is passed to function "ExecuteSQLCmd()". Before
calling ExecuteSQLCmd(), _ConnectionPtr is initialized. Then the
function ExecuteSQLCmd () is called. ExecuteSQLCmd() looks as follows:
void ExecuteSQLCmd(CRdbms &objRdbms, unsigned short *sqlCmd)
{
bstr_t cmd;
cmd = sqlCmd;
CoInitialize(NULL);
try
{
_RecordsetPtr ptrRs("ADODB.Recordset");
if ( objRdbms.RdbmsCmdTimeOut() != -1 )
{
objRdbms.ptrConn->CommandTimeout = objRdbms.RdbmsCmdTimeOut();
}
//Occassionally crash here
ptrRs = objRdbms.ptrConn->Execute(cmd,
NULL, enumtextCmd) ; //enumtextCmd = 1
}
catch (_com_error &e)
{
status = false;
wsprintf("Code = %08lx\n", e.Error());
wsprintf("Code meaning = %s\n", (char*) e.ErrorMessage());
wsprintf("Source = %s\n", (char*) e.Source());
wsprintf("Description = %s\n", (char*) e.Description());
wsprintf ( objRdbms.errMsg, L"%s", (unsigned short *)
e.Description());
}
CoUninitialize();
}
The application is running in multi-threaded environment. The debug
error is coming will executing Execute() function of "_ConnectionPtr"
and if we ignore the message the crash occurs while results are
returned and assigned to ptrRs. i.e. statement
ptrRs = objRdbms.ptrConn->Execute(cmd, NULL, enumtextCmd) ;
//enumtextCmd = 1
It seems that the memory overwrite is taking place in this situation
but not sure why is it happening so.
Please help me to solve the problem.
Thanks and Regards,
Ashish Choudhari|||
A couple of things to try...
Do you have a single instance of your "CRdbms" class that
is shared between all threads? If so, then you may need
to serialise any access to "objRdbms.ptrConn" (use a
critical section inside your ExecuteSQLCmd function)
or create a new connection per thread.
Try moving the CoInitialize/CoUninitialize so that
they happen once per thread.|||I do not have single instance of "CRdbms" object. It is created one per
thread and not shared between any other thread.
There are multiple functions called by each thread and each function
have CoInitialize and CoUnitilize.
I will try moving them so that it occurs only one per thread.
Thanks for your comments.
Regards,
Ashish Choudhary|||Ashish choudhari (ashishtchaudhari@.gmail.com) writes:
> I do not have single instance of "CRdbms" object. It is created one per
> thread and not shared between any other thread.
> There are multiple functions called by each thread and each function
> have CoInitialize and CoUnitilize.
> I will try moving them so that it occurs only one per thread.
I would not say that I know this stuff too well, but I've written
a DLL in C++ that implements a general interface for running queries
from Perl. When the DLL is loaded the first time, I do:
CoInitializeEx(NULL, COINIT_MULTITHREADED);
And I, eh, never call CoUnitialize.
But I would like to stress that I'm a not regular programmer of COM
application, so the above may very well be an example of poor practice.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I modified my code so that CoInitialize is called only once per thread.
Doing this reduced the number of crash locations. But the code still
crash in Open() function of _ConnectionPtr. My Code looks as follows.
bool AdoConnect (CRdbms &objRdbms )
{
bool status = true;
if(FAILED(CoInitialize(NULL)))
{
status = false ;
return status ;
}
try
{
HRESULT hR = objRdbms.Pcn.CreateInstance(__uuidof( Connection ));
if (!SUCCEEDED(hR))
{
_com_issue_error(hR);
status = false;
}
else
{
char *sqlCmd ;
sqlCmd = rdbms.RdbmsConnStr() ;
if (sqlCmd == NULL)
{
printf("Error: No Connection String present") ;
status = false ;
}
else
{
bstr_t cmd(sqlCmd) ;
objRdbms.Pcn->Open ( cmd, "", "", adConnectUnspecified);
status = true;
}
}
}
catch (_com_error &e)
{
if ( status )
{
objRdbms.Pcn.Release();
}
status = false;
printf("Error:\n");
printf("Code = %08lx\n", e.Error());
printf("Code meaning = %s\n", (char*) e.ErrorMessage());
printf("Source = %s\n", (char*) e.Source());
printf("Description = %s\n", (char*) e.Description());
//wcscpy ( rdbms.errMsg, ERR_CONN_FAILED );
wsprintf ( rdbms.errMsg, L"%s", (unsigned short *) e.Description());
}
return status;
}
In the above code SOMETIMES when the statement objRdbms.Pcn->Open() is
execute the application crashes here. Internally, when the function
Open() returns it destroys the object of _bstr_t and it is here it
crashes.
Please do let me know if you have any suggestions.
Thanks and Regards,
Ashish Choudhary
Wednesday, March 7, 2012
cpu quota / resource management
Hello *,
Is there a way to restrict resource consumption by user or connection?
If I execute some long running reports my complete server slowes down. I'd like to assign this reports only limited resources.
Thanks,
Michael
You can configure database mirroring for the target database in your production SQL instance and create snapshot on the mirror server (your backup SQL instnace) on different machine. The principle system can be considered as your normal OLTP business system and the mirror server can be used to support your reporting system. Since the reporting system will be run on differet SQL Server instance on different machine, it will not hurt your normal business onthe principle server. HTH|||Really your easy options are limited to setting query govenor which is time based not resource based.|||Thanks for the hint, "SET QUERY_GOVERNOR_COST_LIMIT" is useful in this context. Additional question:-Is there a way to cancel running querries? For instance by issuing a "cancel" call from a seperate thread?
Thanks,
Michael
Subscribe to:
Posts (Atom)