It might be an old question but wanted to see, if we have any latest techniques (other than bcp).
SELECT Field1, Field2 FROM MyTable
If I want to export the output of the above query to a csv on a network folder? I would like to avoid usage of SSIS package or BCP (as user needs to get additional rights to execute bcp).
I need to generate data models of all databases for an instance. How can I accomplish this?
View 3 Replies View RelatedI've been tasked to generate some test data (a few thousand rows) into a new table in a new database. This database is a whole new idea, so I can't write a query to pull pieces of data from other databases. I cannot consider any third party tools, such as what Redgate or Idera has to offer. I can't consider free tools such as what I've found on GitHub. I've been instructed to restrict myself to Visual Studio 2013 and whatever I can get that works within that.
View 5 Replies View RelatedI am working on SQL SERVER schemas using a plug-in in SQL developer itself now my requirement is to generate DDL for a table which is part of SQL SERVER schema...
View 3 Replies View RelatedOur end users is getting below error, when they try to connect to our database:
"Cannot generate SSPI Context."
That is Windows based application and we have done everything like restart our DB Server , reinstall exe in users system, but still issue is same.The issue has occured from when DB Server has restarted and at the same time few users are connected. before that it was working fine. we could not find what is issue.
I need to generate the week ranges like this format :
Here from date and to date would be picked up from the table but just to make you understand i have hardcoded it but this is the real date which is falling inside the table.
Note : Week should be generated from Monday to Sunday within desired date range
I have data in Sql table , I want to convert it to xml using xsd using script component in ssis.
View 0 Replies View Relatedi need to generate documents for customers to sign automatically as sales staff enter their data into SQL. These are invoice style documents. I currently have word templates of the invoice documentation, i just need to be able to add the clients names, address etc into the relevant spaces for them to print off and sign.
I am good with TSQL and writing Stored Procs etc and can easily get the data ready - i just need to find a way to populate the templates in the right places and then save a copy for emailing.
Just wondering if there is any query which can give me report of locked account in SQL 2008. I know there are options like:-
1) Can go in ssms--security--account and right click and select status. This is applicable to individual account.
2)SELECT LOGINPROPERTY('accountid', 'IsLocked'). Also applicable to individual account
3) SELECT name,type_desc,is_disabled,modify_date,default_database_name from sys.server_principals order by type_desc
Generate the report but give the informatio as is_disabled. But I think is_disabled <> is_locked.
GENERATE 8 CHARACTER ALPHANUMERIC SEQUENCES
Requirements
• ALPHANUMERIC FORMAT – > AA00AA00………..ZZ99ZZ99
Last 8 bytes will alternate between 2 byte alpha/2 byte numeric
• Generate from Alphabets – A through Z Numbers -0 to 9
• Generate Unique Sequence (No Duplicates).
• Must Eliminate letters I and O
Output Expected
• AA00AA00………..ZZ99ZZ99
• Using 24 alphabets & 10 digits ,
24*24*10*10*24*24 = 3 317 760 000 records
Below is my Sql Function -
CREATE function [dbo].[SequenceComplexNEW]
(
@Id BIGINT
)
Returns char(8)
[Code] .....
I need to create a script that adds an incrementing suffix to two columns, but restarts based on the value of another column. I found a similar question in the SQL Server 2000 forum, but it doesn't quite fit and also I'm working with SQL Server 2008 R2. The code below both creates a table with test data and tries to carry out the task. If you run this, you will see that the VISITNUM column has a value of UNS in row 4, UNS.1 in row 5 and UNS.2 in row 6. In row 7 it's V200, then in rows 8 and 9 it's UNS.3 for both. The same suffix gets applied to the VISIT column, but of course if I can solve this for VISITNUM then adding the suffix to VIST as well will be easy.
What I need is for row 8 to have UNS and row 9 to have UNS.1. In other words, any time the VISITNUM is UNS several times in a row, I need to add that ".X" suffix, but if a row has something other than UNS, I need to start over again the next time it's UNS again.
CREATE TABLE #testing(
KitID varchar(20),
SubjID varchar(20),
VISIT varchar(60),
VISITNUM varchar(20),
[code]....
There is a SQL Server 2008 R2 SP3 Clustered Instance that has Transactional Replication. It is by no means a large replication setup in terms of data/article count. SQL Server was recently patched to SP3 and is current on Windows 2008 R2 Patches.
When I added a new article to replication (via 2014 SSMS GUI) it seems to add everything correctly (replication tables/procs show the new article as part of the publication).
The Publication is set to allow the snapshot to generate for just new articles (setting immediate_sync & allow_anonymous to false).
When the snapshot agent is run, it runs without error and claims to have generated a snapshot of 1 article. However the snapshot folder only contains a folder for the instance (that does have the modified time of the snapshot agent execution) and none of the regular bcp/schema files.
The tables never make it to the subscribers and replication continues on without error for the existing articles. No agents produce any errors and running the snapshot agent w/ verbose output provides no errors or insight into any possible issues.
I have tried:
- dropping/re-adding the article in question.
- Setting up a new Snapshot Folder
- Validated all the settings and configurations
I'm hesitant to reinitialize a subscriber since I am not confident a snapshot can be generated. Also wondering if this is related to the SP3 Upgrade, every few months new articles are added to the publication and this is the first time since the upgrade to SP3 that it has been done.
I have a SQL2008 database, running Standard Edition 64-bit, database owns by sa, connected to Management Studio using Windows Authentication mode. When I tried to generate scripts from a database, I got the following error messages:-
[Operation is not valid due to the current state of the object. (SqlManagerUI)]
which happened at one particular table. I have reviewed this table definitions are normal, and I could select data from it.
I couldn't find any information anywhere relating to this error messages, except that someone got it when they were trying to change the authentication mode or sa password.
I am trying to create an alert when there are more than 2500 connections to our ailing SQL Server.However, for now, I need to restart the SQL server service because users begin complaining they can't connect.
1) I created an operator - me.
2) I created a job which runs a query.
INSERT INTO Sessions_alert
SELECT host_name, program_name, login_name, count(c.session_id ) num_sessions, getdate()
FROM sys.dm_exec_connections c JOIN sys.dm_exec_sessions s on c.session_id = s.session_id
GROUP BY host_name,program_name,login_name ORDER BY 4 DESC
3) I created an alert - included the job from above. Type performance condition alert. Object - SQL Server General Statistics. Counter - User connections.Alert if counter rises above 50. Just testing. I really want to know when it gets past 2500.
4) I've set the alert to email and delay is between responses 2 minutes.
It history tables says number of occurences is 18964. However, I don't receive an email.
Shouldn't the alert send an email? Do I need to include email code in the job?
Hi All!
I was thinking of creating a custom generated IDs for my table. I would like the ID to be something like "HR001" or "IT001", the two letter prefix would indicate the dept it belongs to. Initially I thought of having a table that will hold all the seed values for the IDs but I realize that this could have some concurrency problems if there will be a multiple number of users are creating a record at the same time. So now I have totally no idea on how to deal with the concurrency problem.
I will not be using this as a primary key because I already have the Identity field to be my primary key, though of course this field would definitely be unique. I will just be using this to display in the UI.
Any inputs would be greatly appreciated.
Thanks!
I have a balance sheet report developed and working. What I need to do is add the ability to click (or double-click) on a number and have a separate report (drill-down) open with the detail that makes up that number. I've researched drill-through reports, sub reports, etc. but can't find a way to do what I want.
I'm now thinking that I need to add custom code in the report properties, code window to do this. I'm hoping that there is a VB method I can use to call this report and pass a parameter. Pseudo-code for this function would look something like this: RunReport("DrillDownReport", "Parameter").
This seems like it should be pretty easy but I don't know what the function is in VB for running an SSRS report (if there is one).
Is this possible or am I barking up the wrong code tree?
I need creating a store procedure which generates custom IDs for each asset. I am programming for Fixed Assets in VB6 with SQL Server 2005. For example, when a new Asset is added ,I need to auto generate the ID based on existing IDs. New ID should not exist in tblAssets table.
Table Name : tblAssets
Fields : AssetID > Int,Primary Key,this is internal ID (identity seed)
AssetExtID >nvarchar(50),this is external ID, need to generate/user entered.
Below is the example of data in tblAssets :
AssetID AssetExtID ProjectID ItemName Qty UOM UnitCost .....
1 PROSP-00001 PROSPERITY SPLIT-AC 2 NOS $200
2 PROSP-00002 PROSPERITY LAPTOP 1 NOS $500
3 UNIII-00001 UNION III LAPTOP 5 NOS $400
4 UNIII-00002 UNION III RECEIVER 2 NOS $312
The AssetExtID depends on the ProjectID which is in tblProjects.
I will take care of the first 5 characters to generate. But the number part I need to generate by checking existing data. The AssetExtID should not be duplicate. Its unique for each asset.
I am looking for some examples of code for an appliction which will let run RS reports from a custom application. The user will choose the reports from a menu structure
We have an exiting application which we use to run crystal reports which we are going to be migrating to RS. We use custom web controls to capture the required parameter values for the reports, and hope to reuse the same controls (with minimal rework) to capture the parameters values for the RS reports.
What I am after is some code which will
a) let me run the report and display it. I don't want to display the report parameters in the URL etc as we generate some report parameter values based upon the user's ID and do NOT want them displayed back to the users
b) export the report to PDF, CSV etc depending upon the option chosen by the user.
The application will be developed in VS 2005 - VB. Does any one know of some links to some sample code
Hi All,
I am using a Data Flow task which copies data from an Excel Source to a SQL Database Table Destination. From 15 columns I require only 10 columns to be imported to the DB Table. So I have mapped those colums. In SQL DB there is a colum called say X, whose value should be the "Remedy" for all the columns which are imported. Is there any task that can achieve it.
Please Help.
Hi,
I've created a Custom Data Flow Component and added some Custom Properties.
I want the user to set the contents using an expression. I did some research and come up with the folowing:
Code Snippet
IDTSCustomProperty90 SourceTableProperty = ComponentMetaData.CustomPropertyCollection.New();
SourceTableProperty.ExpressionType = DTSCustomPropertyExpressionType.CPET_NOTIFY;
SourceTableProperty.Name = "SourceTable";
But it doesn't work, if I enter @[System:ackageName] in the field. It comes out "@[System:ackageName]" instead of the actual package name.
I'm also unable to find how I can tell the designer to show the Expression editor. I would like to see the elipses (...) next to my field.
Any help would be greatly appreciated!
Thank you
Hi,
I'm trying to enable Expression for a custom property in my custom data flow component.
Here is the code I wrote to declare the custom property:
public override void ProvideComponentProperties()
{
ComponentMetaData.RuntimeConnectionCollection.RemoveAll();
RemoveAllInputsOutputsAndCustomProperties();
IDTSCustomProperty90 prop = ComponentMetaData.CustomPropertyCollection.New();
prop.Name = "MyProperty";
prop.Description = "My property description";
prop.Value = string.Empty;
prop.ExpressionType = DTSCustomPropertyExpressionType.CPET_NOTIFY;
...
}
In design mode, I can assign an expression to my custom property, but it get evaluated in design mode and not in runtime
Here is my expression (a file name based on a date contained in a user variable):
"DB" + (DT_WSTR, 4)YEAR( @[User::varCurrentDate] ) + RIGHT( "0" + (DT_WSTR, 2)MONTH( @[User::varCurrentDate] ), 2 ) + "\" + (DT_WSTR, 4)YEAR( @[User::varCurrentDate] ) + RIGHT( "0" + (DT_WSTR, 2)MONTH( @[User::varCurrentDate] ), 2 ) + ".VER"
@[User::varCurrentDate] is a DateTime variable and is assign to 0 at design time
So the expression is evaluated as: "DB189912189912.VER".
My package contains 2 data flow.
At runtime,
The first one is responsible to set a valid date in @[User::varCurrentDate] variable. (the date is 2007-01-15)
The second one contains my custom data flow component with my custom property that was set to an expression at design time
When my component get executed, my custom property value is still "DB189912189912.VER" and I expected "DB200701200701.VER"
Any idea ?
I want to select weekly data from daily data.lets say Today's date-10/23/2014(Thursday) My data is in date time but i want to see only date
output should be from last week Thursday to this week Wednesday. similar for previous dates
Weekly sum(profit)
10/16 - 10/21 - $1000
10/9 - 10/15 - $4100
10/2 - 10/8 - $ 8038
--
--
--
I have a custom assembly that I'm trying to use with my resport in Reporting Services in SQL Server 2008.
I keep consistently getting a "Request for the permission of type 'System.Data.SqlClient.SqlClientPermission, System.Data,Version=2.0.0.0, Culture=neutral,PublicKeyToken=b77a5c561934e089' failed" error. Even in preview mode.
- I added <Assembly: AllowPartiallyTrustedCallers()> attribute to the assembly
- I'm asserting the permission in the routine that is doing the database call with the follwing lines:
Dim permission As SqlClient.SqlClientPermission = New SqlClient.SqlClientPermission(PermissionState.Unrestricted)
permission.Assert()
- I went in .net 2.0 Framework configuration and made the assembly fully trusted
- I added the approprtiate codegroup to the rssrvpolicy.config file
- I tried the same assembly on another PC running SQL server reporting services 2005, its working just fine.
what is it that I am missing?
I am working through this example:
http://www.devx.com/dbzone/Article/31336/0/page/5
I have following all the steps but I am getting this error:
---------------------------
Microsoft Visual Studio
---------------------------
The designer extension DATASET could not be loaded. Check the configuration file RSReportDesigner.config.
---------------------------
OK
---------------------------
I have doubled checked the edits I made to that config file. Rebooted, relaunched, etc. No matter what, I cannot get past that error message.
I am using a Win XP machine with SQL Server 2005 Express Edition with Advanced Services SP2. Just curious, but does express version support CDEs?
Thanks,
Dan
I need to create a function that replaces the data in a column with an 'X' based on the LEN of the data in the column. I created one that does a replacement, but it fills the column based on the max data length, and not the current length of the string or integer. An example of what I'm trying to accomplish.
Original data in a varchar(30) column:
thisisavalue
thisisanothervalue
thisisanothervalueagain
shortval
replaced with
xxxxxxxxxx
xxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxx
xxxxxxx
My current function is replacing the data like this:
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
is it possible to make a custom query to fetch and update data from sql server 2005 in SharePoint designer
i make a new data source library and use a custom query to get data but don€™t know how to configure update custom command
can any buddy help me out
I have written CDE to retrieve and process data before generating the report. It works very well inside VS.Net. However, when deployed to the report server I get the following error
An error has occurred during report processing. Cannot create a connection to data source 'CallsTaken'. Request for the permission of type 'System.Security.Permissions.SecurityPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.
I have added appropriate CAS entry in rssrvpolicy.config file as following
<CodeGroup
class="UnionCodeGroup"
version="1"
Name="CustomDataExtensionCodeGroup"
Description="Code group for the Custom Data Extension"
PermissionSetName="FullTrust">
<IMembershipCondition
class="UrlMembershipCondition"
version="1"
Url="C:Program FilesMicrosoft SQL ServerMSSQL.2Reporting ServicesReportServerinCSS.CustomDataExtension.dll"/>
</CodeGroup>
I have added appropriate entry into rsreportserver.config for data extension as
<Extension Name="Dataset Extension" Type="CSS.RS.Extensibility.CustomDataExtension.DsConnectionWrapper, CSS.CustomDataExtension"/>
Even then I continue to get this error. Any help? Is there anything else I am missing? I am using VS .Net 2005, SRSS 2005 on W23 machine.
I have been stuck here for a while now. Help is appreciated.
Thanks
Hi , all Microsoft BI experts here,
Thanks for your kind attention.
I am having a question as stated in the subject title, yes, when we want to deploy scorecards to reporting services, as the prerequisite, how can we install and register the scorecard custom data processing extension with the Microsoft reporting services server?
I am looking forward to hearing from you shortly and thank you again.
With best regards,
Yours sincerely,
1) I can't get the 'copy database' function to work from SQL Server 2008 to SQL Server 2005. I connect ok. Everything goes to the last step and then it fails.2) I cant get a SQL server 2008 backup to restore on SQL SEerver 2005 either.
The only way I know that works is to script the creation of all tables then export and import. This does work.
How can I get the 'entire' database, structure and data, from 2008 to 2005?
ThanksSQL newbie.
Production and development servers are on different domains and they do not trust each other. How do I import data from the table t1 from a database db1 in production and load it into table t1 inside database db1 in development?
View 3 Replies View RelatedI have the linked server connection created and works perfectly well. I mean I am able to see the tables while I am on my database.
how do I write a SQL statement to reference the linked server ?
I tried the following:
Select top 100 * from casmpogdbspr1.MPOG_Collations.dbo.AsaClass_Cleaned
Then I get the error message....
Msg 7314, Level 16, State 1, Line 1
The OLE DB provider "SQLNCLI10" for linked server "casmpogdbspr1" does not contain the table ""MPOG_Collations"."dbo"."AsaClass_Cleaned"". The table either does not exist or the current user does not have permissions on that table.
I have below two table and i want ot generate the xml out put as mentioned below.
create table Candidates(eno int,ResumeText varchar(30), Email varchar(30),Active varchar(30),postalcode varchar(30),country int)
insert into Candidates
select 1,'Test','ee@ee.com','Active yes','888888',2
union all
select 2,'Test','ee@ee.com','Active yes','888888',6
[code]...
preparation of a query to get the data from xml which has xmlns in first few lines.
Here I am attaching the xml file(not able to attach ".xml" file, so converted it to ".txt" ).