grant select to public

I'm NOT the dbo
but I am allowed to create table/views/UDF
But I can't not do this:
grant select on vw_I_created to public
got this error:
SQL Server Database Error: Grantor does not have GRANT permission.
Is this right ?Your user was not given GRANT permission, which is the ability to grant a
specified permission to other principals.
"klabu" wrote:
> I'm NOT the dbo
> but I am allowed to create table/views/UDF
> But I can't not do this:
> grant select on vw_I_created to public
> got this error:
> SQL Server Database Error: Grantor does not have GRANT permission.
> Is this right ?
>
>|||Hi,
On specific objects if you have to give grant again to other securiy
accounts you can use or ask the admin for the grant with below option
WITH GRANT OPTION
Thanks
Ajay
"Edgardo Valdez, MCTS / MCITP"
<EdgardoValdezMCTSMCITP@.discussions.microsoft.com> wrote in message
news:B7485BF1-0937-4FE6-97A0-A4486985F37B@.microsoft.com...
> Your user was not given GRANT permission, which is the ability to grant a
> specified permission to other principals.
> "klabu" wrote:
>> I'm NOT the dbo
>> but I am allowed to create table/views/UDF
>> But I can't not do this:
>> grant select on vw_I_created to public
>> got this error:
>> SQL Server Database Error: Grantor does not have GRANT permission.
>> Is this right ?
>>

Grant Select

I am getting a syntax error in SQL 2005 with the command "Grant Select to user". I get "Incorrect syntax near the keyword 'to'". This worked in the previous version, and according to what I can find in the documentation, it should work in this version. I hope I don't have to specify each table individually. Anybody know any more about this?Why not add the user to db_datareader role?

GRANT error in SQL 2005: GRANT SELECT ON sysprocesses TO PUBLIC

The following GRANT statement used to work in SQL 7.0 and 2000, but due to
security enhancements in SQL 2005, it does not work anymore.
GRANT SELECT ON sysprocesses TO PUBLIC
I receive the message:
Msg 4610, Level 16, State 1, Line 2
You can only grant or revoke permissions on objects in the current database.
I have just installed SQL 2005 Beta 3, connected as 'sa' and I have not
modified any security at all in SQL 2005.
Does anybody knows how can I make it work?
And what are the changes in SQL 2005 Security?
If you can send info about it it would be great.
Best regardsTry USE <DBNAME> before executing the grant.
"Alex CT" <AlexCT@.discussions.microsoft.com> wrote in message
news:9F313976-07F1-4099-9A31-ED58153979C2@.microsoft.com...
> The following GRANT statement used to work in SQL 7.0 and 2000, but due to
> security enhancements in SQL 2005, it does not work anymore.
> GRANT SELECT ON sysprocesses TO PUBLIC
> I receive the message:
> Msg 4610, Level 16, State 1, Line 2
> You can only grant or revoke permissions on objects in the current
database.
> I have just installed SQL 2005 Beta 3, connected as 'sa' and I have not
> modified any security at all in SQL 2005.
> Does anybody knows how can I make it work?
> And what are the changes in SQL 2005 Security?
> If you can send info about it it would be great.
> Best regards|||I dont know but I am very curious about where you got Beta 3 ?
Chris
Alex CT wrote:
> The following GRANT statement used to work in SQL 7.0 and 2000, but
due to
> security enhancements in SQL 2005, it does not work anymore.
> GRANT SELECT ON sysprocesses TO PUBLIC
> I receive the message:
> Msg 4610, Level 16, State 1, Line 2
> You can only grant or revoke permissions on objects in the current
database.
> I have just installed SQL 2005 Beta 3, connected as 'sa' and I have
not
> modified any security at all in SQL 2005.
> Does anybody knows how can I make it work?
> And what are the changes in SQL 2005 Security?
> If you can send info about it it would be great.
> Best regards

GRANT command error

Hello,
I am running a very simple command to grant a stored procedure an EXEC
permission to a login that is created for a NT group. The command is as
follows
GRANT EXECUTE ON [dbo].[sp_RptUsersData] TO CORP\AppDev.
I am getting the error like "Incorrect syntax near '\' "
Please note that same name exist for login and database user i.e CORP\AppDev.
Any help in this matter would be greatly appreciated.
Surround the user in square brackets. [CORP\AppDev]
AndyP,
Sr. Database Administrator,
MCDBA 2003
"David" wrote:

> Hello,
> I am running a very simple command to grant a stored procedure an EXEC
> permission to a login that is created for a NT group. The command is as
> follows
> GRANT EXECUTE ON [dbo].[sp_RptUsersData] TO CORP\AppDev.
> I am getting the error like "Incorrect syntax near '\' "
> Please note that same name exist for login and database user i.e CORP\AppDev.
> Any help in this matter would be greatly appreciated.
>
|||Thanks ... I guess i am very slow today
"AndyP" wrote:
[vbcol=seagreen]
> Surround the user in square brackets. [CORP\AppDev]
> --
> AndyP,
> Sr. Database Administrator,
> MCDBA 2003
>
> "David" wrote:

GRANT command error

Hello,
I am running a very simple command to grant a stored procedure an EXEC
permission to a login that is created for a NT group. The command is as
follows
GRANT EXECUTE ON [dbo].[sp_RptUsersData] TO CORP\AppDev.
I am getting the error like "Incorrect syntax near '' "
Please note that same name exist for login and database user i.e CORP\AppDev
.
Any help in this matter would be greatly appreciated.Surround the user in square brackets. [CORP\AppDev]
AndyP,
Sr. Database Administrator,
MCDBA 2003
"David" wrote:

> Hello,
> I am running a very simple command to grant a stored procedure an EXEC
> permission to a login that is created for a NT group. The command is as
> follows
> GRANT EXECUTE ON [dbo].[sp_RptUsersData] TO CORP\AppDev.
> I am getting the error like "Incorrect syntax near '' "
> Please note that same name exist for login and database user i.e CORP\AppD
ev.
> Any help in this matter would be greatly appreciated.
>|||Thanks ... I guess i am very slow today
"AndyP" wrote:
[vbcol=seagreen]
> Surround the user in square brackets. [CORP\AppDev]
> --
> AndyP,
> Sr. Database Administrator,
> MCDBA 2003
>
> "David" wrote:
>

GRANT command error

Hello,
I am running a very simple command to grant a stored procedure an EXEC
permission to a login that is created for a NT group. The command is as
follows
GRANT EXECUTE ON [dbo].[sp_RptUsersData] TO CORP\AppDev.
I am getting the error like "Incorrect syntax near '\' "
Please note that same name exist for login and database user i.e CORP\AppDev.
Any help in this matter would be greatly appreciated.Surround the user in square brackets. [CORP\AppDev]
--
AndyP,
Sr. Database Administrator,
MCDBA 2003
"David" wrote:
> Hello,
> I am running a very simple command to grant a stored procedure an EXEC
> permission to a login that is created for a NT group. The command is as
> follows
> GRANT EXECUTE ON [dbo].[sp_RptUsersData] TO CORP\AppDev.
> I am getting the error like "Incorrect syntax near '\' "
> Please note that same name exist for login and database user i.e CORP\AppDev.
> Any help in this matter would be greatly appreciated.
>|||Thanks ... I guess i am very slow today :)
"AndyP" wrote:
> Surround the user in square brackets. [CORP\AppDev]
> --
> AndyP,
> Sr. Database Administrator,
> MCDBA 2003
>
> "David" wrote:
> > Hello,
> >
> > I am running a very simple command to grant a stored procedure an EXEC
> > permission to a login that is created for a NT group. The command is as
> > follows
> >
> > GRANT EXECUTE ON [dbo].[sp_RptUsersData] TO CORP\AppDev.
> >
> > I am getting the error like "Incorrect syntax near '\' "
> >
> > Please note that same name exist for login and database user i.e CORP\AppDev.
> >
> > Any help in this matter would be greatly appreciated.
> >
> >

Grant All function giving errors

I'm migrating from 2000 to 2005, what is the best way to handle the following error:

The ALL permission is deprecated and maintained only for compatibility. It DOES NOT imply ALL permissions defined on the entity

The code is below:

DECLARE @.sp_name AS sysname;
DECLARE syscursor CURSOR FOR
SELECT name FROM sysobjects
WHERE (xtype = 'P' or xtype='V') AND ((status & 0x80000000) = 0);

OPEN syscursor;
FETCH NEXT FROM syscursor INTO @.sp_name;

WHILE (@.@.FETCH_STATUS = 0)
BEGIN
EXECUTE ('GRANT all ON ' + @.sp_name + ' TO Public');
FETCH NEXT FROM syscursor INTO @.sp_name;
END

CLOSE syscursor;
DEALLOCATE syscursor;

It is not an error, it is a deprecation warning. The grant still works, for now, but ALL will be removed in a future version of SQL Server.

In SQL Server 2005, we have introduced new permissions. ALL does not include these new permissions (otherwise, existing programs might end up running with higher permissions in SQL Server 2005 than they did in SQL Server 2000). So the warning is for letting you know that ALL doesn't really mean "all permissions" anymore.

You should replace GRANT ALL with specific grants of the permissions you want to GRANT, for example, in your case, you can just replace GRANT ALL with GRANT EXECUTE, as EXECUTE is the only permission implied by ALL on stored procedures.

For additional information on ALL, see: http://msdn2.microsoft.com/en-us/library/ms188371.aspx. Actually, now that I look at it, the stored procedure permissions list is incorrect, it should only include EXECUTE, as I mentioned above. I'll file a bug to have the documentation corrected.

Thanks
Laurentiu

|||Commands like "grant all to admin" used to grant all permissions for all objects available in the database. What is the equivalent of this in SQL Server 2005 (it does not need to grant the new CONTROL right in my case)?

|||

CONTROL on the database is probably the closest. You can also make admin a member of db_owner. But there is no real replacement for ALL.

Thanks
Laurentiu

sql

Grant All function giving errors

I'm migrating from 2000 to 2005, what is the best way to handle the following error:

The ALL permission is deprecated and maintained only for compatibility. It DOES NOT imply ALL permissions defined on the entity

The code is below:

DECLARE @.sp_name AS sysname;
DECLARE syscursor CURSOR FOR
SELECT name FROM sysobjects
WHERE (xtype = 'P' or xtype='V') AND ((status & 0x80000000) = 0);

OPEN syscursor;
FETCH NEXT FROM syscursor INTO @.sp_name;

WHILE (@.@.FETCH_STATUS = 0)
BEGIN
EXECUTE ('GRANT all ON ' + @.sp_name + ' TO Public');
FETCH NEXT FROM syscursor INTO @.sp_name;
END

CLOSE syscursor;
DEALLOCATE syscursor;

It is not an error, it is a deprecation warning. The grant still works, for now, but ALL will be removed in a future version of SQL Server.

In SQL Server 2005, we have introduced new permissions. ALL does not include these new permissions (otherwise, existing programs might end up running with higher permissions in SQL Server 2005 than they did in SQL Server 2000). So the warning is for letting you know that ALL doesn't really mean "all permissions" anymore.

You should replace GRANT ALL with specific grants of the permissions you want to GRANT, for example, in your case, you can just replace GRANT ALL with GRANT EXECUTE, as EXECUTE is the only permission implied by ALL on stored procedures.

For additional information on ALL, see: http://msdn2.microsoft.com/en-us/library/ms188371.aspx. Actually, now that I look at it, the stored procedure permissions list is incorrect, it should only include EXECUTE, as I mentioned above. I'll file a bug to have the documentation corrected.

Thanks
Laurentiu

|||Commands like "grant all to admin" used to grant all permissions for all objects available in the database. What is the equivalent of this in SQL Server 2005 (it does not need to grant the new CONTROL right in my case)?

|||

CONTROL on the database is probably the closest. You can also make admin a member of db_owner. But there is no real replacement for ALL.

Thanks
Laurentiu

Grant Access Error

SQL Server returns an error when I try go grant privileges to a username
containing '.' (dot). The statement goes like this:
GRANT <privileges> on <table_name> TO gh.om
The error message point to the '.' Is there a workaround?
We use the format "sitename"."username" on quite a lot of
serverconfigurations in our company and it will be quite a job to change al
l
the logon ids.
Thanks for any assistance
/Leif S
--
Systems AnalystUse brackets as delimiters:
GRANT <privileges> on <table_name> TO [gh.om]
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Leif S" <LeifS@.discussions.microsoft.com> wrote in message
news:39A5D87F-9801-4013-87CF-FD6A729AEF47@.microsoft.com...
SQL Server returns an error when I try go grant privileges to a username
containing '.' (dot). The statement goes like this:
GRANT <privileges> on <table_name> TO gh.om
The error message point to the '.' Is there a workaround?
We use the format "sitename"."username" on quite a lot of
serverconfigurations in our company and it will be quite a job to change
all
the logon ids.
Thanks for any assistance
/Leif S
--
Systems Analyst|||Thanks, Tom! Problem solved.
/Leif S.
--
Systems Analyst
"Tom Moreau" wrote:

> Use brackets as delimiters:
> GRANT <privileges> on <table_name> TO [gh.om]
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> ..
> "Leif S" <LeifS@.discussions.microsoft.com> wrote in message
> news:39A5D87F-9801-4013-87CF-FD6A729AEF47@.microsoft.com...
> SQL Server returns an error when I try go grant privileges to a username
> containing '.' (dot). The statement goes like this:
> GRANT <privileges> on <table_name> TO gh.om
> The error message point to the '.' Is there a workaround?
> We use the format "sitename"."username" on quite a lot of
> serverconfigurations in our company and it will be quite a job to change
> all
> the logon ids.
> Thanks for any assistance
> /Leif S
> --
> Systems Analyst
>

GPF Error

Hello,
Last week I began working in CR and am having a problem that is driving me crazy. I am using CR 10 in an XP envoronment (verson 2002, SP 2). I install CR with no problems and use it for about two or three hours. Than it starts giving me GPF errors every time I try to add an object to a report. This can be a field, parameter, graphic or text or any other object, it does not matter.

In order to fix this I have un-installed and re-installed CR 4 times, on two different drives. I have updated it with the latest Service Pack (6) and still I get the same problem. It works for a few hours after the install and then stops. This is the case for new as well as existing report.

Does anybody have any ideas?

Thanks in advance.

Steerforth.Forget this post. I just found out what the problem was. I have a dual monitor and the GPF only happens when my screen is in the second monitor and CR is maximized.

GOTO problem

I get the following error when I try and use a GOTO statment with my abel at the bottom of the script:

[[A GOTO statement references the label 'ENDSCRIPT' but the label has not been declared.]]

My code structure is as follows:

Some code here

SET NOCOUNT OFF

SELECT * FROM PERSON_STAGE1 WHERE PERSONUID
IN (SELECT PERSONUID FROM PRO_SING_QA_26923_AMMAR..PERSONUID)

IF @.@.ROWCOUNT > 1
BEGIN
PRINT '*** ERROR: SCRIPT ENDING BECAUSE OF DUPLICATE UID'
GOTO ENDSCRIPT
END

SET NOCOUNT ON

.
.
.
.
.
ENDSCRIPT:

when I execute my code I get the error message mentioned above. Any suggestions?

Thanks in advance.hi,

your script (as much as you have posted) works fine in my enviroment !

markus

Got Microsoft OLE DB Provider for ODBC Drivers error '80004005' when running Stored Procedures

when i am running a Stored Procedures, system always returns me error message below and Stored Procedures stops. please help

Microsoft OLE DB Provider for ODBC Drivers error

'80004005'

[Microsoft][ODBC SQL Server Driver] Received an unrecognized datatype 0 from

TDS data stream

sometime it returns error messge like, TDS Buffer Length Too Large
or
Unknown token received from SQL Server

or
Protocol error in TDS stream
or
Bad token from SQL Server: Datastream processing out of sync.
or
Invalid cursor state
or
TDS Buffer Link Too Large
or
Function sequence error

Many thanks, Please help, appreciated

Can you post more information about the configuration:

- What version of SQL Server do you use?

- What OLEDB provider do you use (e.g., SQLOLEDB, SQL Native Client?)?

- What is the definition of relevan tables?

- Code for the stored procedure and for the application calling it (omit any confidential information)?

|||

Thanks Peter:

it's SQL Server 2000 sp4, this problem happens on the OLEDB provider ODBC driver. the detials of sproc is that there is one sproc calling other 4 different sprocs to update 4 different table (about 30,000 columns need to be updated in each table). and it will run only once a month automaticly by Job.

I also tried to run those 4 sprocs separately and manually, still get the same problem, somehow.

cheers

|||anyone can help?|||

Could be a network performance or configuration error.

http://support.microsoft.com/default.aspx/kb/176256

Got Microsoft OLE DB Provider for ODBC Drivers error '80004005' when running Stored Procedures

when i am running a Stored Procedures, system always returns me error message below and Stored Procedures stops. please help

Microsoft OLE DB Provider for ODBC Drivers error

'80004005'

[Microsoft][ODBC SQL Server Driver] Received an unrecognized datatype 0 from

TDS data stream

sometime it returns error messge like, TDS Buffer Length Too Large
or
Unknown token received from SQL Server

or
Protocol error in TDS stream
or
Bad token from SQL Server: Datastream processing out of sync.
or
Invalid cursor state
or
TDS Buffer Link Too Large
or
Function sequence error

Many thanks, Please help, appreciated

Can you post more information about the configuration:

- What version of SQL Server do you use?

- What OLEDB provider do you use (e.g., SQLOLEDB, SQL Native Client?)?

- What is the definition of relevan tables?

- Code for the stored procedure and for the application calling it (omit any confidential information)?

|||

Thanks Peter:

it's SQL Server 2000 sp4, this problem happens on the OLEDB provider ODBC driver. the detials of sproc is that there is one sproc calling other 4 different sprocs to update 4 different table (about 30,000 columns need to be updated in each table). and it will run only once a month automaticly by Job.

I also tried to run those 4 sprocs separately and manually, still get the same problem, somehow.

cheers

|||anyone can help?|||

Could be a network performance or configuration error.

http://support.microsoft.com/default.aspx/kb/176256

got error when using stored procedure with temp table in it

I got an error message when i created a dataset using a stored procedure with temp table in it. The error is:

"could not generate a list of fields for the query. check the query syntax or click the refresh fields on the query toolbar. invalid object name ' #indented' ".

after I click on the refresh button, everything looks fine. But after I put the fields in the table, and go to the Preview, it is a blank report.

Does anybody know what happened?

3x a lot!

Your problem occurs because the sproc isn't returning metadata (column descriptions, etc.) from your temp table dependably -- Nothing that you did wrong, we just can't get meta data from an object which doesn't exist yet...The way we normally get metadata back from sprocs doesn't work when they return data from a temp table.

There are two thing you can try, although I think you've already done #1...

#1. Use the generic query designer, click "Refresh Fields", and you'll get a little dialog asking you for paramter info for your sproc...provide it.. finish your work, cross your fingers, move to layout view and then Preview

#2. In your sproc, return your data using a table variable vs. Select * from ##SomeTempTable

From what I read, you'll actually only have this problem in the designer...if you went ahead and plugged in the correct RDL for your fields manually and then deployed the report to the server, it would run fine...

|||Thank you!|||I also get an error because of a temp table, but this happens when creating data-driven subscription query: The

dataset cannot be generated. An error occurred while connecting to a

data source, or the query is not valid for the data source.

(rsCannotPrepareQuery) Invalid object name '##XX'. Can you help me with

this?|||

Unfortunately, you're running into a behavior you're not going to able to avoid...if you try and set up the subscription via Report Manager it (Report Manager) will call "PrepareQuery" (which we would expect to fail when dealing with temp tables) ...That's why you get your error message.

If you're really serious about using the temp table, you can still get a data-driven subscription working, BUT the price that you pay is you'll have to do it via code, using the Web Service API to do the work...If you go directly against the web service, you bypass Report Manager calling (and then failing against) "PrepareQuery"...and even when/if you get this working, you'll NEVER be able to edit the subscription in the UI, or you'll get the same errors again. So, you really should just try and whack the use of #temp tables in your scenario. :)

|||Thank you for your

answer. My temporary solution was this: when I validate the query I get

the error described above however if I

run the same stored procedure that is used in a query in let's say

management studio query and leave it open, the query in data driven

subscription web interface is validated succesfully. It works for now,

but I will try to get rid of the temp tables.

|||Ha! Great idea...Never would have thought of this!

Got an error when applying Template to the Report

Hi All,
I produced a report in Crystal V9,the report which i got was WITHOUT template.

The Report i generated was the Statndard report.

Now i want to apply template for this report.

For this i clicked on the "Template Expert Button", selected the "Block(Blue)"[Pre Defined Template] and clicked on OK Button

Now i got an error saying "Could Not Apply Template to Document"

How to Handle this error

I want to apply the template for the report.If you are using chart or if the page is too large or small, you may get this problem

Got a unicode error during copying database

I got following problems during copying database around different hosts.

Could anyone help me?

BR.
nathan

Event Type: Error
Event Source: SQLISPackage
Event Category: None
Event ID: 12550
Date: 8/15/2007
Time: 2:53:51 AM
User: NT AUTHORITY\SYSTEM
Computer: DB1
Description:
Event Name: OnError
Message: ERROR : errorCode=-1071636471 description=SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Unicode conversion failed".
helpFile=dtsmsg.rll helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC}
StackTrace: at Microsoft.SqlServer.Management.Dts.DtsTransferProvider.ExecuteTransfer()
at Microsoft.SqlServer.Management.Smo.Transfer.TransferData()
at Microsoft.SqlServer.Dts.Tasks.TransferObjectsTask.TransferObjectsTask.TransferDatabasesUsingSMOTransfer()
Operator: NT AUTHORITY\SYSTEM
Source Name: db_pushfun_com_DB1_Transfer Objects Task
Source ID: {939367C5-D98E-4C92-9688-9F32595DB981}
Execution ID: {09E39535-95E0-47EC-B49C-803B0986B1BB}
Start Time: 8/15/2007 2:53:51 AM
End Time: 8/15/2007 2:53:51 AM
Data Code: 0

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

Well, I apologize if I am stating the obvious but it appears that you are trying to move a "Unicode" data type into a non-unicode target. Check your source and target databases for the data type of the fields. If they are different then you need to use the appropriate Type Case in a Data Conversion task to convert the source format to your target format.|||Actually, I'm copying a database not table. It's so strange that I could copy databases with unicode data from different hosts.

Going nuts here!

Alright- I plugged this code in to tell my form to write to SQLServer when I click the Submit button. However I error out when I load the form:

dim Conn as new OleDbConnection("DSN=MyDB")

Sub On_Click(obj as object, e as EventArgs)
dim params(3) as String
dim strSQL as String

strSQL="INSERT INTO tblSCRequest "(strRequestor, dtRequestDate, strAudience) VALUES (" & _
"'" & params(0) & "'," & _
"'" & params(1) & "'," & _
"'" & params(2) & "'")

ExecuteStatement(strSQL)
end sub

function ExecuteStatement(strSQL)
dim objCmd as new OleDbCommand(strSQL, Conn)

try
objCmd.Connection.Open()
objCmd.ExecuteNonquery()
catch ex as Exception
end try
objCmd.Connection.Close()
end function

I get this error:
C:\Inetpub\wwwroot\SOSComm\CommWeb4.aspx(14) : error BC30471: Expression is not an array or a method, and cannot have an argument list.

strSQL="INSERT INTO tblSCRequest "(strRequestor, dtRequestDate, strAudience) VALUES (" & _
~~~~~~~~~~~~~~~~~~~~~~~~~~~
C:\Inetpub\wwwroot\SOSComm\CommWeb4.aspx(14) : error BC30205: End of statement expected.

strSQL="INSERT INTO tblSCRequest "(strRequestor, dtRequestDate, strAudience) VALUES (" & _
~~~~~~~~~~~~~
C:\Inetpub\wwwroot\SOSComm\CommWeb4.aspx(15) : error BC30035: Syntax error.

"'" & params(0) & "'," & _

Any ideas??try this :

strSQL="INSERT INTO tblSCRequest (strRequestor, dtRequestDate, strAudience) VALUES (" & _
"'" & params(0) & "','" & params(1) & "','" & params(2) & " ')"

better yet, use parameterized queries. (1) you dont have to worry about these quotes (2) you are safe from sql injection attacks.

hth|||So what if for simplicity sake I try this-

strSQL=""INSERT INTO tblSCRequest (strRequestor) VALUES (" & _
";" & params (0) & "')"

Would this work? Also, I saw your link to the param site. If I get this working I'm going to use that going forward. Thanks.|||no a ; indicates end of sql statement. use parameterized queries. thats the best way to go about it.

hth|||Okay. I have this problem now-

Sub Submit(obj as object, e as EventArgs)
dim params(3) as String
dim strSQL as String

strSQL="INSERT INTO Comm (strRequestor, dtRequestDate, strAudience) VALUES (" & _
"'" & params(0) & "','" & params(1) & "','" & params(2) & " ')"

ExecuteStatement(strSQL)

end sub

function ExecuteStatement(strSQL)
dim objCmd as new OleDbCommand()

try
objCmd.Connection.Open()
objCmd.ExecuteNonquery()
catch ex as exception
end try
objCmd.Connection.Close()
end function

I get an error saying "System.NullReferenceException: Object reference not set to an instance of an object." And it is pointing to line 31 as the culprit-

Line 29: catch ex as exception
Line 30: end try
Line 31: objCmd.Connection.Close()<---Error line
Line 32: end function|||(1) use parameterized queries. ( i suggested this earlier too)
(2) make sure you have values in all the parameters.
(3) put the connection.close in the finally block

hth

Go and goto in one sql script gives error label not declared

Hi,

I have a problem:
I am writing an update script for a database and want to check for the
version and Goto the wright update script.

So I read the version from a table and if it match I want to "Goto
Versionxxx"

Where Versionxxx: is set in the script with the right update script.

Whenever I have some script which need Go commands I get error in the
output that

A GOTO statement references the label 'Versionxxx' but the label has
not been declared.

But the label is set in the script by 'Versionxxx:'

Is there a way I can solve this easily?

Thanks in advanceHere's the trick with "GO":

It's not actually a part of the T-SQL language. It's a batch
separator. (Don't believe me? Try running "exec('GO')" in Query
Analyzer.)

Think of it like this: Cut up your script into multiple files,
separated by the "GO" statement. Run each of these files individually,
but use the same connection. That's all "GO" does.

So you need to remove the "GO" batch separators in between your
statements that need to be run in the same batch.

-Dave Markle
http://www.markleconsulting.com/blog
BF wrote:

Quote:

Originally Posted by

Hi,
>
I have a problem:
I am writing an update script for a database and want to check for the
version and Goto the wright update script.
>
So I read the version from a table and if it match I want to "Goto
Versionxxx"
>
Where Versionxxx: is set in the script with the right update script.
>
Whenever I have some script which need Go commands I get error in the
output that
>
A GOTO statement references the label 'Versionxxx' but the label has
not been declared.
>
But the label is set in the script by 'Versionxxx:'
>
Is there a way I can solve this easily?
>
Thanks in advance

|||Thanks for the quick respond.

The solution is not quite what I was hoping for.

For each new version I create an update script, We have an app which
does that and there are lots of Go commands.

I want to have one update script for all versions of the app so we have
2.00 to 2.01 to 2.02 to 2.03 etc.

For each version I have a script and I want to lookup the version, if
version is 2.03 I can start updating from 2.03 to 2.04 with the goto I
can jump over all other updates because they are already done in the
past.

When I use different files I cannot easy control which files to
execute, or I have to run them from the main script.

Grtx Bob

dmarkle schreef:

Quote:

Originally Posted by

Here's the trick with "GO":
>
It's not actually a part of the T-SQL language. It's a batch
separator. (Don't believe me? Try running "exec('GO')" in Query
Analyzer.)
>
Think of it like this: Cut up your script into multiple files,
separated by the "GO" statement. Run each of these files individually,
but use the same connection. That's all "GO" does.
>
So you need to remove the "GO" batch separators in between your
statements that need to be run in the same batch.
>
-Dave Markle
http://www.markleconsulting.com/blog
>

|||To be totally honest with you, I think the easiest/best way to solve
this would be to write a batch file that calls OSQL or SQLCMD against
the proper version of the file. Put your version-switching logic in
the batch file, and simply run OSQL on the appropriate files.

Some people execute their batches using sp_executesql, but it's really
messy and I don't really recommend it. Basically, using this method,
you'd be doing things like:

EXEC sp_executesql 'CREATE TABLE dbo.foo'
EXEC sp_executesql 'CREATE INDEX IX_xxx ON dbo.foo'
...

instead of:

CREATE TABLE dbo.foo
GO
CREATE INDEX IX_xxx ON dbo.foo
...

AFAIK, that's the only way to do what you want to do in 100% pure
T-SQL.

-Dave

BF wrote:

Quote:

Originally Posted by

Thanks for the quick respond.
>
The solution is not quite what I was hoping for.
>
For each new version I create an update script, We have an app which
does that and there are lots of Go commands.
>
I want to have one update script for all versions of the app so we have
2.00 to 2.01 to 2.02 to 2.03 etc.
>
For each version I have a script and I want to lookup the version, if
version is 2.03 I can start updating from 2.03 to 2.04 with the goto I
can jump over all other updates because they are already done in the
past.
>
When I use different files I cannot easy control which files to
execute, or I have to run them from the main script.
>
Grtx Bob
>
dmarkle schreef:

Quote:

Originally Posted by

Here's the trick with "GO":

It's not actually a part of the T-SQL language. It's a batch
separator. (Don't believe me? Try running "exec('GO')" in Query
Analyzer.)

Think of it like this: Cut up your script into multiple files,
separated by the "GO" statement. Run each of these files individually,
but use the same connection. That's all "GO" does.

So you need to remove the "GO" batch separators in between your
statements that need to be run in the same batch.

-Dave Markle
http://www.markleconsulting.com/blog

|||BF (bob@.faessen.net) writes:

Quote:

Originally Posted by

For each new version I create an update script, We have an app which
does that and there are lots of Go commands.

No there isn't. There are a lot of GO separators.

Quote:

Originally Posted by

I want to have one update script for all versions of the app so we have
2.00 to 2.01 to 2.02 to 2.03 etc.
>
For each version I have a script and I want to lookup the version, if
version is 2.03 I can start updating from 2.03 to 2.04 with the goto I
can jump over all other updates because they are already done in the
past.
>
When I use different files I cannot easy control which files to
execute, or I have to run them from the main script.

Right. The best way is to solve this is to write a little script runner that
reads a suite of files, and from the file names decudes which version the
file applies to, and then runs the file if needed. Your script would have to
break the script apart on the "go" separator, but this is trivial stuff.
(Hint: don't worry about "go" being entwined in comments ot string literals.
The standard query tools don't do that either. But care about leading and
trailing blanks, and inconsistent use of upper/lowercase.)

You can write this simple script runner in about any language - except for
T-SQK.

--
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|||Ok, great answers:

I build the updates with installshield 12 and I am no programmer so I
will go and try which will fit for me.

Probably I will place all scripts in the support dir from installshield
12 and run the from a vbscript with sqlcmd based on some tests.

I will try some things this week.

Thanks for the replies.

Grtx Bob

Erland Sommarskog schreef:

Quote:

Originally Posted by

BF (bob@.faessen.net) writes:

Quote:

Originally Posted by

For each new version I create an update script, We have an app which
does that and there are lots of Go commands.

>
No there isn't. There are a lot of GO separators.
>

Quote:

Originally Posted by

I want to have one update script for all versions of the app so we have
2.00 to 2.01 to 2.02 to 2.03 etc.

For each version I have a script and I want to lookup the version, if
version is 2.03 I can start updating from 2.03 to 2.04 with the goto I
can jump over all other updates because they are already done in the
past.

When I use different files I cannot easy control which files to
execute, or I have to run them from the main script.

>
Right. The best way is to solve this is to write a little script runner that
reads a suite of files, and from the file names decudes which version the
file applies to, and then runs the file if needed. Your script would have to
break the script apart on the "go" separator, but this is trivial stuff.
(Hint: don't worry about "go" being entwined in comments ot string literals.
The standard query tools don't do that either. But care about leading and
trailing blanks, and inconsistent use of upper/lowercase.)
>
You can write this simple script runner in about any language - except for
T-SQK.
>
--
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

gmaniz service failed

Dear all,
please help me to find error about 'the gmaniz service failed to start
due to the following error'
Thanks
This is not a SQL Server service. In fact, I didn't get one single hit (except this post) on Google.
My guess is that this is some in-house developed software, so you need to find what it is and talk
to the people responsible for it.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ekodian" <ekodian@.gmail.com> wrote in message news:e8beptSUFHA.3944@.tk2msftngp13.phx.gbl...
> Dear all,
> please help me to find error about 'the gmaniz service failed to start due to the following error'
> Thanks

gmaniz service failed

Dear all,
please help me to find error about 'the gmaniz service failed to start
due to the following error'
ThanksThis is not a SQL Server service. In fact, I didn't get one single hit (except this post) on Google.
My guess is that this is some in-house developed software, so you need to find what it is and talk
to the people responsible for it.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ekodian" <ekodian@.gmail.com> wrote in message news:e8beptSUFHA.3944@.tk2msftngp13.phx.gbl...
> Dear all,
> please help me to find error about 'the gmaniz service failed to start due to the following error'
> Thanks