Showing posts with label dynamic. Show all posts
Showing posts with label dynamic. Show all posts

Friday, March 30, 2012

OUTPUT - help with please

I am using a dynamic t-sql string in proc1 to execute proc2, which returns an int variable named @.Fatal_Error back to proc1.

When I execute proc2 I use the syntax:

EXEC @.SQL @.Params

@.SQL is the Proc Name (varchar) and @.Params is the parameter string (nvarchar).

If I include the @.Fatal_Error variable in the dynamic creation of the @.Params string the returning value from Proc2 is unable to convert int to nvarchar.

I have declared @.Fatal_Error in proc1 as int and tried to add to the end of my dynamic t-sql EXEC but I still get 'Cannot convert int to nvarchar' .

Please help - I'm beginning to pull out hair! :-)

Thanks!

Here' s the syntax I tried when just passing it at the end of the EXEC call:

EXEC @.SQL @.Param_List = @.Fatal_Error

AND I also tried:

EXEC @.SQL @.Param_List + ' '+@.Fatal_Error+' '

You have to use the sp_executesql for parameterized dynamic sql,

Code Snippet

Declare @.idParm as int;

Declare @.nameParm as varchar(100);

Declare @.dynamicSql as nvarchar(1000);

Declare @.dynamicParamDef as nvarchar(1000);

Set @.idParm = 2;

Set @.nameParm = 'sysobjects'

Set @.dynamicSql = N'Select * from sysobjects where id=@.id or name=@.name'

Set @.dynamicParamDef = N'@.id as int, @.name as varchar(100)'

Exec sp_executesql @.dynamicSql, @.dynamicParamDef, @.idParm, @.nameParm

|||

I quote from BOL(look for sp_executesql, building statement at runtime)

"

Transact-SQL supports the following methods of building SQL statements at run time in Transact-SQL scripts, stored procedures, and triggers:

Use the sp_executesql system stored procedure to execute a Unicode string. sp_executesql supports parameter substitution similar to the RAISERROR statement.

Use the EXECUTE statement to execute a character string. The EXECUTE statement does not support parameter substitution in the executed string."|||...and Manivannan prove it|||

Thank you all for your assistance. I will give it a whirl.

I was able to finally execute with EXEC @.SQL @.Params, @.Fatal_Error = @.Fatal_Error

However, I'm sure this will come back to bite me in the long run..

Thank you ALL for your quick replies!

|||

@.SQL is my stored procedure name

@.Param_List is the list of enumerated parameters

SET @.SQL = @.SQL + IsNull(@.Param_List,'');

EXECUTE sp_executesql @.SQL;

Worked like a charm.

Thanks!

Sandy

Monday, March 26, 2012

Outer Join in Data-Source-View

I have 3 tables:

Master, Dynamic, Summary

which are joined as follows:

- Master-Dynamic on e_id

- Dynamic-Summary on p_id + g_no

- Master-Summary on p_id + g_no

Now my Summary table is missing some p_id, hence the Cube deployment fails with these Errors:

Errors in the OLAP storage engine: The attribute key cannot be found: Table: DYNAMIC, Column: p_id, Value: AB2.

Errors in the OLAP storage engine: The attribute key was converted to an unknown member because the attribute key was not found. Attribute SUMMARY of Dimension: SUMMARY from Database: Cohort1, Cube: Analytics, Measure Group: DYNAMIC, Partition: DYNAMIC, Record: 11665.

I tried reversing the table relationships, but that does not help. My constraint is that I am fetching data from a (large) read-only DB, hence I cannot create a view on that DB. My Data source view has these 3 tables and 2 named queries. I don't think I can put the outer join SQL clause anywhere. Also my dynamic table is quite large (189m rows) and takes 8hours to query. Hence I can't add another view to it as it will increase the read-time.

Can anyone suggest how to add instructions for outer join in the .dsv file in the <msdata:Relationship> tag? Is it possible to do it there?

Thanks a lot.

If you cannot modify your relational database your only option is to try and use named queries in DSV to fix problem.

Trying to modify .dsv file directly doesnt help. All functionality avaliable in DSV is exposed to you in DSV editior. Dont think you will get anything by trying to modify it directly.

You can tell Analysis Services to ignore referential integrity errors caught during processing. But that practice is going to slow your processing performance and might lead to later problems with processing partitions. That in case if ignored dimension members have some data assosiated in fact table.

I would say you need to fight for fixing referential integrity errors in relational data base. Analysis Services could go as far as to help reporting them.

Edward Melomed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

Tuesday, March 20, 2012

other characters (codes) that represent single quote

Hi,
I am trying to replace every single quote with two "single quotes", prior to
sending the dynamic sql string to sql server.
( from a C# application)
(in this case I have to send a dynamic string, cannot do parametarized
queries)
Question is....
Is there any other character that sql server will interpret as a "single
quote"?
Any help is deeply appreciated.
Thanks
NalakaHi Nalaka,
Thank you for your post.
Based on my scope, I don't think SQL Server will interpret other character
as single quote.
Here I would like provide some information about passing dynamic string to
SQL Server:
When possible, reject input that contains the following characters.
Input character Meaning in Transact-SQL
; Query delimiter
' Character data string delimiter
-- Comment delimiter
/* ... */ Comment delimiters. Text between /* and
*/ is not evaluated by the server.
Xp_ Begins the name of catalog extended stored
procedures such as xp_cmdshell.
You may need to refer the following article:
SQL Injection
http://msdn2.microsoft.com/en-us/library/ms161953.aspx
Hope this will be helpful!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Thanks Wei,
This is what I don't understand... theoretically if I replace every single
quote with two single quotes.. all is well.
But there is a ton of articles suggesting all these other precautions.....
Nalaka
"Wei Lu" <weilu@.online.microsoft.com> wrote in message
news:1Kfz6%23FhGHA.5608@.TK2MSFTNGXA01.phx.gbl...
> Hi Nalaka,
> Thank you for your post.
> Based on my scope, I don't think SQL Server will interpret other character
> as single quote.
> Here I would like provide some information about passing dynamic string to
> SQL Server:
> When possible, reject input that contains the following characters.
> Input character Meaning in Transact-SQL
> ; Query delimiter
> ' Character data string delimiter
> -- Comment delimiter
> /* ... */ Comment delimiters. Text between /* and
> */ is not evaluated by the server.
> Xp_ Begins the name of catalog extended
> stored
> procedures such as xp_cmdshell.
> You may need to refer the following article:
> SQL Injection
> http://msdn2.microsoft.com/en-us/library/ms161953.aspx
> Hope this will be helpful!
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||"Nalaka" <nalaka12@.nospam.nospam> wrote in message
news:%23CKpYJOhGHA.2188@.TK2MSFTNGP04.phx.gbl...
> Thanks Wei,
> This is what I don't understand... theoretically if I replace every single
> quote with two single quotes.. all is well.
> But there is a ton of articles suggesting all these other
precautions.....
>
Most likely their concern for SQL injection attacks.
Basically any place you accept input, if you're not careful, a bad user
could do something like:
Enter First Name: dummy; select * from sysobjects
And if your select query isn't written well, it now becomes something like:
Select id from table where fname=dummy; select * from sysobjects and now
your sysobjects table has been returned to the hacker.
> Nalaka
>
> "Wei Lu" <weilu@.online.microsoft.com> wrote in message
> news:1Kfz6%23FhGHA.5608@.TK2MSFTNGXA01.phx.gbl...
> > Hi Nalaka,
> >
> > Thank you for your post.
> >
> > Based on my scope, I don't think SQL Server will interpret other
character
> > as single quote.
> >
> > Here I would like provide some information about passing dynamic string
to
> > SQL Server:
> >
> > When possible, reject input that contains the following characters.
> >
> > Input character Meaning in Transact-SQL
> > ; Query delimiter
> >
> > ' Character data string delimiter
> >
> > -- Comment delimiter
> >
> > /* ... */ Comment delimiters. Text between /*
and
> > */ is not evaluated by the server.
> >
> > Xp_ Begins the name of catalog extended
> > stored
> > procedures such as xp_cmdshell.
> >
> > You may need to refer the following article:
> >
> > SQL Injection
> > http://msdn2.microsoft.com/en-us/library/ms161953.aspx
> >
> > Hope this will be helpful!
> >
> > Sincerely,
> >
> > Wei Lu
> > Microsoft Online Community Support
> >
> > ==================================================> >
> > When responding to posts, please "Reply to Group" via your newsreader so
> > that others may learn and benefit from your issue.
> >
> > ==================================================> > This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> >
>|||Hi Nalaka,
Thank you for the update.
As Greg mentioned, there is some secury issue you should considered in you
application code.
SQL injection should be avoid.
You could refer the article I posted in the previous post.
SQL Injection
http://msdn2.microsoft.com/en-us/library/ms161953.aspx
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Thanks...
"Wei Lu" <weilu@.online.microsoft.com> wrote in message
news:ugdnCtVhGHA.5608@.TK2MSFTNGXA01.phx.gbl...
> Hi Nalaka,
> Thank you for the update.
> As Greg mentioned, there is some secury issue you should considered in you
> application code.
> SQL injection should be avoid.
> You could refer the article I posted in the previous post.
> SQL Injection
> http://msdn2.microsoft.com/en-us/library/ms161953.aspx
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||Hi Nalaka,
Glad to hear the information is helpful.
If you have any questions or concerns, please feel free to let me know.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.

other characters (codes) that represent single quote

Hi,
I am trying to replace every single quote with two "single quotes", prior to
sending the dynamic sql string to sql server.
( from a C# application)
(in this case I have to send a dynamic string, cannot do parametarized
queries)
Question is....
Is there any other character that sql server will interpret as a "single
quote"?
Any help is deeply appreciated.
Thanks
NalakaHi Nalaka,
Thank you for your post.
Based on my scope, I don't think SQL Server will interpret other character
as single quote.
Here I would like provide some information about passing dynamic string to
SQL Server:
When possible, reject input that contains the following characters.
Input character Meaning in Transact-SQL
; Query delimiter
' Character data string delimiter
-- Comment delimiter
/* ... */ Comment delimiters. Text between /* and
*/ is not evaluated by the server.
Xp_ Begins the name of catalog extended stored
procedures such as xp_cmdshell.
You may need to refer the following article:
SQL Injection
http://msdn2.microsoft.com/en-us/library/ms161953.aspx
Hope this will be helpful!
Sincerely,
Wei Lu
Microsoft Online Community Support
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.|||Thanks Wei,
This is what I don't understand... theoretically if I replace every single
quote with two single quotes.. all is well.
But there is a ton of articles suggesting all these other precautions.....
Nalaka
"Wei Lu" <weilu@.online.microsoft.com> wrote in message
news:1Kfz6%23FhGHA.5608@.TK2MSFTNGXA01.phx.gbl...
> Hi Nalaka,
> Thank you for your post.
> Based on my scope, I don't think SQL Server will interpret other character
> as single quote.
> Here I would like provide some information about passing dynamic string to
> SQL Server:
> When possible, reject input that contains the following characters.
> Input character Meaning in Transact-SQL
> ; Query delimiter
> ' Character data string delimiter
> -- Comment delimiter
> /* ... */ Comment delimiters. Text between /* and
> */ is not evaluated by the server.
> Xp_ Begins the name of catalog extended
> stored
> procedures such as xp_cmdshell.
> You may need to refer the following article:
> SQL Injection
> http://msdn2.microsoft.com/en-us/library/ms161953.aspx
> Hope this will be helpful!
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ========================================
==========
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
==========
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||"Nalaka" <nalaka12@.nospam.nospam> wrote in message
news:%23CKpYJOhGHA.2188@.TK2MSFTNGP04.phx.gbl...
> Thanks Wei,
> This is what I don't understand... theoretically if I replace every single
> quote with two single quotes.. all is well.
> But there is a ton of articles suggesting all these other
precautions.....
>
Most likely their concern for SQL injection attacks.
Basically any place you accept input, if you're not careful, a bad user
could do something like:
Enter First Name: dummy; select * from sysobjects
And if your select query isn't written well, it now becomes something like:
Select id from table where fname=dummy; select * from sysobjects and now
your sysobjects table has been returned to the hacker.

> Nalaka
>
> "Wei Lu" <weilu@.online.microsoft.com> wrote in message
> news:1Kfz6%23FhGHA.5608@.TK2MSFTNGXA01.phx.gbl...
character[vbcol=seagreen]
to[vbcol=seagreen]
and[vbcol=seagreen]
>|||Hi Nalaka,
Thank you for the update.
As Greg mentioned, there is some secury issue you should considered in you
application code.
SQL injection should be avoid.
You could refer the article I posted in the previous post.
SQL Injection
http://msdn2.microsoft.com/en-us/library/ms161953.aspx
Sincerely,
Wei Lu
Microsoft Online Community Support
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.|||Thanks...
"Wei Lu" <weilu@.online.microsoft.com> wrote in message
news:ugdnCtVhGHA.5608@.TK2MSFTNGXA01.phx.gbl...
> Hi Nalaka,
> Thank you for the update.
> As Greg mentioned, there is some secury issue you should considered in you
> application code.
> SQL injection should be avoid.
> You could refer the article I posted in the previous post.
> SQL Injection
> http://msdn2.microsoft.com/en-us/library/ms161953.aspx
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ========================================
==========
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
==========
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||Hi Nalaka,
Glad to hear the information is helpful.
If you have any questions or concerns, please feel free to let me know.
Sincerely,
Wei Lu
Microsoft Online Community Support
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.

Wednesday, March 7, 2012

osql -o

I would like to create a dynamic output file with the following code:
Declare @.cmd varchar(1000)
Declare @.filepath varchar(1000)
Set @.filepath = '\\server\path\filename+date.csv >>>> I would like the
mm/dd/yy appended to the filename then .csv extension
Set @.cmd = 'Osql -E -S MyservA -d msdb -Q "sp_databases" /o "@.Filepath" -w
2000' >>>> here is where I would like the new filename to be used.
Exec master..xp_cmdshell @.cmd
Is there also anyway to log this to a file to track execution ?
Thanks...Hi,
you might be interested in that:
http://www.databasejournal.com/feat...cle.php/3386661
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Hoosbruin" <Hoosbruin@.Kconline.com> schrieb im Newsbeitrag
news:BNGdnQ_yUIxBcuvfRVn-1Q@.kconline.com...
>I would like to create a dynamic output file with the following code:
>
> Declare @.cmd varchar(1000)
> Declare @.filepath varchar(1000)
> Set @.filepath = '\\server\path\filename+date.csv >>>> I would like the
> mm/dd/yy appended to the filename then .csv extension
> Set @.cmd = 'Osql -E -S MyservA -d msdb -Q "sp_databases" /o "@.Filepath" -w
> 2000' >>>> here is where I would like the new filename to be used.
> Exec master..xp_cmdshell @.cmd
>
> Is there also anyway to log this to a file to track execution ?
>
> Thanks...
>
>
>