Does anyone know of a utility that would perform lines of code, comments and blank lines for all SP's in a given database? One of the guys on the development team was telling me that his management team wants this information. Right now they guess-timate these values but I was wondering if anyone knew of a utility to get accurate counts.
Personally I think management is off their rocker but this isn't my battle to worry about :)
Hi,
I need to run a package wich is located on the file system of a PC. The problem is that the PC from where I'm running a bat file to call the package from the other PC where the package is, doesn't have SSIS.
So I was wondering if this will work. I believe that I will have to copy the DTExec.exe file to the PC where my bat file calls to the dtsx in the other PC. I am right?
My run.bat will have this line: \PCshared_folderpackage.dtsx.
Will this work?
Thanks
how can i create a folder in the integration services-stored packages-MSDB folder using dtutil command line utility.
I could create a folder within a folder inside MSDB using
dtutil /FC SQ; est;temp
but not in the MSDB folder.
Thanks &Regards,
Vivek S
Hi,I have dumped a very large database from mysql (using mysqldump program)as a raw sql file. The reason was, convert this database to a MSSQLdatabase. Since mysqldump creates the file as raw sql file with thedatabase-table structures and the data in it, I thought using OSQL commandline utilities should work to out this whole database in MSSQL server.I have run this command from command line:osql -u sa -i mysqldump.sqlIt is going since yesterday. It has been almost 36 hours that it'sstarted. And in the mssql server, I see no database created yet. On thescreen of the command line, I see bunch of numbers are going in order. Iassume they are row numbers of the tables processed. But, if it is doing it,then where is it saving all this data ? I have checked the tempdb, pub db,other dbs, and I see no tables related to the database I am inserting. Willit populate it at the and of the job ? Or, am I doing something wrong here?Regards.Murtix.
View 2 Replies View RelatedReinstalling SQL Express did the trick
Thought this might help others..
I've exported surface area configuration settings from a server and tried importing them onto another server. The remote connection settings are imported but the SQLBrowser service remains Disabled. Am I doing something wrong?
The command line command I ran (in the C:Program FilesMicrosoft SQL Server90Shared directory) is "sac.exe in sacconfig.xml".
The following is my config file:
<?xml version="1.0" encoding="utf-8"?>
<CommandArguments xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<Components>
<anyType xsi:type="Component">
<Type>SqlServer</Type>
<InternalType>DatabaseEngine</InternalType>
<Instances>
<anyType xsi:type="Instance">
<Name>SQLEXPRESS</Name>
<Service>
<Name>MSSQL$SQLEXPRESS</Name>
<StartMode>Auto</StartMode>
<ServiceState>Running</ServiceState>
</Service>
<Features>
<anyType xsi:type="Pair">
<Key>AdhocRemoteQueries</Key>
<Value xsi:type="xsd:int">0</Value>
</anyType>
<anyType xsi:type="Pair">
<Key>CLRIntegration</Key>
<Value xsi:type="xsd:int">0</Value>
</anyType>
<anyType xsi:type="Pair">
<Key>DedicatedAdministrator</Key>
<Value xsi:type="xsd:int">0</Value>
</anyType>
<anyType xsi:type="Pair">
<Key>DatabaseMail</Key>
<Value xsi:type="xsd:int">0</Value>
</anyType>
<anyType xsi:type="Pair">
<Key>HTTPAccess</Key>
<Value xsi:type="ArrayOfAnyType" />
</anyType>
<anyType xsi:type="Pair">
<Key>OLEAutomation</Key>
<Value xsi:type="xsd:int">0</Value>
</anyType>
<anyType xsi:type="Pair">
<Key>ServiceBroker</Key>
<Value xsi:type="ArrayOfAnyType" />
</anyType>
<anyType xsi:type="Pair">
<Key>SQLMail</Key>
<Value xsi:type="xsd:int">0</Value>
</anyType>
<anyType xsi:type="Pair">
<Key>WebAssistant</Key>
<Value xsi:type="xsd:int">0</Value>
</anyType>
<anyType xsi:type="Pair">
<Key>XP_cmdshell</Key>
<Value xsi:type="xsd:int">0</Value>
</anyType>
</Features>
<Protocol>
<anyType xsi:type="Pair">
<Key>NamedPipes</Key>
<Value xsi:type="xsd:boolean">true</Value>
</anyType>
<anyType xsi:type="Pair">
<Key>TCPIP</Key>
<Value xsi:type="xsd:boolean">true</Value>
</anyType>
</Protocol>
</anyType>
</Instances>
</anyType>
<anyType xsi:type="Component">
<Type>SqlBrowser</Type>
<InternalType>SqlBrowserService</InternalType>
<Service>
<Name>SQLBrowser</Name>
<StartMode>Auto</StartMode>
<ServiceState>Running</ServiceState>
</Service>
</anyType>
</Components>
<Version>9.0.242.0</Version>
</CommandArguments>
Hi,
Can smeone help me with finding out the line count for all the sp's in a database?I tried a lot but could not find the result for this..Please someone help.
Thanks
CREATE TABLE#Info
(
DbName SYSNAME,
ROUTINE_TYPE SYSNAME,
ROUTINE_NAME SYSNAME,
Lines INT
)
EXECsp_msforeachdb'
INSERT#Info
(
DbName,
ROUTINE_TYPE,
ROUTINE_NAME,
Lines
)
SELECT''?'',
d.ROUTINE_TYPE,
d.ROUTINE_NAME,
SUM(CASE WHEN d.c10 < d.c13 THEN d.c13 ELSE d.c10 END)
FROM(
SELECTROUTINE_TYPE,
ROUTINE_NAME,
1 + LEN(ROUTINE_DEFINITION) - LEN(REPLACE(ROUTINE_DEFINITION, CHAR(13), '''')) AS c13,
1 + LEN(ROUTINE_DEFINITION) - LEN(REPLACE(ROUTINE_DEFINITION, CHAR(10), '''')) AS c10
FROM.INFORMATION_SCHEMA.ROUTINES
) AS d
GROUP BYd.ROUTINE_TYPE,
d.ROUTINE_NAME
'
SELECTDbName,
ROUTINE_TYPE,
ROUTINE_NAME,
Lines
FROM#Info
ORDER BYDbName,
ROUTINE_TYPE,
ROUTINE_NAME
DROP TABLE#Info
E 12°55'05.25"
N 56°04'39.16"
Hi,
for some AP issue, the file I upload must be without the line feed/carriage return in the last line.
for example:
original fixed-length file (exported from SSIS)
line NO DATA
1 AA123456 50 60
2 BB123456 30 40
3 CC123456 80 90
4 <-- with line feed/carriage return in the last line
The file format that AP request. The file only has 3 records, so it should end in the third line.
line NO DATA
1 AA123456 50 60
2 BB123456 30 40
3 CC123456 80 90
Should I use script component to do it ? I am new for VB . Anyone would help me ?
Thank you all.
I need the Trend line for the following data in Line chart they are the following data. The following are the graph are my output and i need the trend line for these Key_gap value.
This is the link [URL] ....
I need the same trend line for the Bar-Chart in SSRS 2005.
I hope I'm posting this in the correct forum (forgive me if I'm not) since I'm not sure if this is an issue with inserting an item into a db or the processing of what I get out of it. I wrote a basic commenting system in which someone my post a comment about something written on the site. I wanted to keep it very simple, but I at least want the ability for a user to have newlines in their comment without having to hardcode a <br /> or something like that. Is there a way for me to detect a newline if someone, for example, is going to their next paragraph?
Let me know if you need a better explanation.
Thanks in advance!
G'day everyoneThat's a space between the ticks.It's all part of a longer script but seeing as the failure occurs online 1if exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[config]') and OBJECTPROPERTY(id, N'IsUserTable') =1)drop table [dbo].[config]GOThat's three lines only. Does it matter that they're in Unicode?Any ideas?Kind regards,Bruce M. AxtensSoftware EngineerStrapper Technologies
View 3 Replies View RelatedWe have a line graph which plots the actual data points (x,y), everything is working fine with this graph. Now we need to add a trend line to this existing graph after going thro. the articles we came to know that there is no direct option in SSRS to draw a trend line. So we need to calculate the trend values ourselves which we need to plot as atrend line. This trend line is similar to the trend line which comes in Excel chart, do anyone know how to calculate the trend values from the actual data points. We got through several formulas, but were not clear, have anyone tried out exactly the same, if so please help us out by providing an example to calculate the trend values.
View 1 Replies View RelatedI have a line graph which shows positive and negative values. Is it possible to have the line one color when its negative and another when its positive?
kam
HEllo can anybody tell me how to monitor a long store procedure
line by line. Also how to put progress bar in it to tell user how
much is done.
Sabih.
Hi,
When creating a line chart, I would like to be able to show Markers for the data points, but no line between these points (as you can do in excel).
I have set the line setting to none (for the lines of interest), however the lines still show.
Is this a bug, or am i missing something obvious settings-wise?
Cheers,
M
With the function below, I receive this error:Error:Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 0.Function:Public Shared Function DeleteMesssages(ByVal UserID As String, ByVal MessageIDs As List(Of String)) As Boolean Dim bSuccess As Boolean Dim MyConnection As SqlConnection = GetConnection() Dim cmd As New SqlCommand("", MyConnection) Dim i As Integer Dim fBeginTransCalled As Boolean = False
'messagetype 1 =internal messages Try ' ' Start transaction ' MyConnection.Open() cmd.CommandText = "BEGIN TRANSACTION" cmd.ExecuteNonQuery() fBeginTransCalled = True Dim obj As Object For i = 0 To MessageIDs.Count - 1 bSuccess = False 'delete userid-message reference cmd.CommandText = "DELETE FROM tblUsersAndMessages WHERE MessageID=@MessageID AND UserID=@UserID" cmd.Parameters.Add(New SqlParameter("@UserID", UserID)) cmd.Parameters.Add(New SqlParameter("@MessageID", MessageIDs(i).ToString)) cmd.ExecuteNonQuery() 'then delete the message itself if no other user has a reference cmd.CommandText = "SELECT COUNT(*) FROM tblUsersAndMessages WHERE MessageID=@MessageID1" cmd.Parameters.Add(New SqlParameter("@MessageID1", MessageIDs(i).ToString)) obj = cmd.ExecuteScalar If ((Not (obj) Is Nothing) _ AndAlso ((TypeOf (obj) Is Integer) _ AndAlso (CType(obj, Integer) > 0))) Then 'more references exist so do not delete message Else 'this is the only reference to the message so delete it permanently cmd.CommandText = "DELETE FROM tblMessages WHERE MessageID=@MessageID2" cmd.Parameters.Add(New SqlParameter("@MessageID2", MessageIDs(i).ToString)) cmd.ExecuteNonQuery() End If Next i
' ' End transaction ' cmd.CommandText = "COMMIT TRANSACTION" cmd.ExecuteNonQuery() bSuccess = True fBeginTransCalled = False Catch ex As Exception 'LOG ERROR GlobalFunctions.ReportError("MessageDAL:DeleteMessages", ex.Message) Finally If fBeginTransCalled Then Try cmd = New SqlCommand("ROLLBACK TRANSACTION", MyConnection) cmd.ExecuteNonQuery() Catch e As System.Exception End Try End If MyConnection.Close() End Try Return bSuccess End Function
HiIt's my stored procedure 1 CREATE PROCEDURE singleSearch2
2 @SQ nvarchar(30),
3 @pType nvarchar(11),
4 @pCol nvarchar(11)
5 AS
6 BEGIN
7 DECLARE @SQL NVarChar(1000)
8 SELECT @SQL='SELECT *,'
9 SELECT @SQL=@SQL+' CASE RTRIM(LTRIM(op))'
10 SELECT @SQL=@SQL+' WHEN '+CHAR(39)+'e'+CHAR(39)+' THEN '+CHAR(39)+'اجاره'+ CHAR(39)
11 SELECT @SQL=@SQL+' WHEN '+CHAR(39)+'r'+CHAR(39)+' THEN '+CHAR(39)+'رهن'+CHAR(39)
12 SELECT @SQL=@SQL+' WHEN '+CHAR(39)+'f'+ CHAR(39)+' THEN '+ CHAR(39) +' Ù?روش '+CHAR(39)
13 SELECT @SQL=@SQL+' WHEN '+CHAR(39)+'e r'+CHAR(39)+' THEN '+CHAR(39)+ 'اجاره - رهن '+CHAR(39)
14 SELECT @SQL=@SQL+' WHEN '+CHAR(39)+'e f'+CHAR(39)+' THEN '+CHAR(39)+'اجاره - Ù?روش '+CHAR(39)
15 SELECT @SQL=@SQL+' WHEN '+CHAR(39)+'r f'+CHAR(39)+' THEN '+CHAR(39)+' رهن - Ù?روش '+CHAR(39)
16 SELECT @SQL=@SQL+' WHEN '+CHAR(39)+'e r f'+CHAR(39)+' THEN '+CHAR(39)+'اجاره - رهن - Ù?روش'+CHAR(39)
17 SELECT @SQL=@SQL+' ELSE op END AS xop, CASE LTRIM(RTRIM(type)) '
18 SELECT @SQL=@SQL+' WHEN '+CHAR(39)+'z -'+CHAR(39)+' THEN '+CHAR(39)+'زمین'+CHAR(39)
19 SELECT @SQL=@SQL+' WHEN '+CHAR(39)+'m -'+CHAR(39)+' THEN '+CHAR(39)+'مسکونی'+CHAR(39)
20 SELECT @SQL=@SQL+' WHEN '+CHAR(39)+'t -'+CHAR(39)+' THEN '+CHAR(39)+'تجاری'+CHAR(39)
21 SELECT @SQL=@SQL+' ELSE [type] END AS [xtype] FROM [data] '
22 SELECT @SQL=@SQL+' WHERE ([type] LIKE %'+CHAR(39)+@pType+CHAR(39)+'%) AND ('+@pCol+' LIKE %'+CHAR(39)+@SQ+CHAR(39)+'%)'
23 Exec (@SQL)
24 END
25 GO
and i face this error: Line 1: Incorrect syntax near '-'.
Hi
Anyone have any idea how to make a line style dashed or dotted in a line chart please?
If I change the series style to dashed or dotted it still appears as a solid line, yet the legend displays a dashed or dotted line....
Thanks
My question is about coding style for specifying constraints when creating tables.
Two styles for defining constraints:
In-line:
CREATE TABLE Fruit
(
FruitID INT IDENTITY(1,1)
CONSTRAINT PK_fruit PRIMARY KEY CLUSTERED,
FruitName NVARCHAR(50),
FruitTypeID INT
CONSTRAINT FK_fruit_fruit_types FOREIGN KEY
REFERENCES FruitTypes (FruitTypeID) ON UPDATE CASCADE,
DateCreated DATETIME DEFAULT GETDATE()
)
Out-of-line:
CREATE TABLE Fruit
(
FruitID INT,
FruitName NVARCHAR(50),
FruitTypeID INT,
DateCreated DATETIME
)
ALTER TABLE Fruit ALTER COLUMN FruitID INT NOT NULL
ALTER TABLE Fruit ADD
CONSTRAINT PK_fruit PRIMARY KEY CLUSTERED (FruitID),
CONSTRAINT FK_fruit_fruit_types FOREIGN KEY (FruitTypeID)
REFERENCES FruitTypes (FruitTypeID),
CONSTRAINT DF_fruit_date_created DEFAULT
GETDATE() FOR DateCreated
Which style do you prefer and why?
Hi
If i use this code i cant get the data showed, it show nothing."SELECT COUNT(DogImageDate) AS Amount, DogImageDate, DogImageID FROM EnggaardImages WHERE DogImageDate NOT LIKE (SELECT TOP 1 DogImageDate FROM EnggaardImages ORDER BY DogImageDate DESC;) GROUP BY DogImageDate ORDER BY DogImageDate DESC;"
But if i use this code i get data showed"SELECT COUNT(DogImageDate) AS Amount, DogImageDate, DogImageID FROM EnggaardImages GROUP BY DogImageDate ORDER BY DogImageDate DESC;"
Then i get Images(6)Images(1)Images(1)
But i dont want the first to be showed, therefor i use the Select TOP 1, so i get a look like this
Images(1)Images(1)
But i cant get it to work.
below data,
Countery
parentid
CustomerSkId
sales
A
29097
29097
10
A
29465
29465
30
A
30492
30492
40
[code]....
Â
Output
Countery
parentCount
A
8
B
3
c
3
in my count function,my code look like,
 set buyerset as exists(dimcustomer.leval02.allmembers,custoertypeisRetailers,"Sales")
set saleset(buyerset)
set custdimensionfilter as {custdimensionmemb1,custdimensionmemb2,custdimensionmemb3,custdimensionmemb4}
set finalset as exists(salest,custdimensionfilter,"Sales")
Set ProdIP as dimproduct.dimproduct.prod1
set Othersset as (cyears,ProdIP)
(exists(([FINALSET],Othersset,dimension2.dimension2.item3),[DimCustomerBuyer].[ParentPostalCode].currentmember, "factsales")).count
it will take 12 to 15 min to execute.
I am trying to get count on a varchar field, but it is not giving me distinct count. How can I do that? This is what I have....
Select Distinct
sum(isnull(cast([Total Count] as float),0))
from T_Status_Report
where Type = 'LastMonth' and OrderVal = '1'
Hello ,
I want to know how can i can use the bcp coomand to import a text file into a SQL database table . The text file contains info. like servername , date , time , logininfo and description .
The problem is i cannot import that file into the table through data import wizard . The wizard is not able to separate the fields properly and the import takes place with mixed up data in different columns . The separator in the text file is colon . I even tried fixed length with manual column separation .
The text in the text file are not aligned properly as these are log files generated by the system which i am trying to import in a table .
So how can use the bcp command to specify the column length properly and seperate the fields uniformly so that the data gets imported neatly .
Any thoughts how could the command look like ?
Thank you very much .
Anita.
Hi,
I used bcp utility to import and export data between databases. These exported data is it read only? If so how can I change the parameter so that we could edit as well.
Any suggestions will be helpful. Thank you!
I'm trying to use the bcp command to bcp out a single table in a database, what is the best way to do this in the query analyzer? Thanks in advance.
View 1 Replies View RelatedHi all,
I want to use the BCP utility to import data from a .dat file into my database. The .dat file contains a table called xv_Appointments containing the following fields:
AppointmentKey
SurgerySlotKey
PatientKey
Cancelled
Continuation
Deleted
TimeArrived
I would like to import only two of these fields into a table called tbl_Appointments e.g.
AppointmentKey
TimeArrived
I can't seem to get the BCP util to do this. It only works if I import all of the fields from xv_Appointments. Does anyone know if this is possible?
Thanks
In the process of exporting data from SQL data file to text file through BCP utility I am not getting the Column names.How can I get the column names through BCP utility?
I used this script
exec master..xp_cmdShell 'bcp "select * from regulator.dbo.TEMPTBLBRANCHNOTUPLOAD" QueryOUT \indiadbftprootCLIENT_BRANCH_UPLOADranchnotup loaded.csv -S indiadb -U sa -P sasocrates -k -r -c -t "," -q'
Hello,
I am using the DTS utility available with Enterprise Manager in MSSQL server 2000. I can transfer the tables and views without any issues but when i try to transfer the stored procedures it always gives an error. I have tried transferring individual objects too but it does'nt works. The error given is "the user <username> cannot perform the following action". Any help is appreciated.
Thanks in Advance
Hello, I need to create a stored procedure that creates a csv file. this is the code I have so far.
Script is like
CREATE PROCEDURE dbo.GetQuestionInfoCSV AS
BEGIN
declare @sql varchar(8000)
SET @sql = 'bcp "SELECT * FROM dbo.ISO_table" queryout C:GetCSV.CSV -c -t, -T -S MANCHESTSQLEXPRESS'
print @sql
EXEC master..xp_cmdshell @sql
END
and I get these errors on execution
SQLState = 42S02, NativeError = 208
Error = [Microsoft][SQL Native Client][SQL Server]Invalid object name 'dbo.ISO_table'.
SQLState = 42000, NativeError = 8180
Error = [Microsoft][SQL Native Client][SQL Server]Statement(s) could not be prepared.
NULL
Any help would much appreciated.
Greetings,Just wanted to know if there is any parameter in BCPutility that can ignore triggers, indexes and constraint defined for atable?any help will be greatly appreciatedTIA
View 2 Replies View RelatedI'm using SQL 2005 to export data. I would like to use the bcp utilityto export data to an Excel file.I have to generate quite a few files and the names are dynamic. Theideal would be to loop through records in a stored procedure to createa file name to use in the bcp. My question is how can I use the bcpfrom a stored procedure? I know how to run it from the command prompt.Is there a way to control the command prompt from a stored procedure?Thanks all
View 2 Replies View RelatedHello everyone,
I have a question about dtsrun hoping someone have an answer. Does anyone know if there is a switch that I can turn onoff so that it will suppress the output result when I execute the dtsrun ulitity?
This is what I ran:
master..xp_cmdshell 'dtsrun /Sclancy /E /N ExpDispute'
I get the following result back after it's done:
output
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
DTSRun: Loading...DTSRun:Executing...DTSRun OnStart: Copy Data from Results to [Transplus_TestArchive].[dbo].[SG_DISPUTE] Step DTSRun OnProcess: Copy Data
from Results to [Transplus_TestArchive].[dbo].[SG_DISPUTE] Step; 14 Rows have been transformed or copied.; PercentComplete = 0; ProgressCount = 14 DTSRun OnFinish: Copy Data from Results to [Transplus_TestArchive].[dbo].[SG_DISPUTE] Step DTSRun:
Package execution complete.
(6 row(s) affected)
Thank you in advance for any help.
Mai Nguyen